I am not sure what I am making wrong with this statement,
Declare
cursor cur_dba_users IS
select username from dba_users
where username not in ('ANONYMOUS','APEX_040200','APEX_PUBLIC_USER','APPQOSSYS','AUDSYS','BI','CTXSYS','DBSNMP','DIP','DVF','DVSYS','EXFSYS','FLOWS_FILES','GSMADMIN_INTERNAL','GSMCATUSER','GSMUSER','HR','IX','LBACSYS','MDDATA','MDSYS','OE','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','PM','SCOTT','SH','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSBACKUP','SYSDG','SYSKM','SYSTEM','WMSYS','XDB','SYSMAN','RMAN','RMAN_BACKUP','OWBSYS','OWBSYS_AUDIT','APEX_030200','MGMT_VIEW','OJVMSYS');
Begin
For cur_username in cur_dba_users
Loop
SELECT dbms_metadata.get_ddl('USER',cur_username) FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',cur_username) from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',cur_username) from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',cur_username) from dual;
END LOOP;
CLOSE cur_dba_users;
End;
Error report -
ORA-06550: line 6, column 39:
PL/SQL: ORA-00904: "CUR_USERNAME": invalid identifier
ORA-06550: line 6, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 7, column 55:
PL/SQL: ORA-00904: "CUR_USERNAME": invalid identifier
ORA-06550: line 7, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 8, column 53:
PL/SQL: ORA-00904: "CUR_USERNAME": invalid identifier
ORA-06550: line 8, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 9, column 55:
PL/SQL: ORA-00904: "CUR_USERNAME": invalid identifier
ORA-06550: line 9, column 1:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
A couple of issues:
- You need to reference the name of the columns you're getting from the cursor. cur_username => cur_username.username
- You need an into clause for the select from dual statements
So this:
SELECT dbms_metadata.get_ddl('USER',cur_username) FROM dual;Becomes something like:
SELECT dbms_metadata.get_ddl('USER',cur_username.username)
INTO var
FROM dual;