Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> using sys.all_tables in a cursor

using sys.all_tables in a cursor

From: Travis Bryant <tbryant_at_chevrontexaco.com>
Date: 29 Jul 2002 15:17:31 -0700
Message-ID: <28892ae6.0207291417.559dd830@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 Mon Jul 29 2002 - 17:17:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US