Tag: table_to_comma

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 !!