-
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
-
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.
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|