Inconsistent behavior between SQL*Plus and Perl DBI

From: John <jpeter1978_at_yahoo.com>
Date: 19 Jul 2004 13:52:40 -0700
Message-ID: <545336be.0407191252.1a478239@posting.google.com>

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 );
Received on Mon Jul 19 2004 - 15:52:40 CDT