-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 23, 2006 2:40 PM
To: DBI-Users
Subject: how to invoke .sql file from dbi
>
> Hello all,
>
> We have bunch of sql files ( .sql ) that we want to invoke from dbi (
> Oracle ). How does it work. Dbi expects actual sql statements to be
> given as it's arguments whether in case of do() or prepare() methods.
> I wonder is there any option to just point the dbi to a sql file and
> dbi taking care of it.
No, there isn't.
> I do understand that running sql file is sqlplus feature and may not
> be applicable to dbi, as dbi is more generic. We have hundreds of sql
> files to be run like this. Each sql file has multiple sql statements
> within it.
>
> I know these are my options..
>
> 1) Parse sql files and assign each sql statement to string variable
> and pass the string as an argument to do(). File handler routines
> may come in handy for this.
This is undesirable if your SQL files have anything other than VERY
simple
SQL. For example, if your SQL files contain semicolons embedded in
quotes,
your parser will have to handle that. Also, if the files are written
specifically for sqlplus, they may contain directives to sqlplus (as
opposed to
the database) -- bind variables are one example.
> 2) Do not use DBI. Instead call 'sqlplus' from perl itself with file
> as input parameter.
This is probably both safest and easiest. Rather than spawning a new
sqlplus for every sql file, though, you could just open a pipe to
sqlplus and
feed it the files from within perl, e.g.
use File::Copy;
open(my $SQLPLUS, '|sqlplus user/[EMAIL PROTECTED]') or die $!;
foreach (@files) {
copy($_, $SQLPLUS);
}
close($SQLPLUS);
hth,
Philip
