Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> using sys.all_tables in a cursor
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 Mon Jul 29 2002 - 17:17:31 CDT