Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

REF CURSOR ON GLOBAL TEMPORARY TABLE

user10594152Feb 13 2009 — edited Feb 13 2009
Dear friends,
This is kind of a newbie question on GTT..

I have created a Global Temporary table with ON Commit DELETE Rows.
This is my sample procedure..
CREATE OR REPLACE PROCEDURE procheck (mycur OUT sys_refcursor)
AUTHID CURRENT_USER
AS
   l_tname   VARCHAR2 (30) DEFAULT 'temp_table_' || USERENV ('sessionid');
BEGIN
   EXECUTE IMMEDIATE    'create global temporary table '
                     || l_tname
                     || ' on COMMIT DELETE rows as select * from emp where 1=0 ';

   EXECUTE IMMEDIATE 'insert into ' || l_tname || ' select * from emp';

   OPEN mycur FOR 'select * from ' || l_tname || ' order by ename';

   EXECUTE IMMEDIATE 'drop table ' || l_tname;
END;
/
When I am calling the above procedure from SQL*Plus,
I am getting the following error:
SQL> EXEC procheck (:V);

PL/SQL procedure successfully completed.

SQL> print v;
ERROR:
ORA-01410: invalid ROWID



no rows selected
This post has been answered by BluShadow on Feb 13 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 13 2009
Added on Feb 13 2009
4 comments
1,114 views