in reply to how could i insert data from a file to oracle database

The command line Oracle client is 'sqlplus' (check in $ORACLE_HOME for location). You can call it and pass your SQL file like so: 
$ sqlplus <YOUR CONNECT STRING> @file.sql

[download]
To exit back out, put an QUIT; at the end of your SQL file. You can also run sqlplus interactively and just source in your SQL file, as well. See ORA FAQ for more ideas.

---
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^2: how could i insert data from a file to oracle database
by koleti (Novice) on Nov 09, 2007 at 17:25 UTC
    i want to call the file containg sql statements from perl script
    if i do it individually it worked
    #!/usr/bin/perl
    use DBI;
    $user = "xyz";
    $passwd = "somethingortheother";
    $dbh = DBI-> connect("dbi:Oracle:host=15161.1m1;sid=anaa;port=1521", $user, $passwd)
    or die "Couldn't connect to database: " . DBI-> errstr; $c = "insert into xyz values ('a100','sa00230')";
    $dbh->do($c);
    but if could not open a file and do it.
[reply]
      Is there a particular reason to make your life harder than it has to be?

      Anyway, DBI generally allows only one statement per do or prepare.

      So you have to break it up into statements, for example with a SQL Parser

[reply]
[d/l]
[select]
      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.

[reply]
[d/l]
        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]

In Section Seekers of Perl Wisdom