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
-----------------
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
