GET_DDL

The GET_DDL function is used to retrieve the metadata of a single object as a DDL statement. It is used to extract DDL (Data Definition Language) statements for database objects such as TABLE, VIEW, INDEX, PROCEDURE, SEQUENCE, TRIGGER, SYNONYM, FUNCTION, and PACKAGE.

The GET_xxx functions are used to retrieve the metadata of an object in a single call. Currently, only the GET_DDL function is supported in OceanBase Database.

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

The following examples show how to use the GET_DDL function to retrieve DDL statements for different object types. Note that the object names in the examples, such as 'MY_TABLE_NAME', need to be replaced with the actual object names in your database.

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

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

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

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

• Retrieve the DDL statement for a materialized view (MATERIALIZED VIEW):

  SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', 'MY_MATERIALIZED_VIEW_NAME') FROM DUAL;
  

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Complete examples

We will show two complete examples to demonstrate how to use the GET_DDL function.

• Example 1: Create a function named tEst that accepts three parameters: an integer tEst, a string "nAMe", and a float age. The return type of the function is float.

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

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

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

The DBMS_METADATA.GET_DDL function returns the DDL statement for creating the object.

The returned 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: Create a stored procedure named tEst1 that accepts 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 server output to allow messages to be printed to the SQL command-line interface.
set serveroutput on;

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

The returned result is the DDL statement for creating the stored procedure named TEST1.

The returned result is as follows:

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