Home » SQL & PL/SQL » SQL & PL/SQL » to get rowcount of dynamically executed query
to get rowcount of dynamically executed query [message #427469] Thu, 22 October 2009 15:00 Go to next message
bella13
Messages: 90
Registered: July 2005
Member
I am running a query dynamically. the dynamic query is a select from a table. I need to get the rowcount and based on what it is do some processing.

EXECUTE IMMEDIATE (l_ddl);

where l_ddl = 'select count(*) from table1'

After this i am doing

l_cnt := SQL%ROWCOUNT;

But i never seem to get the rowcount of dyamic query.

I trird adding this to the scriot

where l_ddl = 'select count(*) into l_cnt from table1'

but that did not work..

any ideas?
Re: to get rowcount of dynamically executed query [message #427470 is a reply to message #427469] Thu, 22 October 2009 15:19 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
When all else fails & you get desperate, you should Read The Fine Manual

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm#sthref2679
Re: to get rowcount of dynamically executed query [message #427472 is a reply to message #427469] Thu, 22 October 2009 15:27 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
In Dynamic sql,
The SQL cursor attributes %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT work when you issue an INSERT, UPDATE, DELETE
BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM employees WHERE employee_id > 1000';
  DBMS_OUTPUT.PUT_LINE('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT));
END;
/




[Updated on: Thu, 22 October 2009 15:47]

Report message to a moderator

Re: to get rowcount of dynamically executed query [message #427473 is a reply to message #427469] Thu, 22 October 2009 15:38 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME = 'MYTABLE'
Re: to get rowcount of dynamically executed query [message #427475 is a reply to message #427473] Thu, 22 October 2009 15:48 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
@Blackswan,
You may not get the correct value by fetching NUM_ROWS
Edit: NUM_WOWS may not be equal to count(*) for the table.
Regards,
Ved

[Updated on: Thu, 22 October 2009 15:54]

Report message to a moderator

Re: to get rowcount of dynamically executed query [message #427484 is a reply to message #427475] Thu, 22 October 2009 17:09 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Its_me_ved,
NUM_ROWS is a point in time value.

SELECT COUNT(*) is also a point in time value.
SELECT COUNT(*) could be wrong before the operation completes

only by LOCKING whole table can you ensure a correct count
& leaving the table LOCKED

sometimes close is good enough.
Re: to get rowcount of dynamically executed query [message #427545 is a reply to message #427469] Fri, 23 October 2009 03:51 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
bella13 wrote on Thu, 22 October 2009 21:00
I am running a query dynamically. the dynamic query is a select from a table. I need to get the rowcount and based on what it is do some processing.

EXECUTE IMMEDIATE (l_ddl);

where l_ddl = 'select count(*) from table1'

After this i am doing

l_cnt := SQL%ROWCOUNT;

But i never seem to get the rowcount of dyamic query.

Well since the rowcount of a query that uses an agregate (count, sum, min etc) without a group by is always 1 that was never going to work even if you were using static sql.

You should also note that doing processing based on count(*) isn't generally a good idea since, as BlackSwan mentioned, the count can change between selecting it and doing your processing.
Re: to get rowcount of dynamically executed query [message #427546 is a reply to message #427484] Fri, 23 October 2009 03:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@Blockswan - the SELECT count(*) will give you the result that was correct at the point the query was executed, which is something that cannot be said of the NUM_ROWS approach.

I'm suprised to see someone of your experience recommending such an inadequate solution.

@Bella13 - if you have to use dynamic SQL (and you'd be well advised not to if you can avoid it) you can use the INTO clause to get values back from the queries you execute:
create table test_092 (col_1 number);

insert into test_092 select level from dual connect by level <= 100;

declare
  v_cnt  pls_integer;
begin
  execute immediate 'select count(*) from test_092' into v_cnt;
  
  raise_application_error(-20001,'Rows '||v_cnt);
end;
/
Re: to get rowcount of dynamically executed query [message #427577 is a reply to message #427484] Fri, 23 October 2009 05:50 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member



Quote:

NUM_ROWS is a point in time value

No- I belive it fully depends on stats
If I am not wrong, NUM_ROWS value fully depends on Statistics
If the stats are not upto date then you wont get the count when the query executes
Quote:

NUM_ROWS may not be equal to count(*) for the table.


[Updated on: Fri, 23 October 2009 05:54]

Report message to a moderator

Re: to get rowcount of dynamically executed query [message #427578 is a reply to message #427577] Fri, 23 October 2009 05:53 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Its_me_ved wrote on Fri, 23 October 2009 11:50
Quote:

NUM_ROWS may not be equal to count(*) for the table.


If I am not wrong, NUM_ROWS value fully depends on Statistics
If the stats are not upto date then you wont get the count when the query executes


You're not wrong and on a transactional system you can pretty much assume that num_rows will never be accurate.
Re: to get rowcount of dynamically executed query [message #427584 is a reply to message #427577] Fri, 23 October 2009 06:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
If the stats are not upto date then you wont get the count when the query executes


To be honest, I've never worked anywhere where 'Up to date' for stats meant anything better than 'they were regenerated last night'

So you could easily be 8-10 hours behind the times if you used NUM_ROWS.

The disadvantage of count(*) is that it will go and count all the rows, so in some circumstances, NUM_ROWS could be a much quicker and acceptably accurate alternative. I wouldn't recommend it as a general solution though.
Re: to get rowcount of dynamically executed query [message #427585 is a reply to message #427584] Fri, 23 October 2009 06:40 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
JRowbottom wrote on Fri, 23 October 2009 06:20
Quote:
If the stats are not upto date then you wont get the count when the query executes


To be honest, I've never worked anywhere where 'Up to date' for stats meant anything better than 'they were regenerated last night'

So you could easily be 8-10 hours behind the times if you used NUM_ROWS.

The disadvantage of count(*) is that it will go and count all the rows, so in some circumstances, NUM_ROWS could be a much quicker and acceptably accurate alternative. I wouldn't recommend it as a general solution though.


You are correct as always.
Previous Topic: Inserting record in Parent & child table
Next Topic: median
Goto Forum:
  


Current Time: Sun Dec 04 12:39:04 CST 2016

Total time taken to generate the page: 0.10236 seconds