SELECT_LIST_FROM_QUERY_XL Function

This function is the same as SELECT_LIST_FROM_QUERY, but its return value is a CLOB. This allows its use in SQL queries where you need to handle a column value longer than 4000 characters. Similar to other functions available in the APEX_ITEM package, these select list functions are designed to generate forms with F01 to F50 form array elements.

Syntax

APEX_ITEM.SELECT_LIST_FROM_QUERY_XL(
    p_idx           IN    NUMBER,
    p_value         IN    VARCHAR2 DEFAULT NULL,
    p_query         IN    VARCHAR2,
    p_attributes    IN    VARCHAR2 DEFAULT NULL,
    p_show_null     IN    VARCHAR2 DEFAULT 'YES',
    p_null_value    IN    VARCHAR2 DEFAULT '%NULL%',
    p_null_text     IN    VARCHAR2 DEFAULT '%',
    p_item_id       IN    VARCHAR2 DEFAULT NULL,
    p_item_label    IN    VARCHAR2 DEFAULT NULL,
    p_show_extra    IN    VARCHAR2 DEFAULT 'YES')
    RETURN CLOB;

Parameters

Table: SELECT_LIST_FROM_QUERY_XL Parameters describes the parameters available in the SELECT_LIST_FROM_QUERY_XL function.

SELECT_LIST_FROM_QUERY_XL Parameters

Parameter	Description
`p_idx`	Form element name. For example, `1` equals `F01` and `2` equals `F02`. Typically the `p_idx` parameter is constant for a given column.
`p_value`	Current value. This value should be a value in the `p_query` parameter.
`p_query`	SQL query that is expected to select two columns, a display column, and a return column. For example: SELECT dname, deptno FROM dept Note that this is used only by the `SELECT_LIST_FROM_QUERY_XL` function. Also note, if only one column is specified in the select clause of this query, the value for this column will be used for both display and return purposes.
`p_attributes`	Extra HTML parameters you want to add.
`p_show_null`	Extra select option to enable the NULL selection. Range of values is `YES` and `NO`.
`p_null_value`	Value to be returned when a user selects the NULL option. Only relevant when `p_show_null` equals `YES`.
`p_null_text`	Value to be displayed when a user selects the NULL option. Only relevant when `p_show_null` equals `YES`.
`p_item_id`	HTML attribute ID for the `<select>` tag.
`p_item_label`	Invisible label created for the item.
`p_show_extra`	Show the current value even if the value of `p_value` is not located in the select list.

Example

The following example demonstrates a select list based on a SQL query.

SELECT APEX_ITEM.SELECT_LIST_FROM_QUERY_XL(3,job,'SELECT DISTINCT job FROM emp')job 
FROM emp