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

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');
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.
can anyone help me
  • Comment on how to import sql file into oracle database.

Replies are listed 'Best First'.
Re: how to import sql file into oracle database.
by jZed (Prior) on Nov 13, 2007 at 19:15 UTC
    Try something along the lines of this (assumes SQL statements are terminated by semicolon+newline) 
    ...
open( SQL, 'somefile') or die $!;
my @statements = split(/;\n/,join('',<SQL>));
for my $stmt( @statements ){
    $dbh->do($_);
}

    [download]
[reply]
[d/l]
Re: how to import sql file into oracle database.
by apl (Monsignor) on Nov 13, 2007 at 20:08 UTC
    What you're showing us is not what is running. Could you show us the code that "could not open a file and do it", and the error that was returned?
[reply]
      #!/usr/bin/perl
      use DBI;
      $user = "akoleti";
      $passwd = "somethingortheother";
      $dbh = DBI-> connect("dbi:Oracle:host=172.31.0.87;sid=dola;port=1521", $user, $passwd) or die "Can't connect to database $DBI::errstr\n";
      open( SQL, "gene_enzyme.txt") or die $!;
      my @statements = <SQL>;
      foreach my $stmt( @statements ){
      $dbh->do($_); }
      error
      DBD::Oracle::db do failed: ORA-24373: invalid length specified for statement (DB D ERROR: OCIStmtPrepare) at oracle1.pl line 12, <SQL> line 3118.
[reply]

        Here's your problem: 

        foreach my $stmt( @statements ){
$dbh->do($_); }

        [download]

        There are a couple of things wrong. First, your code is trying to execute the SQL one line at a time. So the first SQL command you are running is: 

        Create table xyz (

        [download]

        which generates the error you're seeing. jZed's advice on splitting on semi-colons and re-joining should work.

        Also, you're naming your variable for the loop but then you are referencing $_, which may or may not work correctly. 

        perl -e 'split//,q{john hurl, pest caretaker}and(map{print @_[$_]}(joi
+n(q{},map{sprintf(qq{%010u},$_)}(2**2*307*4993,5*101*641*5261,7*59*79
+*36997,13*17*71*45131,3**2*67*89*167*181))=~/\d{2}/g));'

        [download]
[reply]
[d/l]
[select]
      #!/usr/bin/perl
      use DBI;
      $user = "akoleti";
      $passwd = "something";
      $dbh = DBI-> connect("dbi:Oracle:host=172.31.0.87;sid=dola;port=1521", $user, $passwd) or die "Can't connect to database $DBI::errstr\n";
      open( SQL, "gene_enzyme.txt") or die $!;
      my @statements = <SQL>;
      foreach my $stmt( @statements ){
      $dbh->do($_); }
      error
      DBD::Oracle::db do failed: ORA-24373: invalid length specified for statement (DB D ERROR: OCIStmtPrepare) at oracle1.pl line 12, <SQL> line 3118.
[reply]

Back to Seekers of Perl Wisdom