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!