Friday, June 3, 2016

How to convert a string to selectable table by a function to use in APEX or ORACLE SQL


Converting comma separated values to a list and table has a long history. I do remember lots of time faced a scenario I need to parse a delimited string. Generally, in Oracle and writing query in APEX, converting these strings to a selectable table is a nice way to face these scenarios and simplify codes.
In APEX, it is common we use "Checkbox Item" in forms. They return a delimited string like "Option1:Option2:Option3". Even there are many ways to parse and use them in condition and query. However, a simple and fast way is to convert them to a table to be used in SQL query and avoid any PL/SQL or custom code.
An example to show what I mean is (PXX_DEP is APEX checkbox item):

 Select * from emp where DEPTNO in (  
   select to_number(str) from table(string_to_selectable_table(:PXX_DEP)) aa  
  )  

I have developed function "string_to_selectable_table" to ease this use similar to APEX original function "APEX_UTIL.STRING_TO_TABLE" but selectable. I did not use APEX array type "APEX_APPLICATION_GLOBAL.VC_ARR2" to show we could create different type and I used a regular expression that can be used to convert more complex strings to the selectable array. You can download it from my GitHub by using this URL, and it contains data type, function, and an example.

Type and function are (use GitHub to access the latest version) :

 CREATE or REPLACE TYPE t_str_row_tab IS TABLE OF VARCHAR2(4000) ;  
 /  
 CREATE or REPLACE FUNCTION string_to_selectable_table ( p_string   IN VARCHAR2,  p_delimiter  IN VARCHAR2 DEFAULT ':')  
    RETURN t_str_row_tab PIPELINED  
   AS  
    n_row NUMBER := 1 ;  
    v_str VARCHAR2(4000) ;  
   BEGIN     
    v_str := trim(regexp_substr(p_string, '[^'|| p_delimiter || ']+', 1, n_row) ) ;  
    WHILE ( v_str is not null )  
    LOOP  
      PIPE ROW ( v_str );  
      n_row := n_row + 1 ;  
      v_str := trim(regexp_substr(p_string, '[^'|| p_delimiter || ']+', 1, n_row) ) ;  
    END LOOP;  
    RETURN;  
 END string_to_selectable_table;  

To test you can run :
 select COLUMN_VALUE from table(string_to_selectable_table('Option1:Option2:Option3', ':') ) aa;  

1 comment:

  1. This comment has been removed by a blog administrator.

    ReplyDelete