15.3 ADD_COLUMN Procedure

This procedure adds a column to the columns collection. Columns collections can be passed the OPEN_*_CONTEXT calls in order to request only a subset of columns. This is particularly useful for web sources where no SQL statement is involved. If no or an empty column array is passed, all columns defined in the web source are being fetched.

Syntax

PROCEDURE 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 );

Parameters

Table 15-1 ADD_COLUMN Procedure Parameters

Parameter	Description
p_columns	Columns array.
p_column_name	Column name.
p_data_type	Column data type.
p_sql_expression	SQL expression in order 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).

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_web_source_query(
        p_module_static_id => '{web source module 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;