Home » Open Source » Programming Interfaces » insert into db via perl dbi (11gr2)
insert into db via perl dbi [message #640993] Sat, 08 August 2015 09:37 Go to next message
hudo
Messages: 165
Registered: May 2004
Senior Member
Hello,
I'm having problems inserting values into db via perl dbi. My NLS_LANGUAGE is german.
The table column is NUMBER(8,2), the delivered data is e.g 1234,56 or 0,00
My code is like
my $stora_insert_article = $dbora->prepare(
	q{INSERT INTO ARTIKEL (
						
                                  PF0
                                , PF1						
						) 
				VALUES ( 
				TO_NUMBER(?, '9999D99' , 'NLS_NUMERIC_CHARACTERS=,.')	 
				,TO_NUMBER(?, '9999D99' , 'NLS_NUMERIC_CHARACTERS=,.') 
						)
	});



The error message is like
Quote:

DBD::Oracle::st execute failed: ORA-01722: invalid number (DBD ERROR: error poss
Re: insert into db via perl dbi [message #640994 is a reply to message #640993] Sat, 08 August 2015 09:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
does INSERT statement succeed when issued from sqlplus?
Re: insert into db via perl dbi [message #640995 is a reply to message #640994] Sat, 08 August 2015 10:06 Go to previous messageGo to next message
hudo
Messages: 165
Registered: May 2004
Senior Member
The following statement succeed from SQLPlus
SQL> INSERT INTO ARTIKEL (MOTOR_NAME , ARTIKEL_VK0) VALUES ( 'hello' , TO_NUMBER
( '1234,56' , '9999D99') );

1 row created.


Same syntax from within perl script does not succeed.

[Updated on: Sat, 08 August 2015 10:06]

Report message to a moderator

Re: insert into db via perl dbi [message #640996 is a reply to message #640993] Sat, 08 August 2015 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The error can't come for the prepare statement.
Post the complete code so we can reproduce what you get.

Re: insert into db via perl dbi [message #640997 is a reply to message #640995] Sat, 08 August 2015 10:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> TO_NUMBER( '1234,56' , '9999D99') );
above is different from below
>TO_NUMBER(?, '9999D99' , 'NLS_NUMERIC_CHARACTERS=,.')
Re: insert into db via perl dbi [message #640998 is a reply to message #640997] Sat, 08 August 2015 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also are you sure you pass a string as 2nd parameter and not a number?

Re: insert into db via perl dbi [message #640999 is a reply to message #640997] Sat, 08 August 2015 10:43 Go to previous messageGo to next message
hudo
Messages: 165
Registered: May 2004
Senior Member
Quote:

#### INSERT ######################################
my $stora_insert_article = $dbora->prepare(
q{INSERT INTO ARTIKEL (ARTIKEL_NR
, MOTOR_NAME
, ARTIKEL_VK0
, ARTIKEL_VK1
)
VALUES ( ?
,?
,TO_NUMBER(?, '9999D99' )
,TO_NUMBER(?, '9999D99' )
)
});


##### INSERT INTO DB #########################
for ( $lauf = 0; $lauf <= $anzahl; $lauf++ ) {

$stora_insert_article->execute( "$article[$lauf]"
,"$mname[$lauf]"
,"$vk0[$lauf]"
,"$vk1[$lauf]"
);


} # for insert
$stora_insert_article->finish();



This is the input csv data with | as separator
048.0544|ABC3456-123|2074,00|1383,00
049.0676|ABC1234-567|0,00|0,00


And this is the error I get
DBD::Oracle::st execute failed: ORA-01722: invalid number (DBD ERROR: error possibly near <*> indicator at char 187 in 'INSERT INTO ARTIKEL (ARTIKEL_NR
                                                , MOTOR_NAME
                                                ,ARTIKEL_VK0
                                                , ARTIKEL_VK1

                                                )
                                VALUES ( :p1
                                                ,:p2
                                                ,TO_NUMBER(:p3, '9999D99' )

                                                ,TO_NUMBER(:<*>p4, '9999D99' )
                                                )
        ') [for Statement "INSERT INTO ARTIKEL (ARTIKEL_NR
                                                , MOTOR_NAME
                                                ,ARTIKEL_VK0
                                                , ARTIKEL_VK1

                                                )
                                VALUES ( ?
                                                ,?
                                                ,TO_NUMBER(?, '9999D99' )

                                                ,TO_NUMBER(?, '9999D99' )
                                                )
        " with ParamValues: :p1='048.0544', :p2='ABC3456-123', :p3='2074,00', :p4='1383,00
'] at erpexport1.pl line 349, <STDIN> line 11.



Re: insert into db via perl dbi [message #641000 is a reply to message #640999] Sat, 08 August 2015 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
TO_NUMBER(:<*>p4, '9999D99' )


Your statement does not specify the numeric characters, so it is most likely your session one is not the ones you think.
Retry with the actual statement you first posted.

Re: insert into db via perl dbi [message #641001 is a reply to message #640999] Sat, 08 August 2015 10:48 Go to previous messageGo to next message
hudo
Messages: 165
Registered: May 2004
Senior Member
Ahh, sorry, I got it. It was the newline contained in vk1.
Re: insert into db via perl dbi [message #641002 is a reply to message #641001] Sat, 08 August 2015 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Indeed, I thought this was a display issue.
You should always "chomp" the lines you read from a file. Smile

Re: insert into db via perl dbi [message #641003 is a reply to message #641002] Sat, 08 August 2015 10:56 Go to previous messageGo to next message
hudo
Messages: 165
Registered: May 2004
Senior Member
Where did you saw the "display error" ??

I checked it now, cause I changed the order of the elements to insert ARTIKEL_NR , MOTOR_NAME , ARTIKEL_VK1 , ARTIKEL_VK0
and the error remained at element ARTIKEL_VK1.
Re: insert into db via perl dbi [message #641004 is a reply to message #641003] Sat, 08 August 2015 10:59 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No error. In your post you have:

Quote:
with ParamValues: :p1='048.0544', :p2='ABC3456-123', :p3='2074,00', :p4='1383,00
'] at erpexport1.pl line 349, <STDIN> line 11.

I thought your line was fold when you copy and paste it, now I know as you said the new line is in the value itself.

Previous Topic: Locked individual records only for individual users on MS ACCESS
Next Topic: How to get SELECT columns size with Perl DBI+DBD::Oracle?
Goto Forum:
  


Current Time: Thu Mar 28 09:50:26 CDT 2024