Convert a colon separated function into a table

The is a useful function that converts the output from the page items below into a table, so that you can manipulate the data using SQL.
  • List Manger
  • Multiple Select
  • Checkboxes
  • Shuttle
CREATE OR REPLACE PACKAGE useful_functions IS

-- Author : SHUNT
-- Created : 25/11/2009
-- Purpose : Lots of useful function and procedures

-- Public type declarations
TYPE ret_val_t IS TABLE OF VARCHAR(4000);

-- Public function and procedure declarations
FUNCTION checkbox_table(p_checkbox VARCHAR2) RETURN ret_val_t
PIPELINED;

END useful_functions;
/
CREATE OR REPLACE PACKAGE BODY useful_functions IS
FUNCTION checkbox_table(p_checkbox VARCHAR2) RETURN ret_val_t
PIPELINED IS
l_vc_arr2 apex_application_global.vc_arr2;
BEGIN
l_vc_arr2 := apex_util.string_to_table(p_checkbox);
FOR z IN 1 .. l_vc_arr2.COUNT LOOP
PIPE ROW(l_vc_arr2(z));
END LOOP;
END;
END useful_functions;
/

To convert the other way i.e. from a table into a colon separated string I would use the StringAgg function.

To return values from a table function use the following syntax:

SELECT
*
FROM
TABLE(useful_functions.checkbox_table('a:b:c:d:e'))

Forum Post

Comments