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 );
Parent topic: APEX_EXEC