SQL & PL/SQL

Hi all,

Can I access DBA_TABLES from a PL/SQL block?

Following is the code I tried:

SQL> SHOW USER
USER is "SYSTEM"
SQL> ED
Wrote file afiedt.buf

  1  CREATE OR REPLACE PACKAGE BODY BARAKAU
  2  AS
  3  PROCEDURE UPPER_COLS ( P_USER VARCHAR2, P_TABLE VARCHAR2 ) IS
  4  BEGIN
  5   -- loop through tables
  6   FOR T IN ( SELECT TABLE_NAME FROM DBA_TABLES WHERE TABLE_NAME = NVL(P_TABLE, TABLE_NAME)
  7               AND OWNER=UPPER(P_USER) ORDER BY TABLE_NAME) LOOP
  8     DBMS_OUTPUT.PUT_LINE(T.TABLE_NAME  || ' processed');
  9   END LOOP;
 10  END UPPER_COLS;
 11* END BARAKAU;
SQL> /

Warning: Package Body created with compilation errors.

SQL> SHOW ERROR
Errors for PACKAGE BODY BARAKAU:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/13     PL/SQL: SQL Statement ignored
6/36     PL/SQL: ORA-00942: table or view does not exist
8/4      PL/SQL: Statement ignored
8/25     PLS-00364: loop index variable 'T' use is invalid

SQL> L 6
  6*  FOR T IN ( SELECT TABLE_NAME FROM DBA_TABLES WHERE TABLE_NAME = NVL(P_TABLE, TABLE_NAME)

Oracle 11g on Windows