Hi all
Here looking for a little help with a script that has got me confused
The purpose of the script is to basically log on to a number of databases with a number of different schema owners and create a stats table containing the row count of all tables under that schema owner and the print the records of that stats table to a file
The script is to then go through each table and print either all records if the table has 500 or less rows, or it should work out the middle 500 rows and print them, to an extract file
This seems to be what is happening, except there are number of errors written to the screen when the script is run
I will first post the code of the script
#!/usr/local/bin/perl -w use strict; use DBI; # for database connectivity my $rv; # Hash for DB connectivity my %db_conn = ( A_MANAGER =>['DBI:Oracle:host=ahostname;sid=ADB01;port=5555',' +a_manager','a_manager',], B_MANAGER =>['DBI:Oracle:host=ahostname;sid=BDB01;port=5555',' +b_manager','b_manager',], ); # Loop for each db schema owner in the db conn hash to run each query +against for my $owner (keys %db_conn) { # File Handle for STATS_TABLE output for each owner open(FH, ">STATS_TABLE_${owner}_output") or die "\n cannot write to the file output file. Check the file permi +ssions"; # Array of query names to order query hash my @querynum = ("query1","query2","query3","query4"); # Loop for each query foreach my $query (@querynum) { # Interactive print to inform user which query is being perfor +med print "Performing $query \n"; # Hash of all queries my %querylist = ( query1 => "BEGIN EXECUTE IMMEDIATE 'DROP TABLE STATS_TABLE_$owner'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;", query2 => "CREATE TABLE STATS_TABLE_$owner (TABLE_NAME VARCHAR2(50), OWNER VARCHAR2(20), RECORD_COUNT NUMBER) TABLESPACE STS_DAT", query3 =>"declare v_count integer; begin for r in (select table_name, owner from all_tables where owner ='$owner' and table_name <> 'STA +TS_TABLE') loop execute immediate 'select count(*) from ' || r.table_n +ame into v_count; INSERT INTO STATS_TABLE_$owner(TABLE_NAME,OWNER,RECORD +_COUNT) VALUES (r.table_name, r.owner, v_count); end loop; end;", query4 => "select table_name, record_count from STATS_T +ABLE_$owner" ); # Assigning the sql query referenced in the query hash my $sql = $querylist{$query}; # Assigning the db conn details from the db conn hash relevant + to the schema owner currently being exercised my @data_source = @{ $db_conn{$owner} }; ##print "@data_source\n"; # Performing database connection my $dbh = DBI->connect(@data_source) or die "Couldn't connect +to database: " . DBI->errstr; # Perfroming the prepare of the current query my $sth = $dbh->prepare($sql) or die "Couldn't prepare statement: " . $dbh->errs +tr; # Executing the current query $sth->execute() or die "Couldn't execute statement: " . $sth-> +errstr; # If the current query is query 4 then do this additional proc +essing if ($query eq "query4") { # Declaring variables for columns my ($tb_name, $rec_count); # Bind Perl variables to columns: $rv = $sth->bind_columns(\$tb_name, \$rec_count); # Column binding to fetch data while ($sth->fetch) { # File handle for data extract open(FHExtract, ">${tb_name}_extract") or die "\n cannot write to the file output file. Check th +e file permissions"; # If the table being queried has a row count greater t +han 500 rows then we want to grab the middle 500 rows if ($rec_count > 500) { # We use int as our rounding method. This is not t +rue rounding though as it only drops the decimal my $min_rec_count = int($rec_count/2-250); my $max_rec_count = int($rec_count/2+250); # Defining a variable with the required select to +guarantee the order based on the index of the table my $extractsql = "select * from ( select a.*, ROWNUM rnum from ( select * from $tb_name order by (select column_name from user_ind_columns + where TABLE_NAME='$tb_name' and COLUMN_POSITION='1') ) a where ROWNUM <= $max_rec_count) where rnum >= $min_rec_count"; # Performing the prepare of the query my $sth1 = $dbh->prepare($extractsql) or die "Couldn't prepare statement: " . $dbh->errs +tr; # Executing the query $sth1->execute() or die "Couldn't execute statemen +t: " . $sth->errstr; # Declaring the ref variable to process the output my $ref; # Looping through each row of the return and print +ing it to earlier defined file handle for data extract while($ref = $sth1->fetchrow_hashref) { print FHExtract join (", ", values %$ref), "\n +"; # Closing the statement handler $sth1->finish; } } # If there are not over 500 rows then do this processi +ng, i.e. select all rows else { # Defining a variable with the required select my $extractsql1 = "select * from $tb_name"; # Performing the prepare of the query my $sth2 = $dbh->prepare($extractsql1) or die "Couldn't prepare statement: " . $dbh->errs +tr; # Executing the query $sth2->execute() or die "Couldn't execute statemen +t: " . $sth->errstr; # Declaring the ref1 variable to process the outpu +t my $ref1; # Looping through each row of the return and print +ing it to the earlier defined file handle for data extract while($ref1 = $sth2->fetchrow_hashref) { print FHExtract join (", ", values %$ref1), "\ +n"; # Closing the statement handler $sth2->finish; } # Closing the file handler for data extract close FHExtract; } # Printing the contents of the stats table to the earlier +defined file handle for the stats table contents print FH "$tb_name: $rec_count\n"; } # Closing the statement handler $sth->finish; } # Disconnecting from the database $dbh->disconnect if defined($dbh); } # Closing the file handler for stats table contents close FH; }
These are the errors (an example of)
DBD::Oracle::st fetchrow_hashref failed: ERROR no statement executing +(perhaps you need to call execute first) [for Statement "select * fro +m STS_VCODE"] at dbmigration.pl line 151. Use of uninitialized value $ref in join or string at dbmigration.pl li +ne 124. DBD::Oracle::st fetchrow_hashref failed: ERROR no statement executing +(perhaps you need to call execute first) [for Statement "select * fro +m ( select a.*, ROWNUM rnum from ( select * from M_AUDIT_ACK order by (select column_name f +rom user_ind_columns where TABLE_NAME='M_AUDIT_ACK' and COLUMN_POSITI +ON='1') ) a where ROWNUM <= 1203) where rnum >= 703"] at dbmigr +ation.pl line 127. DBD::Oracle::st fetchrow_hashref failed: ERROR no statement executing +(perhaps you need to call execute first) [for Statement "select * fro +m FI_S_D_DATA"] at dbmigration.pl line 151. Use of uninitialized value $ref in join or string at dbmigration.pl li +ne 124. DBD::Oracle::st fetchrow_hashref failed: ERROR no statement executing +(perhaps you need to call execute first) [for Statement "select * fro +m ( select a.*, ROWNUM rnum from ( select * from PENDING order by (select column_name f +rom user_ind_columns where TABLE_NAME='PENDING' and COLUMN_POSITION=' +1') ) a where ROWNUM <= 5723) where rnum >= 5223"] at dbmig +ration.pl line 127. DBD::Oracle::st fetchrow_hashref failed: ERROR no statement executing +(perhaps you need to call execute first) [for Statement "select * fro +m ST_SEC_CODE"] at dbmigration.pl line 151. DBD::Oracle::st fetchrow_hashref failed: ERROR no statement executing +(perhaps you need to call execute first) [for Statement "select * fro +m PARTITIONLOG"] at dbmigration.pl line 151.
Any help greatly appreciated. Thanks Chris
In reply to Perl script to check database works or does it? by chris01010
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |