DBMS_UTILITY.table_to_comma

[SpiritOfLennon]

Hi,
Does anyone know the format for using the DBMS_UTILITY.table_to_comma procedure? I am new to using DBMS_UTILITY and don't know the first thing to do.
Sol SOL
I'm only guessing but my guess work generally works for me.

 

[jee]

Here is the blurb from the PL/SQL supplied packages manual:

TABLE_TO_COMMA Procedure
This procedure converts a PL/SQL table of names into a comma-separated list of names. This takes a PL/SQL table, 1..n, terminated with n+1 null.

Syntax
DBMS_UTILITY.TABLE_TO_COMMA (
tab IN UNCL_ARRAY,
tablen OUT BINARY_INTEGER,
list OUT VARCHAR2);
Parameters:
Parameter Description
tab
PL/SQL table which contains list of table names.

tablen
Number of names in the PL/SQL table.

list
Comma separated list of names.



Returns
Returns a comma-separated list and the number of elements found in the table .

 

[SpiritOfLennon]

Hi,
Thanks for that. Does anyone have any practical examples that may give me an idea how to use the utility?
Regards SOL SOL
I'm only guessing but my guess work generally works for me.

 

[sem]

Can you explain what do you need it for? Regards, Dima

 

[jee]

This one isn't very practical, but perhaps it will help:

CREATE OR REPLACE PROCEDURE COMMA_SAMPLE AS
mytable DBMS_UTILITY.uncl_array;
mylist VARCHAR2(80);
mytable_count NUMBER;
BEGIN
mylist := 'Alex, Donna, Hope, Jose, Judy, Julia, Nancy, Paul, Sandy';
DBMS_OUTPUT.PUT_LINE('MYLIST: ' || mylist);
DBMS_UTILITY.COMMA_TO_TABLE(mylist, mytable_count, mytable);
mylist := 'Empty.';
DBMS_OUTPUT.PUT_LINE('MYTABLE: ');
DBMS_OUTPUT.PUT_LINE('---------------------------');
FOR item IN 1..mytable_count LOOP
DBMS_OUTPUT.PUT_LINE(mytable(item));
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
DBMS_UTILITY.TABLE_TO_COMMA(mytable, mytable_count, mylist);
DBMS_OUTPUT.PUT_LINE('MYLIST: ' || mylist);
END;

 

[SpiritOfLennon]

Okay here's what I want to do.
I have a SQL query that I use to generate a report, I want to output the results to a CSV file and then e-mail the file ( from the OS not Oracle ) to users for loading into Excel.
Regards SOL SOL
I'm only guessing but my guess work generally works for me.

 

[sem]

I do not think it may be usefull in your case: in fact this procedure was developed for internal use only and assumes that only lists of VALID ORACLE NAMES will be converted. It's both limited in datatypes (varchar2(127), not more) and not very reliable when using string of "arbitrary" nature (i.e. contatining commas). You may write your own with the same functionality with 10-20 lines of code, but more usefule one needs more efforts. Regards, Dima