13.1 Understanding Page-Level Items

An item is part of an HTML form. An item can be a text field, text area, password, select list, check box, and so on. Item attributes affect the display of items on a page. For example, these attributes can impact where a label displays, how large an item is, and if the item displays next to or below the previous item.

Topics:

13.1.1 About the Differences Between Page Items and Application Items

There are two types of items: page items and application items. Page items are placed on a page and have associated user interface properties, such as Display Only, Label and Label Template. Examples of page-level items include a check box, date picker, display as text, file browse field, popup list of values, select list, or a text area. Application items are not associated with a page and therefore have no user interface properties. You can use an application item as a global variable.

See Also:

"Managing Application-Level Items"

13.1.2 About Item Naming Conventions

When specifying an item name, remember the following rules. Item names must:

Be unique within an application.
Not include quotation marks.
Begin with a letter or a number, and subsequent characters can be letters, numbers, or underscore characters.
Be case-insensitive.
Should not exceed 30 characters. Items longer than 30 characters cannot be referenced using bind variable syntax. See "Referencing Session State Using Bind Variable Syntax."
Cannot contain letters outside the base ASCII character set.

As a best practice Oracle recommends including the page number when naming items. By default, wizards prefix page item names with P<page no>_<item name> (for example, P1_NAME).

13.1.3 Referencing Item Values

You can reference item values stored in session state in regions, computations, processes, validation, and branches. Table 13-1 describes the supported syntax for referencing item values.

Table 13-1 Syntax for Referencing Item Values

Type	Syntax	Description
SQL	`:MY_ITEM`	Standard bind variable syntax for items whose names are no longer than 30 bytes. Use this syntax for references within a SQL query and within PL/SQL code.
PL/SQL	`V('MY_ITEM')`	PL/SQL syntax referencing the item value using the `V` function. Use this syntax in PL/SQL code of packages or stored procedures and functions. Avoid this syntax in SQL statements. It may result in performance problems.
PL/SQL	`NV('MY_NUMERIC_ITEM')`	Standard PL/SQL syntax referencing the numeric item value using the `NV` function. Use this syntax in PL/SQL code of packages or stored procedures and functions. Avoid this syntax in SQL statements. It may result in performance problems.
Static Text (exact)	`&MY_ITEM.`	Static text. Exact Substitution. Note: Exact substitution syntax should be avoided in SQL or PL/SQL code because it can result in SQL Injection vulnerabilities.

You can set the value of an item in your application using any of the following methods:

For page-level items, use the Source Attribute to set the item value.

From the page, select the item name to view the Edit Page Item page. Scroll down to Source and edit the appropriate fields.

You can also set the value of an item in any region based on PL/SQL or a process using the following syntax:
```
BEGIN
 :MY_ITEM :=  'new value';
END;
```
Pass the value on a URL reference using f?p syntax. For example:
```
f?p=100:101:10636547268728380919::NO::MY_ITEM:ABC
```
Set the value using a computation. Computations are designed to set item values. For example:
```
TO_CHAR(SYSDATE,'Day DD Month, YYYY');
```
Use the PL/SQL API to set an item value within a PL/SQL context. For example:
```
APEX_UTIL.SET_SESSION_STATE('MY_ITEM',SYSDATE);
F
```

See Also:

13.1.4 About Referencing Items Using JavaScript

When you reference an item, the best approach is to reference by ID. If you view the HTML source of an Oracle Application Express page in a web browser, you would notice that all items have an id attribute. This ID corresponds to the name of the item, not the item label. For example, if you create an item with the name P1_FIRST_NAME and a label of First Name, the ID is P1_FIRST_NAME.

You can get and set item attributes and values using the JavaScript functions $v('P1_FIRST_NAME') and $s('P1_FIRST_NAME', 'Joe');. Consider the following example:

function showFirstName(){
  alert('First Name is ' +$v('P1_FIRST_NAME'))
};
function setFirstName(pFirstName){
  $s('P1_FIRST_NAME', pFirstName);
};

These functions can be called by other JavaScript functions or with the Execute JavaScript code dynamic action.

See Also:

"APEX_JAVASCRIPT" in Oracle Application Express API Reference

13.1.5 Working with Multiple Select List Item

This section describes how to handle values returned from multiple select list item.

Topics:

13.1.5.1 About Handling Values Returned from a Multiple Select List Item

A multiple select item renders as a multiple select list form element which can be either a Multiselect List or Shuttle item type. When submitted, selected values are returned in a single colon-delimited string. You can handle values in this format in three ways:

Using the INSTR function
Using the APEX_UTIL.STRING_TO_TABLE function
Creating a shuttle

13.1.5.2 Using APEX_UTIL.STRING_TO_TABLE to Convert Selected Values

Suppose you had a report on the EMP and DEPT tables that is limited by the departments selected from a Department multiple select list. First, you create the multiple select item, P1_DEPTNO, using the following query:

SELECT dname, deptno
FROM dept

Second, you return only those employees within the selected departments as follows:

SELECT ename, job, sal, comm, dname
FROM emp e, dept d
WHERE d.deptno = e.deptno
AND instr(':'||:P1_DEPTNO||':',':'||e.deptno||':') > 0

Next, assume you want to programmatically step through the values selected in the multiple select item, P1_DEPTNO. To accomplish this task, convert the colon-delimited string into a PL/SQL array using the APEX_UTIL.STRING_TO_TABLE function. The following example demonstrates how to insert the selected departments into an audit table containing the date of the query.

DECLARE
    l_selected APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
  --
  -- Convert the colon separated string of values into
  -- a PL/SQL array 

  l_selected := APEX_UTIL.STRING_TO_TABLE(:P1_DEPTNO);

  --
  -- Loop over array to insert department numbers and sysdate
  --

  FOR i IN 1..l_selected.count 
  LOOP
    INSERT INTO report_audit_table (report_date, selected_department)
        VALUES (sysdate, l_selected(i));
  END LOOP;
END;

See Also:

"STRING_TO_TABLE Function" in Oracle Application Express API Reference

13.1.5.3 Creating a Shuttle Item

This section demonstrates how to create shuttle item type. First, you first create a report and form on the DEPT table that shows which employees are assigned to a given department. Second, you create a shuttle item that lists employees alphabetically to make it easier to assign employees to a department.

Topics:

13.1.5.3.1 Creating a Report and From on a Table

The procedures in this section assumes you have already created an application.

To create report and form on a table:

On the Workspace home page, click the App Builder icon.
Select the application.
Click Create Page.
For Create a Page:
1. User Interface - Select Desktop.
2. Select a page type - Select Form.
3. Click Next.
Select Report with Form on Table and click Next.
On Report Page Attributes:
1. Edit the required attributes.
  To learn more about an option, see field-level Help.
2. Click Next.
On Navigation Menu, select a Navigation Preference and click Next.
On Data Source:
1. Table/View Owner - Select the owner of the table on which you are building a report.
2. Table/View Name - Select the table or view on which the form will be based.
3. Select Column(s) to be shown in Report(Value Required) - Select one or more columns to be included in the report.
4. Click Next.
On Form Page:
1. Primary Key - Choose Managed by Database (ROWID) to have the form use the ROWID pseudo column to identify rows to update and delete. Choose Select Primary Key Column(s) to use the source table's primary key column(s).
2. Select Column(s) to be included in Form(Value Required) - Select one or more columns to be included in the form.
3. Click Create.

13.1.5.3.2 Creating a Shuttle Item on the Form Page in Page Designer

To create a shuttle on the form page:

View the form page in Page Designer.
In Page Rendering, right-click region containing the form and select Create Page Item.
Next, enter attributes in Property Editor - Page Item.
Under Identification:
1. Name - Enter the name of this item (for example, PX_EMP_LIST).
  
  Tip:
  
  In this example, PX in the item name (for example P2_EMP_LIST) indicates the page on which the item resides.
2. Type - Select Shuttle.
Under List of Values:
1. Type - Select SQL Query.
2. SQL Statement - Enter the SQL query definition to populate this list of values. For example:
```
SELECT ename, empno FROM emp ORDER BY 1
```
Under Source:
- Type - Select SQL Query (return colon separated value)
- Item Source Value - Enter the SQL Query that returns one or more rows to be used as the source for this item value. If the result contains multiple rows then the value from each row is formed into a single colon delimited value. For example:
```
SELECT empno FROM emp WHERE deptno = :P8_DEPTNO ORDER BY ename
```
Click Save.
Click Save and Run to view the page.

13.1.5.3.3 Creating a Shuttle Item on the Form Page in Component View

To create a shuttle on the form page in Component View:

Go to the form page and view it in Component View. See "Viewing a Page in Legacy Component View."
Under Items, click Create.
For Item Type, select Shuttle and click Next.
For Item Name, enter PX_EMP_LIST and click Next.

Tip:

In this example, PX in the item name (for example P2_EMP_LIST) indicates the page on which the item resides.
For Item Attributes, accept the defaults and click Next.
For Settings, accept the defaults and click Next.
For List of Values:
- List of Values Query - Enter:
```
SELECT ename, empno FROM emp ORDER BY 1
```
- Click Next.
For List of Values:
- Source Type - Select SQL Query (return colon separated value)
- Item Source Value - Enter:
```
SELECT empno FROM emp WHERE deptno = :P8_DEPTNO ORDER BY ename
```
- Click Create Item.
Click Run to view the page. You shuttle item should look similar to the following illustration.