Re^2: Multiple sql statements in one sql file

Thanks Roboticus, you got the nerve "It also contains commands that sqlplus uses to format the output." I tried many ways, two popular one are using DBI and DBIx::MultiStatementDo but still no results. Example using DBI

$dbh=DBI->connect("dbi:Oracle:$xmlArray[$flagArray[$outerCounter]][0]"
+,"$xmlArray[$flagArray[$outerCounter]][1]","$xmlArray[$flagArray[$out
+erCounter]][2]",{AutoCommit=>0}) or die "Can't log in: $!"
 open FILE, "<defaultOutput.sql";
           my $file_contents = do { local $/; <FILE> };
printf "\nFinal sql :: %s \n",$file_contents ;
$sth=$dbh->prepare($file_contents) or die "$DBI::errstr";
$sth->execute or die "Can't execute sth: $DBI::errstr.";
while (($mycolumn)=$sth->fetchrow_array)
{   
  print "\n$mycolumn\n" if defined $mycolumn;
}
[download]

Example using DBIx::MultiStatementDo;

$dbh=DBI->connect("dbi:Oracle:$xmlArray[$flagArray[$outerCounter]][0]"
+,"$xmlArray[$flagArray[$outerCounter]][1]","$xmlArray[$flagArray[$out
+erCounter]][2]",{AutoCommit=>0}) or die "Can't log in: $!"
 open FILE, "<defaultOutput.sql";
           my $file_contents = do { local $/; <FILE> };
printf "\nFinal sql :: %s \n",$file_contents ;
            my $batch = DBIx::MultiStatementDo->new( dbh => $dbh );
            my @results = $batch->do( $file_contents ) or die $batch->
+dbh->errstr;
print scalar(@results) . ' statements successfully executed!';
$dbh->disconnect;
[download]

Please let me know where I am doing wrong ? What I should be doing ?

Comment on Re^2: Multiple sql statements in one sql file Select or Download Code

Replies are listed 'Best First'.
Re^3: Multiple sql statements in one sql file by roboticus (Chancellor) on Nov 29, 2011 at 10:44 UTC
leo_jeo: What are you using for the input file, and what error message(s) are you getting when you run it. (I don't use DBIx, so I'm concentrating on the first one.) If you're using the same file from the original post, you'll need to strip out the non-SQL bits before trying to run it. ...roboticus When your only tool is a hammer, all problems look like your thumb.	[reply]
Re^4: Multiple sql statements in one sql file by leo_jeo (Initiate) on Nov 30, 2011 at 16:26 UTC
Thanks roboticus !! This guidance "If you're using the same file from the original post, you'll need to strip out the non-SQL bits before trying to run it." worked like a charm. Thanks a lot,I am good now.	[reply]
Re^4: Multiple sql statements in one sql file by leo_jeo (Initiate) on Dec 06, 2011 at 04:48 UTC
Thanks Roboticus, this resolved the problem. I removed the sqlplus related code and handled LOB and did the text operations in perl and bang I got the result. Thanks a lot!!	[reply]