Re: Return table name and record count

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 18 May 2005 09:41:36 -0700
Message-ID: <1116434496.528510.233330@g47g2000cwa.googlegroups.com>

Bruce G. wrote:
> Hello Group,
> I was trying to write a script that returns the name of my tables and
> the number of records in each table. This is as close as I can get
but
> keep getting a "PLS-00201: identifier 'TAB_NAME' must be declared"
> error. Am I making this more complicated than necessary?
> Thanks
>
> declare
> tab_name USER_TABLES.TABLE_NAME%TYPE;
> rec_count number;
> err_mssg varchar2(512);
> cursor tab_cur is
> select table_name from user_tables;
> tab_rec user_tables%rowtype;
> begin
> for tab_rec in tab_cur loop
> tab_name := tab_rec.table_name;
> select count(*) into rec_count from tab_name;
> dbms_output.put_line(tab_name||','||rec_count);
> end loop;
> exception
> when others then
> err_mssg :=sqlerrm;
> dbms_output.put_line(err_mssg);
> end;
> /

I had this code sample sitting in my scripts.

create or replace function get_row_count (

   table_name_ in varchar2) return number is

   table_row_count_ number;

   stmt_              varchar2(100);
   cid_               integer;
   cnt_               integer;

begin

   stmt_ := 'select count(*) from ' || table_name_;    cid_ := dbms_sql.open_cursor;
   dbms_sql.parse(cid_, stmt_, dbms_sql.native);    dbms_sql.define_column(cid_, 1, table_row_count_);    cnt_ := dbms_sql.execute(cid_);
   cnt_ := dbms_sql.fetch_rows(cid_);
   dbms_sql.column_value(cid_, 1, table_row_count_);    dbms_sql.close_cursor(cid_);
   return table_row_count_;
exception

   when others then

      if (dbms_sql.is_open(cid_)) then
         dbms_sql.close_cursor(cid_);
      end if;
      return null;

end get_row_count;
/

Regards
/Rauf Received on Wed May 18 2005 - 11:41:36 CDT