23.7 GET_TABLES_JSON Function

This function gets a list of table/view names matching regex criteria as JSON. If p_regex is null after trim, an empty array is returned.

Syntax

FUNCTION apex_db_dictionary.get_tables_json (
    p_regex                  IN   VARCHAR2     DEFAULT NULL,
    p_owner                  IN   VARCHAR2     DEFAULT NULL,JSON
    p_object_type            IN   VARCHAR2     DEFAULT NULL,
    p_include_comments       IN   BOOLEAN      DEFAULT TRUE,
    p_include_annotations    IN   BOOLEAN      DEFAULT TRUE )
    RETURN JSON;

Parameters

Parameter	Description
p_regex	Regular expression for object names.
p_owner	Owner of the tables/views (default current user).
p_object_type	TABLE or VIEW. The default NULL returns tables and views.
p_include_comments	Include table/view comments and comment-derived attributes (default TRUE).
p_include_annotations	Include table/view annotations (default TRUE).

Returns

This function returns a JSON array of objects. Each object includes:

• owner: object owner
• name: object name
• type: TABLE or VIEW
• comment: base table/view comment text, excluding embedded common attributes (when present and requested)
• annotations: table/view annotations plus common attributes parsed from comments (when requested)

Example 1

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

declare
    l_json json;
begin
    l_json := apex_db_dictionary.get_tables_json( 
        p_regex => '^EMP_' );  
end;

Example 2

On Oracle Database 19c and earlier, this function returns CLOB.

declare
    l_json clob;
begin
    l_json := apex_db_dictionary.get_tables_json( 
        p_regex            => '^DB_',
        p_include_comments => FALSE );  
end;