in reply to Re^2: how could i insert data from a file to oracle database
in thread how could i insert data from a file to oracle database

If the operations are related and need to be "atomic", then running them individually but as a transaction is a good way to go. Turn off autocommit ($dbh{AutoCommit} => 0, and then run each statement following them with a call to $dbh->commit;. You'll need to check $@ to see if something went wrong, and if so, perform a rollback $dbh->rollback to back out all the data that did go in the transaction.

On the other hand, you can also put multiple statements in a single scalar and have DBI execute that as well, e.g. 

{
local $/;
open SQLFILE, "< file.sql" or die "Can't open file.sql: $!";
$stmts = <SQLFILE>;
close SQLFILE;
}


eval {
    # Turn off autocommit, keep from dying on erros (but print them)
    $dbh{AutoCommit} => 0; 
    $dbh{RaiseError} => 0; 
    $dbh{PrintError} => 1;
    $dbh->do($stmts);
    $dbh->commit;
};
if ($@) {
    print "Error: $@\n $DBI::errstr\n";
    $dbh->rollback;
}

[download]
Just make sure each statement ends in a ';' (semicolon).

---
echo S 1 [ Y V U | perl -ane 'print reverse map { $_ = chr(ord($_)-1) } @F;'
Warning: Any code posted by tuxz0r is untested, unless otherwise stated, and is used at your own risk.

Replies are listed 'Best First'.
Re^4: how could i insert data from a file to oracle database
by johngg (Canon) on Nov 09, 2007 at 22:59 UTC
    It might be worth taking advantage of the three-argument open, lexically scoped filehandles and the automatic closure of same when they go out of scope. So instead of your

    {
local $/;
open SQLFILE, "< file.sql" or die "Can't open file.sql: $!";
$stmts = <SQLFILE>;
close SQLFILE;
}

    [download]

    you could do

    my $stmts = do
   {
       local $/;
       open my $sqlFH, q{<}, q{file.sql} or die qq{open: file.sql: $!\
+n};
       <$sqlFH>;
   };

    [download]

    I hope this is of interest.

    Cheers,

    JohnGG

[reply]
[d/l]
[select]
      it did not worked can you suggest any other way.
[reply]
        What specifically did not work? Can you post some output and the code you tried? I'd be glad to look it over.

        ---
        echo S 1 [ Y V U | perl -ane 'print reverse map { $_ = chr(ord($_)-1) } @F;'
        Warning: Any code posted by tuxz0r is untested, unless otherwise stated, and is used at your own risk.

[reply]

In Section Seekers of Perl Wisdom