Export Your Oracle APEX Database Objects to CSV for Backup

If you’re working with Oracle APEX, creating regular backups of your database structure is a smart habit — especially if you’re dealing with production apps or complex development environments. In this post, I’ll show you how to use a simple but powerful SQL query to extract DDL statements for all your main database objects and export them as an CSV file using the SQL Workshop.

🧰 What You’ll Get

This query gathers the DDL (Data Definition Language) for the following object types in your schema:

• Tables
• Views
• Triggers
• Procedures
• Functions
• Packages & Package Bodies
• Sequences (excluding system-generated ones)
• DB Links
• Indexes (excluding LOB and system-generated)

🧾 The SQL Query

Here’s the full SQL query you can run inside Oracle APEX > SQL Workshop > SQL Commands:

SELECT 'TABLE' AS object_type, 
       t.table_name AS object_name, 
       DBMS_METADATA.GET_DDL('TABLE', t.table_name) AS ddl
FROM user_tables t
UNION ALL
SELECT 'VIEW' AS object_type, 
       v.view_name AS object_name,
       DBMS_METADATA.GET_DDL('VIEW', v.view_name) AS ddl
FROM user_views v
UNION ALL
SELECT 'TRIGGER' AS object_type, 
       tr.trigger_name AS object_name,
       DBMS_METADATA.GET_DDL('TRIGGER', tr.trigger_name) AS ddl
FROM user_triggers tr
UNION ALL
SELECT 'PROCEDURE' AS object_type, 
       o.object_name AS object_name,
       DBMS_METADATA.GET_DDL('PROCEDURE', o.object_name) AS ddl
FROM user_objects o
WHERE o.object_type = 'PROCEDURE'
UNION ALL
SELECT 'FUNCTION' AS object_type, 
       o.object_name AS object_name,
       DBMS_METADATA.GET_DDL('FUNCTION', o.object_name) AS ddl
FROM user_objects o
WHERE o.object_type = 'FUNCTION'
UNION ALL
SELECT 'PACKAGE' AS object_type, 
       o.object_name AS object_name,
       DBMS_METADATA.GET_DDL('PACKAGE', o.object_name) AS ddl
FROM user_objects o
WHERE o.object_type = 'PACKAGE'
UNION ALL
SELECT 'PACKAGE BODY' AS object_type, 
       o.object_name AS object_name,
       DBMS_METADATA.GET_DDL('PACKAGE_BODY', o.object_name) AS ddl
FROM user_objects o
WHERE o.object_type = 'PACKAGE BODY'
UNION ALL
SELECT 'SEQUENCE' AS object_type,
       s.sequence_name AS object_name,
       DBMS_METADATA.GET_DDL('SEQUENCE', s.sequence_name) AS ddl
FROM user_sequences s
WHERE s.sequence_name NOT LIKE 'ISEQ$$_%'  -- Exclude system-generated sequences
UNION ALL
SELECT 'DB_LINK' AS object_type,
       d.db_link AS object_name,
       DBMS_METADATA.GET_DDL('DB_LINK', d.db_link) AS ddl
FROM user_db_links d
UNION ALL
SELECT 'INDEX' AS object_type,
       i.index_name AS object_name,
       DBMS_METADATA.GET_DDL('INDEX', i.index_name) AS ddl
FROM user_indexes i
WHERE i.index_type NOT LIKE '%LOB%'  -- Exclude LOB indexes
AND i.generated = 'N'  -- Exclude system-generated indexes
ORDER BY object_type, object_name;

📤How to Export the Results to CSV

Once you’ve run the query in SQL Workshop, follow these steps:

1. Go to Oracle APEX > SQL Workshop > SQL Commands
2. Paste and run the query
3. Once results are displayed, scroll down.
4. Choose Download
5. Save the CSV file to your local system — this serves as your DDL backup

✅ Summary

Creating a structural backup of your Oracle APEX schema using DDL extraction is easy, fast, and secure. With this method, you’re just a few clicks away from exporting your tables, views, triggers, and more — all into an CSV file.