The COMMA_TO_TABLE procedure converts a comma-delimited list of names into an array of names. Each entry in the list becomes an element in the array.
>>-DBMS_UTILITY.COMMA_TO_TABLE_LNAME--(--list--,--tablen--,--tab--)-><
>>-DBMS_UTILITY.COMMA_TO_TABLE_UNCL--(--list--,--tablen--,--tab--)-><
EXECUTE privilege on the DBMS_UTILITY module.
SET SERVEROUTPUT ON@
CREATE OR REPLACE PROCEDURE comma_to_table(
IN p_list VARCHAR(4096))
BEGIN
DECLARE r_lname DBMS_UTILITY.LNAME_ARRAY;
DECLARE v_length INTEGER;
CALL DBMS_UTILITY.COMMA_TO_TABLE_LNAME(p_list, v_length, r_lname);
BEGIN
DECLARE i INTEGER DEFAULT 1;
DECLARE loop_limit INTEGER;
SET loop_limit = v_length;
WHILE i <= loop_limit DO
CALL DBMS_OUTPUT.PUT_LINE(r_lname[i]);
SET i = i + 1;
END WHILE;
END;
END@
CALL comma_to_table('sample_schema.dept,sample_schema.emp,sample_schema.jobhist')@
SET SERVEROUTPUT ON
DB20000I The SET SERVEROUTPUT command completed successfully.
CREATE OR REPLACE PROCEDURE comma_to_table(
IN p_list VARCHAR(4096))
BEGIN
DECLARE r_lname DBMS_UTILITY.LNAME_ARRAY;
DECLARE v_length INTEGER;
CALL DBMS_UTILITY.COMMA_TO_TABLE_LNAME(p_list, v_length, r_lname);
BEGIN
DECLARE i INTEGER DEFAULT 1;
DECLARE loop_limit INTEGER;
SET loop_limit = v_length;
WHILE i <= loop_limit DO
CALL DBMS_OUTPUT.PUT_LINE(r_lname[i]);
SET i = i + 1;
END WHILE;
END;
END
DB20000I The SQL command completed successfully.
CALL comma_to_table('sample_schema.dept,sample_schema.emp,sample_schema.jobhist')
Return Status = 0
sample_schema.dept
sample_schema.emp
sample_schema.jobhist