Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

pipelined function..please help

557104Apr 26 2007 — edited Apr 26 2007
hi 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 24 2007
Added on Apr 26 2007
7 comments
680 views