GET_DDL returns one object at a time. To extract the DDL of many objects, filtered by name or type, DBMS_METADATA has a cursor-like API: OPEN a request for an object type, set filters and transforms, FETCH each object's DDL in turn, and CLOSE. It is the basis of schema export tools.
Code for This Guide
The main examples are in the examples/pkg-metadata folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
h := dbms_metadata.open(object_type); -- filters such as NAME, NAME_EXPR, SCHEMA, BASE_OBJECT_NAME dbms_metadata.set_filter(h, filter_name, value); th := dbms_metadata.add_transform(h, 'DDL'); dbms_metadata.set_transform_param(th, name, value); loop v_ddl := dbms_metadata.fetch_clob(h); exit when v_ddl is null; end loop; dbms_metadata.close(h);
Every Table Starting with A
Example:
declare
h number;
th number;
v_ddl clob;
begin
h := dbms_metadata.open('TABLE');
dbms_metadata.set_filter(h, 'NAME_EXPR', q'[like 'A%']'); -- every table starting with A
th := dbms_metadata.add_transform(h, 'DDL');
dbms_metadata.set_transform_param(th, 'SEGMENT_ATTRIBUTES', false);
dbms_metadata.set_transform_param(th, 'CONSTRAINTS', false);
dbms_metadata.set_transform_param(th, 'REF_CONSTRAINTS', false);
loop
v_ddl := dbms_metadata.fetch_clob(h);
exit when v_ddl is null;
dbms_output.put_line(regexp_substr(v_ddl, 'CREATE TABLE [^ ]+') || ': '
|| regexp_count(v_ddl, chr(10) || '\s+"') || ' columns');
end loop;
dbms_metadata.close(h);
end;
/Output:
CREATE TABLE "NIMBUS"."AIRCRAFT": 3 columns CREATE TABLE "NIMBUS"."AIRCRAFT_TYPES": 6 columns CREATE TABLE "NIMBUS"."AIRPORTS": 8 columns PL/SQL procedure successfully completed.
NAME_EXPR filters tables with LIKE 'A%', and the transform leaves out storage clauses and constraints. Each fetch returns one table's DDL, summarized here by its name and number of columns.
Indexes of One Table
Example:
declare
h number;
th number;
v_ddl clob;
begin
h := dbms_metadata.open('INDEX');
dbms_metadata.set_filter(h, 'BASE_OBJECT_NAME', 'FLIGHTS'); -- indexes on FLIGHTS
th := dbms_metadata.add_transform(h, 'DDL');
dbms_metadata.set_transform_param(th, 'SEGMENT_ATTRIBUTES', false);
loop
v_ddl := dbms_metadata.fetch_clob(h);
exit when v_ddl is null;
dbms_output.put_line(trim(replace(v_ddl, chr(10))));
end loop;
dbms_metadata.close(h);
end;
/Output:
CREATE UNIQUE INDEX "NIMBUS"."FLIGHTS_PK" ON "NIMBUS"."FLIGHTS" ("FLIGHT_ID")
CREATE INDEX "NIMBUS"."FLIGHTS_ROUTE_IX" ON "NIMBUS"."FLIGHTS" ("ROUTE_ID")
CREATE INDEX "NIMBUS"."FLIGHTS_DEPARTURE_IX" ON "NIMBUS"."FLIGHTS" (SYS_EXTRACT_UTC("SCHEDULED_DEPARTURE"))
PL/SQL procedure successfully completed.For dependent objects such as indexes, the BASE_OBJECT_NAME filter selects those of one table. FLIGHTS has three, one of them a function-based index on the UTC departure time.
Things to Know
- Set filters before the first FETCH; they cannot change during a request.
- FETCH_CLOB returns NULL when there are no more objects.
- Always CLOSE the handle, including in exception handlers.
Related Guides
Conclusion
The DBMS_METADATA browse API extracts the DDL of many objects with one request: open, filter, transform, fetch until NULL, and close. Use it to script out parts of a schema, and write the result to files with DBMS_LOB.CLOB2FILE.
