GET_DDL

The GET_DDL function fetches the metadata of an object and returns the data in DDL. It is used to extract the DDL statements of database objects such as TABLE, VIEW, INDEX, PROCEDURE, SEQUENCE, TRIGGER, SYNONYM, FUNCTION, and PACKAGE.

The GET_xxx functions return the metadata of objects with a single call. The current OceanBase Database version supports only GET_DDL.

Syntax

FUNCTION  GET_DDL (
    object_type     VARCHAR,
    name            VARCHAR,
    ob_schema       VARCHAR DEFAULT NULL,
    version         VARCHAR DEFAULT 'COMPATIBLE',
    model           VARCHAR DEFAULT 'ORACLE',
    transform       VARCHAR DEFAULT 'DDL')
RETURN CLOB;

Parameters

Examples

Simple examples

Below are example SQL statements for different object types, demonstrating how to use GET_DDL to retrieve the corresponding DDL. Note that the object names in these examples (such as 'MY_TABLE_NAME') need to be replaced with the actual object names in your database.

• Extract the DDL statement for a table (TABLE):

  SELECT DBMS_METADATA.GET_DDL('TABLE', 'MY_TABLE_NAME') FROM DUAL;
  

• Extract the DDL statement for a view (VIEW):

  SELECT DBMS_METADATA.GET_DDL('VIEW', 'MY_VIEW_NAME') FROM DUAL;
  

• Extract the DDL statement for an index (INDEX):

  SELECT DBMS_METADATA.GET_DDL('INDEX', 'MY_INDEX_NAME') FROM DUAL;
  

• Extract the DDL statement for a stored procedure (PROCEDURE):

  SELECT DBMS_METADATA.GET_DDL('PROCEDURE', 'MY_PROCEDURE_NAME') FROM DUAL;
  

• Extract the DDL statement for a sequence (SEQUENCE):

  SELECT DBMS_METADATA.GET_DDL('SEQUENCE', 'MY_SEQUENCE_NAME') FROM DUAL;
  

• Extract the DDL statement for a trigger (TRIGGER):

  SELECT DBMS_METADATA.GET_DDL('TRIGGER', 'MY_TRIGGER_NAME') FROM DUAL;
  

• Extract the DDL statement for a synonym (SYNONYM):

  SELECT DBMS_METADATA.GET_DDL('SYNONYM', 'MY_SYNONYM_NAME') FROM DUAL;
  

• Extract the DDL statement for a function (FUNCTION):

  SELECT DBMS_METADATA.GET_DDL('FUNCTION', 'MY_FUNCTION_NAME') FROM DUAL;
  

• Extract the DDL statement for a package (PACKAGE):

  SELECT DBMS_METADATA.GET_DDL('PACKAGE', 'MY_PACKAGE_NAME') FROM DUAL;
  

Complete examples

This section provides two complete examples to illustrate how to use GET_DDL.

• Example 1: Create a function named tEst that takes three parameters: an integer tEst, a string "nAMe", and a float age. The function returns a float.

create function tEst(tEst int, "nAMe" varchar2, age float)
return float
as
begin
  return age;
end;
/

-- Set the server output to allow printing messages to the console.
set serveroutput on;

-- Call the `DBMS_METADATA.GET_DDL` function to retrieve the DDL statement of the function named 'TEST'.
call dbms_output.put_line(dbms_metadata.get_ddl('FUNCTION','TEST'));

DBMS_METADATA.GET_DDL returns the DDL statement for the created object.

The result is as follows:

CREATE OR REPLACE PROCEDURE "SYS"."TEST"
(
 "TEST" IN int, "nAMe" IN varchar2, "AGE" IN float
) IS
begin
  null;
end

• Example 2: Created a stored procedure named tEst1 that takes three parameters: an integer tEst1, a string "nAMe", and a float age.

create procedure tEst1(tEst1 int, "nAMe" varchar2, age float)
as
begin
  null;
end;
/

-- Set the server output to allow printing messages in the SQL command line interface.
set serveroutput on;

-- Call the DBMS_METADATA.GET_DDL function to retrieve the DDL statement of the procedure named 'TEST1'.
call dbms_output.put_line(dbms_metadata.get_ddl('PROCEDURE','TEST1'));

The returned result is a DDL statement created for procedure TEST1.

The result is as follows:

CREATE OR REPLACE PROCEDURE "SYS"."TEST1"
(
 "TEST1" IN int, "nAMe" IN varchar2, "AGE" IN float
) IS
begin
  null;
end