Thanks for the question, Lawrence.

Asked: January 08, 2005 - 6:43 pm UTC

Last updated: May 14, 2019 - 5:07 pm UTC

Version: 8.1.7.4

Viewed 10K+ times! This question is

You Asked

How to I get the number of rows deleted within PL/SQL using the EXECUTE IMMEDIATE command?

and Tom said...

ops$tkyte@ORA9IR2> create table t as select * from all_users;

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select count(*) from t;

COUNT(*)
----------
24

ops$tkyte@ORA9IR2> begin
2 execute immediate 'delete from t where mod(user_id,2) = 0';
3 dbms_output.put_line( 'Deleted ' || sql%rowcount || ' row(s)' );
4 end;
5 /
Deleted 9 row(s)

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> select count(*) from t;

COUNT(*)
----------
15

ops$tkyte@ORA9IR2>

Rating

(7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Use of PL/SQL Implicit Cursor attributes

Lawrence Winkler, January 09, 2005 - 11:02 am UTC

The key to the response was the use of PL/SQL's implicit cursor attributes -- a feature that I had totally forgotten about, and which I will now study and embed in my memory forever!

Why use execute immediate with DML?

Eddie, January 09, 2005 - 11:36 am UTC

Why not just issue a "delete..." instead of using an execute immediate "delete...". I thought execute immediate was mainly used to execute DDL inside PL/SQL!

January 09, 2005 - 12:18 pm UTC

one would have to assume that in real life, there was a need for dynamic sql -- perhaps they passed in the table name, whatever.

Variation on a theme..

A reader, January 09, 2005 - 10:25 pm UTC

Tom, suppose that you were doing 'selects' instead of deletes, how can you get the count of records along with the results ?

I know of two ways of doing this:
1) Querying two times, first for count(*) and then the results
2) Use analytics

Analytics aren't an option here ( 8.1.7 EE ) and the first one is not that performant. What could be your suggestions on this ?

I'll share the problem with you. I am doing some procedures on a Forms 6i application. This process can take some time (few minutes, depending) so I build a 'progress bar' on a window so the user can actually see how it is going. As you can see, my only option to build this dynamic sql is by using dbms_sql. What I have done is to make a server-side procedure, with two parameters, one input (the sql string) and one output ( a dbms_sql.varchar2_table ) to handle this, so that I can send the sql and the server will give me back an array of the records, so that on the client I can do a tabletype.count to know how many records and I can also process the records on the client doing traditional iteration process. I would like to know what are your thoughts into this.

Thanks

January 10, 2005 - 8:31 am UTC

well, if you are doing selects, you are fetching the data, you could "count as you fetch"

if you have my book "Effective Oracle by Design", you'll know I hold google as the gold stanard here...

They guess
They are often (always) wrong on the guess
And they don't care

If you add this count to your process -- the only thing you'll truly achieve is "this process will take much much much longer"

So, do it the way the travel sites (that I use anyway) do it. Give them an animated gif that bounces a blue box back and forth and back and forth saying "hey, we are working, please stand by"

Delete Progress from Big table

prat05, May 06, 2005 - 10:29 am UTC

Hi Tom,

Is it possible to know the status of delete statements? Like - How many records to be deleted, How many records already deleted so far, time elapsed, time remaining and percentage of completion. I checked v$longops, but noticed that it gives details of every process (like table scan, hash joins etc), but does not give the details on actual delete.

Any help will be highly appreciated.

Thanks

May 06, 2005 - 11:41 am UTC

if the delete is using a full scan -- you can monitor the progress in v$session_longops

but nothing will count the rows deleted. It doesn't know how many rows it'll have to delete until it is done.

v$session_longops is the only view into it.

Thanks

prat05, May 06, 2005 - 11:47 am UTC

Thanks Tom for a quick reply. I was just hoping you would have some magical way (as you always have :)) to identify the progress of deletes.
But I guess I will have to settle with the fact that it will not be possible :(

Concise and Accurate

A reader, September 15, 2018 - 12:17 am UTC

This was exactly what I needed and answered the question thoroughly without opening up the can of worms that is wondering why someone wants to do it this way instead of alternative ways.

Direct question, direct answer. Not sure why it's rated so low.

September 16, 2018 - 3:00 am UTC

Welcome to our world :-)

Nikita, May 14, 2019 - 5:01 pm UTC

I am using sql%rowcount in a loop for getting deleted record counts and storing it in a variable by concatinating to previous value.
Deletion count is 2 million + records and i am iterating loop for every thousand record.
But i am getting wrong result with sql%rowcount.. Any idea how can i get the correct count of records deleted.

May 14, 2019 - 5:07 pm UTC

You're going to have to show us a test case (create table + sample inserts + your loop/delete code) to say for sure.

But I'm betting the solution is:

Ditch the loop and run the delete once!

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating