SQL> create or replace procedure p (p_cur in out sys_refcursor)
  2  is
  3  begin
  4    open p_cur for select * from emp where rownum<=3;
  5  end;
  6  /

Procedure created.

SQL> var x refcursor;
SQL> exec p(:x)

PL/SQL procedure successfully completed.

SQL> print x
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17/12/1980 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 20/02/1981 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 22/02/1981 00:00:00       1250        500         30

3 rows selected.

CREATE GLOBAL TEMPORARY TABLE my_temp_table2 (name1 VARCHAR(255), name2 VARCHAR(255));

CREATE OR REPLACE procedure p2 (p_cur in out sys_refcursor)
is
begin

INSERT INTO my_temp_table2
select Name1, Name2 from EXEMPLOYEE where rownum<=3;

open p_cur for select * from my_temp_table2;
end;
/

WITH my_temp_table1 AS (SELECT 10 c1,20 c2 FROM dual), 
     my_temp_table2 AS (SELECT 10 c3,30 c4 FROM dual) 
SELECT * 
  FROM my_temp_table1
  join my_temp_table2 ON c3=c1

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Aug 9 17:48:23 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE GLOBAL TEMPORARY TABLE my_temp_table2 (name VARCHAR(255));

Table created.

SQL>
SQL> CREATE OR REPLACE procedure p2 (p_cur in out sys_refcursor)
  2  is
  3  begin
  4
  5  INSERT INTO my_temp_table2
  6  select 'foo' FROM dual;
  7
  8  open p_cur for select * from my_temp_table2;
  9  end;
 10  /

Procedure created.

SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Aug 9 17:48:49 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> VARIABLE resultSet REFCURSOR
SQL> EXEC p2 (:resultSet);

PL/SQL procedure successfully completed.

SQL> print resultset

NAME
--------------------------------------------------------------------------------

foo

SQL>