CODEWITHSUNDEEP
oracle-databaseplsql
Szymon
Szymon

Reputation: 633

How to run the same SQL query for different schemas in PL/SQL loop

Suppose I have three schemas: A, B and C. According this Oracle documentation Syntax for Schema Objects and Parts in SQL Statements the general syntax for referring to an object is:

schema.object

for example:

hr.departments

where hr is schema (owner) and departments is object (ie. table).

I need to use different schema names in the SQL query inside PL/SQL block. In other words, I need run exactly the same SQL query, but for different schemas (owners).

I have following PL/SQL code so far:

set serveroutput on size unlimited
set head off
set feedback off
set echo off

DECLARE
    i_schema VARCHAR2(100);

BEGIN
  FOR r IN (SELECT username FROM dba_users where username in ('A', 'B', 'C') order by 1)
  LOOP
    i_schema := r.owner || '.' || 'TABLE_NAME';
    dbms_output.put_line(chr(10) || 'SCHEMA ===> ' || i_schema );
    FOR t IN (SELECT * FROM i_schema order by 2)
    LOOP
        dbms_output.put_line(RPAD(t.COLUMN1,75) || '|' || t.COLUMN2);
    END LOOP;
   END LOOP;
END;
/

In the 14th line I tried also different notation:

FOR t IN (SELECT * FROM "r.username".TABLE_NAME ORDER BY 2)

but in the both cases I got below error:

PL/SQL: ORA-00942: table or view does not exist

Thanks in advance for any help received.

Upvotes: 2

Views: 2477

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49122

FOR r IN (SELECT username FROM dba_users where username in ('A', 'B', 'C') order by 1)
  LOOP
    i_schema := r.owner || '.' || 'TABLE_NAME';
    dbms_output.put_line(chr(10) || 'SCHEMA ===> ' || i_schema );
    FOR t IN (SELECT * FROM i_schema order by 2)
    LOOP
        dbms_output.put_line(RPAD(t.COLUMN1,75) || '|' || t.COLUMN2);
    END LOOP;
   END LOOP;

You cannot do that since SQL doesn't allow dynamic table_name, it must be static. In order to achieve the task, you need to (ab)use EXECUTE IMMEDIATE in your PL/SQL block.

Declare a string type variable, prepare the dynamic query and assign it to the string variable.

However, looking at what you are further doing, I don't see any reason to (ab)use EXECUTE IMMEDIATE. Simple use a REFCURSOR.

But I see all this is pointless as you are anyway hardcoding the schema name, so what's the point in writing the PL/SQL code when you could do it in simply 3 SQL queries in pure SQL. It would hardly take less than a minute using a good text editor to replace the schema name in 3 SQLs.

Use UNION ALL:

SELECT RPAD(t.COLUMN1,75) || '|' || t.COLUMN2 FROM A.tablle_name t
UNION ALL
SELECT RPAD(t.COLUMN1,75) || '|' || t.COLUMN2 FROM B.tablle_name t
UNION ALL
SELECT RPAD(t.COLUMN1,75) || '|' || t.COLUMN2 FROM C.tablle_name t

Upvotes: 1

Related Questions