Link to home
Start Free TrialLog in
Avatar of LindaC
LindaCFlag for Puerto Rico

asked on

pl/sql not working

Hi experts.
The execution of the following procedure is not working.
Any clues?

CREATE OR REPLACE procedure SYSTEM.del1_dw_service_orders is
   CURSOR del1 is
   select * from dw.dw_service_orders where to_char(transaction_date,'RR')='14' for update;
   del1_rec dw.dw_service_orders%ROWTYPE;
BEGIN
    execute immediate 'alter session enable parallel DML';
   OPEN del1;
   LOOP
    fetch del1 into del1_rec;
    exit when del1%NOTFOUND;
      delete from dw.dw_service_orders where current of del1;
       if del1%ROWCOUNT > 100
       then
       commit;
       END IF;
     END LOOP;
   CLOSE del1;
   commit;
END;
/




SQL> SQL> SQL> mapdbprd-oracle:/home/oracle/dba/scripts/Mapdb> cat del_dw_serv_orders.log
SQL> execute system.del1_dw_service_orders;
BEGIN system.del1_dw_service_orders; END;

*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at "SYSTEM.DEL1_DW_SERVICE_ORDERS", line 9
ORA-06512: at line 1


Elapsed: 00:43:21.01
SQL> spool off;
ASKER CERTIFIED SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of LindaC

ASKER

Oh wow!
I will be executing this.
Avatar of LindaC

ASKER

Done.

Thank you!


rw-r--r--    1 oracle   dba             124 Mar 27 12:47 del_dw_serv_orders.log
-rw-r--r--    1 oracle   dba             496 Mar 27 12:47 del.log
mapdbprd-oracle:/home/oracle/dba/scripts/Mapdb> cat del.log

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 27 12:34:41 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> Connected.
SQL> SQL>
Session altered.

Elapsed: 00:00:00.04
SQL>
Session altered.

Elapsed: 00:00:00.00
SQL>
Session altered.

Elapsed: 00:00:00.00
SQL> SQL> SQL>
PL/SQL procedure successfully completed.

Elapsed: 00:12:33.23
SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
mapdbprd-oracle:/home/oracle/dba/scripts/Mapdb>