How to SQL Query Large List of Tables using variable for table names?

Question

I have a question about running a Oracle DB query on multiple tables. Is there a way to make the table names variables to be iterated as opposed to having to state each table name?

Background Example

There are a large number of tables (ex. TABLE_1...TABLE_100).
Each of these tables are listed in the NAME column of another table (ex. TABLE_LIST) listing an even larger number of tables along with TYPE (ex. "Account")
Each of these tables has columnn VALUE a boolean column, ACTIVE.

Requirements

Query the TABLE_LIST by TYPE = 'Account'
For Each table found, query that table for all records where column ACTIVE = 'N'
Results show table NAME and VALUE from each table row where ACTIVE = 'N'.

Any tips would be appreciated.

Yes, you can put your table names into a temp table and define a cursor over it to loop through. You'll also have a string of dynamic SQL which is parametrized to plug in the current table name. — Error_2646
– Error_2646, Commented Jul 5, 2018 at 20:01
Stackoverflow is not a code writing service. Show us the code that you wrote, whether you are facing any issues or errors? Also, 'N' is not a Boolean value, it is a character. Do also show some sample output for your example to avoid confusion. — Kaushik Nayak
– Kaushik Nayak, Commented Jul 6, 2018 at 2:46

wolφi · Accepted Answer · 2018-07-06 18:04:14Z

1

There is a low tech and a high tech way. I'll put them in separate answers so that people can vote for them. This is the high tech version.

Set up: Same as in low tech version.

CREATE TYPE my_row AS OBJECT (name VARCHAR2(128), value NUMBER)
/
CREATE TYPE my_tab AS TABLE OF my_row
/

CREATE OR REPLACE FUNCTION my_fun RETURN my_tab PIPELINED IS
  rec my_row := my_row(null, null);
  cur SYS_REFCURSOR;
BEGIN
  FOR t IN (SELECT name FROM table_list WHERE table_type='Account') LOOP
    rec.name  := dbms_assert.sql_object_name(t.name);
    OPEN cur FOR 'SELECT value FROM '||t.name||' WHERE active=''N''';
    LOOP
      FETCH cur INTO rec.value;
      EXIT WHEN cur%NOTFOUND;
      PIPE ROW(rec);      
    END LOOP;
    CLOSE cur;
  END LOOP;
END my_fun;
/

SELECT * FROM TABLE(my_fun);

NAME     VALUE
TABLE_1      1
TABLE_3      3

edited Jul 6, 2018 at 18:04

answered Jul 6, 2018 at 17:20

wolφi

8,3914 gold badges41 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lukasz Szozda Over a year ago

I would add checking for table name with dbms_assert.sql_object_name(t.name) to mitigate risk of SQL Injection. Just in case :)

wolφi · Accepted Answer · 2018-07-06 17:20:56Z

There is a low tech and a high tech way. I'll put them in separate answers so that people can vote for them. This is the low tech version.

Set up:

CREATE TABLE table_1 (value NUMBER, active VARCHAR2(1) CHECK(active IN ('Y','N')));
CREATE TABLE table_2 (value NUMBER, active VARCHAR2(1) CHECK(active IN ('Y','N')));
CREATE TABLE table_3 (value NUMBER, active VARCHAR2(1) CHECK(active IN ('Y','N')));
INSERT INTO table_1 VALUES (1, 'N');
INSERT INTO table_1 VALUES (2, 'Y');
INSERT INTO table_3 VALUES (3, 'N');
INSERT INTO table_3 VALUES (4, 'Y');

CREATE TABLE table_list (name VARCHAR2(128 BYTE) NOT NULL, table_type VARCHAR2(10)); 
INSERT INTO  table_list (name, table_type) VALUES ('TABLE_1', 'Account');
INSERT INTO  table_list (name, table_type) VALUES ('TABLE_2', 'Something');
INSERT INTO  table_list (name, table_type) VALUES ('TABLE_3', 'Account');

The quick and easy way is to use a query to generate another query. I do that quite often, especially for one off jobs:

SELECT 'SELECT '''||name||''' as name, value FROM '||name||
       ' WHERE active=''N'' UNION ALL' as sql  
  FROM table_list 
 WHERE table_type='Account';

SELECT 'TABLE_1' as name, value FROM TABLE_1 WHERE active='N' UNION ALL
SELECT 'TABLE_3' as name, value FROM TABLE_3 WHERE active='N' UNION ALL

You'll have to remove the last UNION ALL and execute the rest of the query. The result is

NAME     VALUE
TABLE_1      1
TABLE_3      3

Collectives™ on Stack Overflow

How to SQL Query Large List of Tables using variable for table names?

Background Example

Requirements

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Background Example

Requirements

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related