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 session to the name of this script.
    })
    or error_check('N','Y','N',"Could Not Connect To Database: ". $DBI::errstr);

##</code><code>##

$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" and
		print sample_table_log '$source_dbh->errstr = ' . $source_dbh->errstr . "\n" and
		print sample_table_log '$source_dbh->err = ' . $source_dbh->err . "\n" and
		update_sample_table_driver_tb($t_owner,$t_name,'FAILED') and
		exit 1
	};

##</code><code>##

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

##</code><code>##

#!/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 session 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 tables
	
	$sql = qq(select SYS.sampler_return_ddl_test(upper('$t_owner'),upper('$t_name'),'NO','$sample_tblspc') FROM DUAL); 
		
	$sth = $source_dbh->prepare($sql) or {
		print "Prepare Error: $sql \n" . $source_dbh->errstr . "\n" and  
		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 the column definitions
	##the below regular expression deletes the first instance of a parenthesis 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 DELETE 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" and
		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_dbh->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;

}