Hi
I have created a GTT with CT AS syntax .
I have used it in what ever way i want .
Then when i want to drop it in the same session.I ma unable to drop the GTT.
The GTT is created with preserve rows (session based.)
whast the reason for this?
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
ORA-06512: at line 36
/* Formatted on 2/7/2014 7:11:19 PM (QP5 v5.119.811.4010) */
DECLARE
lRetVal CLOB;
lHTMLOutput XMLTYPE;
lXSL CLOB;
lXMLData XMLTYPE;
lContext DBMS_XMLGEN.CTXHANDLE;
v_string VARCHAR2 (32767);
v_num NUMBER;
v_query_in VARCHAR2 (32767) := 'select 1 as count from dual';
v_count_query_part VARCHAR2 (32767);
v_table_created VARCHAR2 (32767);
v_count NUMBER;
rf sys_refcursor;
v_cur_open_query VARCHAR2 (32767);
v_drop_string VARCHAR2 (32767);
v_opn_cur VARCHAR2 (32767);
BEGIN
SELECT global_table_seq.NEXTVAL INTO v_num FROM DUAL;
v_table_created := 'global_' || v_num;
v_drop_string := 'drop table ' || v_table_created;
v_string :=
'create global temporary table global_'
|| v_num
|| ' on commit preserve rows as '
|| v_query_in;
DBMS_OUTPUT.put_line (v_table_created);
DBMS_OUTPUT.put_line (v_drop_string);
DBMS_OUTPUT.put_line (v_string);
EXECUTE IMMEDIATE v_string;
EXECUTE IMMEDIATE v_drop_string;
END;
I have a situation where a GTT has to be created with the on the fly query using CTAS syntax .
I use this GTT in like to select data , count etc .After that i want to drop the gtt as i dont require it anymore.
NOTE: I dont want to do it by using cursor because i use this tabe for mutiple select and return a sysrefcursor at the end .
please advise why the owner who created the GTT is not able to drop the GTT .?