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@xxxxxxx [mailto:Ravi.Kongara@xxxxxxx]
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
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 <Ravi.Kongara@xxxxxxx>/* 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
<http://us.rd.yahoo.com/evt=40762/*http://www.yahoo.com/preview>. It's

not radically different. Just radically better.


This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
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.

.

Relevant Pages

  • RE: Newline inserted?
    ... Are you processing individual SQL statements (does your file contain SQL ... DBI is not your best bet to do this. ... When I attempt to load this file into Oracle 10g using the DBI I get the ...
    (perl.dbi.users)
  • Re: how to invoke .sql file from dbi
    ... The sql statements are SELECT statements and we are expecting plain output from these statements. ... But it is little surprising that dbi doesn't support executing sql files directly. ... 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. ...
    (perl.dbi.users)
  • RE: how to invoke .sql file from dbi
    ... Dbi expects actual sql statements to be given as it's ... to just point the dbi to a sql file and dbi taking care of it. ... sender of the delivery error by replying to this message, or notify us by ...
    (perl.dbi.users)
  • how to invoke .sql file from dbi
    ... to just point the dbi to a sql file and dbi taking care of it. ... sql file is sqlplus feature and may not be applicable to dbi, ... Each sql file has multiple sql statements within it. ...
    (perl.dbi.users)
  • RE: how to invoke .sql file from dbi
    ... how to invoke .sql file from dbi ... Each sql file has multiple sql statements ... specifically for sqlplus, they may contain directives to sqlplus (as ...
    (perl.dbi.users)
Quantcast