Oracle - SQL to get data from all tables with specific column and value

Question

I have dozen of connected tables with COLUMN_NAME ENTITY_ID and I want to find all related records with specific value, I can find table with specific columns

select * from all_tab_cols a where a.COLUMN_NAME='ENTITY_ID';

And I can find records per table

select * from TABLENAME where ENTITY_ID='100';

Is there a way to view all related records in all tables using single SQL?

and all the tables have the same structure? How can one result provide you output that contains the data from the different tables whose structure is not already known? Please share the sample data and expected output — Popeye
– Popeye, Commented Nov 21, 2019 at 6:28
@Tejash Irrelevant, just get all data, as select * from a,b,c — Ori Marko
– Ori Marko, Commented Nov 21, 2019 at 6:30
@user7294900 if you are comming from teh Java world, there is possible misunderstanding. SELECT * does not mean select Object - it is only shortcut for all columns of one specific table. — Marmite Bomber
– Marmite Bomber, Commented Nov 21, 2019 at 7:08
@MarmiteBomber Still you can select * from a,b,c in oracle without Java and display all columns — Ori Marko
– Ori Marko, Commented Nov 21, 2019 at 7:09
For sure, but a Cartesian join is not always what you want for reporting data... (You get 1.000 rows for three 10 rows tables). — Marmite Bomber
– Marmite Bomber, Commented Nov 21, 2019 at 7:35

Popeye · Accepted Answer · 2019-11-21 06:49:03Z

I am not sure what is expected so answering as per my understanding as following:

Let's say we have a table ACCOUNT which has CUST_ID column as following:

SQL> SELECT OWNER, A.TABLE_NAME, COLUMN_NAME FROM
  2      ALL_TAB_COLS A
  3  WHERE
  4      A.COLUMN_NAME = 'CUST_ID';

OWNER      TABLE_NAME           COLUMN_NAME
---------- -------------------- --------------------
TEJASH     ACCOUNT              CUST_ID

SQL> SELECT * FROM ACCOUNT;

    ACC_NR       SUM_    CUST_ID
---------- ---------- ----------
       500       3400        100

Now, I want to search in all the tables (on which I have access) to find all the tables having a CUST_ID column with value 100 in it.

SQL> SELECT
  2      table_name, COLUMN_NAME,
  3      to_number(xmlquery('/ROWSET/ROW/C/text()'
  4      passing xmltype(dbms_xmlgen.getxml(
  5        'select count(1) as c '
  6        || 'from "' || owner || '"."' || table_name || '" WHERE ' || COLUMN_NAME  || '=''100'''))
  7    returning content)) as cOUNT
  8  FROM ALL_TAB_COLS A
  9  WHERE A.COLUMN_NAME = 'CUST_ID';

TABLE_NAME           COLUMN_NAME               COUNT
-------------------- -------------------- ----------
ACCOUNT              CUST_ID                       1

Here, every table with the column CUST_ID will appear and COUNT column displays the number of records in that table with CUST_ID = 100

Now, let's add a column to another table and see the effect:

SQL> ALTER TABLE ACTIVE_USERS ADD CUST_ID VARCHAR2(100);

Table altered.

SQL> INSERT INTO ACTIVE_USERS VALUES (5,SYSDATE, SYSDATE, 200);

1 row created.

SQL> SELECT * FROM ACTIVE_USERS;

  CUST_NUM START_DATE           END_DATE             CUST
---------- -------------------- -------------------- ----
########## 21-NOV-19            21-NOV-19            200
########## 21-NOV-19            21-NOV-19
########## 01-JAN-18            01-JAN-19
########## 01-JAN-18
########## 01-JAN-19            01-JUN-19
########## 01-JAN-17            01-MAR-19

6 rows selected.

Now, Again running our query to find the data from all the tables:

SQL> SELECT
  2      table_name, COLUMN_NAME,
  3      to_number(xmlquery('/ROWSET/ROW/C/text()'
  4      passing xmltype(dbms_xmlgen.getxml(
  5        'select count(1) as c '
  6        || 'from "' || owner || '"."' || table_name || '" WHERE ' || COLUMN_NAME  || '=''100'''))
  7    returning content)) as cOUNT
  8  FROM ALL_TAB_COLS A
  9  WHERE A.COLUMN_NAME = 'CUST_ID';

TABLE_NAME           COLUMN_NAME               COUNT
-------------------- -------------------- ----------
ACCOUNT              CUST_ID                       1
ACTIVE_USERS         CUST_ID                       0

SQL>

And, It worked!!

Again adding more data to the ACTIVE_USERS table and see the result.

SQL> INSERT INTO ACTIVE_USERS VALUES (6,SYSDATE-1, SYSDATE, 100);

1 row created.

SQL> INSERT INTO ACTIVE_USERS VALUES (7,SYSDATE-2, SYSDATE, 100);

1 row created.

SQL> INSERT INTO ACTIVE_USERS VALUES (8,SYSDATE-3, SYSDATE, 100);

1 row created.

SQL> SELECT * FROM ACTIVE_USERS;

  CUST_NUM START_DATE           END_DATE             CUST
---------- -------------------- -------------------- ----
########## 21-NOV-19            21-NOV-19            200
########## 20-NOV-19            21-NOV-19            100
########## 19-NOV-19            21-NOV-19            100
########## 18-NOV-19            21-NOV-19            100
########## 21-NOV-19            21-NOV-19
########## 01-JAN-18            01-JAN-19
########## 01-JAN-18
########## 01-JAN-19            01-JUN-19
########## 01-JAN-17            01-MAR-19

9 rows selected.

Let's check the result of our query now.

SQL> SELECT
  2      table_name, COLUMN_NAME,
  3      to_number(xmlquery('/ROWSET/ROW/C/text()'
  4      passing xmltype(dbms_xmlgen.getxml(
  5        'select count(1) as c '
  6        || 'from "' || owner || '"."' || table_name || '" WHERE ' || COLUMN_NAME  || '=''100'''))
  7    returning content)) as cOUNT
  8  FROM ALL_TAB_COLS A
  9  WHERE A.COLUMN_NAME = 'CUST_ID';

TABLE_NAME           COLUMN_NAME               COUNT
-------------------- -------------------- ----------
ACCOUNT              CUST_ID                       1
ACTIVE_USERS         CUST_ID                       3

SQL>

Again, It worked !! :)

Cheers!!

Thanks! I prefer select * and it returns error ORA-06502 PL/SQL: numeric or value error
Select * can not work as every table has different number and type of columns. Instead you can use loop in pl/sql .

Collectives™ on Stack Overflow

Oracle - SQL to get data from all tables with specific column and value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related