how to import sql file into oracle database.

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]
Re^2: how to import sql file into oracle database. by Anonymous Monk on Nov 13, 2007 at 20:34 UTC
#!/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]
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*23074993,51016415261,75979 +36997,13177145131,3*26789167*181))=~/\d{2}/g));'` [download]	[reply] [d/l] [select]
Re^4: how to import sql file into oracle database. by koleti (Novice) on Nov 13, 2007 at 22:18 UTC
Re^5: how to import sql file into oracle database. by Anonymous Monk on Jun 04, 2008 at 02:09 UTC
Re^2: how to import sql file into oracle database. by Anonymous Monk on Nov 13, 2007 at 20:35 UTC
#!/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]


Think about Loose Coupling
	PerlMonks