in reply to Re: how to import sql file into oracle database.
in thread how to import sql file into oracle database.

#!/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.
  • Comment on Re^2: how to import sql file into oracle database.

Replies are listed 'Best First'.
Re^3: how to import sql file into oracle database.
by agianni (Hermit) on Nov 13, 2007 at 21:07 UTC

    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]
      this is the error i am facing now
      Placeholder :0 invalid, placeholders must be >= 1 at C:/Perl/site/lib/DBD/Oracle .pm line 262, <SQL> line 10009.
[reply]
        If your SQL has anything preceeded by a colon (eg :01) then the oracle interprets it as a placeholder, to be filled using bind calls of some description.
[reply]

In Section Seekers of Perl Wisdom