Re: Inconsistent behavior between SQL*Plus and Perl DBI

On Tue, 20 Jul 2004 13:23:48 -0400, Richard Morse <remorse_at_partners.org> wrote:

>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.

 But the OP isn't using SQL*Plus, he's using Perl DBI. This uses OCI directly, not SQL*Plus. So using '/' would result in an error.

D:\Temp>type test.pl
#!perl
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect('dbi:Oracle:', 'test', 'test',

   { AutoCommit => 0, RaiseError => 1 });

$dbh->do(<<'');
create or replace procedure t
as
begin
  null;
end;

print "Created.\n";

$dbh->do(<<'');
create or replace procedure t
as
begin
  null;
end;
/

print "Created.\n";

$dbh->disconnect;

D:\Temp>perl test.pl
Created.
DBD::Oracle::db do failed: ORA-24344: success with compilation error (DBD SUCCES
S_WITH_INFO: OCIStmtExecute) [for Statement "create or replace procedure t as
begin
  null;
end;
/
"] at test.pl line 19.

--
Andy Hassall <andy_at_andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk         / http://www.andyhsoftware.co.uk/space