Mastering Generate DDL for Database Object Management

Oracle APEX 24 introduces a powerful feature called Generate DDL (Data Definition Language) — a game-changer for developers and DBAs who want full control over their database object definitions. Whether you need to document your schema, migrate objects across environments, or create structured backup scripts, Generate DDL simplifies the process dramatically.

In this article, we’ll explore what Generate DDL is, how it works, practical examples, advanced options, and best practices for exporting to file.

---

What is Generate DDL?

Generate DDL allows you to generate SQL scripts that represent the structure of your database objects. This includes tables, views, indexes, packages, triggers, and more. The tool is accessible directly from the APEX UI under SQL Workshop > Utilities.

---

Key Use Cases

• Schema Documentation: Generate a complete structural definition of your database objects.

• Environment Migration: Move objects seamlessly between dev, test, and production environments.

• Disaster Recovery: Rebuild object definitions in case of loss or corruption.

• Backup Automation: Create DDL-based backups of critical database components.

---

How to Use Generate DDL in Oracle APEX 24

1. Navigate to SQL Workshop > Utilities > Generate DDL.

2. Select the object types you want to export (tables, views, packages, etc.).

3. Apply filters by schema or name patterns.

4. Enable or disable advanced options.

5. Click Generate DDL.

6. Download or copy the script.

---

Practical Examples

Example 1: Table

CREATE TABLE "HR"."EMPLOYEES" 
(
  "EMPLOYEE_ID" NUMBER(6,0), 
  "FIRST_NAME" VARCHAR2(20), 
  "LAST_NAME" VARCHAR2(25), 
  "EMAIL" VARCHAR2(25), 
  "HIRE_DATE" DATE, 
  "JOB_ID" VARCHAR2(10), 
  "SALARY" NUMBER(8,2), 
  "DEPARTMENT_ID" NUMBER(4,0), 
  CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
);

Example 2: Index

CREATE INDEX "HR"."EMP_DEPARTMENT_IX" 
ON "HR"."EMPLOYEES" ("DEPARTMENT_ID");

Example 3: View

CREATE OR REPLACE FORCE VIEW "HR"."EMP_DEPT_VIEW" AS 
SELECT e.employee_id, 
       e.first_name || ' ' || e.last_name AS full_name, 
       d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Example 4: Package

CREATE OR REPLACE PACKAGE "HR"."EMP_PKG" AS
  PROCEDURE update_salary(p_emp_id IN NUMBER, p_amount IN NUMBER);
  FUNCTION get_employee_name(p_emp_id IN NUMBER) RETURN VARCHAR2;
END EMP_PKG;
/

CREATE OR REPLACE PACKAGE BODY "HR"."EMP_PKG" AS
  PROCEDURE update_salary(p_emp_id IN NUMBER, p_amount IN NUMBER) IS
  BEGIN
    UPDATE employees SET salary = salary + p_amount WHERE employee_id = p_emp_id;
  END;

  FUNCTION get_employee_name(p_emp_id IN NUMBER) RETURN VARCHAR2 IS
    l_name VARCHAR2(50);
  BEGIN
    SELECT first_name || ' ' || last_name INTO l_name
    FROM employees WHERE employee_id = p_emp_id;
    RETURN l_name;
  END;
END EMP_PKG;
/

---

Filtering Options

Generate DDL provides robust filtering capabilities:

Filter by Object Type

You can choose to generate DDL only for specific object types:

• Tables

• Views

• Materialized Views

• Packages

• Procedures

• Functions

• Triggers

• Sequences

• Synonyms

• Types

• Indexes

• Constraints

Filter by Schema Owner

Limit the objects to a specific schema (e.g., HR, APP_USER).

Filter by Name Pattern

Use wildcard filters like %EMP% to select objects with names containing EMP.

---

Advanced Options

Table Options

• Include Storage: Tablespace, storage parameters (INITRANS, PCTFREE, etc.)

• Include Constraints: Primary/Foreign keys, check constraints

• Include Grants: Object-level privileges

• Include Triggers: Associated triggers

• Include Indexes: All indexes for the table

General Options

• Include DROP Statements: Adds DROP before each CREATE

• Include Comments: Includes comments on tables, columns, etc.

• Include Schema Prefix: Adds schema name before object name

• Include Tablespace Info: Adds tablespace storage details

Formatting Options

• Pretty Print: Beautify the output with indentation

• Line Size: Set line width

• Page Size: Control pagination in output

---

Exporting DDL to File

Option 1: Download Button

After generation, click Download to save the .sql file directly.

Option 2: Manual Copy-Paste

1. Copy the DDL script

2. Paste it into a text editor like Notepad++ or VS Code

3. Save with .sql extension

Option 3: PL/SQL Scripting (Advanced)

You can also write a PL/SQL block to save DDL to the file system using UTL_FILE:

BEGIN
  UTL_FILE.PUT_LINE(
    UTL_FILE.FOPEN('MY_DIR', 'my_ddl_script.sql', 'W'),
    '-- DDL Generated by APEX'
  );

  FOR ddl_line IN (SELECT ddl_text FROM generated_ddl_view) LOOP
    UTL_FILE.PUT_LINE('MY_DIR', 'my_ddl_script.sql', ddl_line.ddl_text);
  END LOOP;

  UTL_FILE.FCLOSE('MY_DIR', 'my_ddl_script.sql');
END;

---

Real-World Scenario: Full DDL with Advanced Options

Goal: Export full DDL of all HR schema tables

Step-by-Step:

1. Go to SQL Workshop > Utilities > Generate DDL

2. Set object type to Tables

3. Schema: HR

4. Name Pattern: % (all tables)

5. Enable all advanced options:

   • Include Storage

   • Include Constraints

   • Include Grants

   • Include Triggers

   • Include Indexes

   • Include Comments

6. Click Generate DDL

7. Click Download, e.g., HR_Tables_Full_DDL.sql

Sample Output:

-- DDL for Table HR.EMPLOYEES
CREATE TABLE "HR"."EMPLOYEES" 
(
  "EMPLOYEE_ID" NUMBER(6,0),
  "FIRST_NAME" VARCHAR2(20),
  ...
)
TABLESPACE "USERS"
PCTFREE 10 INITRANS 1
STORAGE (
  INITIAL 65536
  NEXT 1048576
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  PCTINCREASE 0
);

---

Best Practices & Considerations

• Large Files: For large schemas, the output file can be massive. Filter wisely.

• DROP Statements: Use with caution in production environments.

• Security: DDL scripts may expose internal structures—treat them securely.

• Version Control: Store your DDL scripts in Git or another VCS for traceability.

---

Conclusion

The Generate DDL utility in Oracle APEX 24 empowers developers to manage database objects with clarity, precision, and flexibility. Whether you're automating deployments, documenting your schema, or preparing for audits, this tool provides everything you need in a clean, configurable interface.

Take advantage of its advanced filtering and export options to bring your APEX development workflow to the next level.