CREATE OR REPLACE
  PROCEDURE P1
    IS
      V_CUR SYS_REFCURSOR;
      V_DNAME VARCHAR2(10);
      V_ENAME VARCHAR2(10);
    BEGIN
        OPEN V_CUR
          FOR 'SELECT  D.DNAME,
                       E.ENAME
                 FROM  ORA$PTT_TEST_TBL E,
                       DEPT D
                 WHERE D.DEPTNO = E.DEPTNO';
        LOOP
          FETCH V_CUR
            INTO V_DNAME,
                 V_ENAME;
          EXIT WHEN V_CUR%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE(RPAD(V_ENAME,11) || V_DNAME);
        END LOOP;
        CLOSE V_CUR;
END;
/

Procedure created.

SQL> CREATE PRIVATE TEMPORARY TABLE ORA$PTT_TEST_TBL
  2    AS
  3      SELECT  *
  4        FROM  EMP
  5        WHERE JOB = 'CLERK'
  6  /

Table created.

SQL> SET SERVEROUTPUT ON
SQL> EXEC P1
MILLER     ACCOUNTING
SMITH      RESEARCH
ADAMS      RESEARCH
JAMES      SALES

PL/SQL procedure successfully completed.

SQL> DROP TABLE ORA$PTT_TEST_TBL PURGE
  2  /

Table dropped.

SQL> CREATE PRIVATE TEMPORARY TABLE ORA$PTT_TEST_TBL
  2    AS
  3      SELECT  *
  4        FROM  EMP
  5        WHERE JOB = 'SALESMAN'
  6  /

Table created.

SQL> EXEC P1
ALLEN      SALES
WARD       SALES
MARTIN     SALES
TURNER     SALES

PL/SQL procedure successfully completed.

SQL>

SQL> select  e.ename,
  2          e.job,
  3          e.sal
  4    from  emp e,
  5          table(sys.OdciNumberList(7654,7788,7900)) c
  6    where e.empno = c.column_value
  7  /

ENAME      JOB              SAL
---------- --------- ----------
MARTIN     SALESMAN        1250
SCOTT      ANALYST         3000
JAMES      CLERK            950

SQL>

SQL> select  e.ename,
  2          e.job,
  3          e.sal
  4    from  emp e,
  5          sys.OdciNumberList(7654,7788,7900) c
  6    where e.empno = c.column_value
  7  /

ENAME      JOB              SAL
---------- --------- ----------
MARTIN     SALESMAN        1250
SCOTT      ANALYST         3000
JAMES      CLERK            950

SQL>

WITH запрос1 AS (...),
     запрос2 AS (...)
...
     запросN AS (...)
SELECT ...
  FROM  запрос1,...запросN
  WHERE ...