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 <[EMAIL PROTECTED]>/* 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.
