I would highly recommend using the -w option (and 'use strict;' if you are not already). I'd also recommend pulling the prepare out of the loop - If you are using bind variables you shouldn't need to prepare the statement every time through the loop.

The hanging behavior is odd. I (and presumably many people on the list)
have used bind variables for years without trouble. It may be that -w and 'use strict;' will give you helpful info; these were both required rules (as much as I could enforce them) back in my days of iron-fisted DBA.


--Chris


On Aug 13, 2004, at 1:28 PM, Shah, Urmil wrote:

Ok I tried to use method 3 and as I said it hangs. Since it's a dynamic
sql some variables are undefined and they do not have any value, now if
they don't have any value is it possible that DBI/Oracle will interpret
as empty values ? else I have to check the values of each variable and
then hardcode to null if there is no value.

The print statement shows what is going to be populated. If it is empty
I want them to be empty.



My print statement and Trace output:

Values to insert:
08/12/04,DMOS5,POLARIS100,P1831C02,91,22,49,,2,10,,,2,32,

    DBI::db=HASH(0x49ea08) trace level set to 1 in DBI 1.20-nothread
    Note: perl is running without the recommended perl -w option
    <- prepare('insert into OPER_QUERY
values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)' CODE)= DBI::st=HASH(0x49eaa4) at
oper_query.pl line 352
    DBI::db=HASH(0x49ea08) trace level set to 2 in DBI 1.20-nothread
    Note: perl is running without the recommended perl -w option
    <- trace= 1 at oper_query.pl line 353

^C    !! ERROR: 1013 'ORA-01013: user requested cancel of current
operation (DBD: oexec error)'
    <- execute('08/12/04' 'DMOS5' ...)= undef at oper_query.pl line 354
DBD::Oracle::st execute failed: ORA-01013: user requested cancel of
current operation (DBD: oexec error) at oper_query.pl line 354.
DBD::Oracle::st execute failed: ORA-01013: user requested cancel of
current operation (DBD: oexec error) at oper_query.pl line 354.
    <- DESTROY= undef
    -> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x49ea08)~INNER)
    <- DESTROY= undef during global destruction


Regards, Urmil

-----Original Message-----
From: Reidy, Ron [mailto:[EMAIL PROTECTED]
Sent: Friday, August 13, 2004 12:16 PM
To: Kong, Alan; Shah, Urmil; [EMAIL PROTECTED]
Subject: RE: SQL- Insert problem

He should use bind variables (method 3).

-----------------
Ron Reidy
Senior DBA
Array BioPharma, Inc.


-----Original Message----- From: Kong, Alan [mailto:[EMAIL PROTECTED] Sent: Friday, August 13, 2004 11:13 AM To: Shah, Urmil; Reidy, Ron; [EMAIL PROTECTED] Subject: RE: SQL- Insert problem


Try the mothed 2 with non-numeric fields single-quoted.

-----Original Message-----
From: Shah, Urmil [mailto:[EMAIL PROTECTED]
Sent: Friday, August 13, 2004 1:07 PM
To: Reidy, Ron; [EMAIL PROTECTED]
Subject: RE: SQL- Insert problem


I tried to use different methods to insert but all fails.

1) $sth=$dbh->prepare(insert into OPER_QUERY values ($a,$b,$c,$d....) )
    $sth->execute() or die;

2) $sth=$dbh->prepare(insert into OPER_QUERY (col1, col2, col3..co15)
values    ($a,$b,$c,$d....) )
     $sth->execute() or die;

3) $sth=$dbh->prepare(insert into OPER_QUERY values (?,?,?,?,....?) )
     $sth->execute($a,$b,$c,$d,...$x) or die;


For case 1 and 2 it gives error saying the value is empty. In case 3 it just hangs and does not populate anything and I have to kill the script. If there is any other way to insert please let me know.

Urmil


-----Original Message----- From: Reidy, Ron [mailto:[EMAIL PROTECTED] Sent: Friday, August 13, 2004 11:57 AM To: Shah, Urmil; [EMAIL PROTECTED] Subject: RE: SQL- Insert problem

You have a NULL (not the word NULL) in your statement.

FYI - You should look into using bind variables.

-----------------
Ron Reidy
Senior DBA
Array BioPharma, Inc.


-----Original Message----- From: Shah, Urmil [mailto:[EMAIL PROTECTED] Sent: Friday, August 13, 2004 10:43 AM To: [EMAIL PROTECTED] Subject: SQL- Insert problem


Need help in trying to insert an sql statement.



1)       I have 15 columns in oracle database defined.

2)       Through Perl DBI I am trying to loop and populate these 15
columns for 'n' of rows.

3)       Some of the column values that I am trying to insert is
null/empty and it is ok if they are empty <-- It gives me error when it
find null value.



Code:



foreach my $type(sort keys %H_CNT_BY_PGM){

 foreach my $pgm(sort keys %{$H_CNT_BY_PGM{$type}}){



 $sth1 = $dbh->prepare(qq{insert into OPER_QUERY
(end_date,site,tester_type,program,total,oper_yes,rmenu_yes,opr_dev_type
,opr_test_type,opr_temp_type,opr_lot_type,rmenu_dev_type,rmenu_test_type
,rmenu_temp_type,rmenu_lot_type) values
($mydate,$site,$type,$pgm,$Tot,$Tot_yes,$Tot_rmenu,$dev_type_err,$test_t
ype_err,$temp_type_err,$lot_type_err,$rmenu_dev_type_err,$rmenu_test_typ
e_err,$rmenu_temp_type_err,$rmenu_lot_type_err)}) or die"Cannot prepare
sql:$DBI::errstr\n";



$sth1->execute() or die"Cannot prepare sql:$DBI::errstr\n";



 }#foreach

}#foreach





dbi Trace(1) output :



start inserting:
08/12/04,DMOS5,POLARIS100,P1831C02,91,22,49,,2,10,,,2,32,

    DBI::db=HASH(0x49fcbc) trace level set to 1 in DBI 1.20-nothread

    Note: perl is running without the recommended perl -w option

    !! ERROR: 936 'ORA-00936: missing expression (DBD: error possibly
near <*> indicator at char 252 in 'insert into OPER_QUERY
(end_date,site,tester_type,program,total,oper_yes,rmenu_yes,opr_dev_type
,opr_test_type,opr_temp_type,opr_lot_type,rmenu_dev_type,rmenu_test_type
,rmenu_temp_type,rmenu_lot_type)
values(08/12/04,DMOS5,POLARIS100,P1831C02,91,22,49,<*>,2,10,,,2,32,)')'

    <- prepare('insert into OPER_QUERY
(end_date,site,tester_type,program,total,oper_yes,rmenu_yes,opr_dev_type
,opr_test_type,opr_temp_type,opr_lot_type,rmenu_dev_type,rmenu_test_type
,rmenu_temp_type,rmenu_lot_type)
values(08/12/04,DMOS5,POLARIS100,P1831C02,91,22,49,,2,10,,,2,32,)'
CODE)= undef at oper_query.pl line 351

DBD::Oracle::db prepare failed: ORA-00936: missing expression (DBD:
error possibly near <*> indicator at char 252 in 'insert into OPER_QUERY
(end_date,site,tester_type,program,total,oper_yes,rmenu_yes,opr_dev_type
,opr_test_type,opr_temp_type,opr_lot_type,rmenu_dev_type,rmenu_test_type
,rmenu_temp_type,rmenu_lot_type)
values(08/12/04,DMOS5,POLARIS100,P1831C02,91,22,49,<*>,2,10,,,2,32,)')
at oper_query.pl line 351.

DBD::Oracle::db prepare failed: ORA-00936: missing expression (DBD:
error possibly near <*> indicator at char 252 in 'insert into OPER_QUERY
(end_date,site,tester_type,program,total,oper_yes,rmenu_yes,opr_dev_type
,opr_test_type,opr_temp_type,opr_lot_type,rmenu_dev_type,rmenu_test_type
,rmenu_temp_type,rmenu_lot_type)
values(08/12/04,DMOS5,POLARIS100,P1831C02,91,22,49,<*>,2,10,,,2,32,)')
at oper_query.pl line 351.

    <- DESTROY= undef

    <- DESTROY= undef during global destruction





This column is an integer and its ok if it is null I don't have any
constraints defined of not null on this column.







*       I tried to prepare the statement outside the foreach loop and
then pass on the values for those columns in the loop it hangs.



Eg:



  $sth=$dbh->prepare(qq{insert into OPER_QUERY values (?,?,?,?.....?) or
die........................;



foreach loop{



   $sth->execute($mydate,$type, ..............) or die ..............



   }



Here it Hangs and does not insert any values.



Can any one help on whats going on. I do not want to check each of 15
paramater values to see if they are null or not null before inserting in
the db.





Regards,

Urmil










This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.


This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.



Reply via email to