PLS-00201: identifier 'SYS.DBA_TABLES' must be declared
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: PLS-00201: identifier 'SYS.DBA_TABLES' must be declared

  1. #1
    Join Date
    Feb 2001
    Posts
    100

    PLS-00201: identifier 'SYS.DBA_TABLES' must be declared

    Hi,

    I am using Oracle 8.1.6 on Windows 2000.

    I connect as user with DBA role and tried to compile this stored procedure.

    CREATE OR REPLACE PROCEDURE UpdOrgId (USRNAME IN VARCHAR2(30))
    IS
    SQL_STMT VARCHAR2(200);
    CURSOR MyCurs IS
    SELECT TABLE_NAME FROM SYS.DBA_TABLES
    WHERE OWNER = USRNAME;

    BEGIN
    FOR I IN MyCurs LOOP
    SQL_STMT:='UPDATE '||I.TABLE_NAME ||' SET ORGANIZATIONID = 1 ;';
    EXECUTE IMMEDIATE SQL_STMT;
    END LOOP;
    END UpdOrgId ;


    Unfortunately I got the following error.

    5/2 PL/SQL: SQL Statement ignored
    5/25 PLS-00201: identifier 'SYS.DBA_TABLES' must be declared
    9/4 PL/SQL: Statement ignored


    Any idea how to resolve that.

    Thanks

  2. #2
    Join Date
    Feb 2001
    Posts
    100
    It works when I connect as SYSDBA.

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The user needs explicit privileges on the sys.dba_tables view to be able to reference it from within PL/SQL -- role-based privileges are not sufficient.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Feb 2001
    Posts
    100
    Thanks a lot Slimdave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width