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. 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.
2) Do not use DBI. Instead call 'sqlplus' from perl itself with file as
input parameter.

Did anyone come across this. Is there any simple way to do this.

Thanks,
Ravi

RE: how to invoke .sql file from dbi

-----Original Message-----
From: Ravi.Kongara@Sun.COM [mailto:Ravi.Kongara@Sun.COM]=20
Sent: Tuesday, May 23, 2006 2:40 PM
To: DBI-Users
Subject: how to invoke .sql file from dbi
>=20
> Hello all,
>=20
> 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/pass@dsn') or die $!;
foreach (@files) {
copy($_, $SQLPLUS);
}
close($SQLPLUS);

hth,
Philip

RE: how to invoke .sql file from dbi

use Expect;

-----Original Message-----
From: Ravi.Kongara@Sun.COM [mailto:Ravi.Kongara@Sun.COM]=20
Sent: Tuesday, May 23, 2006 12: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 (=20
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=20
there any option
to just point the dbi to a sql file and dbi taking care of it. I do=20
understand that running
sql file is sqlplus feature and may not be applicable to dbi, as dbi is=20
more generic. We have
hundreds of sql files to be run like this. Each sql file has multiple=20
sql statements within it.

I know these are my options..

1) Parse sql files and assign each sql statement to string variable and=20
pass the string as an argument to do().
File handler routines may come in handy for this.
2) Do not use DBI. Instead call 'sqlplus' from perl itself with file as=20
input parameter.

Did anyone come across this. Is there any simple way to do this.

Thanks,
Ravi

This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

RE: how to invoke .sql file from dbi

Philip,

This is an excellent idea. I created a sub-routine to parse out the sql
file in to a string like Ravi had suggested like the following. It is
ugly, but has been working for awhile.

sub sub_readSQLFile($) {
print STDERR "\n***** sub_readSQLFile() *****\n\n";
my ($sqlFile) =3D @_;
print STDERR "SQL FILE IS: $sqlFile\n";
my ($stmtType, $sqlString);
unless (open(FH, "$sqlFile")) {
$MESSAGE =3D "sub_readSQLFile() - Unable to open SQL file
$sqlFile.";
$STATUS =3D $FAILURE;
sub_exit();
}
while () {
chomp;
s/--.*$//;
next if /^\//;
next if /^\s*spool/i;
next if /^\s*--/;
next if /^\s*$/;
next if /^\s*exit/i;
next if /^\s*quit/i;
$sqlString .=3D " $_";
}
$sqlString =3D~ s/^\s+//;
$sqlString =3D~ s/\s+/ /g;
$sqlString =3D~ s/(\s+),/,/g; # <===3D Newly added
$sqlString =3D~ s/(\s)*;(\s)*$//;
close(FH);
$stmtType =3D "insert" if $sqlString =3D~ /^\s+insert/i;
$stmtType =3D "select" if $sqlString =3D~ /^\s+select/i;
$stmtType =3D "delete" if $sqlString =3D~ /^\s+delete/i;
$stmtType =3D "update" if $sqlString =3D~ /^\s+update/i;
$stmtType =3D "plsql" if $sqlString =3D~ /^\s+begin/i;
print STDERR "RETURNING STATEMENT TYPE IS: $stmtType\n";
print STDERR "RETURNING SQL STRING IS: $sqlString\n\n";
return($stmtType, qq{$sqlString});
} #<===3D (End of sub_readSQLFile)=20


=20
Peter Loo
Wolters Kluwer Health
(602) 381-9553

-----Original Message-----
From: Garrett, Philip (MAN-Corporate)
[mailto:Philip.Garrett@manheim.com]=20
Sent: Wednesday, May 24, 2006 7:12 AM
To: DBI-Users
Subject: RE: how to invoke .sql file from dbi

-----Original Message-----
From: Ravi.Kongara@Sun.COM [mailto:Ravi.Kongara@Sun.COM]
Sent: Tuesday, May 23, 2006 2:40 PM
To: DBI-Users
Subject: how to invoke .sql file from dbi
>=20
> Hello all,
>=20
> We have bunch of sql files ( .sql ) that we want to invoke from dbi (=20
> Oracle ). How does it work. Dbi expects actual sql statements to be=20
> 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=20
> dbi taking care of it.

No, there isn't.

> I do understand that running sql file is sqlplus feature and may not=20
> be applicable to dbi, as dbi is more generic. We have hundreds of sql=20
> files to be run like this. Each sql file has multiple sql statements=20
> 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/pass@dsn') or die $!;
foreach (@files) {
copy($_, $SQLPLUS);
}
close($SQLPLUS);

hth,
Philip


This E-mail message is for the sole use of the intended recipient(s) and
may contain confidential and privileged information. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not
the intended recipient, please contact the sender by reply E-mail, and
destroy all copies of the original message.


This E-mail message is for the sole use of the intended recipient(s) and =
may contain confidential and privileged information. Any unauthorized =
review, use, disclosure or distribution is prohibited. If you are not =
the intended recipient, please contact the sender by reply E-mail, and =
destroy all copies of the original message.

Re: how to invoke .sql file from dbi

Hi Miller,

The sql statements are SELECT statements and we are expecting plain ( no
formatting required) output from these statements.
They are static and expect input values for substitution.
Yes, i thought over converting all sqls to pl/sql but it is a overkill
for us.
Currently i sticked to sqlplus approach as it doesn't need much effort
from our side.
But it is little surprising that dbi doesn't support executing sql files
directly.
I haven't explored 'use Expect' or any other workarounds.

Thanks,
Ravi

Job Miller wrote On 05/24/06 05:58,:

> #2 is the no-brainer approach to this. SQL*Plus can do a lot for
> you. What do the SQL statements do. Are you expecting a printout of
> results formatted as SQL*PLus can format them. Are they just a bunch
> of inserts/updates or ddl?
>
> SQL*Plus is really the best approach for this, since it has error
> handling for sql scripts, formatting for output, and lots of other things.
>
> Is this something you are generating or just something you will be
> periodically executing?
>
> If it is pure SQL dml statements (insert/update) read in the file and
> wrap up the SQL in an anonymous pl/sql block and submit that at
> runtime or if it is really always static, create a procedure and just
> execute that. I suspect its not static, otherwise you wouldn't be
> asking about this.
>
>
> The anonymous PL/SQL block would be a way to avoid parsing all of it
> and executing it as individual statements, but still executing it via
> DBI without calling out to sql*plus (which is a perfectly valid option).
>
> Job
>
>
> */ravi kongara /* wrote:
>
> 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. 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.
> 2) Do not use DBI. Instead call 'sqlplus' from perl itself with
> file as
> input parameter.
>
> Did anyone come across this. Is there any simple way to do this.
>
> Thanks,
> Ravi
>
>
> ------------------------------------------------------------ ------------
> Sneak preview the all-new Yahoo.com
> . It's
> not radically different. Just radically better.

Re: how to invoke .sql file from dbi

ravi kongara wrote:
> But it is little surprising that dbi doesn't support executing sql files
> directly.

What you are calling "sql file" is actually an *Oracle SQLPlus file*.

Maybe DBD::Oracle could have a SQLPlus emulation mode, but even if
such a beast existed, it would certainly not be part of *DBI*.

Mark

Re: how to invoke .sql file from dbi

On Wednesday 24 May 2006 22:11, mark wrote:
> ravi kongara wrote:
> > But it is little surprising that dbi doesn't support executing sql files
> > directly.
>
> What you are calling "sql file" is actually an *Oracle SQLPlus file*.
>
> Maybe DBD::Oracle could have a SQLPlus emulation mode, but even if
> such a beast existed, it would certainly not be part of *DBI*.
>
> Mark


What I find surprising is the amount of DBAs who, driven by standard Oracle
tools' limitations, hear about this "powerful Perl/DBI thing", assuming that
their Oracle/shell script skills will be enough to handle the migration, and
then complaining and blaming DBI when something does not work the way they
expected it work... *sigh*

Re: how to invoke .sql file from dbi

mark wrote:
> ravi kongara wrote:
>> But it is little surprising that dbi doesn't support executing sql
>> files directly.
>
> What you are calling "sql file" is actually an *Oracle SQLPlus file*.
I just read about SQL::Library in O'Reilly's new Perl Hacks book (Hack
23. Build a SQL Library). I haven't used it, but it looks like it might
be helpful for the Ravi's situation.

--
Mike Schienle

RE: how to invoke .sql file from dbi

I came across this issue at a former client site years ago. I
recommended changing all the SQL*Plus scripts into DBI calls. This was
rejected to save $. That is when I stumbled across the Expect module.
I have used in many ways since and find its use indispensable.
Expect.pm and the expect utility are truly "glue" type utilities that
can help keep legacy apps from being rewritten.

rr

-----Original Message-----
From: Ravi.Kongara@Sun.COM [mailto:Ravi.Kongara@Sun.COM]=20
Sent: Wednesday, May 24, 2006 3:15 PM
To: Job Miller
Cc: DBI-Users
Subject: Re: how to invoke .sql file from dbi

Hi Miller,

The sql statements are SELECT statements and we are expecting plain ( no

formatting required) output from these statements.
They are static and expect input values for substitution.
Yes, i thought over converting all sqls to pl/sql but it is a overkill=20
for us.
Currently i sticked to sqlplus approach as it doesn't need much effort=20
from our side.
But it is little surprising that dbi doesn't support executing sql files

directly.
I haven't explored 'use Expect' or any other workarounds.

Thanks,
Ravi

Job Miller wrote On 05/24/06 05:58,:

> #2 is the no-brainer approach to this. SQL*Plus can do a lot for=20
> you. What do the SQL statements do. Are you expecting a printout of=20
> results formatted as SQL*PLus can format them. Are they just a bunch=20
> of inserts/updates or ddl?=20
> =20
> SQL*Plus is really the best approach for this, since it has error=20
> handling for sql scripts, formatting for output, and lots of other
things.
> =20
> Is this something you are generating or just something you will be=20
> periodically executing?=20
> =20
> If it is pure SQL dml statements (insert/update) read in the file and=20
> wrap up the SQL in an anonymous pl/sql block and submit that at=20
> runtime or if it is really always static, create a procedure and just=20
> execute that. I suspect its not static, otherwise you wouldn't be=20
> asking about this.
> =20
> =20
> The anonymous PL/SQL block would be a way to avoid parsing all of it=20
> and executing it as individual statements, but still executing it via=20
> DBI without calling out to sql*plus (which is a perfectly valid
option).
> =20
> Job
>
>
> */ravi kongara /* wrote:
>
> 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. 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.
> 2) Do not use DBI. Instead call 'sqlplus' from perl itself with
> file as
> input parameter.
>
> Did anyone come across this. Is there any simple way to do this.
>
> Thanks,
> Ravi
>
>
>
------------------------------------------------------------ ------------
> Sneak preview the all-new Yahoo.com=20
> . =
It's

> not radically different. Just radically better.=20


This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.