Re: using sys.all_tables in a cursor

Travis Bryant wrote:

> I'm writing a PL/SQL program that gets a cursor to list all tables for
> a particular schema, then loop through that list and get the counts of
> each table. Here's the SELECT statement for the cursor:
> CURSOR table_cur IS
> SELECT table_name
> FROM sys.all_tables
> WHERE owner = 'BASEOIL';
>
> (BASEOIL is hardcoded for a test for 1 that I know returns rows)
>
> When I run this program, it seems that the cursor returns 0 rows, even
> though when I run that SQL statement outside of a stored procedure it
> returns the # of rows that I am expecting. I have access to these
> views in SYS with the id that the stored procedure is stored under.
>
> Can I use a cursor to loop through sys.all_tables? I also tried "FROM
> sys.dba_tables" and it gave me a PL/SQL identifier error, meaning it
> couldn't find the variable name even though it wasn't a variable.
>
> Any help would be appreciated.
>
> Thanks,
> Travis
> tbryant_at_chevrontexaco.com

Try selecting from all_tables, not sys.all_tables.

Daniel Morgan Received on Mon Jul 29 2002 - 17:22:26 CDT