Working with Multiple Select List Item

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, you would 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;

Creating a Shuttle Item Type

Suppose you have a form on the DEPT table that shows which employees are assigned to a given department. To made assigning employees to a department easier, you can create a shuttle item that lists employees alphabetically.

To create a shuttle item:

1. Create a new Form page on DEPT using Form on a Table with Report:

   1. On the Workspace home page, click the Application Builder icon.

   2. Select an application.

   3. Click Create Page.

   4. For Page Type, select Form.

   5. Under Forms, select Form on a Table with Report.

   6. Select the appropriate schema.

   7. For Table/View Name, select DEPT.

   8. Accept the remaining defaults and follow the on-screen instructions. To learn more about a specific field, click the item label.

2. Create a shuttle named P2_EMP_LIST and the form page.

   1. Go the Page Definition for the form page.

   2. Right-click the region containing the form and select Create Page Item.

   3. For Item Type, select Shuttle.

   4. For Item Name, enter P2_EMP_LIST and click Next.

      
      

      Tip: In this example, "P2" in the item name P2_EMP_LIST indicates the item resides on page 2.

      
      

   5. For Item Attributes, accept the defaults and click Next.

   6. For Setting, accept the defaults and click Next.

   7. For List of Values:

      • List of Values Query - Enter:

        SELECT ename, empno FROM emp ORDER BY 1
        

      • Click Next.

   8. For Source:

      • Source Type - Select SQL Query (return color separated value)

      • Item Source Value - Enter:

        SELECT empno FROM emp WHERE deptno = :P2_DEPTNO ORDER BY ename
        

      • Click Create Item.

   9. Run the page by clicking the Run Page icon.

      
      Description of the illustration shuttle_example.gif