koleti has asked for the wisdom of the Perl Monks concerning the following question:

I have a file containing sql statements how could i load that file into oracle database.
my file is like this 
Create table xyz ( 

abc     (VARCHAR2(64))     NOT NULL 

def (VARCHAR2(150))     NOT NULL 

CONSTRAINT xyz_pk PRIMARY KEY (acb)); 

insert into xyz ('a:100','sa00230'); 

insert into xyz('a:1000','sa04514');

insert into xyz ('a:10000','a04010');

insert into xyz ('a:10000','a04012');

[download]

20071125 Janitored by Corion: Added formatting, code tags, as per Writeup Formatting Tips

Replies are listed 'Best First'.
Re: how could i insert data from a file to oracle database
by moritz (Cardinal) on Nov 09, 2007 at 16:51 UTC
    Most DBMs come with a command line client that can execute SQL statements. You can just pipe your file into that. (Sorry, don't know how it's called for oracle).
[reply]
Re: how could i insert data from a file to oracle database
by tuxz0r (Pilgrim) on Nov 09, 2007 at 17:05 UTC
    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.

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

Back to Seekers of Perl Wisdom