in reply to Multiple sql statements in one sql file

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

[download]

...roboticus

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

Replies are listed 'Best First'.
Re^2: Multiple sql statements in one sql file
by leo_jeo (Initiate) on Nov 29, 2011 at 04:31 UTC

    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 ?

[reply]
[d/l]
[select]

      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]

        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]

        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]

In Section Seekers of Perl Wisdom