| Input (String) | a,bc,de,fg,hi |
| Output (Table) |  |
Since output is to expected to be a table, it does not mean to create a temporary table to store the comma separated values.
Oracle provides an option to define a type as user required, some thing like user type.
In similar to programming language as we have int, float, string which are predefined data types, oracle too has such predefined types like varchar, number, char, etc…
In programming language we can define user defined type like classes defined by user,
Also in similar fashion we can create user defined types in oracle using Type clause.
Syntax:-
CREATE OR REPLACE TYPE [TYPE NAME] AS [TYPE];
Ex :-
CREATE OR REPLACE TYPE SampleType AS OBJECT (
A VARCHAR2(100),
B VARCHAR2(100),
C NUMBER
)
In our case to return a table from oracle function, we should create a object of Type table.
Since table constitutes of columns to append rows, so first we should define an object which holds those columns and next an another object of type table for the above object which holds the columns.
That is,
1)
--An object type that holds the values that are comma separated or
--since a table has to hold the comma separated values, so we
--need a single column of varchar datatype
CREATE OR REPLACE TYPE ValueType AS OBJECT (
Val VARCHAR2(100)
)
2)
--We need to create a table that holds the values of the comma separated values,
--such that this table type is table of the above object type (ValueType).
CREATE OR REPLACE TYPE ValueType_Tbl AS TABLE OF ValueType
 |
3) Oracle function.
Please copy and paste this function into toad or an editors to understand comments against each line.
--argStr is the input string that has to splitted based upon delimeter
--delimeter is input string which has to be considered to split,
--such for this comma seperated values delimeter is ","
CREATE OR REPLACE FUNCTION Csv(argStr IN VARCHAR,delimeter IN VARCHAR)
--Return type mean to say that the table type defined as expaind above,
--rows that are to piped in while executing this function should
--be inserted or pipelined into ValueType_Tbl.
--such that this function returns this pipelined table (ValueType_Tbl)
RETURN ValueType_Tbl PIPELINED IS
tmpStr VARCHAR2(2000);
tmpSubStr VARCHAR2(100);
--Declaring a varaible of the new row type in table (ValueType_Tbl) indirectly
--since ValueType_Tbl is table of ValueType
out_rec ValueType := ValueType(NULL);
--ValueType(null) mean that it is default value
BEGIN
--taking the input string into another variable
tmpStr:=argStr;
--chekcing the length of the given input string
WHILE(LENGTH(tmpStr)>0 AND tmpStr IS NOT NULL)
LOOP
--taking first par of substring from the string till delimeter occurs (,)
tmpSubStr:=SUBSTR(tmpStr,1,INSTR(tmpStr,delimeter)-1);
--chekcing if there is no other part of string then asking to pipe line this row
-- and exit the loop
IF(tmpSubStr IS NULL)THEN
out_rec.Val:=tmpStr;
PIPE ROW(out_rec);
EXIT;
END IF;
--Assigning the sub string value to the record and pipe linening into the considered table type
out_rec.Val:=tmpSubStr;
PIPE ROW(out_rec);
--Taking the other of string after removing the sub string
tmpStr:=SUBSTR(tmpStr,INSTR(tmpStr,delimeter)+1);
END LOOP;
RETURN ;
END;
/
 |
 |
Output:-
Query that has to be executed to run this oracle function
SELECT * FROM TABLE(Csv('1,bc,de,fg',','))
We can observe that this function which returns, we are trying to type cast the pipelined rows to a table, such that we can have the result set in table format
 |
2 comments:
This is really help to me,
thank you very much.
great, here is this solution using regexp:
CREATE OR REPLACE TYPE virtual_table_type AS TABLE OF varchar2(100);
/
CREATE OR REPLACE function INBAL.string_to_list (p_string IN VARCHAR2, p_deliminator IN VARCHAR)
RETURN virtual_table_type PIPELINED
as
v_cur_val varchar2(100);
cursor v_cur is select
regexp_substr(str,'[^' || p_deliminator || ']+',1,level) val
from (select p_string str from dual) t
connect by level <= length(str)-length(replace(str,p_deliminator))+1;
BEGIN
for v_rec in v_cur loop
v_cur_val := v_rec.val;
PIPE ROW(v_cur_val);
end loop;
return ;
END;
/
--you can use the function in both options:
-- return a table
select * from table(string_to_list('asd,asf,sad,s',',') );
-- return a data set
select string_to_list('asd,asf,sad,s',',') from dual;
Post a Comment