Getting objects definitions in PL/SQL with DBMS

SQL Developer is a powerful and user-friendly tool that greatly simplifies the work of database administrators. It provides a graphical interface for database management and development, making it easier to execute SQL queries, create and manage database objects, and perform other database-related tasks.

SQL Developer provides two quick and efficient methods to extract the DDL statements of any database object. Both methods are Quick DDL and Export as outlined below.

I tend to use the latter more frequently as it offers greater flexibility, allowing me to select the specific clauses I want.

However, it’s important to remember that there may be situations where SQL Developer is not available or suitable for use. For instance, you might be working on a system that doesn’t have SQL Developer installed, or you might need to perform tasks that are not supported by SQL Developer.

In such cases, knowing alternative ways to interact with the database can be invaluable. This could include using SQL*Plus, a command-line tool that comes with every Oracle Database installation, or using PL/SQL, Oracle’s procedural language for SQL, which can be used to write scripts and create stored procedures and functions.

It’s not that using a graphical tool is wrong, but if you want to work like a professional or need to automate the handling of multiple database objects, it’s better to make use of the extensive library of stored PL/SQL procedures. In this case, we will use the DBMS_METADATA.GET_DDL function which, together with DBMS_METADATA.SET_TRANSFORM_PARAM, allows us to extract the DDL statement in the form we need.

In the provided code snippet, the SET_TRANSFORM_PARAM procedure is configured to instruct the database to exclude the segment clause and to format the output with indents and line breaks for improved readability. Finally, the GET_DDL function is invoked to retrieve the DDL statement corresponding to the specified database object.

EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES', FALSE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY', TRUE);

select DBMS_METADATA.GET_DDL(
    object_type => 'TABLE'
    , name      => 'COUNTRIES'
    , schema    => 'HR')
from dual;

CREATE TABLE "HR"."COUNTRIES" 
   (	"COUNTRY_ID" CHAR(2) CONSTRAINT "COUNTRY_ID_NN" NOT NULL ENABLE, 
	"COUNTRY_NAME" VARCHAR2(60), 
	"REGION_ID" NUMBER, 
	 CONSTRAINT "COUNTRY_C_ID_PK" PRIMARY KEY ("COUNTRY_ID") ENABLE, 
	 CONSTRAINT "COUNTR_REG_FK" FOREIGN KEY ("REGION_ID")
	  REFERENCES "HR"."REGIONS" ("REGION_ID") ENABLE
   ) ORGANIZATION INDEX NOCOMPRESS

The next example, retrieves the CREATE statement for the tablespace USERS.

SQL> select DBMS_METADATA.GET_DDL(
         object_type => 'TABLESPACE',
         name => 'USERS'
        ) from dual;


DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'TABLESPACE',NAME=>'USERS')
--------------------------------------------------------------------------------

  CREATE TABLESPACE "USERS" DATAFILE
  '/opt/oracle/oradata/XE/XEPDB1/users01.

SQL>

Now, we’ll use a cursor to show the DDL statement of every user’s table.

SET SERVEROUTPUT ON

BEGIN
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES', FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY', FALSE);

for r in (select owner, table_name from all_tables where owner = 'HR') loop
    dbms_output.put_line(DBMS_METADATA.GET_DDL(
        object_type => 'TABLE'
        , name      => r.table_name
        , schema    => r.owner));
end loop;

END;
/

  CREATE TABLE "HR"."DEPARTMENTS" ("DEPARTMENT_ID" NUMBER(4,0), "DEPARTMENT_NAME" VARCHAR2(30 BYTE), "MANAGER_ID" NUMBER(6,0), "LOCATION_ID" NUMBER(4,0)) ;

  CREATE TABLE "HR"."EMPLOYEES" ("EMPLOYEE_ID" NUMBER(6,0), "FIRST_NAME" VARCHAR2(20 BYTE), "LAST_NAME" VARCHAR2(25 BYTE), "EMAIL" VARCHAR2(25 BYTE), "PHONE_NUMBER" VARCHAR2(20 BYTE), "HIRE_DATE" DATE, "JOB_ID" VARCHAR2(10 BYTE), "SALARY" NUMBER(8,2), "COMMISSION_PCT" NUMBER(2,2), "MANAGER_ID" NUMBER(6,0), "DEPARTMENT_ID" NUMBER(4,0)) ;

  CREATE TABLE "HR"."EMP_TEMP" ("NAME" VARCHAR2(300 BYTE)) ;

  CREATE TABLE "HR"."JOBS" ("JOB_ID" VARCHAR2(10 BYTE), "JOB_TITLE" VARCHAR2(35 BYTE), "MIN_SALARY" NUMBER(6,0), "MAX_SALARY" NUMBER(6,0)) ;

  CREATE TABLE "HR"."JOB_HISTORY" ("EMPLOYEE_ID" NUMBER(6,0), "START_DATE" DATE, "END_DATE" DATE, "JOB_ID" VARCHAR2(10 BYTE), "DEPARTMENT_ID" NUMBER(4,0)) ;

  CREATE TABLE "HR"."J_PURCHASEORDER" ("ID" VARCHAR2(32 BYTE), "DATE_LOADED" TIMESTAMP (6) WITH TIME ZONE, "PO_DOCUMENT" VARCHAR2(3276 BYTE)) ;

  CREATE TABLE "HR"."LOCATIONS" ("LOCATION_ID" NUMBER(4,0), "STREET_ADDRESS" VARCHAR2(40 BYTE), "POSTAL_CODE" VARCHAR2(12 BYTE), "CITY" VARCHAR2(30 BYTE), "STATE_PROVINCE" VARCHAR2(25 BYTE), "COUNTRY_ID" CHAR(2 BYTE)) ;

  CREATE TABLE "HR"."REGIONS" ("REGION_ID" NUMBER, "REGION_NAME" VARCHAR2(25 BYTE)) ;

  CREATE TABLE "HR"."COUNTRIES" ("COUNTRY_ID" CHAR(2 BYTE), "COUNTRY_NAME" VARCHAR2(60 BYTE), "REGION_ID" NUMBER,  CONSTRAINT "COUNTRY_C_ID_PK" PRIMARY KEY ("COUNTRY_ID") ENABLE) ORGANIZATION INDEX NOCOMPRESS ;


PL/SQL procedure successfully completed.

Finally, let’s expand the cursor to include every user’s object regardless its type.

SET SERVEROUTPUT ON

BEGIN
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES', FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY', FALSE);

for r in (select owner, object_type, object_name from all_objects where owner = 'HR' order by object_type, object_name) loop
    dbms_output.put_line(DBMS_METADATA.GET_DDL(
        object_type => r.object_type
        , name      => r.object_name
        , schema    => r.owner));
end loop;

END;
/

Note that the output of this example presents a dependency problem as it does not take into account the correct execution order. That is, before creating a foreign key or a view, we must ensure that the tables being referenced have already been created. You are invited to share your thoughts on how you would tackle this problem.

To wrap up

While SQL Developer is a fantastic tool, it’s always beneficial to have a diverse set of skills and tools at your disposal when working with Oracle databases. This ensures that you’re prepared for any situation and can work effectively under a variety of conditions.