SQL & PL/SQL

hi

i made two functions one of them which use varchar2 data type with pipelined and

another with clob data type with pipelined.

i am giving parameters to both of them first varch2 with pipelined is working fine.

but another is not.

and i made diff type for both of them.

like clob object type for second.

and varchar2 type for first.

my first function is like 

TYPE "CSVOBJECTFORMAT"  AS OBJECT ( "S"   VARCHAR2(500));

TYPE "CSVTABLETYPE" AS TABLE OF CSVOBJECTFORMAT;

CREATE OR REPLACE FUNCTION "FN_PARSECSVSTRING" (p_list
    VARCHAR2, p_delim VARCHAR2:=' ') RETURN CsvTableType PIPELINED
    IS
l_idx PLS_INTEGER;
l_list VARCHAR2(32767) := p_list;
l_value VARCHAR2(32767);
BEGIN
LOOP
l_idx := INSTR(l_list, p_delim);
IF l_idx > 0 THEN
PIPE ROW(CsvObjectFormat(SUBSTR(l_list, 1, l_idx-1)));
l_list := SUBSTR(l_list, l_idx+LENGTH(p_delim));
ELSE
PIPE ROW(CsvObjectFormat(l_list));
EXIT;
END IF;
END LOOP;
RETURN;
END fn_ParseCSVString;

and out put for this function is like

which is correct.

SQL>   SELECT s  FROM  TABLE( CAST( fn_ParseCSVString('+588675,1~#588675^1^99^~2~16~115~99~SP5601~~~~~0~~', '~') as CsvTableType)) ;

S
--------------------------------------------------------------------------------
+588675,1
#588675^1^99^
2
16
115
99
SP5601





S
--------------------------------------------------------------------------------
0



14 rows selected.

SQL>


my second function is like

TYPE "CSVOBJECTFORMAT1"  AS OBJECT ( "S"   clob); 

TYPE "CSVTABLETYPE1" AS TABLE OF CSVOBJECTFORMAT1;

CREATE OR REPLACE FUNCTION "FN_PARSECSVSTRING1" (p_list
    clob, p_delim VARCHAR2:=' ') RETURN CsvTableType1 PIPELINED
    IS
l_idx PLS_INTEGER;
l_list  clob := p_list;
l_value VARCHAR2(32767);
BEGIN

dbms_output.put_line('hello');
  LOOP
  l_idx := INSTR(l_list, p_delim);
  IF l_idx > 0 THEN
PIPE ROW(CsvObjectFormat1(substr(l_list, 1, l_idx-1)));
l_list :=  dbms_lob.substr(l_list, l_idx+LENGTH(p_delim));
ELSE
PIPE ROW(CsvObjectFormat1(l_list));
 exit;
END IF;
 
  END LOOP; 
 
RETURN;
END fn_ParseCSVString1;

SQL>   SELECT s  FROM  TABLE( CAST( fn_ParseCSVString1('+588675,1~#588675^1^99^~2~16~115~99~SP5601~~~~~0~~', '~') as CsvTableType1)) ;

S
--------------------------------------------------------------------------------
+588675,1
+588675,1
+588675,1
+588675,1
+588675,1
+588675,1
+588675,1
+588675,1
+588675,1
+588675,1
+588675,1

and it goes on until i use ctrl+C to break it.

actually i want to make a function which can accept large values  so i am trying to change first function.

thanks