OraFAQ Forum: SQL & PL/SQL » to get rowcount of dynamically executed query

Home » SQL & PL/SQL » SQL & PL/SQL » to get rowcount of dynamically executed query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

to get rowcount of dynamically executed query [message #427469]

Thu, 22 October 2009 15:00

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?

Report message to a moderator

Re: to get rowcount of dynamically executed query [message #427470 is a reply to message #427469]

Thu, 22 October 2009 15:19

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: to get rowcount of dynamically executed query [message #427472 is a reply to message #427469]

Thu, 22 October 2009 15:27

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME = 'MYTABLE'

Report message to a moderator

Re: to get rowcount of dynamically executed query [message #427475 is a reply to message #427473]

Thu, 22 October 2009 15:48

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

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: to get rowcount of dynamically executed query [message #427545 is a reply to message #427469]

Fri, 23 October 2009 03:51

cookiemonster
Messages: 13966
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.

Report message to a moderator

Re: to get rowcount of dynamically executed query [message #427546 is a reply to message #427484]

Fri, 23 October 2009 03:51

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;
/

Report message to a moderator

Re: to get rowcount of dynamically executed query [message #427577 is a reply to message #427484]

Fri, 23 October 2009 05:50

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

cookiemonster
Messages: 13966
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.

Report message to a moderator

Re: to get rowcount of dynamically executed query [message #427584 is a reply to message #427577]

Fri, 23 October 2009 06:20

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.

Report message to a moderator

Re: to get rowcount of dynamically executed query [message #427585 is a reply to message #427584]

Fri, 23 October 2009 06:40

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.

Report message to a moderator

Previous Topic:	Inserting record in Parent & child table
Next Topic:	median

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 28 02:47:21 CDT 2025