PolarDB:DBMS_METADATA

Subprograms

GET_DDL

This function is used to retrieve the metadata of an object as DDL statements. This function is defined in the DBMS_METADATA package.

Syntax

FUNCTION get_ddl(
    object_type IN VARCHAR2,
    name        IN VARCHAR2,
    schema      IN VARCHAR2 DEFAULT NULL,
    version     IN VARCHAR2 DEFAULT 'compatible',
    model       IN VARCHAR2 DEFAULT 'polardb',
    transform   IN VARCHAR2 DEFAULT 'ddl'
) RETURN CLOB

Parameters

Example

The following example shows how to use the DBMS_METADATA.GET_DDL function to retrieve the metadata of a table as DDL statements.

You can create a table named t in public mode, and then use the DBMS_METADATA.GET_DDL function to retrieve the metadata of the table as DDL statements. In this case, the object_type parameter is set to table, the name parameter is set to t, and the schema parameter is set to public.

CREATE TABLE t(a int, b text);
SELECT DBMS_METADATA.GET_DDL('table', 't', 'public');

 get_ddl
---------------------------------------
 CREATE TABLE IF NOT EXISTS public.t (
 a integer, 
 b text COLLATE "default" 
 )
(1 row)

Usage notes

Parameter case-sensitivity

• The object_type parameter is case-insensitive. For example, the values Table, TABLE, and Table are equivalent.

• The name parameter is case-sensitive. For example, if the object name is BIG_t, you cannot enter big_t.

• The schema parameter is case-sensitive. For example, if the schema is public, you cannot enter PUBLIC.

• If the name and schema parameters are in uppercase, the object and schema names are enclosed in double quotation marks (" ") to ensure case sensitivity in the retrieved DDL statements.

Example:

CREATE TABLE "BIG_t"("BIG_a" int, "BIG_b" text);

# The object_type parameter is case-insensitive: For example, the values table and TABLE are equivalent.
SELECT DBMS_METADATA.GET_DDL('table', 'BIG_t', 'public');

                   get_ddl
---------------------------------------------
 CREATE TABLE IF NOT EXISTS public."BIG_t" (
     "BIG_a" integer,                       
     "BIG_b" text COLLATE "default"         
 )
(1 row)

SELECT DBMS_METADATA.GET_DDL('TABLE', 'BIG_t', 'public');
                   get_ddl
---------------------------------------------
 CREATE TABLE IF NOT EXISTS public."BIG_t" (
     "BIG_a" integer,                       
     "BIG_b" text COLLATE "default"         
 )
(1 row)

# The name and schema parameter are case-sensitive. You must enter the correct object and schema names.
SELECT DBMS_METADATA.GET_DDL('table', 'big_t', 'public');
ERROR:  Polar-31603: Object "big_t" of type "table" not found in schema "public"

SELECT DBMS_METADATA.GET_DDL('table', 'BIG_t', 'PUBLIC');
ERROR:  Polar-31603: Object "BIG_t" of type "table" not found in schema "PUBLIC"

Unsupported schemas

• You cannot specify schemas for some object types, if these objects do not belong to the specified schemas. For example, if you specify a schema for the role type, the -31600 exception is thrown.

  create role role1;
  select dbms_metadata.get_ddl('role', 'role1');
  
                    get_ddl
  -------------------------------------------
   CREATE ROLE role1 WITH                   
   NOSUPERUSER NOCREATEDB NOCREATEROLE      
   INHERIT NOLOGIN NOREPLICATION NOBYPASSRLS
   CONNECTION LIMIT -1 PASSWORD NULL
  (1 row)
  
  select dbms_metadata.get_ddl('role', 'role1', 'public');
  
  ERROR:  Polar-31600: Invalid input value "public" for parameter SCHEMA in function get_ddl
  DETAIL:  No need to specify schema for type ROLE/USER

• You can specify schemas for some object types such as the trigger in Oracle. However, you cannot specify a schema for the trigger in PolarDB because the trigger does not belong to the specified schema. To maintain compatibility with Oracle, the system reports no error when you specify a schema for an object type. The system reports only a warning indicating that the schema is ignored, and then continues to return results.

  create trigger trigger1 after insert on public.t for each row execute procedure print_insert();
  select dbms_metadata.get_ddl('trigger', 'trigger1', 'public');
  WARNING:  No need to specify schema for trigger, ignore it.
                                                                get_ddl
  -----------------------------------------------------------------------------------------------------------------------------------
   create trigger trigger1 after insert on public.t each row execute procedure print_insert()
  (1 row)

Supported object types

The following table describes the object types for which you can retrieve DDL statements.

Exceptions

The DBMS_METADATA.GET_DDL function of PolarDB may return the following common types of exceptions:

• If an error such as an invalid object type, an empty object type, or an empty object name occurs, an exception that has code -31600 is thrown, which indicates that the parameter is abnormal.

• If the object does not exist, an exception that has code -31603 is thrown.