ADD_COLUMN Procedure

This procedure adds a column to the columns collection.

Columns collections can be passed to the OPEN_*_CONTEXT calls in order to request only a subset of columns. This is particularly useful for REST Data Sources without a SQL statement. If no or an empty column array is passed, all columns defined in the web source are fetched.

Syntax

APEX_EXEC.ADD_COLUMN (
    p_columns               IN OUT NOCOPY   t_columns,
    p_column_name           IN              VARCHAR2,
    p_data_type             IN              t_data_type DEFAULT NULL,
    p_sql_expression        IN              VARCHAR2    DEFAULT NULL,
    p_format_mask           IN              VARCHAR2    DEFAULT NULL,
    p_is_primary_key        IN              BOOLEAN     DEFAULT FALSE,
    p_is_query_only         IN              BOOLEAN     DEFAULT FALSE,
    p_is_returning          IN              BOOLEAN     DEFAULT FALSE,
    p_is_checksum           IN              BOOLEAN     DEFAULT FALSE,
    p_parent_column_path    IN              VARCHAR2    DEFAULT NULL );

Parameters

Parameter	Description
`p_columns`	Columns array.
`p_column_name`	Column name.
`p_data_type`	Column data type.
`p_sql_expression`	SQL expression used to derive a column from other columns.
`p_format_mask`	Format mask to use for this column.
`p_is_primary_key`	Whether this is a primary key column (default `FALSE`).
`p_is_query_only`	Query only columns are not written in a DML context (default `FALSE`).
`p_is_returning`	Whether to retrieve the `RETURNING` column after DML has been executed (default `FALSE`).
`p_is_checksum`	Whether this is a checksum (row version) column (default `FALSE`).
`p_parent_column_path`	Path to the parent column to look the index up within.

Example

DECLARE
    l_columns     apex_exec.t_columns;
    l_context     apex_exec.t_context;
BEGIN
    apex_exec.add_column(
        p_columns     => l_columns,
        p_column_name => 'ENAME' );

    apex_exec.add_column(
        p_columns     => l_columns,
        p_column_name => 'SAL' );

    l_context := apex_exec.open_rest_source_query(
        p_module_static_id => '{REST Data Source static ID}', 
        p_columns          => l_columns
        p_max_rows         => 1000 );

        while apex_exec.next_row( l_context ) LOOP
           -- process rows here ...
        END LOOP;

    apex_exec.close( l_context );
EXCEPTION
    when others then
         apex_exec.close( l_context );
         raise;
END;