Re: how to invoke .sql file from dbi
- From: Ravi.Kongara@xxxxxxx (Ravi Kongara)
- Date: Wed, 24 May 2006 14:15:11 -0700
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.
.
- Follow-Ups:
- Re: how to invoke .sql file from dbi
- From: Mark
- Re: how to invoke .sql file from dbi
- Prev by Date: DBD::Sybase and $sth->{syb_result_type} v. DBD::ODBC (Sql Server)
- Next by Date: Re: problem with dbi module.
- Previous by thread: RE: how to invoke .sql file from dbi
- Next by thread: Re: how to invoke .sql file from dbi
- Index(es):
Relevant Pages
|
|
