CREATE OR REPLACE PROCEDURE COLSTRANS.csv
 

 (
   ipr_string       varchar2,
   ipr_delim        varchar2 DEFAULT ',' ,
   s            OUT sys_refcursor
)
IS
   first_time     varchar2 (40);
   pos            NUMBER (10);
   first_part     VARCHAR2 (40);
   len            number (10);
   p              NUMBER (10) := 1;
   pos2           NUMBER (10);
   last_part      VARCHAR2 (40);
   v_sql          varchar2 (10000);
   len_for_last   VARCHAR2 (40);
   not_a_csv_string exception;
   pragma exception_init(not_a_csv_string,-01428);
BEGIN
   first_time := 'TRUE';

   len_for_last := LENGTH (ipr_string);



   SELECT   (LENGTH (ipr_string)
             - LENGTH (REPLACE (ipr_string, ipr_delim, '')))
     INTO   len
     FROM   DUAL;

   SELECT   INSTR (ipr_string,
                   ipr_delim,
                   1,
                   p)
     INTO   pos
     FROM   DUAL;


   SELECT   SUBSTR (ipr_string, P, POS - 1) INTO first_part FROM DUAL;



   first_time := 'FALSE';


   IF first_time <> 'TRUE'
   THEN
      FOR i IN 1 .. len
      LOOP
         --looping for the coma only  betwwen second and but one last chratcer



         p := p + 1;

         SELECT   INSTR (ipr_string,
                         ipr_delim,
                         1,
                         p)
           INTO   pos2
           FROM   DUAL;

         pos := pos + 1;

if pos2<>0 then 
         
         

         v_sql :=
               v_sql
            || 'select SUBSTR('
            || CHR (39)
            || ipr_string
            || CHR (39)
            || ipr_delim
            || pos
            || ipr_delim
            || pos2
            || '-'
            || pos
            || ')   FROM DUAL';
         v_sql := v_sql || '  ';
         v_sql := v_sql || 'union';
         v_sql := v_sql || '  ';
end if;
         pos := pos2;
       
      END LOOP;


      BEGIN
--         DBMS_OUTPUT.put_line ('lenhth upto last comma' || len);

         SELECT   INSTR (ipr_string,
                         ipr_delim,
                         1,
                         len)
           INTO   pos
           FROM   DUAL;

         --len is length of last comma
--         DBMS_OUTPUT.put_line ('occ of last comma' || pos);

         SELECT   SUBSTR (ipr_string, pos + 1, len_for_last)
           INTO   last_part
           FROM   DUAL;

--         DBMS_OUTPUT.put_line ('lenhth ofwhole string ' || len_for_last);
--         DBMS_OUTPUT.put_line ('harm last part' || last_part);
      --len_for_last is length of wholestring
      END;



      SELECT   RTRIM (v_sql,'union') INTO v_sql FROM DUAL;
   END IF;


   v_sql :=
         v_sql
      || ' '
      || 'select '
      || CHR (39)
      || first_part
      || CHR (39)
      || ' from dual UNION'
      || ' '
      || v_sql
      || ' '
      || 'select '
      || CHR (39)
      || last_part
      || CHR (39)
      || 'from dual';

 


   OPEN S FOR v_sql;
 
EXCEPTION
WHEN not_a_csv_string THEN
OPEN S FOR
SELECT ipr_string FROM DUAL;
 
END csv;
/