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