Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

pl/sql not working

Posted on 2014-03-27
3
506 Views
Last Modified: 2014-03-27
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;
0
Comment
Question by:LindaC
  • 2
3 Comments
 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
ID: 39959291
First off, this procedure should not be created in the SYSTEM schema.  That is reserved for Oracle objects and you could run into issues at a later date.

The RR date format isn't doing anything for you that YY doesn't do because you are converting to character.

You don't have to do all the cursor manipulation yourself.  The looping structure in Oracle will do it for you.

You shouldn't be selecting the entire record.  There is no need to be passing all that data through memory when you aren't using it.

This is a simplified version of what you had originally.

CREATE OR replace PROCEDURE SYSTEM.Del1_dw_service_orders 
IS 
  commit_ctr PLS_INTEGER := 0; 
BEGIN 
    FOR del1_rec IN (SELECT ROWID 
                     FROM   dw.dw_service_orders 
                     WHERE  To_char(transaction_date, 'YY') = '14') LOOP 
        DELETE FROM dw.dw_service_orders 
        WHERE  ROWID = del1_rec.ROWID; 

        commit_ctr := commit_ctr + 1; 

        IF commit_ctr > 100 THEN 
          COMMIT; 

          commit_ctr := 1; 
        END IF; 
    END LOOP; 

    COMMIT; 
END; 

/ 

Open in new window


Be aware that this could result in a ORA-01555.  This is a classic case of fetch across commit.  If you aren't deleting that many records and the table is small enough that you can scan it and delete all the records before redo get used again, you may avoid it.  But, you some day in the future you could hit it.  Re-running could complete.  Just want you to be aware that it could happen.
0
 
LVL 8

Author Comment

by:LindaC
ID: 39959435
Oh wow!
I will be executing this.
0
 
LVL 8

Author Closing Comment

by:LindaC
ID: 39959504
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>
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
best datatype for oracle table email creation 8 55
Read XML values 8 41
update using pipeline function 3 20
setting local variables in a cursor block 3 17
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question