Re: Inconsistent behavior between SQL*Plus and Perl DBI

From: Abhinav <matrix_calling_at_yahoo.dot.com>
Date: Fri, 23 Jul 2004 21:12:19 +0530
Message-ID: <BsaMc.29$pk.81@news.oracle.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
> 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.

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

--

Abhinav

Received on Fri Jul 23 2004 - 10:42:19 CDT