Analytics Software

I had some free time to kill, so tried this!
Not well tested, but seems to work on provided data.

SQL> COLUMN chr_data FORMAT a10
SQL> COLUMN num_data FORMAT a10
SQL> VARIABLE chr_data VARCHAR2(100) ;
SQL> VARIABLE num_data VARCHAR2(100) ;
SQL> EXEC :chr_data := 'A,B,C,D,E,F,G,H' ;
 
PL/SQL procedure successfully completed.
 
SQL> EXEC :num_data := '1,2,3,4,5,6,7,8' ;
 
PL/SQL procedure successfully completed.
 
SQL> SELECT chr_data, num_data
  2  FROM   (SELECT ROWNUM rn,
  3                 substr(chr_data,
  4                        instr(chr_data, ',', 1, rownum) + 1,
  5                        (instr(chr_data, ',', 1, rownum + 1) + 1) -
  6                        (instr(chr_data, ',', 1, rownum) + 1) - 1) chr_data
  7          FROM   (SELECT ',' || :chr_data || ',' chr_data FROM dual)
  8          CONNECT BY 1 = 1 AND
  9                     rownum <
 10                     (length(chr_data) - length(REPLACE(chr_data, ',')))) t1,
 11         (SELECT ROWNUM rn,
 12                 substr(num_data,
 13                        instr(num_data, ',', 1, rownum) + 1,
 14                        (instr(num_data, ',', 1, rownum + 1) + 1) -
 15                        (instr(num_data, ',', 1, rownum) + 1) - 1) num_data
 16          FROM   (SELECT ',' || :num_data || ',' num_data FROM dual)
 17          CONNECT BY 1 = 1 AND
 18                     rownum <
 19                     (length(num_data) - length(REPLACE(num_data, ',')))) t2
 20  WHERE  t1.rn = t2.rn
 21  /
 
CHR_DATA   NUM_DATA
---------- ----------
A          1
B          2
C          3
D          4
E          5
F          6
G          7
H          8
 
8 rows selected.
 
SQL>

Thank You Kamal,
Appreciate your help.
Have a great day.
Srinivas

Here is using PL/SQL :

DECLARE
v_char_type VARCHAR2(500);
v_num_type VARCHAR2(250);
v_char VARCHAR2(1);
v_num VARCHAR2(1);
i NUMBER;
char_len NUMBER;
num_len NUMBER;
BEGIN
v_char_type := 'A,B,C,D,E,F,G,H';
v_num_type := '1,2,3,4,5,6,7,8';

char_len := LENGTH(v_char_type);
num_len := LENGTH(v_num_type);

i:= 1;
WHILE (i <= char_len AND i <= num_len)
LOOP
v_char := substr(v_char_type, i, 1);
v_num := substr(v_num_type,i,1);
DBMS_OUTPUT.PUT_LINE(v_char||' '||v_num);
i := i+2;
END LOOP;
END;

Hallo,

If you have only one-number digits and resprectively one-char words (as in your example):
Then:

DECLARE

v_char_type VARCHAR2(500);
v_num_type VARCHAR2(250);
i number;
BEGIN

v_char_type := 'A,B,C,D,E,F,G,H';
v_num_type := '1,2,3,4,5,6,7,8';
WHILE i<=length(v_char_type)
LOOP
  dbms_output.put_line(substr(v_char_type,i,1)||' '||substr(v_num_type,i,1));
  i := i + 2;
END LOOP;
END;

Regards
Dmytro

Dmytro,
You missed i := 1; in your logic.

In the following case does your logic works ?

v_char_type := 'AB,BC,CD,DE,EF,FG,GH,HI';
v_num_type := '10,20,30,40,50,60,70,80';

Need the generic logic for any words combination separated by comma.
thanks.
Srini

Dmytro,
You missed i := 1; in your logic.

Hi,
yes, you are right!

In the following case does your logic works ?

v_char_type := 'AB,BC,CD,DE,EF,FG,GH,HI';
v_num_type := '10,20,30,40,50,60,70,80';

No , as i said in my previous post, the logic is only for 1 digit number and 1 char word.
Universal solution is a bit more difficilt. You must work with INSTR for search nach comma and with SUBSTR to cut the parts.
Try it at first yourself. Probably I can provide solution tomorrow.
Regards
Dmytro

I have also another idea :-)
You can try to use procedure
dbms_utility.comma_to_table
(pi_string IN,
po_len OUT,
po_table OUT)
This procedure converts string with comma delimiters in table:

Something like this
(only sample, cannot test now)

DECLARE

v_char_type VARCHAR2(500);
v_num_type VARCHAR2(250);
v_char_count NUMBER;
v_num_count NUMBER;
mytab_char  dbms_utility.uncl_array;
mytab_num  dbms_utility.uncl_array;

BEGIN

v_char_type := 'A,B,C,D,E,F,G,H';
v_num_type := '1,2,3,4,5,6,7,8';

dbms_utility.comma_to_table
(v_char_type, v_char_count, mytab_char);
dbms_utility.comma_to_table
(v_num_type, v_num_count, mytab_num);
FOR I IN 1..v_char_count -- or v_num_count
LOOP
dbms_output.put_line(mytab_char(i)||' '||mytab_num(i));
END LOOP;
END;

HTH
Regards
Dmytro

Message was edited by:
Dmytro Dekhtyaryuk

Dmytro:

Your solution with DBMS_UTILTY.COMMA_TO_TABLE is valid in principle, but COMMA_TO_TABLE has a couple of limitations:

SQL> CREATE PROCEDURE p(p_str IN VARCHAR2) AS
  2     l_tab   DBMS_UTILITY.Uncl_Array;
  3     l_count NUMBER;
  4  BEGIN
  5     DBMS_UTILITY.Comma_To_Table(p_str, l_count, l_tab);
  6  END;
  7  /
 
Procedure created.
 
SQL> EXEC p('HELLO, WORLD');
 
PL/SQL procedure successfully completed.
 
SQL> EXEC p('HELLO, SELECT');
BEGIN p('HELLO, SELECT'); END;
*
ERROR at line 1:
ORA-00931: missing identifier
ORA-06512: at "SYS.DBMS_UTILITY", line 125
ORA-06512: at "SYS.DBMS_UTILITY", line 160
ORA-06512: at "SYS.DBMS_UTILITY", line 202
ORA-06512: at "MPPROD.P", line 5
ORA-06512: at line 1
 
SQL> EXEC p('1, 2, 3');
BEGIN p('1, 2, 3'); END;
*
ERROR at line 1:
ORA-00931: missing identifier
ORA-06512: at "SYS.DBMS_UTILITY", line 125
ORA-06512: at "SYS.DBMS_UTILITY", line 160
ORA-06512: at "SYS.DBMS_UTILITY", line 202
ORA-06512: at "MPPROD.P", line 5
ORA-06512: at line 1

It chokes on anything that is not a valid Oracle identifier (like numbers), and on many reserved words.

Since other people have linked to this procedure of mine to replace Comma_To_Table, I may as well do it too. Try this instead

HTH
John

another generic variant :

DECLARE
v_char_type VARCHAR2(500);
v_num_type VARCHAR2(250);
v_char VARCHAR2(200);
v_num VARCHAR2(200);
i NUMBER :=1;
j NUMBER :=1;
char_len NUMBER;
num_len NUMBER;
char_pos NUMBER := 1;
num_pos NUMBER := 1;
BEGIN
v_char_type := 'AB,BC,CD,D,E,F,G,H';
v_num_type := '10,20,30,40,50,60,70,80';

char_len := LENGTH(v_char_type);
num_len := LENGTH(v_num_type);

WHILE (char_pos > 0)
LOOP
char_pos := INSTR(v_char_type,',',i);
num_pos := INSTR(v_num_type,',',j);
v_char := SUBSTR(v_char_type, i, char_pos-i);
v_num := SUBSTR(v_num_type,j,num_pos-j);
DBMS_OUTPUT.PUT_LINE(v_char||' '||v_num);
IF char_pos = 0 THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(v_char_type, i, char_len)||' '||SUBSTR(v_num_type,j,num_len));
END IF;
i := char_pos+1;
j := num_pos+1;
END LOOP;

END;

John,
thank you , very interesting response.
I had not practical experience with comma_to_table and didn't
know the limitations.
Your comment was very useful.
Then I found also interesting thread from Tom Kyte about comma_to_table:
http://asktom.oracle.com/pls/ask/f?p=4950:8:9649128002993894306::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1415803954123.
He shows, that we can work also with list of numbers, if we enclose each one in double quotes : "12", "23", "34".

But your procedure is really more useful, because it works without additionial processing of string.

Regards
Dmytro.

we can work also with list of numbers, if we enclose each one in double quotes : "12", "23", "34".

That would work, however, another limitation would be that the each individual data value between the commas may not be more than 30 characters long - the maximum length of a valid identifier.
This all severly limits the applicability of this procedure and a more general solution as posted above by John is worth having.

SQL> DECLARE
  2      tab   sys.dbms_utility.lname_array;
  3      l_cnt PLS_INTEGER;
  4  BEGIN
  5      -- Call the procedure
  6      sys.dbms_utility.comma_to_table(list   => 'A12345678901234567890123456789',
  7                                      tablen => l_cnt,
  8                                      tab    => tab);
  9  END;
 10  /
 
PL/SQL procedure successfully completed.
 
SQL> DECLARE
  2      tab   sys.dbms_utility.lname_array;
  3      l_cnt PLS_INTEGER;
  4  BEGIN
  5      -- Call the procedure
  6      sys.dbms_utility.comma_to_table(list   => 'A123456789012345678901234567890',
  7                                      tablen => l_cnt,
  8                                      tab    => tab);
  9  END;
 10  /
DECLARE
*
ERROR at line 1:
ORA-00972: identifier is too long
ORA-06512: at "SYS.DBMS_UTILITY", line 132
ORA-06512: at "SYS.DBMS_UTILITY", line 164
ORA-06512: at "SYS.DBMS_UTILITY", line 218
ORA-06512: at line 6
 
 
SQL>

Message was edited by:
Kamal Kishore

This solution should work for all comma separated strings (and other delimiters):

DECLARE
  TYPE t_arr IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
  v_arr1 t_arr;
  v_arr2 t_arr;
  --
  v_char_type VARCHAR2(500);
  v_num_type  VARCHAR2(250);
  --
  v_Line      VARCHAR2(80);
  --
  v_i         NUMBER;
  --
  PROCEDURE list2arr(
    p_List  IN  VARCHAR2,
    p_Delim IN  VARCHAR2 := ',', 
    p_Arr   OUT t_arr
  )
  IS 
    v_Pos NUMBER;
    v_Len NUMBER;
    v_Idx NUMBER;
  BEGIN
    v_Idx := 1;
    v_Pos := 1;
    v_Len := INSTR(p_List, p_Delim, v_Pos);
    --
    WHILE v_Len > 0
    LOOP
      p_Arr(v_Idx) := SUBSTR(p_List, v_Pos, v_Len - v_Pos);
      v_Idx        := v_Idx + 1;
      --
      v_Pos        := v_Len + 1;    
      v_Len        := INSTR(p_List, p_Delim, v_Pos);
    END LOOP; 
    --
    p_Arr(v_Idx) := SUBSTR(p_List, v_Pos, LENGTH(p_List) - v_Pos + 1);
  END;
BEGIN
  v_char_type := 'A,B,C,D,E,F,G,H';
  v_num_type  := '1,2,3,4,5,6,7,8';
  --
  list2arr(p_List => v_char_type, p_Arr => v_arr1);
  list2arr(p_List => v_num_type,  p_Arr => v_arr2);
  --
  v_i := 1;
  --
  WHILE (v_i <= v_arr1.COUNT) OR (v_i <= v_arr2.COUNT)
  LOOP
    v_Line := '';
    --
    IF v_arr1.EXISTS(v_i) THEN 
       v_Line := v_Line || v_arr1(v_i);
    END IF;
    --
    IF v_arr2.EXISTS(v_i) THEN 
       v_Line := v_Line || ' ' || v_arr2(v_i);
    END IF;
    --
    dbms_output.put_line(v_Line);
    --
    v_i := v_i + 1;
  END LOOP;
END;

C.

declare
v_char_type constant varchar2(32) := 'AB,BC,CD,DE,EF,FG,GH,HI,JK,OP,QR';
v_num_type constant varchar2(34) := '10,20,30,40,50,60,70,80,90,100,110';
begin
for rec_Work in
(select
RegExp_Replace(v_char_type,'^([^,]+,){' || to_char(RowNum-1) || '}([^,]+).*$','\2') as v_char,
RegExp_Replace(v_num_type ,'^([^,]+,){' || to_char(RowNum-1) || '}([^,]+).*$','\2') as v_num
from all_catalog
where RowNum <= Length(v_char_type) - Length(Replace(v_char_type,','))+1) Loop

DBMS_Output.Put_Line(rec_Work.v_char || ' ' || rec_Work.v_num);
end Loop;
end;
/

Please try the below code :

DECLARE
v_char_type VARCHAR2(500);
v_num_type VARCHAR2(250);
vLen Number(10);
vStr VARCHAR2(100);
vPos NUMBER(10);
vPos1 NUMBER(10);

j number(10) := 1;
k number(10) := 1;

BEGIN
v_char_type := 'AB,BC,CD,DE,EF,FG,GH,HI';
v_num_type := '11,22,33,44,55,66,77,88';
vLen := Length(v_char_type);
dbms_output.put_line(vLen);

For i in 1 .. vLen loop
select instr(v_char_type,',',1,i) into vPos from dual;
select instr(v_num_type,',',1,i) into vPos1 from dual;

vStr := substr(v_char_type,j,vPos-j)||' '||substr(v_num_type,k,vPos1-k);
dbms_output.put_line(vStr);

j := vPos+1;
k := vPos1+1;
End Loop;

END;

Thanks,
Tandra