3 APEX_ITEM

You can use the APEX_ITEM package to create form elements dynamically based on a SQL query instead of creating individual items page by page.

This section contains the following topics:

CHECKBOX Function
DATE_POPUP Function
DISPLAY_AND_SAVE Function
HIDDEN Function
MD5_CHECKSUM Function
MD5_HIDDEN Function
POPUP_FROM_LOV Function
POPUP_FROM_QUERY Function
POPUPKEY_FROM_LOV Function
POPUPKEY_FROM_QUERY Function
RADIOGROUP Function
SELECT_LIST Function
SELECT_LIST_FROM_LOV Function
SELECT_LIST_FROM_LOV_XL Function
SELECT_LIST_FROM_QUERY Function
SELECT_LIST_FROM_QUERY_XL Function
TEXT Function
TEXTAREA Function
TEXT_FROM_LOV Function
TEXT_FROM_LOV_QUERY Function

CHECKBOX Function

This function creates check boxes.

Syntax

APEX_ITEM.CHECKBOX(
    p_idx                       IN    NUMBER,
    p_value                     IN    VARCHAR2 DEFAULT NULL,
    p_attributes                IN    VARCHAR2 DEFAULT NULL,
    p_checked_values            IN    VARCHAR2 DEFAULT NULL,
    p_checked_values_delimitor  IN    VARCHAR2 DEFAULT ':',
    p_item_id                   IN    VARCHAR2 DEFAULT NULL,
    p_item_label                IN    VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2;

Parameters

Table 3-1 describes the parameters available in the CHECKBOX function.

Table 3-1 CHECKBOX Parameters

Parameter	Description
`p_idx`	Number that determines which `APEX_APPLICATION` global variable will be used. Valid range of values is 1 to 50. For example 1 creates `F01` and 2 creates `F02`
`p_value`	Value of a check box, hidden field, or input form item
`p_attributes`	Controls HTML tag attributes (such as disabled)
`p_checked_values`	Values to be checked by default
`p_checked_values_delimitor`	Delimits the values in the previous parameter, `p_checked_values`
`p_item_id`	HTML attribute ID for the `<input>` tag
`p_item_label`	Invisible label created for the item

Examples of Default Check Box Behavior

The following example demonstrates how to create a selected check box for each employee in the emp table.

SELECT APEX_ITEM.CHECKBOX(1,empno,'CHECKED') "Select",
    ename, job
FROM   emp
ORDER BY 1

The following example demonstrates how to have all check boxes for employees display without being selected.

SELECT APEX_ITEM.CHECKBOX(1,empno) "Select",
    ename, job
FROM   emp
ORDER BY 1

The following example demonstrates how to select the check boxes for employees who work in department 10.

SELECT APEX_ITEM.CHECKBOX(1,empno,DECODE(deptno,10,'CHECKED',NULL)) "Select",
    ename, job
FROM   emp
ORDER BY 1

The next example demonstrates how to select the check boxes for employees who work in department 10 or department 20.

SELECT APEX_ITEM.CHECKBOX(1,deptno,NULL,'10:20',':') "Select",
    ename, job
FROM   emp
ORDER BY 1

Creating an On-Submit Process

If you are using check boxes in your application, you might need to create an On Submit process to perform a specific type of action on the selected rows. For example, you could have a Delete button that utilizes the following logic:

SELECT APEX_ITEM.CHECKBOX(1,empno) "Select",
    ename, job
FROM   emp
ORDER  by 1

Consider the following sample on-submit process:

FOR I in 1..APEX_APPLICATION.G_F01.COUNT LOOP
    DELETE FROM emp WHERE empno = to_number(APEX_APPLICATION.G_F01(i));
END LOOP;

The following example demonstrates how to create unselected checkboxes for each employee in the emp table, with a unique ID. This is useful for referencing records from within JavaScript code:

SELECT APEX_ITEM.CHECKBOX(1,empno,NULL,NULL,NULL,'f01_#ROWNUM#') "Select",
    ename, job
FROM   emp
ORDER BY 1

DATE_POPUP Function

Use this function with forms that include date fields. The DATE_POPUP function dynamically generates a date field that has a popup calendar button.

Syntax

APEX_ITEM.DATE_POPUP(
    p_idx                       IN    NUMBER,
    p_row                       IN    NUMBER,
    p_value                     IN    VARCHAR2 DEFAULT NULL,
    p_date_format               IN    DATE DEFAULT 'DD-MON-YYYY',
    p_size                      IN    NUMBER DEFAULT 20,
    p_maxlength                 IN    NUMBER DEFAULT 2000,
    p_attributes                IN    VARCHAR2 DEFAULT NULL,
    p_item_id                   IN    VARCHAR2 DEFAULT NULL,
    p_item_label                IN    VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2;

Parameters

Table 3-2 describes the parameters available in the DATE_POPUP function.

Table 3-2 DATE_POPUP Parameters

Parameter	Description
`p_idx`	Number that determines which `APEX_APPLICATION` global variable will be used.Valid range of values is 1 to 50. For example, 1 creates `F01` and 2 creates `F02`
`p_row`	This parameter is deprecated. Anything specified for this value will be ignored
`p_value`	Value of a field item
`p_date_format`	Valid database date format
`p_size`	Controls HTML tag attributes (such as disabled)
`p_maxlength`	Determines the maximum number of enterable characters. Becomes the maxlength attribute of the `<input>` HTML tag
`p_attributes`	Extra HTML parameters you want to add
`p_item_id`	HTML attribute ID for the `<input>` tag
`p_item_label`	Invisible label created for the item

DISPLAY_AND_SAVE Function

Use this function to display an item as text, but save its value to session state.

Syntax

APEX_ITEM.DISPLAY_AND_SAVE(
    p_idx         IN    NUMBER,
    p_value       IN    VARCHAR2 DEFAULT NULL,
    p_item_id     IN    VARCHAR2 DEFAULT NULL,
    p_item_label  IN    VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2;

Parameters

Table 3-3 describes the parameters available in the DISPLAY_AND_SAVE function.

Table 3-3 DISPLAY_AND_SAVE Parameters

Parameter	Description
`p_idx`	Number that determines which `APEX_APPLICATION` global variable will be used.Valid range of values is 1 to 50. For example, 1 creates `F01` and 2 creates `F02`
`p_value`	Current value
`p_item_id`	HTML attribute ID for the `<span>` tag
`p_item_label`	Invisible label created for the item

Example

The following example demonstrates how to use the APEX_ITEM.DISPLAY_AND_SAVE function.

SELECT APEX_ITEM.DISPLAY_AND_SAVE(10,empno) c FROM emp

HIDDEN Function

This function dynamically generates hidden form items.

Syntax

APEX_ITEM.HIDDEN(
    p_idx         IN    NUMBER,
    p_value       IN    VARCHAR2 DEFAULT
    p_attributes  IN    VARCHAR2 DEFAULT NULL,
    p_item_id     IN    VARCHAR2 DEFAULT NULL,
    p_item_label  IN    VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2;

Parameters

Table 3-4 describes the parameters available in the HIDDEN function.

Table 3-4 HIDDEN Parameters

Parameter	Description
`p_idx`	Number to identify the item you want to generate. The number will determine which `G_FXX` global is populated See Also: "APEX_APPLICATION"
`p_value`	Value of the hidden input form item
`p_attributes`	Extra HTML parameters you want to add
`p_item_id`	HTML attribute ID for the `<input>` tag
`p_item_label`	Invisible label created for the item

Example

Typically, the primary key of a table is stored as a hidden column and used for subsequent update processing, for example:

SELECT
    empno, 
    APEX_ITEM.HIDDEN(1,empno)||
    APEX_ITEM.TEXT(2,ename) ename,
    APEX_ITEM.TEXT(3,job) job, 
    mgr, 
    APEX_ITEM.DATE_POPUP(4,rownum,hiredate,'dd-mon-yyyy') hiredate,
    APEX_ITEM.TEXT(5,sal) sal, 
    APEX_ITEM.TEXT(6,comm) comm, 
    deptno
FROM emp
ORDER BY 1

The previous query could use the following page process to process the results:

BEGIN 
    FOR i IN 1..APEX_APPLICATION.G_F01.COUNT LOOP
        UPDATE emp
            SET
                ename=APEX_APPLICATION.G_F02(i),
                job=APEX_APPLICATION.G_F03(i),
                    hiredate=to_date(APEX_APPLICATION.G_F04(i),'dd-mon-yyyy'),
                    sal=APEX_APPLICATION.G_F05(i),
                    comm=APEX_APPLICATION.G_F06(i)
        WHERE empno=to_number(APEX_APPLICATION.G_F01(i));
    END LOOP;
END;

Note that the G_F01 column (which corresponds to the hidden EMPNO) is used as the key to update each row.

MD5_CHECKSUM Function

This function is used for lost update detection. Lost update detection ensures data integrity in applications where data can be accessed concurrently.

This function produces hidden form field(s) with a name attribute equal to 'fcs' and includes 50 inputs. APEX_ITEM.MD5_CHECKSUM also produces an MD5 checksum using the Oracle database DBMS_OBFUSCATION_TOOLKIT:

UTL_RAW.CAST_TO_RAW(DBMS_OBFUSCATION_TOOLKIT.MD5())

An MD5 checksum provides data integrity through hashing and sequencing to ensure that data is not altered or stolen as it is transmitted over a network.

Syntax

APEX_ITEM.MD5_CHECKSUM(
    p_value01   IN    VARCHAR2 DEFAULT NULL,
    p_value02   IN    VARCHAR2 DEFAULT NULL,
    p_value03   IN    VARCHAR2 DEFAULT NULL,
    ...
    p_value50   IN    VARCHAR2 DEFAULT NULL,
    p_col_sep   IN    VARCHAR2 DEFAULT '|')
    RETURN VARCHAR2;

Parameters

Table 3-5 describes the parameters available in the MD5_CHECKSUM function.

Table 3-5 MD5_CHECKSUM Parameters

Parameter Description

Parameter	Description
`p_value01` ... `p_value50`	Fifty available inputs. If no parameters are supplied, the default to NULL
`p_col_sep`	String used to separate `p_value` inputs. Defaults to the pipe symbol (\|)

p_value01

...

p_value50

Fifty available inputs. If no parameters are supplied, the default to NULL

p_col_sep

String used to separate p_value inputs. Defaults to the pipe symbol (|)

Example

This function generates hidden form elements with the name 'fcs'. The values can subsequently be accessed via the APEX_APPLICATION.G_FCS array.

SELECT APEX_ITEM.MD5_CHECKSUM(ename,job,sal) md5_cks,
       ename, job, sal
FROM emp

MD5_HIDDEN Function

This function is used for lost update detection. Lost update detection ensures data integrity in applications where data can be accessed concurrently.

This function produces a hidden form field and includes 50 inputs. APEX_ITEM.MD5_HIDDEN also produces an MD5 checksum using the Oracle database DBMS_OBFUSCATION_TOOLKIT:

UTL_RAW.CAST_TO_RAW(DBMS_OBFUSCATION_TOOLKIT.MD5())

An MD5 checksum provides data integrity through hashing and sequencing to ensure that data is not altered or stolen as it is transmitted over a network

Syntax

APEX_ITEM.MD5_HIDDEN(
    p_idx       IN    NUMBER,
    p_value01   IN    VARCHAR2 DEFAULT NULL,
    p_value02   IN    VARCHAR2 DEFAULT NULL,
    p_value03   IN    VARCHAR2 DEFAULT NULL,
    ...
    p_value50   IN    VARCHAR2 DEFAULT NULL,
    p_col_sep   IN    VARCHAR2 DEFAULT '|')
    RETURN VARCHAR2;

Parameters

Table 3-6 describes the parameters available in the MD5_HIDDEN function.

Table 3-6 MD5_HIDDEN Parameters

Parameter Description

Parameter	Description
`p_idx`	Indicates the form element to be generated. For example, 1 equals `F01` and 2 equals `F02`. Typically the `p_idx` parameter is constant for a given column
`p_value01` ... `p_value50`	Fifty available inputs. Parameters not supplied default to NULL
`p_col_sep`	String used to separate `p_value` inputs. Defaults to the pipe symbol (\|)

p_idx

Indicates the form element to be generated. For example, 1 equals F01 and 2 equals F02. Typically the p_idx parameter is constant for a given column

p_value01

...

p_value50

Fifty available inputs. Parameters not supplied default to NULL

p_col_sep

String used to separate p_value inputs. Defaults to the pipe symbol (|)

Example

The p_idx parameter specifies the FXX form element to be generated. In the following example, 7 generates F07. Also note that an HTML hidden form element will be generated.

SELECT APEX_ITEM.MD5_HIDDEN(7,ename,job,sal)md5_h, ename, job, sal 
FROM emp

POPUP_FROM_LOV Function

This function generates an HTML popup select list from an application shared list of values (LOV). Like other available functions in the APEX_ITEM package, POPUP_FROM_LOV function is designed to generate forms with F01 to F50 form array elements.

Syntax

APEX_ITEM.POPUP_FROM_LOV(
    p_idx              IN    NUMBER,
    p_value            IN    VARCHAR2 DEFAULT NULL,
    p_lov_name         IN    VARCHAR2,
    p_width            IN    VARCHAR2 DEFAULT NULL,
    p_max_length       IN    VARCHAR2 DEFAULT NULL,
    p_form_index       IN    VARCHAR2 DEFAULT '0',
    p_escape_html      IN    VARCHAR2 DEFAULT NULL,
    p_max_elements     IN    VARCHAR2 DEFAULT NULL,
    p_attributes       IN    VARCHAR2 DEFAULT NULL,
    p_ok_to_query      IN    VARCHAR2 DEFAULT 'YES',
    p_item_id          IN    VARCHAR2 DEFAULT NULL,
    p_item_label       IN    VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2;

Parameters

Table 3-7 describes the some parameters in the POPUP_FROM_LOV function.

Table 3-7 POPUP_FROM_LOV Parameters

Parameter	Description
`p_idx`	Form element name. For example, `1` equals `F01` and `2` equals `F02`. Typically, `p_idx` is a constant for a given column
`p_value`	Form element current value. This value should be one of the values in the `p_lov_name` parameter
`p_lov_name`	Named LOV used for this popup
`p_width`	Width of the text box
`p_max_length`	Maximum number of characters that can be entered in the text box
`p_form_index`	HTML form on the page in which an item is contained. Defaults to 0 and rarely used. Only use this parameter when it is necessary to embed a custom form in your page template (such as a search field that posts to a different Web site). If this form comes before the `#FORM_OPEN#` substitution string, then its index is zero and the form opened automatically by Oracle Application Express must be referenced as form 1. This functionality supports the JavaScript used in the popup LOV that passes a value back to a form element.
`p_escape_html`	Replacements for special characters that require an escaped equivalent: `<` for `<` `>` for `>` `&` for `&` Range of values is `YES` and `NO`. If `YES`, special characters will be escaped. This parameter is useful if you know your query will return illegal HTML.
`p_max_elements`	Limit on the number of rows that can be returned by your query. Limits the performance impact of user searches. By entering a value in this parameter, you force the user to search for a narrower set of results.
`p_attributes`	Additional HTML attributes to use for the form item.
`p_ok_to_query`	Range of values is `YES` and `NO`. If `YES`, a popup returns first set of rows for the LOV. If `NO`, a search is initiated to return rows.
`p_item_id`	ID attribute of the form element.
`p_item_label`	Invisible label created for the item.

Example

The following example demonstrates a sample query the generates a popup from an LOV named DEPT_LOV.

SELECT APEX_ITEM.POPUP_FROM_LOV (1,deptno,'DEPT_LOV') dt 
FROM emp

POPUP_FROM_QUERY Function

This function generates an HTML popup select list from a query. Like other available functions in the APEX_ITEM package, the POPUP_FROM_QUERY function is designed to generate forms with F01 to F50 form array elements.

Syntax

APEX_ITEM.POPUP_FROM_QUERY(

    p_idx              IN    NUMBER,
    p_value            IN    VARCHAR2 DEFAULT NULL,
    p_lov_query        IN    VARCHAR2,
    p_width            IN    VARCHAR2 DEFAULT NULL,
    p_max_length       IN    VARCHAR2 DEFAULT NULL,
    p_form_index       IN    VARCHAR2 DEFAULT '0',
    p_escape_html      IN    VARCHAR2 DEFAULT NULL,
    p_max_elements     IN    VARCHAR2 DEFAULT NULL,
    p_attributes       IN    VARCHAR2 DEFAULT NULL,
    p_ok_to_query      IN    VARCHAR2 DEFAULT 'YES',
    p_item_id          IN    VARCHAR2 DEFAULT NULL,
    p_item_label       IN    VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2;

Parameters

Table 3-8 describes the parameters in the POPUP_FROM_QUERY function.

Table 3-8 POPUP_FROM_QUERY Parameters

Parameter	Description
`p_idx`	Form element name. For example, `1` equals `F01` and `2` equals `F02`. Typically, `p_idx` is a constant for a given column.
`p_value`	Form element current value. This value should be one of the values in the `p_lov_query` parameter.
`p_lov_query`	SQL query that is expected to select two columns (a display column and a return column). For example: SELECT dname, deptno FROM dept
`p_width`	Width of the text box.
`p_max_length`	Maximum number of characters that can be entered in the text box.
`p_form_index`	HTML form on the page in which an item is contained. Defaults to 0 and rarely used. Only use this parameter when it is necessary to embed a custom form in your page template (such as a search field that posts to a different Web site). If this form comes before the `#FORM_OPEN#` substitution string, then its index is zero and the form opened automatically by Oracle Application Express must be referenced as form 1. This functionality supports the JavaScript used in the popup LOV that passes a value back to a form element.
`p_escape_html`	Replacements for special characters that require an escaped equivalent. `<` for `<` `>` for `>` `&` for `&` Range of values is `YES` and `NO`. If `YES`, special characters will be escaped. This parameter is useful if you know your query will return illegal HTML.
`p_max_elements`	Limit on the number of rows that can be returned by your query. Limits the performance impact of user searches. By entering a value in this parameter, you force the user to search for a narrower set of results.
`p_attributes`	Additional HTML attributes to use for the form item.
`p_ok_to_query`	Range of values is `YES` and `NO`. If `YES`, a popup returns the first set of rows for the LOV. If `NO`, a search is initiated to return rows.
`p_item_id`	ID attribute of the form element.
`p_item_label`	Invisible label created for the item.

Example

The following example demonstrates a sample query the generates a popup select list from the emp table.

SELECT APEX_ITEM.POPUP_FROM_QUERY (1,deptno,'SELECT dname, deptno FROM dept') dt 
FROM emp

POPUPKEY_FROM_LOV Function

This function generates a popup key select list from a shared list of values (LOV). Similar to other available functions in the APEX_ITEM package, the POPUPKEY_FROM_LOV function is designed to generate forms with F01 to F50 form array elements.

Syntax

APEX_ITEM.POPUPKEY_FROM_LOV(
    p_idx              IN    NUMBER,
    p_value            IN    VARCHAR2 DEFAULT NULL,
    p_lov_name         IN    VARCHAR2,
    p_width            IN    VARCHAR2 DEFAULT NULL,
    p_max_length       IN    VARCHAR2 DEFAULT NULL,
    p_form_index       IN    VARCHAR2 DEFAULT '0',
    p_escape_html      IN    VARCHAR2 DEFAULT NULL,
    p_max_elements     IN    VARCHAR2 DEFAULT NULL,
    p_attributes       IN    VARCHAR2 DEFAULT NULL,
    p_ok_to_query      IN    VARCHAR2 DEFAULT 'YES',
    p_item_id          IN    VARCHAR2 DEFAULT NULL,
    p_item_label       IN    VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2;

Although the text field associated with the popup displays in the first column in the LOV query, the actual value is specified in the second column in the query.

Parameters

Table 3-9 describes the some parameters in the POPUPKEY_FROM_LOV function.

Table 3-9 POPUPKEY_FROM_LOV Parameters

Parameter	Description
`p_idx`	Identifies a form element name. For example, `1` equals `F01` and `2` equals `F02`. Typically, `p_idx` is a constant for a given column Because of the behavior of `POPUPKEY_FROM_QUERY`, the next index value should be `p_idx + 1`. For example: SELECT APEX_ITEM.POPUPKEY_FROM_LOV (1,deptno,'DEPT') dt, APEX_ITEM.HIDDEN(3,empno) eno
`p_value`	Indicates the current value. This value should be one of the values in the `P_LOV_NAME` parameter.
`p_lov_name`	Identifies a named LOV used for this popup.
`p_width`	Width of the text box.
`p_max_length`	Maximum number of characters that can be entered in the text box.
`p_form_index`	HTML form on the page in which an item is contained. Defaults to 0 and rarely used. Only use this parameter when it is necessary to embed a custom form in your page template (such as a search field that posts to a different Web site). If this form comes before the `#FORM_OPEN#` substitution string, then its index is zero and the form opened automatically by Oracle Application Express must be referenced as form 1. This functionality supports the JavaScript used in the popup LOV that passes a value back to a form element.
`p_escape_html`	Replacements for special characters that require an escaped equivalent. `<` for `<` `>` for `>` `&` for `&` This parameter is useful if you know your query will return illegal HTML.
`p_max_elements`	Limit on the number of rows that can be returned by your query. Limits the performance impact of user searches. By entering a value in this parameter, you force the user to search for a narrower set of results.
`p_attributes`	Additional HTML attributes to use for the form item.
`p_ok_to_query`	Range of values is `YES` and `NO`. If `YES`, a popup returns the first set of rows for the LOV. If `NO`, a search is initiated to return rows.
`p_item_id`	HTML attribute ID for the <input> tag
`p_item_label`	Invisible label created for the item

Example

The following example demonstrates how to generate a popup key select list from a shared list of values (LOV).

SELECT APEX_ITEM.POPUPKEY_FROM_LOV (1,deptno,'DEPT') dt 
FROM emp

POPUPKEY_FROM_QUERY Function

This function generates a popup key select list from a SQL query. Similar to other available functions in the APEX_ITEM package, the POPUPKEY_FROM_QUERY function is designed to generate forms with F01 to F50 form array elements.

Syntax

APEX_ITEM.POPUPKEY_FROM_QUERY(
    p_idx              IN    NUMBER,
    p_value            IN    VARCHAR2 DEFAULT NULL,
    p_lov_query        IN    VARCHAR2,
    p_width            IN    VARCHAR2 DEFAULT NULL,
    p_max_length       IN    VARCHAR2 DEFAULT NULL,
    p_form_index       IN    VARCHAR2 DEFAULT '0',
    p_escape_html      IN    VARCHAR2 DEFAULT NULL,
    p_max_elements     IN    VARCHAR2 DEFAULT NULL,
    p_attributes       IN    VARCHAR2 DEFAULT NULL,
    p_ok_to_query      IN    VARCHAR2 DEFAULT 'YES',
    p_item_id          IN    VARCHAR2 DEFAULT NULL,
    p_item_label       IN    VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2;

Parameters

Table 3-10 describes the some parameters in the POPUPKEY_FROM_QUERY function.

Table 3-10 POPUPKEY_FROM_QUERY Parameters

Parameter	Description
`p_idx`	Form element name. For example, `1` equals `F01` and `2` equals `F02`. Typically, `p_idx` is a constant for a given column. Because of the behavior of `POPUPKEY_FROM_QUERY`, the next index value should be `p_idx + 1`. For example: SELECT APEX_ITEM.POPUPKEY_FROM_QUERY (1,deptno,'SELECT dname, deptno FROM dept') dt, APEX_ITEM.HIDDEN(3,empno) eno
`p_value`	Form element current value. This value should be one of the values in the `P_LOV_QUERY` parameter.
`p_lov_query`	LOV query used for this popup.
`p_width`	Width of the text box.
`p_max_length`	Maximum number of characters that can be entered in the text box.
`p_form_index`	HTML form on the page in which an item is contained. Defaults to 0 and rarely used. Only use this parameter when it is necessary to embed a custom form in your page template (such as a search field that posts to a different Web site). If this form comes before the `#FORM_OPEN#` substitution string, then its index is zero and the form opened automatically by Oracle Application Express must be referenced as form 1. This functionality supports the JavaScript used in the popup LOV that passes a value back to a form element.
`p_escape_html`	Replacements for special characters that require an escaped equivalent. `<` for `<` `>` for `>` `&` for `&` This parameter is useful if you know your query will return illegal HTML.
`p_max_elements`	Limit on the number of rows that can be returned by your query. Limits the performance impact of user searches. By entering a value in this parameter, you force the user to search for a narrower set of results.
`p_attributes`	Additional HTML attributes to use for the form item.
`p_ok_to_query`	Range of values is `YES` and `NO`. If `YES`, a popup returns first set of rows for the LOV. If `NO`, a search is initiated to return rows.
`p_item_id`	ID attribute of the form element.
`p_item_label`	Invisible label created for the item.

Example

The following example demonstrates how to generate a popup select list from a SQL query.

SELECT APEX_ITEM.POPUPKEY_FROM_QUERY (1,deptno,'SELECT dname, deptno FROM dept') dt 
FROM emp

RADIOGROUP Function

This function generates a radio group from a SQL query.

Syntax

APEX_ITEM.RADIOGROUP(
    p_idx              IN    NUMBER,
    p_value            IN    VARCHAR2 DEFAULT NULL,
    p_selected_value   IN    VARCHAR2 DEFAULT NULL,
    p_display          IN    VARCHAR2 DEFAULT NULL,
    p_attributes       IN    VARCHAR2 DEFAULT NULL,
    p_onblur           IN    VARCHAR2 DEFAULT NULL,
    p_onchange         IN    VARCHAR2 DEFAULT NULL,
    p_onfocus          IN    VARCHAR2 DEFAULT NULL,
    p_item_id          IN    VARCHAR2 DEFAULT NULL,
    p_item_label       IN    VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2;

Parameters

Table 3-11 describes the parameters available in the RADIOGROUP function.

Table 3-11 RADIOGROUP Parameters

Parameter	Description
`p_idx`	Number that determines which `APEX_APPLICATION` global variable will be used. Valid range of values is `1` to `50`.For example `1` creates `F01` and `2` creates `F02`.
`p_value`	Value of the radio group.
`p_selected_value`	Value that should be selected.
`p_display`	Text to display next to the radio option.
`p_attributes`	Extra HTML parameters you want to add.
`p_onblur`	JavaScript to execute in the `onBlur` event.
`p_onchange`	JavaScript to execute in the `onChange` event.
`p_onfocus`	JavaScript to execute in the `onFocus` event.
`p_item_id`	HTML attribute ID for the <input> tag
`p_item_label`	Invisible label created for the item

Example

The following example demonstrates how to select department 20 from the emp table as a default in a radio group.

SELECT APEX_ITEM.RADIOGROUP (1,deptno,'20',dname) dt
FROM   dept
ORDER  BY 1

SELECT_LIST Function

This function dynamically generates a static select list. 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(
    p_idx           IN   NUMBER,
    p_value         IN   VARCHAR2 DEFAULT NULL,
    p_list_values   IN   VARCHAR2 DEFAULT NULL,
    p_attributes    IN   VARCHAR2 DEFAULT NULL,
    p_show_null     IN   VARCHAR2 DEFAULT 'NO',
    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 VARCHAR2;

Parameters

Table 3-12 describes the parameters available in the SELECT_LIST function.

Table 3-12 SELECT_LIST 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_LIST_VALUES` parameter.
`p_list_values`	List of static values separated by commas. Displays values and returns values that are separated by semicolons. Note that this is only available in the `SELECT_LIST` function.
`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 <`input`> tag.
`p_item_label`	Invisible lable created for the item.
`p_show_extra`	Shows the current value even if the value of p_value is not located in the select list.

Example

The following example demonstrates a static select list that displays Yes, returns Y, defaults to Y, and generates a F01 form item.

SELECT APEX_ITEM.SELECT_LIST(1,'Y','Yes;Y,No;N')yn 
FROM emp

The following example demonstrates the use of APEX_ITEM.SELECT_LIST to generate a static select list where:

A form array element F03 will be generated (p_idx parameter).
The initial value for each element will be equal to the value for deptno for the row from emp (p_value parameter).
The select list will contain 4 options (p_list_values parameter).
The text within the select list will display in red (p_attributes parameter).
A null option will be displayed (p_show_null) and this option will display -Select- as the text (p_null_text parameter).
An HTML ID attribute will be generated for each row, where #ROWNUM# will be substituted for the current row rownum (p_item_id parameter). (So an ID of 'f03_4' will be generated for row 4.)
A HTML label element will be generated for each row (p_item_label parameter).
The current value for deptno will be displayed, even if it is not contained with the list of values passed in the p_list_values parameter (p_show_extra parameter).

SELECT  empno "Employee #", 
    ename "Name",
    APEX_ITEM.SELECT_LIST(
        p_idx           =>   3,
        p_value         =>   deptno,
        p_list_values   =>   'ACCOUNTING;10,RESEARCH;20,SALES;30,OPERATIONS;40',
        p_attributes    =>   'style="color:red;"',
        p_show_null     =>   'YES',
        p_null_value    =>   NULL,
        p_null_text     =>   '-Select-',
        p_item_id       =>   'f03_#ROWNUM#',
        p_item_label    =>   'Label for f03_#ROWNUM#',
        p_show_extra    =>   'YES') "Department"
  FROM  emp;

SELECT_LIST_FROM_LOV Function

This function dynamically generates select lists from a shared list of values (LOV). 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_LOV(
    p_idx           IN   NUMBER,
    p_value         IN   VARCHAR2 DEFAULT NULL,
    p_lov           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 VARCHAR2;

Parameters

Table 3-13 describes the parameters available in the SELECT_LIST_FROM_LOV function.

Table 3-13 SELECT_LIST_FROM_LOV 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_lov` parameter.
`p_lov`	Text name of an application list of values. This list of values must be defined in your application. This parameter is used only by the `select_list_from_lov` function.
`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`	Shows 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 an LOV defined in the application.

SELECT APEX_ITEM.SELECT_LIST_FROM_LOV(2,job,'JOB_FLOW_LOV')job 
FROM emp

SELECT_LIST_FROM_LOV_XL Function

This function dynamically generates very large select lists (greater than 32K) from a shared list of values (LOV). 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. This function is the same as SELECT_LIST_FROM_LOV, but its return value is CLOB. This enables you to use it in SQL queries where you need to handle a column value longer than 4000 characters.

Syntax

APEX_ITEM.SELECT_LIST_FROM_LOV_XL(
    p_idx           IN   NUMBER,
    p_value         IN   VARCHAR2 DEFAULT NULL,
    p_lov           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 3-14 describes the parameters available in the SELECT_LIST_FROM_LOV_XL function.

Table 3-14 SELECT_LIST_FROM_LOV_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_lov` parameter.
`p_lov`	Text name of a list of values. This list of values must be defined in your application. This parameter is used only by the `select_list_from_lov` function.
`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`	Shows the current value even if the value of `p_value` is not located in the select list.

Example

The following example demonstrates how to create a select list based on an LOV defined in the application.

SELECT APEX_ITEM.SELECT_LIST_FROM_LOV_XL(2,job,'JOB_FLOW_LOV')job 
FROM emp

SELECT_LIST_FROM_QUERY Function

This function dynamically generates a select list from a query. 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(
    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 VARCHAR2;

Parameters

Table 3-15 describes the parameters available in the SELECT_LIST_FROM_QUERY function.

Table 3-15 SELECT_LIST_FROM_QUERY 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` 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(3,job,'SELECT DISTINCT job FROM emp')job 
FROM emp

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 3-16 describes the parameters available in the SELECT_LIST_FROM_QUERY_XL function.

Table 3-16 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

TEXT Function

This function generates text fields (or text input form items) from a SQL query.

Syntax

APEX_ITEM.TEXT(
    p_idx         IN    NUMBER,
    p_value       IN    VARCHAR2 DEFAULT NULL,
    p_size        IN    NUMBER DEFAULT NULL,
    p_maxlength   IN    NUMBER DEFAULT NULL,
    p_attributes  IN    VARCHAR2 DEFAULT NULL,
    p_item_id     IN    VARCHAR2 DEFAULT NULL,
    p_item_label  IN    VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2;

Parameters

Table 3-17 describes the parameters available in the TEXT function.

Table 3-17 TEXT Parameters

Parameter	Description
`p_idx`	Number to identify the item you want to generate. The number will determine which `G_FXX` global is populated. See Also: "APEX_APPLICATION"
`p_value`	Value of a text field item.
`p_size`	Controls HTML tag attributes (such as disabled).
`p_maxlength`	Maximum number of characters that can be entered in the text box.
`p_attributes`	Extra HTML parameters you want to add.
`p_item_id`	HTML attribute ID for the `<input>` tag.
`p_item_label`	Invisible label created for the item.

Example

The following sample query demonstrates how to generate one update field for each row. Note that the ename, sal, and comm columns use the APEX_ITEM.TEXT function to generate an HTML text field for each row. Also, notice that each item in the query is passed a unique p_idx parameter to ensure that each column is stored in its own array.

SELECT 
  empno, 
  APEX_ITEM.HIDDEN(1,empno)||
  APEX_ITEM.TEXT(2,ename) ename, 
  APEX_ITEM.TEXT(3,job) job, 
  mgr, 
  APEX_ITEM.DATE_POPUP(4,rownum,hiredate,'dd-mon-yyyy') hiredate,
  APEX_ITEM.TEXT(5,sal) sal, 
  APEX_ITEM.TEXT(6,comm) comm,
  deptno
FROM emp
ORDER BY 1

TEXTAREA Function

This function creates text areas.

Syntax

APEX_ITEM.TEXTAREA(
    p_idx         IN    NUMBER,
    p_value       IN    VARCHAR2 DEFAULT NULL,
    p_rows        IN    NUMBER DEAULT 40,
    p_cols        IN    NUMBER DEFAULT 4,
    p_attributes  IN    VARCHAR2 DEFAULT NULL,
    p_item_id     IN    VARCHAR2 DEFAULT NULL,
    p_item_label  IN    VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2;

Parameters

Table 3-18 describes the parameters available in the TEXTAREA function.

Table 3-18 TEXTAREA Parameters

Parameter	Description
`p_idx`	Number to identify the item you want to generate. The number will determine which `G_FXX` global is populated. See Also: "APEX_APPLICATION"
`p_value`	Value of the text area item.
`p_rows`	Height of the text area (HTML rows attribute)
`p_cols`	Width of the text area (HTML column attribute).
`p_attributes`	Extra HTML parameters you want to add.
`p_item_id`	HTML attribute ID for the `<textarea>` tag.
`p_item_label`	Invisible label created for the item.

Example

The following example demonstrates how to create a text area based on a SQL query.

SELECT APEX_ITEM.TEXTAREA(3,ename,5,80) a
FROM emp

TEXT_FROM_LOV Function

Use this function to display an item as text, deriving the display value of the named LOV.

Syntax

APEX_ITEM.TEXT_FROM_LOV (
    p_value       IN    VARCHAR2 DEFAULT NULL,
    p_lov         IN    VARCHAR2,
    p_null_text   IN    VARCHAR2 DEFAULT '%')
    RETURN VARCHAR2;

Parameters

Table 3-19 describes the parameters available in the TEXT_FROM_LOV function.

Table 3-19 TEXT_FROM_LOV Parameters

Parameter Description

Parameter	Description
`p_value`	Value of a field item. Note that if `p_value` is not located in the list of values, `p_null_text` is value displayed.
`p_lov`	Text name of a shared list of values. This list of values must be defined in your application.
`p_null_text`	Value displayed when the value of the field item is NULL.

p_value

Value of a field item.

Note that if p_value is not located in the list of values, p_null_text is value displayed.

p_lov

Text name of a shared list of values. This list of values must be defined in your application.

p_null_text

Value displayed when the value of the field item is NULL.

Example

The following example demonstrates how to derive the display value from a named LOV (EMPNO_ENAME_LOV).

SELECT APEX_ITEM.TEXT_FROM_LOV(empno,'EMPNO_ENAME_LOV') c FROM emp

TEXT_FROM_LOV_QUERY Function

Use this function to display an item as text, deriving the display value from a list of values query.

Syntax

APEX_ITEM.TEXT_FROM_LOV_QUERY (
    p_value       IN    VARCHAR2 DEFAULT NULL,
    p_query       IN    VARCHAR2,
    p_null_text   IN    VARCHAR2 DEFAULT '%')
    RETURN VARCHAR2;

Parameters

Table 3-20 describes the parameters available in the TEXT_FROM_LOV_QUERY function.

Table 3-20 TEXT_FROM_LOV_QUERY Parameters

Parameter Description

Parameter	Description
`p_value`	Value of a field item.
`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 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_null_text`	Value to be displayed when the value of the field item is NULL or a corresponding entry is not located for the value `p_value` in the list of values query.

p_value

Value of a field item.

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 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_null_text

Value to be displayed when the value of the field item is NULL or a corresponding entry is not located for the value p_value in the list of values query.

Example

The following example demonstrates how to derive the display value from a query.

SELECT APEX_ITEM.TEXT_FROM_LOV_QUERY(empno,'SELECT ename, empno FROM emp') c from emp