GET_METADATA Function

This procedure wraps DBMS_DEVELOPER.GET_METADATA for calling from within an APEX application. Parameters are modeled to match DBMS_DEVELOPER.GET_METADATA and pass unmodified. The return value may be passed as is to APEX_DB_DICTIONARY.FORMAT_METADATA.

Syntax

FUNCTION apex_db_dictionary.get_metadata (
    p_name           IN   VARCHAR2,
    p_schema         IN   VARCHAR2     DEFAULT NULL,
    p_object_type    IN   VARCHAR2     DEFAULT NULL,
    p_level          IN   VARCHAR2     DEFAULT 'typical',
    p_etag           IN   RAW          DEFAULT NULL )
    RETURN JSON;

Parameters

Parameter	Description
`p_name`	The object name. A synonym of the object name can also be provided. It is case sensitive and should be provided as it appears in the data dictionary.
`p_schema`	The schema name. The default is the current user. It is case sensitive and should be provided as it appears in the data dictionary.
`p_object_type`	The type of object that you want to be retrieved. This is optional as name resolution can also be done without specifying object_type. Supported values: `TABLE`, `INDEX`, and `VIEW`.
`p_level`	The level of detail. The default is `TYPICAL`. Available values are `BASIC` (columns only), `TYPICAL` (includes constraints and annotations), or `ALL` (includes storage info).
`p_etag`	A unique identifier for a specific version of the document. This etag value lets an application determine whether the content of a particular version of a document is the same as that of another version.

Returns

The function returns metadata in JSON form as JSON on Oracle Database 21c and later and as CLOB on earlier supported releases.

Example 1

On Oracle Database 21c and later, this function returns JSON.

declare
    l_json json;
begin
    l_json := apex_db_dictionary.get_metadata( 
        p_name => 'EMP', 
        p_level => 'ALL' ); 
end;

Example 2

On earlier supported releases, this function returns CLOB.

declare
    l_json clob;
begin
    l_json := apex_db_dictionary.get_metadata( 
        p_name => 'EMP', 
        p_level => 'ALL' ); 
end;

23.1 GET_METADATA Function