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

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Fri, 16 Mar 2007 17:21:00 +0100
Message-ID: <45fac3eb$0$16073$426a74cc@news.free.fr>

<djm1969_at_hotmail.com> a écrit dans le message de news: 1174055374.452516.171320_at_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

|
|

select owner, table_name, column_name
from dba_tab_columns
where (owner, table_name) in

   (select owner, table_name from SDE.layers) order by 1,2,3
/

On the result of this query apply what has been explained at: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:12864646978683#163254800346080990 and below

Regards
Michel Cadot Received on Fri Mar 16 2007 - 11:21:00 CDT