Oracle Apex - String

APEX_UTIL Function

STRING_TO_TABLE

String to table

The function is passed the string 'One:Two:Three' in the p_string parameter and it returns a PL/SQL array of type APEX_APPLICATION_GLOBAL.VC_ARR2 containing 3 elements, the element at position 1 contains the value 'One', position 2 contains the value 'Two' and position 3 contains the value 'Three'. This is then output using the HTP.P function call.

DECLARE
    l_vc_arr2    APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
    l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE('One:Two:Three');
    FOR z IN 1..l_vc_arr2.count LOOP
        htp.p(l_vc_arr2(z));
    END LOOP;
END;

TABLE_TO_STRING

Table to string

The following function returns a comma delimited string of contact names that are associated with the provided cust_id.

create or replace function get_contacts ( 
    p_cust_id  in  number ) 
    return varchar2 
is 
    l_vc_arr2   apex_application_global.vc_arr2; 
    l_contacts  varchar2(32000); 
begin 
 
    select contact_name 
        bulk collect 
        into l_vc_arr2 
        from contacts 
    where cust_id = p_cust_id 
        order by contact_name; 
 
    l_contacts :=  apex_util.table_to_string ( 
                       p_table => l_vc_arr2, 
                       p_string => ', '); 
 
   return l_contacts; 
 
end get_contacts;