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

From: <djm1969_at_hotmail.com>
Date: 16 Mar 2007 07:29:34 -0700
Message-ID: <1174055374.452516.171320@l77g2000hsb.googlegroups.com>

On Mar 16, 9:14 am, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
> 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 --- Hide quoted text -
>
> - Show quoted text -

Sorry, I wasn't totally clear. want to have output (and ultimately a table of the following)...
One column has the table name in owner.layer notation. The second column has all of the columns in that table in alphabetical order as a comma separated string.
Then when the table is built I can query for a distinct count on the second column and any quantities over 1 will be duplicate tables (even though the owner and / or table name is different).

LAYER_NAME COLUMNS_LIST

---------------------- -------------------------

IHS.GEOLOGY,ID,SHAPE,X,Y,ROCK_TYPE,DATE
AEOUSER.BASINS,OBJECT_ID,AREA,PERIMETER,AEOBSNS_ID,GP_ID SDE.ROCKS,ID,SHAPE,X,Y,ROCK_TYPE,DATE So records 1 and 3 are flagged as a possible duplicate dataset. Since both have the value 'ID,SHAPE,X,Y,ROCK_TYPE,DATE' in the COLUMNS_LIST field.

I'll look into that other mechanism though, sounds like it is more appropriate to my needs.

Thanks Received on Fri Mar 16 2007 - 09:29:34 CDT