DBI & Oracle Command Files

Ronnie has asked for the wisdom of the Perl Monks concerning the following question:

Hello cellmates. I've been using Perl for a while now instead of the blasphemous UNIX scripting with very good results. My latest venture into the realm of DBI/Oracle has brought me a tiny but thus far resolute problem. I can run Oracle Stored procedures from DBI and my own DBI queries but when I try and run a command file (ANY command file) the DBI prepares it fine but the execution fails with an ORA-0900. There are no SQL errors so it must be my method of calling the command file that is in error. I can find nothing in the excellent PERL DBI book to help but as I'm a complete novice to Oracle I'm not sure of my terminology and may not be looking in the correct places. By command file I mean a locally held file which contains valid SQL, ready for execution. A sample of my code is as follows :-

#
###########################################################
#         Connect to the DB                               #
###########################################################
#
$msg = "\n\tDBI - Failed to connect to $db_name" ;
$dbh = DBI->connect( "dbi:Oracle:$db_name", "$username", "$password", 
+\%attr) or
                    &end_it($mail_msg,$file_open,$to,$domain,$from,$su
+bject,$msg,$null,$logfile) ;
#
###########################################################
#         Update logfile                                  #
###########################################################
#
$msg = "\n\tDBI - User $username connected to $db_name." ;
print LOG "$msg" or
    &end_it($mail_msg,$file_open,$to,$domain,$from,$subject,$msg,$text
+,$logfile) ;
#
###########################################################
#         Prepare SQL                                     #
###########################################################
#
$msg = "\n\tDBI - Preparing SQL." ;
print LOG "$msg" or
    &end_it($mail_msg,$file_open,$to,$domain,$from,$subject,$msg,$text
+,$logfile) ;

$sth = $dbh->prepare( "\@OBFX001S") or
     &end_it($mail_msg,$file_open,$to,$domain,$from,$subject,$msg,$nul
+l,$logfile) ;
#
$msg = "\n\tDBI - prepared SQL." ;
print LOG "$msg" or
    &end_it($mail_msg,$file_open,$to,$domain,$from,$subject,$msg,$text
+,$logfile) ;
#
###########################################################
#         Execute the prepared SQL                        #
###########################################################
#
$msg = "\n\tDBI - Failed to execute SQL (OBFX001S)." ;
$sth->execute() or
    &end_it($mail_msg,$file_open,$to,$domain,$from,$subject,$msg,$null
+,$logfile) ;

$msg = "\n\tDBI - SQL (\@OBFX001S) executed." ;
print LOG "$msg" or
    &end_it($mail_msg,$file_open,$to,$domain,$from,$subject,$msg,$text
+,$logfile) ;
#
###########################################################
#         Disconnect from the DB                          #
###########################################################
#
$msg = "\n\tDBI - Failed to disconnect from $db_name." ;
$dbh->disconnect() or
      &end_it($mail_msg,$file_open,$to,$domain,$from,$subject,$msg,$nu
+ll,$logfile) ;
#
###########################################################
#
[download]

The log file for this contains the "DBI - prepared SQL." entry and the script terminates with the "DBI - Failed to execute SQL (OBFX001S)." message. This message is also correctly present in Email the script sends. Can "command files" be executed from DBI? Thanks in anticipation, Ronnie Cruickshank

Edit: Added <code> tags. larsen

Comment on DBI & Oracle Command Files Select or Download Code

Replies are listed 'Best First'.
Re: DBI & Oracle Command Files by mpeppler (Vicar) on Aug 19, 2003 at 15:24 UTC
Is the `@OBFX001S` bit what you'd use in SQLPlus to execute the command file? If so then this will not work - because DBI isn't SQLPlus, and SQL*Plus has a number of addition features on the client side that will (presumably) run the command file. If the command file is a collection of SQL statements then you have to open the file in perl and parse it so that you can execute each of the SQL statements individually. Michael	[reply] [d/l]
Re: Re: DBI & Oracle Command Files by Ronnie (Scribe) on Aug 20, 2003 at 08:56 UTC
Thanks you're absolutely correct & I feel ever so humble for not realising that! (I think I'm lucky still to be within the monastery for asking such a foolish question!) Cheers, Ronnie	[reply]
Re: DBI & Oracle Command Files by dbwiz (Curate) on Aug 19, 2003 at 15:51 UTC
The ability of executing multiple SQL statements from a file is a feature of sqlplus, the basic Oracle client. There, you execute a list of SQL commands from a file, which you call with the "@" prefix. The DBI does not support such feature, AFAIK. Please see this previous discussion on the same issue for some workarounds.	[reply]
Re: DBI & Oracle Command Files by bean (Monk) on Aug 19, 2003 at 17:15 UTC
ORA-0900 is an invalid SQL statement - so I think the SQL is the problem, not the Perl. If you are checking the SQL in sqlplus, you should know that sqlplus has its own syntax and you need to remove the sqlplus specific stuff (the / in particular - there may be more, but I don't know because I don't use sqlplus) and send pure SQL or PL/SQL to Oracle from DBI. I recommend not using sqlplus at all - I use Toad, and I'm sure there are other Oracle clients out there that do not make you add non-SQL syntax. I'm a little confused by your terminology, but I think what you are calling a "command file" is known as a "PL/SQL block". First of all, make sure you are not sending DOS style line endings in your PL/SQL block - that will kill it every time. Next, buy a book on PL/SQL. Here's a quick tutorial in the meantime: A PL/SQL block starts with an optional "DECLARE" section, in which you declare your variables, then has a "BEGIN", after which you put the multiple sql statements (don't use the / to separate/run them as you do in sqlplus), then an optional "EXCEPTION" section, then an "END;" to finish it off. It's almost exactly like a procedure definition, except for the explicit DECLARE section (which you get for free in a procedure after the IS). Example: DECLARE null_tmpString EXCEPTION; tmpString VARCHAR2(20); BEGIN --someProcedure sets tmpString as an OUT parameter someProcedure( 'asdf', 1, tmpString ); INSERT INTO someTable ( column1, column2 ) VALUES ( tmpString, 'qwer' ); IF tmpString IS NULL THEN --raise an error to go to the exceptions section --in this case, we skip the update but still --commit the insert RAISE null_tmpString; END IF; UPDATE someOtherTable SET otherColumn1 = 5 WHERE otherColumn2 = tmpString; COMMIT; EXCEPTION WHEN null_tmpString THEN COMMIT; WHEN OTHERS THEN ROLLBACK; END; [download] As for how to execute it in DBI - I'm trapped in PHPland so I can't tell you precisely, but you basically just want to "parse" it and "execute" it (maybe set the prefetch before executing), both of which you are doing. So again, I don't think the problem is with your perl code. Update Clarified the main point, that the SQL (which was not given) is probably in error, not the perl code.	[reply] [d/l]