First off, if you are reading this you have already given some of your time to help solve the problem. Therefore, thank you.
Secondly, to put to give you some background of my Perl knowledge, I know enough to be dangerous but not enough to be known as an expert or Perl developer for that matter. So please don't judge too harshly if you see an obvious mistake. Also you may have to explain something a little more than usual.
I have a script that forks process to create multiple tables at once. The script gets the DDL needed for a table and then runs a $dbh->do command. The script works well for the most part. However, there are 4 tables out of 5000 that consistently fail. No error string is in $dbh->errstr, $dbh->err, or DBI::errstr. My connect code is:
my $dbh = DBI->connect("$connection", "$user", "$pass",
{PrintError => 0,
PrintWarn => 0,
RaiseError => 1,
LongReadLen => 2000000100, # add 100 to the longest possible long
+, as per DBI man page
ora_module_name => $base_scriptname # set module name for this s
+ession to the name of this script.
})
or error_check('N','Y','N',"Could Not Connect To Database: ". $DBI
+::errstr);
my do script is:
$rows = $source_dbh->do($ctas_sql) or {
print sample_table_log "Create table failed at " . get_date()
+. "\n" and
print sample_table_log "Value for rows is: [$rows]\n" and
print sample_table_log "\nCannot run $ctas_sql \n" and
print sample_table_log 'DBI::errstr = ' . DBI::errstr . "\n" a
+nd
print sample_table_log '$source_dbh->errstr = ' . $source_dbh-
+>errstr . "\n" and
print sample_table_log '$source_dbh->err = ' . $source_dbh->er
+r . "\n" and
update_sample_table_driver_tb($t_owner,$t_name,'FAILED') and
exit 1
};
I am in debugging mode right now, that is why you see so many print error strings. Here is the log generated:
Create table failed at 09/18/14 10:58:07
Value for rows is: []
Cannot run
CREATE TABLE "sampler_user"."table"
SEGMENT CREATION IMMEDIATE
PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 214748364
+5
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "S_BCM_DATA"
PARALLEL 32
as
select a.* from BCM.RESPONSE_CAMP_TB a
DBI::errstr =
$source_dbh->errstr =
$source_dbh->err =
However, i can go into the database and see the table created. My biggest question is, why does the script run fine for all the other tables but not for these. I can run the sql in sqlplus just fine as well in Toad.
I just ran this outside of my sampler script. Here is a full code that you can test with:
#!/usr/bin/perl
use strict;
use DBI;
use Getopt::Long;
use File::Basename;
require "/home/fdsprod/bin/setoraenv.pl";
###build connection strings
my $HOST=`hostname`;
my $sid="$ENV{ORACLE_SID}";
my $source_connection_string=qq(dbi:Oracle:host=$HOST;sid=$sid);
my $source_dbh = connect_to_db($source_connection_string);
my $rows;
create_table();
######################################################################
+###############
# Connect To the source Database.
######################################################################
+###############
sub connect_to_db {
my $errormessage;
# get the base filename for this script
my $base_scriptname = basename($0);
my $user = 'sampler_fdsglobal';
my $pass = 'sample';
# Just connect to the db.
my $connection = $_[0];
my $dbh = DBI->connect("$connection", "$user", "$pass",
{PrintError => 0,
PrintWarn => 0,
RaiseError => 1,
LongReadLen => 2000000100, # add 100 to the longest possible long
+, as per DBI man page
ora_module_name => $base_scriptname # set module name for this s
+ession to the name of this script.
})
or error_check('N','Y','N',"Could Not Connect To Database: ". $DBI
+::errstr);
return $dbh;
}
######################################################################
+###############
# Connect To the source Database.
######################################################################
+###############
sub create_table{
my $t_owner = 'BCM';
my $t_name = 'RESPONSE_CAMP_TB';
my $sample_tblspc = 'S_BCM_DATA';
my $t_multi = 'NO';
my $t_condition = '';
my ($sql, $sth, $rows);
my $ctas_sql;
##get ddl for table. The below will remap to new schema and table
+s
$sql = qq(select SYS.sampler_return_ddl_test(upper('$t_owner'),upp
+er('$t_name'),'NO','$sample_tblspc') FROM DUAL);
$sth = $source_dbh->prepare($sql) or {
print "Prepare Error: $sql \n" . $source_dbh->errstr . "\n" an
+d
exit 1
};
$sth->execute() or {
print "Execute Error: $sql \n". $source_dbh->errstr ." \n" and
+
exit 1
};
$ctas_sql = $sth->fetchrow_array();
##send email failure if $ctas is null.
if (!$ctas_sql){
error_check('N','Y','N',"Create table as sql varabie is null\n
+")
};
##in oder to run a 'create table as' statement. you must delete t
+he column definitions
##the below regular expression deletes the first instance of a par
+enthesis group and its contents,
##which is the column definition when pulled from dbms_metadata.
+The dbms_metadata is the
##foundation of the sys.sampler_return_ddl function. DO NOT DELET
+E THE FOLLOWING REGULAR EXPRESSION
$ctas_sql =~ s/\((?>[^()]|(?R))*\)//;
$ctas_sql = $ctas_sql . qq(\nas \nselect a.* from $t_owner.$t_name
+ a $t_condition);
print "Begin Create table at ". get_date() ."\n\n";
print "Value for ctas_sql is:\n$ctas_sql\n\n\n";
$rows = $source_dbh->do($ctas_sql) or {
print "Create table failed at " . get_date() . "\n" and
print "Value for rows is: [$rows]\n" and
print "\nCannot run $ctas_sql \n" and
print 'DBI::errstr = ' . DBI::errstr . "\n" and
print '$source_dbh->errstr = ' . $source_dbh->errstr . "\n" a
+nd
print '$source_dbh->err = ' . $source_dbh->err . "\n" and
print '$! = ' . $! . "\n" and
exit 1
};
print "Completed create table at ". get_date() ." Rows Created = [
+$rows]\n";
#if(defined $rows){
# print sample_table_log "Completed create table at ". get_date
+() ." Rows Created = [$rows]\n";
#}
#else {
# print sample_table_log "\nCannot run $ctas_sql \n". $source_d
+bh->errstr;
# update_sample_table_driver_tb($t_owner,$t_name,'FAILED');
# exit 1;
#}
}
######################################################################
+###############
# Get Date
+ #
######################################################################
+###############
sub get_date {
# Needed an easy way to get the system date.
my $tmp_date= `/usr/bin/date "+%D %T"`;
chomp($tmp_date);
return $tmp_date;
}