Re: Inconsistent behavior between SQL*Plus and Perl DBI

Richard Morse <remorse_at_partners.org> wrote in message news:<remorse-66C3E2.13234820072004_at_plato.harvard.edu>...
> In article <545336be.0407191252.1a478239_at_posting.google.com>,
> jpeter1978_at_yahoo.com (John) wrote:
>
> > I'm trying to create a PL/SQL procedure with Perl's DBI module. I
> > first create the query and then use the "do" method to send it to
> > Oracle. However, when I run the script I get the following error:
> >
> > DBD::Oracle::db do failed: ORA-24344: success with compilation error
> > (DBD SUCCESS_WITH_INFO: OCIStmtExecute) ...
> >
> > Oddly enough, if I print out this command and paste it into SQL*Plus,
> > the procedure is created and works as expected.
> >
> > I can't seem to find much on this error, so I hope someone else has
> > figured this one out. BTW, I'm using Oracle 9i and SQL*Plus under
> > RedHat.
> >
> > *** Code Snippet ***
> >
> > $temp_query = <<"QUERY_CREATE_PROC";
> > create or replace procedure $pager_proc
> > ( PAGER_NAME in varchar2, EXPIRATION_AGE in number ) as
> [snip]
> > commit;
> > end $pager_proc;
>
> you need a '/' on a blank line here...
>
> > QUERY_CREATE_PROC
> >
> > $dbh->do( $temp_query );
>
> SQL*Plus has special magic to handle PL/SQL. Try adding a line with a
> '/' at the end of your procedure -- this tells Oracle to go ahead and
> execute your code immediately.

Oddly enough, putting a '/' after the statement actually inserts it as part of the procedure. However, when executing it in SQL*Plus the '/' is interpreted. This appears to be the problem. Apparently ending things with a '/' is SQL*Plus specific. Even if I put 'commit;' after "end $pager_proc", it is added to the procedure. I seem to have found a solution that works however. I just make the commit call separately. Like so:

$dbh->do( $temp_query );
$dbh->do( 'commit' );

Thanks for all the help. You guys have been great.

> HTH,
> Ricky
Received on Fri Jul 23 2004 - 10:46:10 CDT