Home » SQL & PL/SQL » SQL & PL/SQL » count rows in tables (oracle 9i)
count rows in tables [message #424225] Wed, 30 September 2009 21:22 Go to next message
cesarnz
Messages: 10
Registered: November 2008
Junior Member
hi all,
am trying to write a stored procedure in pl/sql which will select distinct table_names from all_tables and try and count the rows in each of those tables.

i have declared a cursor to store the list of table names but i dont know how to use that to count the rows in the table. below is the code and the error i get when i try and compile the stored procedure. Error points to the line where i do a "count(*)".


CREATE OR REPLACE PROCEDURE getTableCount AS

vartableName all_tables.table_name%TYPE;
varrowCount INTEGER;

CURSOR tableNameList IS select distinct table_name from all_tables;

BEGIN

DBMS_OUTPUT.ENABLE(1000000);

DBMS_OUTPUT.PUT_LINE('about to loop through the cursor');

OPEN tableNameList;
LOOP
FETCH tableNameList into vartableName;
 EXIT WHEN tableNameList%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(vartableName || 'here');

select count(*) into varrowCount from  [b]vartableName [/b];

insert into tableRowCount(Name, num) values (vartableName, varrowCount);

END LOOP;
CLOSE tableNameList;

EXCEPTION
      WHEN OTHERS
         THEN
         DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;




Error thrown :

PL/SQL: ORA-00942: table or view does not exist
Re: count rows in tables [message #424228 is a reply to message #424225] Wed, 30 September 2009 22:13 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
You can not SELECT FROM variable_NAME.
You need to build the SQL one SELECT at a time & then EXECUTE IMMEDIATE

If you actually SEARCH this forum, you'll discover this is done at least once a month (FAQ)


Alternatively just do:

SELECT TABLE_NAME, NUM_ROWS FROM DBA_TABLES;

[Updated on: Wed, 30 September 2009 22:28]

Report message to a moderator

Re: count rows in tables [message #424259 is a reply to message #424225] Thu, 01 October 2009 03:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As @Blackswan points out, the error in the statement
select count(*) into varrowCount from  vartableName
is that you have no table called VarTableName.

You could use a Ref Cursor to do this:
create table test_073 (col_1 number);

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

commit;

declare
  c_cur     sys_refcursor;
  v_table   varchar2(30) := 'TEST_073';
  v_cnt     pls_integer;
begin
  open c_cur for 'SELECT count(*) FROM '||v_table;
  fetch c_cur into v_cnt;
  close c_cur;
  
  dbms_output.put_line('Rows in '||v_table||' : '||v_cnt);
end;
/

or you can use Native Dynamic SQL:
declare
  v_table   varchar2(30) := 'TEST_073';
  v_cnt     pls_integer;
begin
  execute immediate 'SELECT count(*) FROM '||v_table into v_cnt;
  dbms_output.put_line('Rows in '||v_table||' : '||v_cnt);
end;
/
Re: count rows in tables [message #424265 is a reply to message #424259] Thu, 01 October 2009 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or you can do it in a single SQL statement:
select table_name,
       to_number(extractvalue(
                   dbms_xmlgen.getXMLtype ('select count(*) cnt from '||table_name),
                   '/ROWSET/ROW/CNT')) NbRows
from user_tables
where tablespace_name is not null or partitioned='YES' 
order by 1
/

Regards
Michel

[Updated on: Thu, 01 October 2009 03:56]

Report message to a moderator

Re: count rows in tables [message #424467 is a reply to message #424225] Fri, 02 October 2009 08:49 Go to previous messageGo to next message
mohd_dba
Messages: 19
Registered: October 2009
Junior Member
It might be the problem with the table.Without table if you create procedure.It will throw an error.

Thanks,
Afroze.
Re: count rows in tables [message #424471 is a reply to message #424467] Fri, 02 October 2009 09:29 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
mohd_dba wrote on Fri, 02 October 2009 14:49
It might be the problem with the table.Without table if you create procedure.It will throw an error.

Thanks,
Afroze.


What's this got to do with the current thread?
Re: count rows in tables [message #424489 is a reply to message #424467] Fri, 02 October 2009 12:12 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
mohd_dba wrote on Fri, 02 October 2009 09:49
It might be the problem with the table.Without table if you create procedure.It will throw an error.

Thanks,
Afroze.

Your answers are either incorrect, add nothing to the thread, answer 4 year old questions or are just plain babble. Did you make a bet with someone about the number of posts you can have in a single day?
Re: count rows in tables [message #424490 is a reply to message #424489] Fri, 02 October 2009 12:25 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I PMed him about his posts in the following way:
Quote:
Could you STOP posting on old topics above all when your posts do not add anything (or very few) to previous posts.
It should be FAR better if you answer the current topics.

His answers was (in extenso):
mohd_dba wrote on Fri, 02 October 2009 15:36
Thanks.

Regards
Michel
Previous Topic: Unable to retrieve all records
Next Topic: Convertion long to clob
Goto Forum:
  


Current Time: Wed Dec 07 04:39:34 CST 2016

Total time taken to generate the page: 0.08535 seconds