PLS-00201: identifier 'SYS.DBA_TABLES' must be declared
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))
CURSOR MyCurs IS
SELECT TABLE_NAME FROM SYS.DBA_TABLES
WHERE OWNER = USRNAME;
FOR I IN MyCurs LOOP
SQL_STMT:='UPDATE '||I.TABLE_NAME ||' SET ORGANIZATIONID = 1 ;';
EXECUTE IMMEDIATE SQL_STMT;
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.
It works when I connect as SYSDBA.
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.
Click Here to Expand Forum to Full Width