title: How to run the same SQL query for different schemas in PL/SQL loop
body: <p>Suppose I have three schemas: <code>A</code>, <code>B</code> and <code>C</code>. According this Oracle documentation <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements009.htm" rel="nofollow">Syntax for Schema Objects and Parts in SQL Statements</a> the general syntax for referring to an object is:</p>
  
  <pre><code>schema.object
  </code></pre>
  
  <p>for example:</p>
  
  <pre><code>hr.departments
  </code></pre>
  
  <p>where <code>hr</code> is schema (owner) and <code>departments</code> is object (ie. table).</p>
  
  <p>I need to use different schema names in the SQL query inside PL/SQL block. In other words, <strong>I need run exactly the same SQL query, but for different schemas (owners).</strong></p>
  
  <p>I have following PL/SQL code so far:</p>
  
  <pre><code>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 ===&gt; ' || 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;
  /
  </code></pre>
  
  <p>In the 14th line I tried also different notation:</p>
  
  <pre><code>FOR t IN (SELECT * FROM "r.username".TABLE_NAME ORDER BY 2)
  </code></pre>
  
  <p>but in the both cases I got below error:</p>
  
  <pre><code>PL/SQL: ORA-00942: table or view does not exist
  </code></pre>
  
  <p>Thanks in advance for any help received.</p>