Oracle Split function

In order for the PL/SQL function to return multiple rows of data, it must be done by returning a REF CURSOR or a data collection. This case of REF CURSOR is limited to the data that can be selected from the query, and the entire collection must be materialized before it can be returned. Oracle 9i corrects the latter case by introducing a pipelined table function. A table function is a function that returns the entire set of rows (usually as a collection), which can be queried directly from an SQL statement as if it were a real database table. The pipelined table function is similar, but it returns the data as it was built, rather than returning it all at once. pipelining table functions are more efficient because the data can be returned as quickly as possible.

The pipelined table function must return a collection. In the function, the PIPE ROW statement is used to return a single element of the collection, and the function must end with an empty return statement to indicate that it has completed. Once we have created the above function, we can invoke it from the SQL query using the TABLE operator.

pipelined table functions are often used to transform data from one type to another.

Create or ReplaceType Type_split as Table  of varchar2( -);--Create a type that, if you want the split function to be generic, set its size larger. Create or Replace functionStrsplit (P_valuevarchar2, P_splitvarchar2:= ',')--Usage:select * FROM table (strsplit (' 1,2,3,4,5 ')) returnStrsplit_type pipelined isV_idxinteger; V_strvarchar2( -); V_strs_lastvarchar2(4000) :=P_value;beginLoop V_idx:=InStr (V_strs_last, P_split); Exit  whenV_idx= 0; V_STR:=SUBSTR (V_strs_last,1, V_idx- 1); V_strs_last:=substr (V_strs_last, V_idx+ 1); Piperow (V_STR); EndLoop; Piperow (v_strs_last); return;EndStrsplit;

View Code

Oracle Split function