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

In reply to DBI & Oracle Command Files by Ronnie

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.