Multiple sql statements in one sql file

[leo_jeo]

Hi Monks, My problem is that I have to use a sql file,containing anonymous sql code,from one perl script.I tried DBI but still no avail.Can someone guide me to the proper documentation. Actually I have to port existing shell scripts to perl script,where shell script contains syntax like

`sqlplus -s userid/password@db<<EOF
start abc.sql parameters
EOF`

And the sql file contains syntax is

WHENEVER SQLERROR EXIT FAILURE
set pagesize 0 
column XXX format a200
    set feedback off
    set echo off 
    set verify off
    set termout off 
    set heading off
set trimspool on
set linesize 500
set long 32000
spool c:\\abc.txt 
select * from side_table 
/
spool off
set termout on

Issues are 1.I cannot spool file 2.I cannot handle clob 3.I cannot handle set commands Please guide

[roboticus]

leo_jeo:

There are several problems, first of which is that your SQL file doesn't just contain SQL. It also contains commands that sqlplus uses to format the output. It would be easier to help if you'd provided some of your DBI code that failed.

The interesting bits of your converted script should be something like:

my $ST = $DB->prepare('select * from side_table');
$ST->execute;
while (my $hr=$ST->fetchrow_hashref) {
    # Print formatted columns
    printf "% 8u ",   $$hr{FIRST_COLUMN};
    printf "%12.2f ", $$hr{COLUMN_2};
    ...etc...
    printf "%-200.200s\n", $$hr{XXX};
}

...roboticus

When your only tool is a hammer, all problems look like your thumb.

[leo_jeo]

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;
}

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;

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

[roboticus]

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.

[marto]

"I have to use a sql file,containing anonymous sql code,from one perl script"

For clarification, do you mean that the SQL in the file is unknown and you just want to run it from Perl? It looks as though you're trying to dump the contents of a table from oracle to a file. You could achieve this using DBI (see DBD::Oracle for a discussion of CLOBS).