RE: SQL- Insert problem

[amonotod]

> From: "Shah, Urmil" <[EMAIL PROTECTED]>
> Date: 2004/08/13 Fri PM 07:48:44 GMT
> 
> $sth1 = $dbh->prepare(qq{insert into temp values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}) or 
> die"Cannot 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 die"Cannot 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 die"Cannot 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 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 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 to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.

<<image001.jpg>>