Oracle 10g : table_to_comma and comma_to_table features

Let's see how to convert a csv list to a table and vice versa.

TABLE_TO_COMMA

dbms_utility.table_to_comma (
tab IN UNCL_ARRAY,
tablen OUT BINARY_INTEGER,
list OUT VARCHAR2);
set serveroutput on

DECLARE
x dbms_utility.uncl_array;
y BINARY_INTEGER;
z VARCHAR2(4000);
BEGIN
x(1) := 'ABC,DEF';
x(2) := 'GHI,JKL,MNO';
x(3) := 'PQR,STU,VWX,YZ1';
x(4) := '2,3,4,5,6';
x(5) := 'ABC,January,Morgan,University of Washington';
dbms_output.put_line('1: ' || x(1));
dbms_output.put_line('2: ' || x(2));
dbms_output.put_line('3: ' || x(3));
dbms_output.put_line('4: ' || x(4));
dbms_output.put_line('5: ' || x(5));
dbms_utility.table_to_comma(x, y, z);
dbms_output.put_line('Array Size: ' || TO_CHAR(y));
dbms_output.put_line('List: ' || z);
END;
/

dbms_utility.table_to_comma (
tab IN lname_array,
tablen OUT BINARY_INTEGER,
list OUT VARCHAR2);
set serveroutput on

DECLARE
x dbms_utility.lname_array;
y BINARY_INTEGER;
z VARCHAR2(4000);
BEGIN
x(1) := 'ABC,DEF';
x(2) := 'GHI,JKL,MNO';
x(3) := 'PQR,STU,VWX,YZ1';
x(4) := '2,3,4,5,6';
x(5) := 'ABC,January,Morgan,University of Washington';
dbms_output.put_line('1: ' || x(1));
dbms_output.put_line('2: ' || x(2));
dbms_output.put_line('3: ' || x(3));
dbms_output.put_line('4: ' || x(4));
dbms_output.put_line('5: ' || x(5));
dbms_utility.table_to_comma(x, y, z);
dbms_output.put_line('Array Size: ' || TO_CHAR(y));
dbms_output.put_line('List: ' || z);
END;
/

COMMA_TO_TABLE

dbms_utility.comma_to_table(
list IN VARCHAR2,
tablen OUT BINARY_INTEGER,
tab OUT UNCL_ARRAY);
CREATE TABLE c2t_test (
readline VARCHAR2(200));

INSERT INTO c2t_test VALUES ('"1","Mainframe","31-DEC-2001"');
INSERT INTO c2t_test VALUES ('"2","MPP","01-JAN-2002"');
INSERT INTO c2t_test VALUES ('"3","Mid-Size","02-FEB-2003"');
INSERT INTO c2t_test VALUES ('"4","PC","03-MAR-2004"');
INSERT INTO c2t_test VALUES ('"5","Macintosh","04-APR-2005"');
COMMIT;

SELECT * FROM c2t_test;

CREATE TABLE test_import (
src_no NUMBER(5),
src_desc VARCHAR2(20),
load_date DATE);

CREATE OR REPLACE PROCEDURE load_c2t_test IS

c_string VARCHAR2(250);
cnt BINARY_INTEGER;
my_table dbms_utility.uncl_array;

BEGIN
FOR t_rec IN (SELECT * FROM c2t_test)
LOOP
dbms_utility.comma_to_table(t_rec.readline, cnt, my_table);

my_table(1) := TRANSLATE(my_table(1), 'A"','A');
my_table(2) := TRANSLATE(my_table(2), 'A"','A');
my_table(3) := TRANSLATE(my_table(3), 'A"','A');

INSERT INTO test_import
(src_no, src_desc, load_date)
VALUES
(TO_NUMBER(my_table(1)), my_table(2), TO_DATE(my_table(3)));
END LOOP;
COMMIT;
END load_c2t_test;
/

exec load_c2t_test;

SELECT * FROM test_import;

Overload 2 :

dbms_utility.comma_to_table(
list IN VARCHAR2,
tablen OUT BINARY_INTEGER,
tab OUT lname_array);
CREATE TABLE c2t_test (
readline VARCHAR2(200));

INSERT INTO c2t_test VALUES ('"1","Mainframe","31-DEC-2001"');
INSERT INTO c2t_test VALUES ('"2","MPP","01-JAN-2002"');
INSERT INTO c2t_test VALUES ('"3","Mid-Size","02-FEB-2003"');
INSERT INTO c2t_test VALUES ('"4","PC","03-MAR-2004"');
INSERT INTO c2t_test VALUES ('"5","Macintosh","04-APR-2005"');
COMMIT;

SELECT * FROM c2t_test;

CREATE TABLE test_import (
src_no NUMBER(5),
src_desc VARCHAR2(20),
load_date DATE);

CREATE OR REPLACE PROCEDURE load_c2t_test IS

c_string VARCHAR2(250);
cnt BINARY_INTEGER;
my_table dbms_utility.lname_array;

BEGIN
FOR t_rec IN (SELECT * FROM c2t_test)
LOOP
dbms_utility.comma_to_table(t_rec.readline, cnt, my_table);

my_table(1) := TRANSLATE(my_table(1), 'A"','A');
my_table(2) := TRANSLATE(my_table(2), 'A"','A');
my_table(3) := TRANSLATE(my_table(3), 'A"','A');

INSERT INTO test_import
(src_no, src_desc, load_date)
VALUES
(TO_NUMBER(my_table(1)), my_table(2), TO_DATE(my_table(3)));
END LOOP;
COMMIT;
END load_c2t_test;
/

exec load_c2t_test;

SELECT * FROM test_import;

Long live "Tom Kyte".

Good Luck !!
r-a-v-i