Data Types

The data types used by the APEX_EXEC package are described in this section.

Generic

subtype t_column_name is varchar2(32767);

subtype t_data_type   is pls_integer range 1..13;

type t_value is record (
    varchar2_value      varchar2(32767),
    number_value        number,
    date_value          date,
    timestamp_value     timestamp,
    timestamp_tz_value  timestamp with time zone,
    timestamp_ltz_value timestamp with local time zone,
    interval_y2m_value  interval year to month,
    interval_d2s_value  interval day to second,
    blob_value          blob,
    bfile_value         bfile,
    clob_value          clob,
    anydata_value       sys.anydata );

type t_values is table of t_value index by pls_integer;

Bind variables

type t_parameter is record (
    name      t_column_name,
    data_type t_data_type,
    value     t_value );

type t_parameters is table of t_parameter index by pls_integer;

Column Metadata

subtype t_lov_type is pls_integer range 1..3;

-- Internal type definition. ** DO NOT USE **
type t_lov is record (
    lov_type            t_lov_type,
    shared_lov_id       varchar2(32767),
    sql_query           varchar2(32767),
    static_values       varchar2(32767) ); 

-- Internal type definition. ** DO NOT USE **
type t_column is record (
    name                 t_column_name,
    sql_expression       varchar2(4000),
    -- for analytic functions - apply these after filtering; otherwise apply before filtering to make the
    -- column available to filters
    is_aggregation       boolean default false,
    -- SQL expressions of end user columns can only work on columns specified in the p_columns array parameter
    is_end_user          boolean default true,
    -- required for Generic Columns. If COL25 has "Compute Sum" enabled, we generate a SUM (OVER) clause for it.
    -- When the base SQL query does not return COL25, we have to remove that derived column, so we need to know, on 
    -- which column it depends. 
    dependent_on         t_column_name,
    data_type            t_data_type,
    data_type_length     pls_integer,
    data_type_name       t_column_name, -- $$$ look for a better name
                                        -- required for "display as LOV" in interactive reports
    lov                  t_lov,
    format_mask          varchar2(4000),
    is_required          boolean default false,
    is_primary_key       boolean default false,
    is_query_only        boolean default false,
    is_checksum          boolean default false,
    is_returning         boolean default false );

type t_columns is table of t_column index by pls_integer;

Filters

subtype t_filter_type          is pls_integer range 1..27;
subtype t_filter_interval_type is varchar2(2);

type t_filter is record (
    column_name       t_column_name, -- $$$ should we reference the index of t_columns instead? -> Always require to specify p_columns? Would avoid having to specify data_type
    data_type         t_data_type,
    filter_type       t_filter_type,
    filter_values     t_values,
    sql_expression    varchar2(32767),
    search_columns    t_columns,
    null_result       boolean default false,
    is_case_sensitive boolean default true );

type t_filters is table of t_filter index by pls_integer;

Order Bys

subtype t_order_direction is pls_integer range 1..2;
subtype t_order_nulls     is pls_integer range 1..2;

type t_order_by is record (
    column_name t_column_name, -- $$$ should we reference the index of t_columns instead? -> Always require to specify p_columns?
    position    pls_integer,   -- if given, we will generate the order by clause with a column position 
    lov         t_lov,
    direction   t_order_direction, 
    order_nulls t_order_nulls );

type t_order_bys is table of t_order_by index by pls_integer;

subtype t_location     is varchar2(12);

Context Handle

subtype t_context is pls_integer;

Data Source Capabilities

type t_source_capabilities is record(
    location               t_location,
    --
    pagination             boolean default false,
    --
    allow_fetch_all_rows   boolean default false,
    --
    filtering              boolean default false,
    order_by               boolean default false,
    group_by               boolean default false,
    --
    filter_eq              boolean default false,
    filter_not_eq          boolean default false,
    filter_gt              boolean default false,
    filter_gte             boolean default false,
    filter_lt              boolean default false,
    filter_lte             boolean default false,
    filter_null            boolean default false,
    filter_not_null        boolean default false,
    filter_contains        boolean default false,
    filter_not_contains    boolean default false,
    filter_like            boolean default false,
    filter_not_like        boolean default false,
    filter_starts_with     boolean default false,
    filter_not_starts_with boolean default false,
    filter_between         boolean default false,
    filter_not_between     boolean default false,
    filter_in              boolean default false,
    filter_not_in          boolean default false,
    filter_regexp          boolean default false,
    filter_last            boolean default false,
    filter_not_last        boolean default false,
    filter_next            boolean default false,
    filter_not_next        boolean default false,
    --
    orderby_asc            boolean default false,
    orderby_desc           boolean default false,
    orderby_nulls          boolean default false );