pipelined function..please help
557104Apr 26 2007 — edited Apr 26 2007hi all...
i need a fuction which will split the data n insert into the table...
i created a fuction like this :
CREATE OR REPLACE FUNCTION FN_SPLIT(text IN VARCHAR2 DEFAULT NULL,delimiter IN VARCHAR2 DEFAULT ' ')
RETURN SPLIT_TYPE_TABLE PIPELINED
IS
PRAGMA AUTONOMOUS_TRANSACTION;
TYPE ref0 is REF CURSOR;
myCursor ref0;
out_rec SPLIT_TYPE := SPLIT_TYPE(null, null);
--CURSOR MSTFRC IS SELECT * FROM TEMP_SPLIT;
index_ NUMBER(10,0);
BEGIN
BEGIN
/*index_ := -1;
cnt:=0;
l_str:=text;
loop
l_n := instr( l_str, delimiter );
exit when (nvl(l_n,0) = 0);
cnt:=cnt+1;
INSERT INTO TEMP_SPLIT (ID,NAME)
VALUES (cnt,ltrim(rtrim(substr(l_str,1,l_n-1))));
l_str := substr( l_str, l_n+1 );
end loop;*/
Load_Temp_Splitting(text,delimiter);
open myCursor for select * from temp_split;
LOOP FETCH myCursor into out_rec.ID,out_rec.NAME;
EXIT WHEN myCursor%NOTFOUND;
PIPE ROW(out_rec);
END LOOP;
CLOSE myCursor;
RETURN;
END;
END FN_SPLIT;
/
it created succesfully without errors but when i run this function it showing an error like cannot evaluate pipelined function..
my requirement is to split the data like 'as,af,er,yt' split this by comma n insert into the table with row id like
1 as
2 af
3 er
like...
please help friends....
thnks in advance...
lol
Neethu