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;