OraFAQ Forum: Programming Interfaces » insert into db via perl dbi

Home » Open Source » Programming Interfaces » insert into db via perl dbi (11gr2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

insert into db via perl dbi [message #640993]

Sat, 08 August 2015 09:37

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

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: insert into db via perl dbi [message #640994 is a reply to message #640993]

Sat, 08 August 2015 09:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

does INSERT statement succeed when issued from sqlplus?

Report message to a moderator

Re: insert into db via perl dbi [message #640995 is a reply to message #640994]

Sat, 08 August 2015 10:06

hudo
Messages: 165
Registered: May 2004

Senior Member

The following statement succeed from SQLPlus

Code: [Select all] [Show/ hide]

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

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: insert into db via perl dbi [message #640997 is a reply to message #640995]

Sat, 08 August 2015 10:24

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=,.')

Report message to a moderator

Re: insert into db via perl dbi [message #640998 is a reply to message #640997]

Sat, 08 August 2015 10:33

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: insert into db via perl dbi [message #640999 is a reply to message #640997]

Sat, 08 August 2015 10:43

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

Code: [Select all] [Show/ hide]

048.0544|ABC3456-123|2074,00|1383,00
049.0676|ABC1234-567|0,00|0,00

And this is the error I get

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: insert into db via perl dbi [message #641000 is a reply to message #640999]

Sat, 08 August 2015 10:48

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: insert into db via perl dbi [message #641001 is a reply to message #640999]

Sat, 08 August 2015 10:48

hudo
Messages: 165
Registered: May 2004

Senior Member

Ahh, sorry, I got it. It was the newline contained in vk1.

Report message to a moderator

Re: insert into db via perl dbi [message #641002 is a reply to message #641001]

Sat, 08 August 2015 10:50

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: insert into db via perl dbi [message #641003 is a reply to message #641002]

Sat, 08 August 2015 10:56

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.

Report message to a moderator

Re: insert into db via perl dbi [message #641004 is a reply to message #641003]

Sat, 08 August 2015 10:59

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No error. In your post you have:

Quote:

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 01 19:49:11 CDT 2025