Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Generating User DDL in SQLcl

3 Mins Read

The DDL command in SQLcl allows you to get the same information you’d see in an object editor’s SQL panel in SQL Developer.

Like so:

It’s all DBMS_METADATA calls under the covers…

But a user asked me recently about doing this for an actual Oracle USER, say, ‘HR’.

So let’s make this happen TODAY, versus waiting for a product enhancement update.

Make Your Own USER Command

One of the SQLcl commands is called ‘ALIAS.’ It allows you to take a query or script and map it to a new command in SQLcl. It also allows for positional :binds in your code.

So, I’m going to steal the SQL that SQL Developer uses when generating DDL for a schema, and I’m going to re-purpose it to a new command in SQLcl called ‘USER.’

Getting the SQL We Need

We just do ‘the work’ in SQL Developer, and then observe the ‘Statements’ panel.

Use the Filter feature to help you find the right Queries – SQL Developer sends a LOT to the database on your behalf.

Now that we have our code identified, copy and paste it into SQLcl (although I did format it first in SQL Developer so it’d be easier to read here.

The ALIAS Command

No worries, I share the code below!

There’s only one :bind in the query – that’s ‘:NAME’ – so when I invoke the command, I only need one parameter – the name of the USER I want the DDL for.

Here’s the SQL you need (tested on an 18c Oracle Database)

SELECT DBMS_METADATA.GET_DDL(
    'USER',
    :NAME
)
  FROM DUAL
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL(
    'ROLE_GRANT',
    GRANTEE
)
  FROM DBA_ROLE_PRIVS
 WHERE GRANTEE = :NAME
   AND ROWNUM = 1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL(
    'SYSTEM_GRANT',
    GRANTEE
)
  FROM DBA_SYS_PRIVS          SP,
       SYSTEM_PRIVILEGE_MAP   SPM
 WHERE SP.GRANTEE = :NAME
   AND SP.PRIVILEGE  = SPM.NAME
   AND SPM.PROPERTY <> 1
   AND ROWNUM        = 1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL(
    'OBJECT_GRANT',
    GRANTEE
)
  FROM DBA_TAB_PRIVS
 WHERE GRANTEE = :NAME
   AND ROWNUM = 1

So when you need a command that we haven’t built yet, build it yourself 🙂

But Wait, This Won’t Run – There’s no Semicolons!

Dawn pointed out in the comments, that DBMS_METADATA leaves out statement delimiters by default. This is by design as a program running SQL directly on a driver, say JDBC, doesn’t need to include semicolons.

But, we’re generating code which will ultimately be ran ad-hoc most likely in SQLcl or some other query interface.

To get the semicolons, just put this in your login.sql or run this before running your new USER command:

BEGIN
dbms_metadata.set_transform_param(
        dbms_metadata.session_transform,
        'SQLTERMINATOR',TRUE);
END;
/

And now when we run the command again, we get…

SQL> SET long 1000
SQL> USER HR
 
DBMS_METADATA.GET_DDL('USER',:NAME)
--------------------------------------------------------------------------------
 
   CREATE USER "HR" IDENTIFIED BY VALUES 'S:74A7D802F0664676487836016A9F085517CC
153D4DE1384C459CCD17DD75;T:C7067A2E84D0376E29B317DF9CFE73087D379589842FB537C46D7
406C388F735CD6ED4FC83222D9F340DBDA9A3443192C690764BFE4BB46260B9B3B5980F839B7252F
969AA89FB889EBD1115D6A8955D'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";
 
 
   GRANT "DBA" TO "HR";
   GRANT "SELECT_CATALOG_ROLE" TO "HR";
 
DBMS_METADATA.GET_DDL('USER',:NAME)
--------------------------------------------------------------------------------
   GRANT "EXECUTE_CATALOG_ROLE" TO "HR";
   GRANT "ROLE_TD" TO "HR" WITH ADMIN OPTION;
   GRANT "ROLE_TD3" TO "HR" WITH ADMIN OPTION;

Notice also I’ve set LONG to a bigger number as this package returns the DDL as a CLOB, and SQLcl will need more characters available to display the entire script.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

6 Comments

  1. Fahad AlHoshan Reply

    I tried it with
    Oracle SQLDeveloper Command-Line (SQLcl) version: 22.2.0.0 build: 22.2.0.173.1733

    with no success.

  2. harry Reply

    But you can already do a ‘create like’ in sql developer for a user.

    Just use the DBA view, security, right click the user you want and away you go.

    Note, object level grants aren’t replicated unfortunately

    • thatjeffsmith Post Author

      But I’m at a cmd prompt, not in a GUI…I want my cake and I want to eat it 🙂

      Also, if you want the grants in SQLDev, you have to enable them in preferences under Database, Export.

  3. Boneist Reply

    Now, if only dbms_metadata subprograms could return commands that you can just copy and paste to rerun, instead of manually having to add semi-colons all over the place! Looking at your example, that still doesn’t happen automatically!

    • thatjeffsmith Post Author

      Just need to set the transformer

      dbms_metadata.set_transform_param(indexTransHandle,
      ‘SQLTERMINATOR’, TRUE);

Write A Comment