1

I have 500+ tables all containing a column called COLUMNDATA. The table names are like: TABLE_FIRST, TABLE_SECOND, ... ,TABLE_FIVE_HUNDREDTH. What I am trying to get is a column containing all the unique entries from COLUMNDATA across all of these tables. The problem is made a little more challenging because all tables in the DB contain this column, and I am not interested in data from the tables which aren't called TABLE_NUMBER.

I have tried various recipes including UNION and INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'TABLE%' but to no avail.

If anyone has an idea that would be great! Otherwise I'll just stick with the 1000 line script I've got already. For now I have a nasty but gets-the-job-done

CREATE TABLE TAB_NAME AS SELECT COLUMNDATA FROM TABLE_FIRST UNION SELECT COLUMNDATA FROM TABLE_SECOND UNION . . .

ad infinitum

1

Yes, you need a script but it doesn't have to be 1000 lines.

--make a table or global temporary table to put the results in

CREATE TABLE TEMP_COLUMN_DATA
( PK_ID NUMBER(9),
  TABLE_SOURCE VARCHAR2(30),
  TEXT_CONTENT VARCHAR2(4000));

--add the primary key of PK_ID, are contents larger than 4000 characters? use a CLOB

--create a trigger on the table if the PK_ID is null add a sequence value

DECLARE
v_text  VARCHAR2(4000);
CURSOR tab_col IS
Select TABLE_NAME from all_tab_cols
where COLUMN_NAME = 'COLUMNDATA'
and table_name LIKE 'TABLE%'
--modify the cursor here to exclude any tables you dont want
--and table_name not in('xxxx','xxxxx','xxxxx')
ORDER BY 1;
BEGIN
  FOR THE_TABS IN TAB_COL LOOP
    V_TEXT := 'INSERT INTO TEMP_COLUMN_DATA SELECT NULL,'
    || THE_TABS.TABLE_NAME 
    ||',' 
    || THE_TABS.TABLE_NAME 
    || '.COLUMNDATA ' 
    || ' from ' 
    || THE_TABS.TABLE_NAME 
    || ' order by 2';
    DBMS_OUTPUT.PUT_LINE(V_TEXT);
    EXECUTE_IMMEDIATE(v_text);
  END LOOP;
 END;

--then do a select distinct columndata from temp_column_data

  • Maybe I need to mention that there are other tables which contain the same column name which I am not interested in? It seems that this solution will grab data from any table containing COLUMNDATA, right? – Melvyn Drag Jun 12 '15 at 18:59
  • @MelvynDrag see my edit – kevinsky Jun 12 '15 at 19:08
  • I edited the question. The column I am trying to grab is in nearly every table in the database. So, to write out a list of undesired table names doesn't solve the problem so much as transform it into an equally tedious one! – Melvyn Drag Jun 12 '15 at 19:31
  • @MelvynDrag what is the characteristic of the tables you don't want? – kevinsky Jun 12 '15 at 19:32
  • There isn't any real unifying characteristic other than the fact that they have names which aren't like TABLE_NUMBER. I'm very surprised that this isn't a common SQL scripting task! I do this sort of thing regularly in bash. – Melvyn Drag Jun 12 '15 at 19:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.