CODEWITHSUNDEEP
sqloracle-databaseplsql
user1698161
user1698161

Reputation: 42

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

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

  1. There are a large number of tables (ex. TABLE_1...TABLE_100).
  2. 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")
  3. Each of these tables has columnn VALUE a boolean column, ACTIVE.

Requirements

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

Any tips would be appreciated.

Upvotes: 0

Views: 371

Answers (2)

wolφi
wolφi

Reputation: 8361

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

Upvotes: 1

wolφi
wolφi

Reputation: 8361

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

Upvotes: 0

Related Questions