Comments inline.
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";
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.
> 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 - 16:09:05 CDT
