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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 <[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. 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.
