Inconsistent behavior between SQL*Plus and Perl DBI

[John]

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
cursor PAGER_VIEWS is (
select OBJECT_NAME, CREATED from ALL_OBJECTS
where OBJECT_NAME like 'PAGER_' || PAGER_NAME || '_%' and
OBJECT_TYPE = 'VIEW'
);
EXECUTE_DROP integer default dbms_sql.open_cursor;
DUMMY integer;
begin
for ROW in PAGER_VIEWS
loop
if ( SYSDATE - ROW.CREATED ) * 24 * 60 > EXPIRATION_AGE then
dbms_sql.parse( EXECUTE_DROP,
'drop view ' || ROW.OBJECT_NAME,
dbms_sql.native );
DUMMY := dbms_sql.execute( EXECUTE_DROP );
end if;
end loop;
commit;
end $pager_proc;
QUERY_CREATE_PROC

$dbh->do( $temp_query );

 

[Mladen Gogala]

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

If I am allowed to guess, there is a problem with quoting
in your create statement. You can, of course, take a look at
USER_ERRORS, which will tell you at which line did the error
happen and what was the message. If I may ask, why are you trying to
create procedure from DBI? That's precisely what sqlplus is all about.
You should only be calling procedure from DBI, not try creating it.

 

[Tony Dare]

Comments inline.

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";

I think this might be your problem --^

Perhaps oughtn't put a line terminator there. For the assignment you
don't need the <<"QUERY_CREATE_PROC" either. perl is perfectly happy to
read a multiline string up to the ";" terminator.

 

[Andy Hassall]

I think this might be your problem --^

Perhaps oughtn't put a line terminator there. For the assignment you
don't need the <<"QUERY_CREATE_PROC" either. perl is perfectly happy to
read a multiline string up to the ";" terminator.

That's the start of a perfectly good interpolated heredoc. Nothing wrong with
it. There's a matching QUERY_CREATE_PROC at the end.

 

[Gregory Toomey]

John wrote:

....

Oddly enough, if I print out this command and paste it into SQL*Plus,
the procedure is created and works as expected.

....


Try printing out $temp_query after you assign it to the here doc, then cut &
paste into sql*plus. There may be some Perl variable interpolation you did
not expect.

gtoomey

 

[Mladen Gogala]

Try printing out $temp_query after you assign it to the here doc, then cut &
paste into sql*plus. There may be some Perl variable interpolation you did
not expect.

Oracle has a table called USER_ERRORS in which the last compilation error
is recorded. There is also a table called USER_SOURCE, from which the
lines of the compiled source can be obtained. A logical step would be
to extract the source of the unit that was actually compiled and
compare it with the desired. Perl is a mighty tool, and I use it
frequently, but it shouldn't be the only tool.

 

[Richard Morse]

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

 

[Andy Hassall]

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.

 

[Richard Morse]

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.

My apologies for the incorrect information -- you are correct, I was
getting confused...

Ricky

 

[John]

If I am allowed to guess, there is a problem with quoting
in your create statement. You can, of course, take a look at
USER_ERRORS, which will tell you at which line did the error
happen and what was the message. If I may ask, why are you trying to
create procedure from DBI? That's precisely what sqlplus is all about.
You should only be calling procedure from DBI, not try creating it.

I'm pretty sure there isn't a problem with the statement because if I
print it out after creating it and put the printed query into
SQL*Plus, the procedure is created and works as expected. It's only
when I try to create it from within Perl using $dbh->do( $query );
that things go awry.

The USER_ERRORS table is empty.

I need to ensure that the procedure is made. It makes one less
dependency that maintainers will have to know/worry about.

 

[John]

*** Code Snippet ***

I think this might be your problem --^

Don't think so. I've used this format frequently. It's never been a
issue.

Perhaps oughtn't put a line terminator there. For the assignment you
don't need the <<"QUERY_CREATE_PROC" either. perl is perfectly happy to
read a multiline string up to the ";" terminator.

I use here-docs everywhere. They are not only good for printing
multiple lines of text, but are also an easy way to document code.

 

[John]

John wrote:

...
...


Try printing out $temp_query after you assign it to the here doc, then cut &
paste into sql*plus. There may be some Perl variable interpolation you did
not expect.

Been there, done that. Apparently I didn't make that clear.

 

[John]

Oracle has a table called USER_ERRORS in which the last compilation error
is recorded. There is also a table called USER_SOURCE, from which the
lines of the compiled source can be obtained. A logical step would be
to extract the source of the unit that was actually compiled and
compare it with the desired. Perl is a mighty tool, and I use it
frequently, but it shouldn't be the only tool.

Thanks for cluing me in on USER_SOURCE. I've been looking for a way to
do that. Call me lazy, but it seems a little cumbersome to have to
type

select TEXT from USER_SOURCE where NAME = 'PROC_NAME';

every time I want to print out a procedure. Do you know of any
shortcut in SQL*Plus? Or should I write my own procedure to do it?

 

[Abhinav]

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
cursor PAGER_VIEWS is (
select OBJECT_NAME, CREATED from ALL_OBJECTS
where OBJECT_NAME like 'PAGER_' || PAGER_NAME || '_%' and
OBJECT_TYPE = 'VIEW'
);
EXECUTE_DROP integer default dbms_sql.open_cursor;
DUMMY integer;
begin
for ROW in PAGER_VIEWS
loop
if ( SYSDATE - ROW.CREATED ) * 24 * 60 > EXPIRATION_AGE then

^^^^^
Not sure about this, but the error could be with SYSDATE.
I am no pl/sql user, but one of my colleagues was having a problem with the
quoting related to SYSDATE.

I will fill in with more details when I can get in touch with him. In the
meantime, hope this helps in some way.

 

[John]

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.