Before you go any further, look up the discussions from yesterday
under the subject title "all_tab_columns in a stored procedure?".
Probably will find a simple explanation to your problem there.
tbryant_at_chevrontexaco.com (Travis Bryant) wrote in message news:<28892ae6.0207291417.559dd830_at_posting.google.com>...
> 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
Received on Tue Jul 30 2002 - 02:11:02 CDT
