Skip to Main Content
  • Questions
  • PL\SQL to GetDDL for all user account except system account using cursor and loop

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Sudhakaran.

Asked: July 06, 2020 - 8:49 am UTC

Answered by: Chris Saxon - Last updated: July 27, 2020 - 2:15 am UTC

Category: Database Administration - Version: 11g

Viewed 100+ times

You Asked

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:

and we said...

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;

and you rated our response

  (1 rating)

Reviews

Column ORACLE MAINTAINED

July 24, 2020 - 2:17 pm UTC

Reviewer: David D. from FRANCE

Hello masters,

Starting with Oracle 12.1, there is no need to write this infinite list of names
"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');"

you can use the new magic column ORACLE_MAINTAINED which is in DBA_USERS, DBA_ROLES and DBA_OBJECTS.

DBA_USERS doc says : "Denotes whether the user was created, and is maintained, by Oracle-supplied scripts (such as catalog.sql or catproc.sql). A user for which this column has the value Y must not be changed in any way except by running an Oracle-supplied script."

So you can change your WHERE clause by
where username not in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED = 'Y')

Have a nice day

David D.
Connor McDonald

Followup  

July 27, 2020 - 2:15 am UTC

nice input

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.