Some times the situation arises where we want to output the result from the table as a comma separated string. This situation can be answered using many methods. The most efficient method, however, is using TABLE_TO_COMMA procedure of DBMS_UTILITY package. Below is the example that I encountered.
set echo on serveroutput on
declare
cursor c1 is select dept_no, dept_name, location
from dept12;
output_table dbms_utility.uncl_array;
cnt binary_integer;
output_string varchar2(80);
counter integer:= 0;
begin
for i in c1 loop
output_table(counter+1) := I.dept_no;
output_table(counter+2) := I.dept_name;
output_table(counter+3) := I.location;
counter := counter + 3;
end loop;
DBMS_UTILITY.TABLE_TO_COMMA(output_table, cnt, output_string);
dbms_output.put_line(output_string);
end;
Hope this helps !!
Persistent Storage Solutions 
Hi,
Can the the table contain Alpha numeric data types
i create a procedure but how can i see the output
create procedure test
as
L_TAB DBMS_UTILITY.UNCL_ARRAY;
L_LIST1 VARCHAR2 (32767);
L_TABLEN BINARY_INTEGER;
cursor test_cur is
select * from user_objects where rownum < 10;
begin
DBMS_UTILITY.TABLE_TO_COMMA (L_TAB, L_TABLEN, L_LIST1);
DBMS_OUTPUT.PUT_LINE(L_LIST1);
end;
/
No wonder you don’t see output. l_tab is empty, as you don’t fill it at all.
Try something like this:
declare
L_TAB DBMS_UTILITY.LNAME_ARRAY;
L_LIST1 VARCHAR2 (32767);
L_TABLEN BINARY_INTEGER;
begin
select object_name
bulk collect into l_tab
from user_objects where rownum < 10;
DBMS_UTILITY.TABLE_TO_COMMA (L_TAB, L_TABLEN, L_LIST1);
DBMS_OUTPUT.PUT_LINE(L_LIST1);
end;
/