Oracle character separation function (split)

To allow PL/SQL functions to return multiple rows of data, a ref cursor or a data set must be returned. Ref cursor is limited to the data that can be selected from the query. The entire set must be specific before it can be returned. Oracle 9i corrected the latter situation through the introduced pipeline table function. A table function is a function that returns the entire row set (usually as a set). It can be queried directly from an SQL statement, just as if it is a real database table. Pipeline table functions are similar, but they return data as they are during build, rather than all at once. Pipeline table functions are more effective because data can be returned as quickly as possible.

The canonicalized table function must return a set. In a function, the pipe row statement is used to return a single element of the set. The function must end with an empty return statement to indicate that it has been completed. Once we create the above function, we can use the table operator to call it from SQL queries.

Pipeline table functions are often used to convert data from one type to another.

 Create   Or   Replace   Function Strsplit (p_value Varchar2  , P_split  Varchar2 :=   '  ,  '  )  --  Usage: Select * from table (strsplit ('1, 2, 3, 4, 5 '))   Return  Strsplit_type pipelined  Is  V_idx  Integer  ; V_str  Varchar2 ( 500 ); V_strs_last  Varchar2 ( 4000 ): =  P_value;  Begin  Loop v_idx:  =  Instr (v_strs_last, p_split );  Exit   When V_idx =   0  ; V_str:  = Substr (v_strs_last, 1 , V_idx -   1  ); V_strs_last:  = Substr (v_strs_last, v_idx +   1  );  Pipe  Row (v_str );  End  Loop;  Pipe  Row (v_strs_last ); Return  ;  End Strsplit;

 
Usage: Select * from table (strsplit ('1, 2, 3, 4, 5 '))

 

1 1
22
33
44
55