CODEWITHSUNDEEP
oracle-databasefunctionplsql
Nick
Nick

Reputation: 533

PL/SQL iterate all schemas and run the same query on all of them

I have various Oracle databases, eg ONE.db TWO.db THIRD.db etc and I want to run a SELECT which runs successfully on ONE.db, for example, SELECT * FROM CUSTOMERS etc. and then get the results per database. How can I do this using PL/SQL? (no db links or tools etc.)

Upvotes: 0

Views: 3984

Answers (1)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

Assuming you're talking about schemas, not separate database instances, and assuming the structure of all the CUSTOMERS tables are exactly identical (including column order), you could log in as a user that has SELECT access to all of the tables, then:

SELECT 'ONE' owner, c.* FROM one.customers c
UNION ALL
SELECT 'TWO' owner, c.* FROM two.customers c
UNION ALL
SELECT 'THIRD' owner, c.* FROM third.customers c
;

Whether you need the first column "owner" or not is up to you.

If you want to generate the above statement at runtime with PL/SQL, you can do something like this:

DECLARE
  qry VARCHAR2(32767);
  rc sys_refcursor;
BEGIN
  FOR r IN (SELECT owner, table_name
            FROM all_tables
            WHERE table_name = 'CUSTOMERS') LOOP
    IF qry IS NOT NULL THEN
      qry := qry || ' UNION ALL ';
    END IF;
    qry := qry || 'SELECT '''
               || r.owner
               || ''' owner, c.* FROM '
               || r.owner
               || '.customers c';
  END LOOP;
  OPEN rc FOR qry;
  -- fetch from the ref cursor and do whatever you want with the records
END;

Upvotes: 3

Related Questions