Re: Inconsistent behavior between SQL*Plus and Perl DBI

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.

HTH,
Ricky Received on Tue Jul 20 2004 - 12:23:48 CDT