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 dieCannot prepare
sql:$DBI::errstr\n;
$sth1-execute() or dieCannot 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.
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 dieCannot prepare
sql:$DBI::errstr\n;
$sth1-execute() or dieCannot 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
RE: SQL- Insert problem
From your original post ...
!! 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,)')'
The error is ---^
This is invalid SQL, as are the others in this statement like this. Because are
building dynamic SQL and your variables are 'undef'ined, this is the statement you are
sending to the RDBMS engine. You need to ensure your variables have a value and all
should be well.
However, you should still look at bind variables.
-
Ron Reidy
Senior DBA
Array BioPharma, Inc.
-Original Message-
From: Shah, Urmil [mailto:[EMAIL PROTECTED]
Sent: Friday, August 13, 2004 11:07 AM
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 dieCannot prepare
sql:$DBI::errstr\n;
$sth1-execute() or dieCannot 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
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 dieCannot prepare
sql:$DBI::errstr\n;
$sth1-execute() or dieCannot 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
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 dieCannot prepare
sql:$DBI::errstr\n;
$sth1-execute() or dieCannot 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
RE: SQL- Insert problem
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 dieCannot prepare
sql:$DBI::errstr\n;
$sth1-execute() or dieCannot 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
RE: SQL- Insert problem
Just in case the op doesn't make the connection, he needs to test his
variables for undef before the execute, and if one is undef, then put
null() in it, something like this:
if (!defined($a)) {$a = ;}
if (!defined($b)) {$b = ;}
... and so on - you get the idea...
$sth=$dbh-prepare(insert into OPER_QUERY values (?,?,?,?,?)
)
$sth-execute($a,$b,$c,$d,...$x) or die;
HTH.
Hardy Merrill
Reidy, Ron [EMAIL PROTECTED] 08/13/04 01:13PM
From your original post ...
!! 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,)')'
The error is ---^
This is invalid SQL, as are the others in this statement like this.
Because are building dynamic SQL and your variables are 'undef'ined,
this is the statement you are sending to the RDBMS engine. You need to
ensure your variables have a value and all should be well.
However, you should still look at bind variables.
-
Ron Reidy
Senior DBA
Array BioPharma, Inc.
-Original Message-
From: Shah, Urmil [mailto:[EMAIL PROTECTED]
Sent: Friday, August 13, 2004 11:07 AM
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 dieCannot
prepare
sql:$DBI::errstr\n;
$sth1-execute() or dieCannot 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
RE: SQL- Insert problem
You need to ensure your variables have values. In the case where they are undef,
assigning them the empty string () should be sufficient.
If this statement still hangs when doing this, you need to run a 10046 trace for your
session to see what is going on (see your DBA for this).
-
Ron Reidy
Senior DBA
Array BioPharma, Inc.
303.386.1480
-Original Message-
From: Shah, Urmil [mailto:[EMAIL PROTECTED]
Sent: Friday, August 13, 2004 11:29 AM
To: Reidy, Ron; Kong, Alan; [EMAIL PROTECTED]
Subject: RE: SQL- Insert problem
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 dieCannot prepare
sql:$DBI::errstr\n;
$sth1-execute() or dieCannot prepare sql:$DBI::errstr\n;
}#foreach
RE: SQL- Insert problem
Hardy Merrill [mailto:[EMAIL PROTECTED] wrote:
Just in case the op doesn't make the connection, he needs to test his
variables for undef before the execute, and if one is undef, then put
null() in it, something like this:
if (!defined($a)) {$a = ;}
if (!defined($b)) {$b = ;}
... and so on - you get the idea...
$sth=$dbh-prepare(insert into OPER_QUERY values (?,?,?,?,?)
)
$sth-execute($a,$b,$c,$d,...$x) or die;
Why does he need to do that? I have never had to test my bind values for
defined-ness when using placeholders. An undef in Perl becomes a NULL in
Oracle, and vice versa.
Ronald
RE: SQL- Insert problem
Ronald J Kimball [EMAIL PROTECTED] 08/13/04 01:59PM
Hardy Merrill [mailto:[EMAIL PROTECTED] wrote:
Just in case the op doesn't make the connection, he needs to test
his
variables for undef before the execute, and if one is undef, then
put
null() in it, something like this:
if (!defined($a)) {$a = ;}
if (!defined($b)) {$b = ;}
... and so on - you get the idea...
$sth=$dbh-prepare(insert into OPER_QUERY values
(?,?,?,?,?)
)
$sth-execute($a,$b,$c,$d,...$x) or die;
Why does he need to do that? I have never had to test my bind values
for
defined-ness when using placeholders. An undef in Perl becomes a NULL
in
Oracle, and vice versa.
Ronald
I have to plead ignorance - I thought his problem was caused by one or
more the variables (being passed to placeholders) being undefined.
Sorry, I must not have read his post well enough that included the
actual error(s).
Hardy
Re: SQL- Insert problem
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 dieCannot prepare
sql:$DBI::errstr\n;
$sth1-execute() or dieCannot prepare sql:$DBI::errstr\n;
}#foreach
}#foreach
dbi
RE: SQL- Insert problem
See below ... - Ron Reidy Senior DBA Array BioPharma, Inc. 303.386.1480 -Original Message- From: Shah, Urmil [mailto:[EMAIL PROTECTED] Sent: Friday, August 13, 2004 1:02 PM To: Reidy, Ron; Kong, Alan; [EMAIL PROTECTED] Subject: RE: SQL- Insert problem Ok Here is the main problem. When I do bind columns, i.e prepare my sql statement outside the foreach loop and inside the loop when I pass on the parameters ( even the empty ones without any values) i found that it hangs. [Reidy, Ronald] Did you run a 10046 trace for the session? It will be virtaully impossible to determine what the problem is without this. I went to TOAD to see what the problem is and did any data get loaded in this table and when I tried to rename that table it gave me the following error. As soon as I hit 'ok' button , the looping and inserting from my unix box gets completed successfully and data is inserted (empty variables remain as they are). [Reidy, Ronald] Did you commit before trying to rename the table? Also this db is not heavily used and my sql query is also very simple. I am not sure what causes the LOCK and NOWAIT in oracle. I have never had this problem before and all resources seem to be in normal condition. [Reidy, Ronald] It means a process has some sort of a lock on the table (or one of it's indexes) and another process is trying to obtain a lock of similar strength. The NOWAIT means the statement was issued without the NOWAIT clause. 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.
Re: SQL- Insert problem
From: Shah, Urmil [EMAIL PROTECTED]
Date: 2004/08/13 Fri PM 04:42:54 GMT
Need help in trying to insert an sql statement.
As everyone else has been saying, you need to use placeholders.
See my script below... Note that I tested with MSSQL, but
Regards,
Urmil
Regards,
amonotod
#!perl -w
use strict;
eval (use DBI);
if ($@) { die This system does not have the DBI installed\n; }
my ($mydate,$site,$type,$pgm,$Tot,$Tot_yes,$Tot_rmenu,$dev_type_err,
$test_type_err,$temp_type_err,$lot_type_err,$rmenu_dev_type_err,
$rmenu_test_type_err,$rmenu_temp_type_err,$rmenu_lot_type_err);
# Note that I have declared the variables, but not assigned any value to them...
my %H_CNT_BY_PGM;
my $olimit = 10;
while ($olimit 0) {
my $type = ;
my $typeLen = rand(10)+1;
while ($typeLen 0) { $type .= chr((rand(26)+97)); $typeLen--;}
#print $type \n;
my $ilimit = 10;
while ($ilimit 0) {
my $pgm = ;
my $pgmLen = rand(10)+1;
while ($pgmLen 0) { $pgm .= chr((rand(26)+97)); $pgmLen--;}
#print \t$pgm \n;
$H_CNT_BY_PGM{$type}{$pgm} = 1;
$ilimit--;
}
$olimit--;
}
my $dbh = DBI-connect( dbi:Oracle:host=ORADEV9I;sid=ORADEV9I, 'scott', 'tiger',
{RaiseError = 0, AutoCommit = 1} );
my $sqlStatement = DROP TABLE OPER_QUERY;
my $sth = $dbh-prepare($sqlStatement) or die(Cannot prepare sql:$dbh-errstr\n);
unless ($sth-execute) {
print errors: $dbh-errstr \n;
print \nErrors were encountered during table drop, exiting...\n;
}
$sqlStatement = SQL;
create table OPER_QUERY (
end_date varchar (1),
site varchar (1),
tester_type varchar (15),
program varchar (15),
total varchar (1),
oper_yes varchar (1),
rmenu_yes varchar (1),
opr_dev_type varchar (1),
opr_test_type varchar (1),
opr_temp_type varchar (1),
opr_lot_type varchar (1),
rmenu_dev_type varchar (1),
rmenu_test_type varchar (1),
rmenu_temp_type varchar (1),
rmenu_lot_type varchar (1)
)
SQL
$sth = $dbh-prepare($sqlStatement) or die(Cannot prepare sql:$dbh-errstr\n);
unless ($sth-execute) {
print errors: $dbh-errstr \n;
print \nErrors were encountered during table create, exiting...\n;
}
$sqlStatement = 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 (?,?,?,?,?,?,?,?,?,.
?,?,?,?,?,?);
$sth = $dbh-prepare($sqlStatement) or die(Cannot prepare sql:$dbh-errstr\n);
foreach my $type(sort keys %H_CNT_BY_PGM){
foreach my $pgm(sort keys %{$H_CNT_BY_PGM{$type}}){
$sth-execute($mydate,$site,$type,$pgm,$Tot,$Tot_yes,$Tot_rmenu,$dev_type_err,
$test_type_err,$temp_type_err,$lot_type_err,$rmenu_dev_type_err,
$rmenu_test_type_err,$rmenu_temp_type_err,$rmenu_lot_type_err)
or dieCannot send the data... $dbh-errstr\n;
}
}
--
`\|||/ amonotod@| sun|perl|windows
(@@) charter.net | sysadmin|dba
ooO_(_)_Ooo
_|_|_|_|_|_|_|_|
Re: SQL- Insert problem
From: amonotod [EMAIL PROTECTED] Date: 2004/08/13 Fri PM 07:29:35 GMT Note that I tested with MSSQL, but Well, yes, I did test with MSSQL, but as you can see from the DBI connection string, I also tested with Oracle 9i... :-) amonotod -- `\|||/ amonotod@| sun|perl|windows (@@) charter.net | sysadmin|dba ooO_(_)_Ooo _|_|_|_|_|_|_|_|
RE: SQL- Insert problem
Here is the code if it helps.
I do not know how to run trace 10046 but I
have put output from V$session_wait for my session.
my $dbh = $db-connect($graph_fab);
$sth1 = $dbh-prepare(qq{insert into
temp values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}) or dieCannot prepare sql:$DBI::errstr\n;
foreach my $type(sort keys %H_CNT_BY_PGM){
foreach my $pgm(sort keys %{$H_CNT_BY_PGM{$type}}){
my $string=$H_CNT_BY_PGM{$type}{$pgm};
($dev_type_err,$test_type_err,$lot_type_err,$temp_type_err,$rmenu_dev_type_err,$rmenu_test_type_err,$rmenu_lot_type_err,$rmenu_temp_type_err)=split(/,/,$string);
print Values to insert:
$mydate,$site,$type,$pgm,$Tot,$Tot_yes,$Tot_rmenu,$dev_type_err,$test_type_err,$temp_type_err,$lot_type_err,$rmenu_dev_type_err,$rmenu_test_type_err,$rmenu_temp_type_err,$rmenu_lot_type_err\n\n;
$sth1-execute($mydate,$site,$type,$pgm,$Tot,$Tot_yes,$Tot_rmenu,$dev_type_err,$test_type_err,$temp_type_err,$lot_type_err,$rmenu_dev_type_err,$rmenu_test_type_err,$rmenu_temp_type_err,$rmenu_lot_type_err)
or dieCannot execute with values:$DBI::ERRSTR;
}#foreach
}#foreach
print Finished Inserting in temp\n;
$sth1-finish();
$dbh-disconnect();
select * from v$session_wait
where sid=21
SID
SEQ#
EVENT
P1TEXT
P1
P1RAW
P2TEXT
P2
P2RAW
P3TEXT
P3
P3RAW
WAIT_TIME
SECONDS_IN_WAIT
STATE
21
59
enqueue
name|mode
1415053316
54580004
id1
262176
00040020
id2
417280
00065E00
0
141
WAITING
I will try to contact my DBA and get the 10046
trace but it will take some time. If its short and simple and if some one is
willing to explain I can try to do it myself.
[Reidy, Ronald]Did you commit before
trying to rename the table?
-- Currently I am just testing to see if
my code works to populate the table. I did not change the name or did any
commit. After I hit ok button it populates all the data in db
without any problems. I delete all the data delete from temp and
then try to run the sql since there are some PK and unique value constraints.
Urmil
-Original Message-
From: Reidy, Ron
[mailto:[EMAIL PROTECTED]
Sent: Friday, August
13, 2004 2:06 PM
To: Shah, Urmil; Kong, Alan;
[EMAIL PROTECTED]
Subject: RE: SQL- Insert problem
See below ...
-
Ron
Reidy
Senior
DBA
Array
BioPharma, Inc.
303.386.1480
-Original
Message-
From: Shah, Urmil
[mailto:[EMAIL PROTECTED]
Sent: Friday, August
13, 2004 1:02 PM
To: Reidy, Ron; Kong, Alan;
[EMAIL PROTECTED]
Subject: RE: SQL- Insert problem
Ok Here is the main problem.
When I do bind columns, i.e prepare my sql statement
outside the foreach loop and inside the loop when I pass on the parameters (
even the empty ones without any values) i found that it hangs.
[Reidy, Ronald]Did you run a 10046 trace for the
session? It will be virtaully impossible to determine what the problem is
without this.
I went to TOAD to see what the problem is and did any
data get loaded in this table and when I tried to rename that table it gave me
the following error. As soon as I hit ok button , the looping and
inserting from my unix box gets completed successfully and data is inserted
(empty variables remain as they are).
[Reidy, Ronald]Did you commit before trying to rename the table?
Also this db is not heavily used and my sql query is
also very simple. I am not sure what causes the LOCK and NOWAIT in oracle. I
have never had this problem before and all resources seem to be in normal
condition.
[Reidy, Ronald]It means a process has somesort of a
lock on the table (or one of it's indexes) and another process is trying to
obtaina lock of similar strength. The NOWAIT means the statement
was issued withou the NOWAIT clause.
Regards,
Urmil
-Original Message-
From: Reidy, Ron [mailto:[EMAIL PROTECTED]
Sent: Friday, August 13, 2004 12:35 PM
To: Shah, Urmil; Kong, Alan; [EMAIL PROTECTED]
Subject: RE: SQL- Insert problem
You need to ensure your variables have values.
In the case where they are undef, assigning them the empty string
() should be sufficient.
If this statement still hangs when doing this, you
need to run a 10046 trace for your session to see what is going on (see your
DBA for this).
-
Ron Reidy
Senior DBA
Array BioPharma, Inc.
303.386.1480
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
RE: SQL- Insert problem
From: Shah, Urmil [EMAIL PROTECTED]
Date: 2004/08/13 Fri PM 07:48:44 GMT
$sth1 = $dbh-prepare(qq{insert into temp values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}) or
dieCannot prepare sql:$DBI::errstr\n;
My understanding was that placeholders must have column names associated
with them, so I dont' think that would work...
HTH, and HINW,
amonotod
--
`\|||/ amonotod@| sun|perl|windows
(@@) charter.net | sysadmin|dba
ooO_(_)_Ooo
_|_|_|_|_|_|_|_|
Here is the code if it helps.
I do not know how to run trace 10046 but I
have put output from V$session_wait for my session.
my $dbh = $db-connect($graph_fab);
$sth1 = $dbh-prepare(qq{insert into
temp values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}) or dieCannot prepare sql:$DBI::errstr\n;
foreach my $type(sort keys %H_CNT_BY_PGM){
foreach my $pgm(sort keys %{$H_CNT_BY_PGM{$type}}){
my $string=$H_CNT_BY_PGM{$type}{$pgm};
($dev_type_err,$test_type_err,$lot_type_err,$temp_type_err,$rmenu_dev_type_err,$rmenu_test_type_err,$rmenu_lot_type_err,$rmenu_temp_type_err)=split(/,/,$string);
print Values to insert:
$mydate,$site,$type,$pgm,$Tot,$Tot_yes,$Tot_rmenu,$dev_type_err,$test_type_err,$temp_type_err,$lot_type_err,$rmenu_dev_type_err,$rmenu_test_type_err,$rmenu_temp_type_err,$rmenu_lot_type_err\n\n;
$sth1-execute($mydate,$site,$type,$pgm,$Tot,$Tot_yes,$Tot_rmenu,$dev_type_err,$test_type_err,$temp_type_err,$lot_type_err,$rmenu_dev_type_err,$rmenu_test_type_err,$rmenu_temp_type_err,$rmenu_lot_type_err)
or dieCannot execute with values:$DBI::ERRSTR;
}#foreach
}#foreach
print Finished Inserting in temp\n;
$sth1-finish();
$dbh-disconnect();
select * from v$session_wait
where sid=21
SID
SEQ#
EVENT
P1TEXT
P1
P1RAW
P2TEXT
P2
P2RAW
P3TEXT
P3
P3RAW
WAIT_TIME
SECONDS_IN_WAIT
STATE
21
59
enqueue
name|mode
1415053316
54580004
id1
262176
00040020
id2
417280
00065E00
0
141
WAITING
I will try to contact my DBA and get the 10046
trace but it will take some time. If its short and simple and if some one is
willing to explain I can try to do it myself.
[Reidy, Ronald]Did you commit before
trying to rename the table?
-- Currently I am just testing to see if
my code works to populate the table. I did not change the name or did any
commit. After I hit ok button it populates all the data in db
without any problems. I delete all the data delete from temp and
then try to run the sql since there are some PK and unique value constraints.
Urmil
-Original Message-
From: Reidy, Ron
[mailto:[EMAIL PROTECTED]
Sent: Friday, August
13, 2004 2:06 PM
To: Shah, Urmil; Kong, Alan;
[EMAIL PROTECTED]
Subject: RE: SQL- Insert problem
See below ...
-
Ron
Reidy
Senior
DBA
Array
BioPharma, Inc.
303.386.1480
-Original
Message-
From: Shah, Urmil
[mailto:[EMAIL PROTECTED]
Sent: Friday, August
13, 2004 1:02 PM
To: Reidy, Ron; Kong, Alan;
[EMAIL PROTECTED]
Subject: RE: SQL- Insert problem
Ok Here is the main problem.
When I do bind columns, i.e prepare my sql statement
outside the foreach loop and inside the loop when I pass on the parameters (
even the empty ones without any values) i found that it hangs.
[Reidy, Ronald]Did you run a 10046 trace for the
session? It will be virtaully impossible to determine what the problem is
without this.
I went to TOAD to see what the problem is and did any
data get loaded in this table and when I tried to rename that table it gave me
the following error. As soon as I hit ok button , the looping and
inserting from my unix box gets completed successfully and data is inserted
(empty variables remain as they are).
[Reidy, Ronald]Did you commit before trying to rename the table?
Also this db is not heavily used and my sql query is
also very simple. I am not sure what causes the LOCK and NOWAIT in oracle. I
have never had this problem before and all resources seem to be in normal
condition.
[Reidy, Ronald]It means a process has somesort of a
lock on the table (or one of it's indexes) and another process is trying to
obtaina lock of similar strength. The NOWAIT means the statement
was issued withou the NOWAIT clause.
Regards,
Urmil
-Original Message-
From: Reidy, Ron [mailto:[EMAIL PROTECTED]
Sent: Friday, August 13, 2004 12:35 PM
To: Shah, Urmil; Kong, Alan; [EMAIL PROTECTED]
Subject: RE: SQL- Insert problem
You need to ensure your variables have values.
In the case where they are undef, assigning them the empty string
() should be sufficient.
If this statement still hangs when doing this, you
need to run a 10046 trace for your session to see what is going
RE: SQL- Insert problem
See below ...
-
Ron Reidy
Senior DBA
Array BioPharma, Inc.
303.386.1480
-Original Message-
From: Shah, Urmil [mailto:[EMAIL PROTECTED]
Sent: Friday, August 13, 2004 1:49 PM
To: [EMAIL PROTECTED]
Subject: RE: SQL- Insert problem
Here is the code if it helps.
I do not know how to run trace 10046 but I have put output from V$session_wait for my
session.
[Reidy, Ronald] Not enough. Do alter session set events '10046 trace name context
forever, level 8' after your connection.
my $dbh = $db-connect($graph_fab);
$sth1 = $dbh-prepare(qq{insert into temp values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}) or
dieCannot prepare sql:$DBI::errstr\n;
foreach my $type(sort keys %H_CNT_BY_PGM){
foreach my $pgm(sort keys %{$H_CNT_BY_PGM{$type}}){
my $string=$H_CNT_BY_PGM{$type}{$pgm};
($dev_type_err,$test_type_err,$lot_type_err,$temp_type_err,$rmenu_dev_type_err,$rmenu_test_type_err,$rmenu_lot_type_err,$rmenu_temp_type_err)=split(/,/,$string);
print Values to insert:
$mydate,$site,$type,$pgm,$Tot,$Tot_yes,$Tot_rmenu,$dev_type_err,$test_type_err,$temp_type_err,$lot_type_err,$rmenu_dev_type_err,$rmenu_test_type_err,$rmenu_temp_type_err,$rmenu_lot_type_err\n\n;
$sth1-execute($mydate,$site,$type,$pgm,$Tot,$Tot_yes,$Tot_rmenu,$dev_type_err,$test_type_err,$temp_type_err,$lot_type_err,$rmenu_dev_type_err,$rmenu_test_type_err,$rmenu_temp_type_err,$rmenu_lot_type_err)
or dieCannot execute with values:$DBI::ERRSTR;
}#foreach
}#foreach
print Finished Inserting in temp\n;
$sth1-finish();
$dbh-disconnect();
select * from v$session_wait
where sid=21
SID
SEQ#
EVENT
P1TEXT
P1
P1RAW
P2TEXT
P2
P2RAW
P3TEXT
P3
P3RAW
WAIT_TIME
SECONDS_IN_WAIT
STATE
21
59
enqueue
name|mode
1415053316
54580004
id1
262176
00040020
id2
417280
00065E00
0
141
WAITING
I will try to contact my DBA and get the 10046 trace but it will take some time. If
its short and simple and if some one is willing to explain I can try to do it myself.
[Reidy, Ronald] Did you commit before trying to rename the table?
-- Currently I am just testing to see if my code works to populate the table. I did
not change the name or did any commit. After I hit 'ok' button it populates all the
data in db without any problems. I delete all the data delete from temp and then try
to run the sql since there are some PK and unique value constraints.
[Reidy, Ronald] Get out of toad and run this.
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.
RE: SQL- Insert problem
From: Ronald J Kimball [EMAIL PROTECTED] Date: 2004/08/13 Fri PM 05:59:39 GMT Hardy Merrill [mailto:[EMAIL PROTECTED] wrote: Just in case the op doesn't make the connection, he needs to test his variables for undef before the execute, and if one is undef, then put Why does he need to do that? I have never had to test my bind values for defined-ness when using placeholders. An undef in Perl becomes a NULL in Oracle, and vice versa. Programming the Perl DBI, Page 221, paragragh 1, sentence 3: Undefined values or 'undef' can be used to indicate null values. Ronald HTH, amonotod -- `\|||/ amonotod@| sun|perl|windows (@@) charter.net | sysadmin|dba ooO_(_)_Ooo _|_|_|_|_|_|_|_|
RE: SQL- Insert problem
Correct, but he he building dynamic SQL. The resulting insert statement's values clause looks like (v1,v1,,v4...); the problem is the ,,. This is why I suggested to use bind variables. - Ron Reidy Senior DBA Array BioPharma, Inc. -Original Message- From: amonotod [mailto:[EMAIL PROTECTED] Sent: Friday, August 13, 2004 2:04 PM To: [EMAIL PROTECTED] Cc: Ronald J Kimball; 'Hardy Merrill' Subject: RE: SQL- Insert problem From: Ronald J Kimball [EMAIL PROTECTED] Date: 2004/08/13 Fri PM 05:59:39 GMT Hardy Merrill [mailto:[EMAIL PROTECTED] wrote: Just in case the op doesn't make the connection, he needs to test his variables for undef before the execute, and if one is undef, then put Why does he need to do that? I have never had to test my bind values for defined-ness when using placeholders. An undef in Perl becomes a NULL in Oracle, and vice versa. Programming the Perl DBI, Page 221, paragragh 1, sentence 3: Undefined values or 'undef' can be used to indicate null values. Ronald HTH, amonotod -- `\|||/ amonotod@| sun|perl|windows (@@) charter.net | sysadmin|dba ooO_(_)_Ooo _|_|_|_|_|_|_|_| 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.
RE: SQL- Insert problem
Ron/Amonotod/Ronald/Merrill Its working !!! Thank you very much for all your time and extended help. It was my mistake that I was unaware and caused this problem. I had a perl script that would insert data into the table. Some columns in this table had unique value constraint so I had to delete values from the table when it was updated by the script (I had changed the name of the table and it used to update the table for the first time only). To delete the table I used TOAD and wrote a query delete from oper_query and it did delete every rows. Then I use to go back to unix box and try to run my script and it used to hang. Here is the problem, I did not COMMIT my delete session in TOAD and that was the culprit. After I realized my mistake it started working. Also binding columns is working properly when there is no value in the variable. I learned many new things today from all your experience and my mistakes. May be today is Friday and it might be one of the reasons :-) Thanks again. Urmil -Original Message- From: Reidy, Ron [mailto:[EMAIL PROTECTED] Sent: Friday, August 13, 2004 3:19 PM To: amonotod; [EMAIL PROTECTED] Cc: Ronald J Kimball; Hardy Merrill Subject: RE: SQL- Insert problem Correct, but he he building dynamic SQL. The resulting insert statement's values clause looks like (v1,v1,,v4...); the problem is the ,,. This is why I suggested to use bind variables. - Ron Reidy Senior DBA Array BioPharma, Inc. -Original Message- From: amonotod [mailto:[EMAIL PROTECTED] Sent: Friday, August 13, 2004 2:04 PM To: [EMAIL PROTECTED] Cc: Ronald J Kimball; 'Hardy Merrill' Subject: RE: SQL- Insert problem From: Ronald J Kimball [EMAIL PROTECTED] Date: 2004/08/13 Fri PM 05:59:39 GMT Hardy Merrill [mailto:[EMAIL PROTECTED] wrote: Just in case the op doesn't make the connection, he needs to test his variables for undef before the execute, and if one is undef, then put Why does he need to do that? I have never had to test my bind values for defined-ness when using placeholders. An undef in Perl becomes a NULL in Oracle, and vice versa. Programming the Perl DBI, Page 221, paragragh 1, sentence 3: Undefined values or 'undef' can be used to indicate null values. Ronald HTH, amonotod -- `\|||/ amonotod@| sun|perl|windows (@@) charter.net | sysadmin|dba ooO_(_)_Ooo _|_|_|_|_|_|_|_| 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.
