Using TABLE_TO_COMMA in PLSQL

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

3 Comments

  1. 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;
    /

    Reply

  2. 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;
    /

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s