Re: How to obtain a string of comma separated column names from a table

On Mar 16, 10:00 am, djm1..._at_hotmail.com wrote:
> I am trying to find occurances of duplicate tables under different
> schemas in some oracle instances. I've been able to loop through the
> table names, but I've been unsuccessfull in building a string of all
> of the column names for each table due to DBMS.OUTPUT_PUT_LINE
> limitations (255 chars, etc). How can I build a such a string and
> display it ? I ultimately need to write it to another table I've
> created. But seeing how it behaves via screen output would be best for
> now.
>
> Thanks in advance...
>
> SET SERVEROUTPUT ON SIZE 1000000
> declare
> layer_name varchar2(100);
> column_list char(2048);
> CURSOR layers
> is select owner,table_name from SDE.layers order by
> owner,table_name;
>
> CURSOR cur_cols(in_owner VARCHAR, in_table VARCHAR)
> is select column_name from all_tab_columns where owner =
> in_owner
> and table_name = in_table;
>
> BEGIN
>
> --Out Layers Loop
> FOR index_row in layers LOOP
>
> DBMS_OUTPUT.PUT_LINE('Layer Name is : ' || index_row.owner
> || '.' || index_row.table_name);
>
> --Inner Table's Columns Loop
> FOR rec_cols IN cur_cols(index_row.owner,
> index_row.table_name) LOOP
> column_list := column_list || ',' || column_list;
> /*DBMS_OUTPUT.PUT_LINE('Column: ' ||
> rec_cols.column_name);*/
> DBMS_OUTPUT.PUT_LINE(column_list);
> END LOOP; --End Inner
> END LOOP; --End Outer
> End;
> /
>
> SQL> @list_layers_ora.sql
> Layer Name is : ILADMIN.DDMETA
> Layer Name is : ILOWNER.AIRPORTP
> Layer Name is : ILOWNER.BASE_LOCATIONS
> Layer Name is : ILOWNER.COUNTY_BOUNDARIES
> declare
> *
> ERROR at line 1:
> ORA-20000: ORU-10028: line length overflow, limit of 255 chars per
> line
> ORA-06512: at "SYS.DBMS_OUTPUT", line 35
> ORA-06512: at "SYS.DBMS_OUTPUT", line 133
> ORA-06512: at line 37
>
> SQL>
Why place all the table names into one string? Why not just write them out one per line?

You can pivot the rows into columns and concatenate them if you really want one string. But what happens if you have hundreds of duplicates?

With your existing code you could try replacing dbms_output with utl_file since it can handle a longer line (default around 1000 bytes, expandable to 32k).

HTH -- Mark D Powell -- Received on Fri Mar 16 2007 - 09:14:22 CDT