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 tables, views, indexes, procedures, sequences, triggers, synonyms, functions, and packages.

A GET_xxx function returns 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 syntax

Here are some examples of using GET_DDL to fetch the DDL statements of different types of objects. You need to replace the object names, such as 'MY_TABLE_NAME', with the actual ones.

• Fetch the DDL statement of a table:

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

• Fetch the DDL statement of a view:

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

• Fetch the DDL statement of a materialized view:

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

• Fetch the DDL statement of an index:

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

• Fetch the DDL statement of a stored procedure:

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

• Fetch the DDL statement of a sequence:

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

• Fetch the DDL statement of a trigger:

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

• Fetch the DDL statement of a synonym:

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

• Fetch the DDL statement of a function:

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

• Fetch the DDL statement of a package:

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

Complete syntax

Here are two complete examples:

• Example 1: Create a function named tEst that accepts three arguments: an integer tEst, a character string "nAMe", and a floating-point number age. The function returns a floating-point number.

  create function tEst(tEst int, "nAMe" varchar2, age float)
  return float
  as
  begin
  return age;
  end;
  /
  
  -- Turn on server output to allow messages to be displayed in the console.
  set serveroutput on;
  
  -- Call the `DBMS_METADATA.GET_DDL` function to obtain 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 used to create the object.

  The return 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 arguments: an integer tEst1, a character string "nAMe", and a floating-point number age.

  create procedure tEst1(tEst1 int, "nAMe" varchar2, age float)
  as
  begin
  null;
  end;
  /
  
  -- Turn on server output to allow messages to be displayed in on the CLI.
  set serveroutput on;
  
  -- Call the `DBMS_METADATA.GET_DDL` function to obtain the DDL statement of the stored procedure named 'TEST1'.
  call dbms_output.put_line(dbms_metadata.get_ddl('PROCEDURE','TEST1'));
  

  DBMS_METADATA.GET_DDL returns the DDL statement used to create the TEST1 stored procedure.

  The return result is as follows:

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