Re: Oracle Procedure Problem

On 21 Apr 2006 06:05:12 -0700, "Kuldeep" <kuldeepchitrakar_at_gmail.com> wrote:

>create procedure tab_rows
> IS
> tab_name varchar(50);
> declare cursor all_tabs is
> select table_name
> from user_tables;
> begin
> create table temptab (rowc number (10));
> open all_tabs;
> Loop
> fetch all_tabs into tab_name;
> insert into temptab(rowc) select
>count(*) from tab_name;
> exit when all_tabs%notfound;
> End loop;
> close all_tabs;
> End;
>
>temptab has only one column rowc(number)
>
>Objective : ; procedure will return count(*) of all user tables.
>
>But above procedure is giving Error :Procedure created with compilation
>errors.
>
>Can anybody has workaround over it

create function tab_rows return number is dummy number;
temp number;
begin
dummy := 0;
for tab in (select table_name from user_tables) loop execute immediate 'select count(*) from '||tab.table_name into temp; dummy := dummy + temp;
end loop;
return dummy;
end;
/

--
Sybrand Bakker, Senior Oracle DBA