thank you for your response
but i am still don't have answer to my problem still my code doesn't slution to be run on oracle by other words
can convert this code to be run inside stored procedure

Of course, it can be done, but may qualify as "worst practice":

SQL> ed
Wrote file c:/users/DGANOTE/sqlplusedit.sql

  1  DECLARE x integer;
  2  BEGIN
  3    SELECT count(*) INTO x
  4      FROM user_tables;
  5    DBMS_OUTPUT.PUT_LINE('start: # tables = '||X);
  6    FOR rec IN ( SELECT table_name FROM user_tables
  7                  WHERE table_name like '#TT%'
  8                  ORDER BY 1 )  LOOP
  9     EXECUTE IMMEDIATE 'drop table "'||rec.table_name||'"';
 10    END LOOP;
 11    EXECUTE IMMEDIATE 'create table "#TT1" AS SELECT * FROM user_objects';
 12    EXECUTE IMMEDIATE 'create table "#TT2" AS SELECT view_name FROM user_views';
 13    EXECUTE IMMEDIATE 'create table "#TT3" AS SELECT * FROM user_tables';
 14    EXECUTE IMMEDIATE 'create table "#TT4" AS SELECT object_name FROM "#TT1"'
 15      ||' JOIN "#TT2" ON object_name = view_name';
 16    DBMS_OUTPUT.PUT_LINE('almost: # views = '||SQL%ROWCOUNT);
 17    EXECUTE IMMEDIATE 'create table "#TT5" AS SELECT object_name FROM "#TT1"'
 18      ||' JOIN "#TT3" ON object_name = table_name';
 19    DBMS_OUTPUT.PUT_LINE('finish: # tables = '||SQL%ROWCOUNT);
 20* END;
SQL> /
start: # tables = 85
almost: # views = 19
finish: # tables = 81

PL/SQL procedure successfully completed.

I don't generally use temp tables. I retain my 'work tables' -- just in case there are later questions. For example, during the day after the users have started to use the results, they may wonder about the results.

If it's important enough to stick in a table, it's important enough it keep around.

many thanks for your replay
now i write code to do it in stored procedure and i drop all TEMP table before procedure end to can run it again
but when i drop table the select statement which contain output say table does not exist although i making select statement before drop statement as below

create or replace PROCEDURE IU_DOWN ( IUD OUT sys_refcursor ) IS

stmt1 VARCHAR2(2000);

stmt2 VARCHAR2(2000);

stmt3 VARCHAR2(2000);

stmt4 VARCHAR2(2000);

stmt5 VARCHAR2(2000);

stmt6 VARCHAR2(2000);

stmt7 VARCHAR2(2000);

BEGIN



stmt1 :='create GLOBAL TEMPORARY table TT1 on COMMIT PRESERVE ROWS AS

select DATETIME "TIME",extract(day from DATETIME)"DAYID",TO_CHAR(DATETIME,''HH24'')"HOURID",RNC"RNC",NODE_B"NENAME",AVA"AVA",

case when instr(upper(NODE_B),''('',1)>0 then substr(upper(NODE_B),1,instr(upper(NODE_B),''('',1))

else substr(upper(NODE_B),1,length(NODE_B)) end "TEST"

from NODE_B_AVA

where AVA=0

and extract(day from DATETIME) >= extract(day from sysdate -2)';

stmt2 :='create GLOBAL TEMPORARY table TT2 on COMMIT PRESERVE ROWS AS

select TIME,DAYID,HOURID,RNC,TEST,

case when instr(upper(TEST),''CAI'',1)>0 then substr(upper(TEST),instr(upper(TEST),''CAI'',1),7)

when instr(upper(TEST),''UPP'',1)>0 then substr(upper(TEST),instr(upper(TEST),''UPP'',1),7)

when instr(upper(TEST),''DEL'',1)>0 then substr(upper(TEST),instr(upper(TEST),''DEL'',1),7)

when instr(upper(TEST),''SIN'',1)>0 then substr(upper(TEST),instr(upper(TEST),''SIN'',1),7)

when instr(upper(TEST),''ALX'',1)>0 then substr(upper(TEST),instr(upper(TEST),''ALX'',1),7)

else ''Other'' end Site_ID

from TT1';

stmt3 :='create GLOBAL TEMPORARY table TT3 on COMMIT PRESERVE ROWS AS

select DATETIME "TIME",extract(day from DATETIME)"DAYID",TO_CHAR(DATETIME,''HH24'')"HOURID",NENAME"NENAME",NODEB"NODEB",IU_UL"UL",IU_DL"DL",

case when instr(upper(NENAME),''('',1)>0 then substr(upper(NENAME),1,instr(upper(NENAME),''('',1))

else substr(upper(NENAME),1,length(NENAME)) end "TEST"

from IUB_DROP

where IU_DL>5000

and extract(day from DATETIME) >= extract(day from sysdate -2)';

stmt4 :='create GLOBAL TEMPORARY table TT4 on COMMIT PRESERVE ROWS AS

select Time,DAYID,HOURID,NENAME,UL,DL,

case when instr(upper(TEST),''CAI'',1)>0 then substr(upper(TEST),instr(upper(TEST),''CAI'',1),7)

when instr(upper(TEST),''UPP'',1)>0 then substr(upper(TEST),instr(upper(TEST),''UPP'',1),7)

when instr(upper(TEST),''DEL'',1)>0 then substr(upper(TEST),instr(upper(TEST),''DEL'',1),7)

when instr(upper(TEST),''SIN'',1)>0 then substr(upper(TEST),instr(upper(TEST),''SIN'',1),7)

when instr(upper(TEST),''ALX'',1)>0 then substr(upper(TEST),instr(upper(TEST),''ALX'',1),7)

else ''Other'' end Site_ID

from TT3';

stmt5 :='create GLOBAL TEMPORARY table TT5 on COMMIT PRESERVE ROWS AS

select a.TIME,a.DAYID,a.HOURID,a.Site_ID,b.RNC

from TT4 a , TT2 b

where a.TIME=b.TIME

and a.Site_ID=b.Site_ID';

stmt6 :='create GLOBAL TEMPORARY table TT6 on COMMIT PRESERVE ROWS AS

select SITE_ID,RNC,DAYID from TT5 group by SITE_ID,RNC,DAYID having count(HOURID)>3';

stmt7 :='OPEN IUD for select SITE_ID,RNC from TT6 GROUP BY SITE_ID,RNC having COUNT(*)>=1';

EXECUTE IMMEDIATE stmt1;

EXECUTE IMMEDIATE stmt2;

EXECUTE IMMEDIATE stmt3;

EXECUTE IMMEDIATE stmt4;

EXECUTE IMMEDIATE stmt5;

EXECUTE IMMEDIATE stmt6;

--EXECUTE IMMEDIATE stmt7;

OPEN IUD for select SITE_ID,RNC from TT6 GROUP BY SITE_ID,RNC having COUNT(*)>=1;

EXECUTE IMMEDIATE 'truncate table TT1';

EXECUTE IMMEDIATE 'drop table TT1';

EXECUTE IMMEDIATE 'truncate table TT2';

EXECUTE IMMEDIATE 'drop table TT2';

EXECUTE IMMEDIATE 'truncate table TT3';

EXECUTE IMMEDIATE 'drop table TT3';

EXECUTE IMMEDIATE 'truncate table TT4';

EXECUTE IMMEDIATE 'drop table TT4';

EXECUTE IMMEDIATE 'truncate table TT5';

EXECUTE IMMEDIATE 'drop table TT5';

EXECUTE IMMEDIATE 'truncate table TT6';

EXECUTE IMMEDIATE 'drop table TT6';

END IU_DOWN;

*******again thanks for replay ************