Example of Comma separation Procedure

--Self created variable array data type of length 2.

CREATE Or Replace TYPE x AS VARRAY(2) OF number;

Procedure made by Tahir Khalid to get seprated values

create or replace procedure sep_vals(a varchar2)

is

z x;

c varchar2(2);

d varchar2(2);

e varchar2(2);

begin

z:=x(0,0,0,0,0,0,0,0,0,0);

for i in 1..9 loop

select  instr(a,',',1,i) into z(i) from dual;

end loop;

dbms_output.put_line('The value of z(1)='||z(1));

select substr(a,z(1)-2,2) into c from dual;

dbms_output.put_line('The value of z(2)='||z(2));

select substr(a,z(2)-2,2) into d from dual;

dbms_output.put_line('The value of z(3)='||z(3));

select substr(a,z(3)-2,2) into e from dual;

dbms_output.put_line(c);

dbms_output.put_line(d);

dbms_output.put_line(e);

end;

calling environment in test window for above procedure

declare

  -- Local variables here

  a varchar2(30) :='10,20,30,40,50,60,70,80,90,100';

 begin

 sep_vals(a);

 end;