Home » SQL & PL/SQL » SQL & PL/SQL » Return rows deleted Using PL/SQL
icon6.gif  Return rows deleted Using PL/SQL [message #277716] Wed, 31 October 2007 05:15 Go to next message
kprasanna_79
Messages: 5
Registered: October 2007
Junior Member
Hi,
I have the below query.
DECLARE
    TYPE tt_delete IS TABLE OF varchar2(100);
    t_delete tt_delete;
    rowcnt number:=0;
    CURSOR c_delete IS
        SELECT ROWID
            FROM TRACK_DATA
             WHERE  (
             ((trunc(TIME_LOGGED) < trunc( SYSDATE ) -    5 ) AND MESSAGE_TYPE = 'INFO')
             OR ((trunc(TIME_LOGGED) < trunc( SYSDATE ) -   15 ) AND MESSAGE_TYPE = 'ERROR')
            OR ((trunc(TIME_LOGGED) < trunc( SYSDATE ) - 10 ) AND MESSAGE_TYPE = 'WARNING'
            )
        )
        AND ENVIRONMENT = 'TST';
            l_delete_buffer PLS_INTEGER := 5000;
         BEGIN
            OPEN c_delete;
            LOOP
            FETCH c_delete BULK COLLECT
            INTO t_delete LIMIT l_delete_buffer;
            FORALL i IN 1..t_delete.COUNT
            DELETE TRACK_DATA
            WHERE ROWID = t_delete (i);
            rowcnt:=rowcnt+SQL%ROWCOUNT;
            EXIT WHEN c_delete%NOTFOUND;
            COMMIT;
            END LOOP;
            CLOSE c_delete;
            DBMS_OUTPUT.put_line(to_char(rowcnt));
        END;


Instead of returning the number of rows deleted its returning 1. Please guide me. I am comparing this o/p with actual inserted data into other table.

Thanks..
-Prasanna.K
Re: Return rows deleted Using PL/SQL [message #277720 is a reply to message #277716] Wed, 31 October 2007 05:28 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
1 - First of all: have you tried your query seperately? Maybe it's just selecting one row

2 - I've made a test-case myself based upon your code and strangely enough I get the result that I wanted: the number of rows deleted

3 - Why-o-why this complex way of deleting rows from a table? Why a cursor and row-per-row delete? Is there code left out between the FETCH and DELETE?

Following delete would do the very same trick:
DELETE TRACK_DATA
WHERE (
((trunc(TIME_LOGGED) < trunc( SYSDATE ) -  5 ) AND MESSAGE_TYPE = 'INFO') OR
((trunc(TIME_LOGGED) < trunc( SYSDATE ) - 15 ) AND MESSAGE_TYPE = 'ERROR') OR
((trunc(TIME_LOGGED) < trunc( SYSDATE ) - 10 ) AND MESSAGE_TYPE = 'WARNING')
)
AND ENVIRONMENT = 'TST';

DBMS_OUTPUT.put_line(to_char(SQL%ROWCOUNT));

[Updated on: Wed, 31 October 2007 05:29]

Report message to a moderator

Re: Return rows deleted Using PL/SQL [message #277725 is a reply to message #277716] Wed, 31 October 2007 05:34 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Have a look at bulk_rowcount.

MHE
Re: Return rows deleted Using PL/SQL [message #277732 is a reply to message #277720] Wed, 31 October 2007 05:39 Go to previous messageGo to next message
kprasanna_79
Messages: 5
Registered: October 2007
Junior Member
I did this complex delete, because there are more number of rows and delete fails saying that there is lack of undo table space....so i need to do like this......tell why the same SQL%ROWCOUNT returns 1 when given inside the FORALL

-Prasanna.K
Re: Return rows deleted Using PL/SQL [message #277735 is a reply to message #277732] Wed, 31 October 2007 05:45 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
kprasanna_79 wrote on Wed, 31 October 2007 11:39

I did this complex delete, because there are more number of rows and delete fails saying that there is lack of undo table space....so i need to do like this......tell why the same SQL%ROWCOUNT returns 1 when given inside the FORALL

-Prasanna.K


1 - SQL%ROWCOUNT will always give 1 if you do a delete using ROWID. ROWID is a unique identifier for your row, there is only one row with a given ROWID

2 - Why don't you increase the undo tablespace instead? Looks like a more practical solution for this problem.
Re: Return rows deleted Using PL/SQL [message #277736 is a reply to message #277725] Wed, 31 October 2007 05:46 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Maaher wrote on Wed, 31 October 2007 11:34

Have a look at bulk_rowcount.

MHE

Very Happy
Re: Return rows deleted Using PL/SQL [message #277737 is a reply to message #277732] Wed, 31 October 2007 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maarten gave the answer: use the correct attribute.

Regards
Michel
Re: Return rows deleted Using PL/SQL [message #277845 is a reply to message #277716] Wed, 31 October 2007 19:31 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I agree with MarcS, best thing would be to increase undo. Have a talk with your DBAs. If you are deleting so many rows that you simply can't afford the undo, then maybe you should consider another somewhat unsavory but effective alterative

maybe truncate
maybe drop table and recreate
maybe partitioning of table and truncate/drop partitions

these would be way more efficient that lots of deletes. Then again each has its own drawbacks.

Good luck, Kevin
Previous Topic: printing a variable larger than 225
Next Topic: Stored procedure error ORA-06512/ORA-06502
Goto Forum:
  


Current Time: Fri Dec 09 19:30:59 CST 2016

Total time taken to generate the page: 0.09035 seconds