Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Multiple sql statements in one sql file

by leo_jeo (Initiate)
on Nov 28, 2011 at 10:27 UTC ( #940335=perlquestion: print w/ replies, xml ) Need Help??
leo_jeo has asked for the wisdom of the Perl Monks concerning the following question:

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

Comment on Multiple sql statements in one sql file
Select or Download Code
Re: Multiple sql statements in one sql file
by marto (Chancellor) on Nov 28, 2011 at 10:48 UTC

    "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).

Re: Multiple sql statements in one sql file
by roboticus (Canon) on Nov 28, 2011 at 10:51 UTC

    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.

      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 ?

        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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://940335]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (4)
As of 2014-08-30 11:34 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (293 votes), past polls