I did that but it did not help. The same error still pops up. Any ideas anybody?
Regards, Marc Torres Alvarez Hey Marc, Try putting a $sth1->finish(); before you do the next do statement. See your code below, I marked it with *** Try that and see if it helps. Thanks, Sam Gold -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, March 12, 2004 7:10 AM To: [EMAIL PROTECTED] Subject: help required for DBI script! Hi all I have this script that keeps bugging me with an Oracle error ORA-00942 that should not be there. All the tables in the script are owned by 'DBREPORTER' who is running the script. I have executed the same SQL commands directly with SQLPlus and they work fine. Even more, the same SQL commands had been running within a PLSQL procedure for 1 year and they worked fine. So there's something I am missing. To be precise, I have also attached the error message. If anybody knows what I am doing wrong I would greatly appreciate some help. Regards, Marc Torres Alvarez ################################################3 #### error message /export/home/oracle>DBD::Oracle::db do failed: ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 542 in 'INSERT INTO system_events SELECT a.database, sysdate, a.event, a.total_waits, a.total_timeouts, a.time_waited/100, a.average_wait, (a.total_waits - b.total_waits), (a.total_timeouts - b.total_timeouts), (a.time_waited/100 - b.time_waited), (sysdate - b.time)*24*60*60 FROM t_se a, (SELECT event, total_waits, total_timeouts, time_waited, time FROM dbreporter.<*>system_events WHERE database = 'BISP.emea.fedex.com' AND time = (SELECT MAX(time) FROM system_events WHERE database = 'BISP.emea.fedex.com')) b WHERE a.event = b.event(+) AND a.database = 'BISP.emea.fedex.com' ') [for Statement "INSERT INTO system_events SELECT a.database, sysdate, a.event, a.total_waits, a.total_timeouts, a.time_waited/100, a.average_wait, (a.total_waits - b.total_waits), (a.total_timeouts - b.total_timeouts), (a.time_waited/100 - b.time_waited), (sysdate - b.time)*24*60*60 FROM t_se a, (SELECT event, total_waits, total_timeouts, time_waited, time FROM system_events WHERE database = 'BISP.emea.fedex.com' AND time = (SELECT MAX(time) FROM system_events WHERE database = 'BISP.emea.fedex.com')) b WHERE a.event = b.event AND a.database = 'BISP.emea.fedex.com' "] at ./.dbreporter/system_events2.pl line 34. Can't call method "execute" on an undefined value at ./.dbreporter/system_events2.pl line 55. ############################################ #### script #! /usr/local/bin/perl -w ############################################################################ ############## # Script: system_events2.pl # # Task: this script is called by system_events.pl and connects to one database to # # pull out system_events related statistics. # ############################################################################ ############## # the DBI drivers are loaded use DBI; # make sure the ORACLE settings are visible from within the script $ENV{"ORACLE_HOME"} = "/opt/oracle/product/9.2"; # handle the input (ORACLE_SID) $SID = $ARGV[0]; # open the connection to the monitored and monitoring databases $dbh1 = DBI->connect("dbi:Oracle:$SID", "dbreporter", "reporter1"); $dbh2 = DBI->connect("dbi:Oracle:DBSTATS", "dbreporter", "reporter1"); # extract the data from v_$system_event $sth1 = $dbh1->prepare("SELECT event, total_waits, total_timeouts, time_waited, average_wait FROM sys.v_\$system_event "); $sth1->execute(); while ( ($event, $total_waits, $total_timeouts, $time_waited,$average_wait) = $sth1->fetchrow_array) { # insert the data into T_SE $sth2 = $dbh2->do("INSERT INTO t_se VALUES('$SID', '$event', $total_waits, $total_timeouts, $time_waited, $average_wait) "); } *************************************************************** $sth1->finish(); *************************************************************** # insert the data into SYSTEM_EVENTS $sth1 = $dbh1->do("INSERT INTO system_events SELECT a.database, sysdate, a.event, a.total_waits, a.total_timeouts, a.time_waited/100, a.average_wait, (a.total_waits - b.total_waits), (a.total_timeouts - b.total_timeouts), (a.time_waited/100 - b.time_waited), (sysdate - b.time)*24*60*60 FROM t_se a, (SELECT event, total_waits, total_timeouts, time_waited, time FROM system_events WHERE database = '$SID' AND time = (SELECT MAX(time) FROM system_events WHERE database = '$SID')) b WHERE a.event = b.event(+) AND a.database = '$SID' "); $sth1->execute(); # delete the records older than the number of days specified in DATABASE_SETTINGS $sth2 = $dbh2->do("DELETE FROM system_events WHERE database = '$SID' AND time < (SELECT sysdate - sysevents_days_keep FROM database_settings WHERE database = '$SID') "); exit;
