How to create a stored prog with temp tables

[neskin]

Hi there,
I am using Toad for oracle 9i
I am new to oracle.How can I create a stored prog with temp tables.I start with simply one and getting errors.I had created in sql server so now i have to use oracle
Here is my code

CREATE OR REPLACE PROCEDURE Spcryrpt_TEST
(p_recordset OUT sys_refcursor,
--p_airportid IN VARCHAR2,
p_startdate IN DATE,
p_enddate IN DATE)

is

begin
--SET ONCOUNT ON it is sql
--creating temporary table
create table TMP_EVENTS_MON
(
gatename VARCHAR2(15) null,
runwayname VARCHAR2(32) null,
adflag CHAR(1) null,
dir INTEGER null ,
actualtime DATE null,
date_part VARCHAR2(15) null

);


--populating temporary table
INSERT INTO TMP_EVENTS_MON(gatename,runwayname,adflag,dir,actualtime,date_part)
SELECT select gatename,runwayname,adflag,dir,actualtime,
(case when (to_number(to_char(actualtime,'hh24mi'))<=2200 and to_number(to_char(actualtime,'hh24mi'))>=600) then 'day' else
'night'end) date_part
from oper o,ganout g
where adflag in ('A','D')and
( trunc(o.actualtime) between trunc(p_startdate) and trunc(p_enddate)) and
--aircraftcategory in ('J','B','R') and
--airportid = 'EGLL' and
o.opnum = g.opnum


OPEN p_recordset FOR

select gatename,runwayname,adflag,dir,actualtime,date_part
from TMP_EVENTS_MON;


--prompt drop temporary table if it exists
drop table TMP_EVENTS_MON

--SET NOCOUNT OFF
END;

/

thanks
Nat

 

[SantaMufasa]

Nat, try your code again, but place any DDL commands ("CREATE...", "ALTER...", and "DROP...") inside an "execute immediate" command:

...
execute immediate
   'create table TMP_EVENTS_MON
   (gatename VARCHAR2(15)   null,
    runwayname VARCHAR2(32) null,
    adflag CHAR(1)          null,
    dir INTEGER             null,
    actualtime DATE         null,
    date_part VARCHAR2(15)  null)';
...
execute immediate 'drop table TMP_EVENTS_MON';
...
end;
/

Let us know how this works for you. If you still encounter errors, please post a copy-and-paste of both your code attempt and the error message(s).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[neskin]

Hi Mufasa,
I have added execute and the error is PL/SQL: ORA-00942: table or view does not exist


CREATE OR REPLACE PROCEDURE Spcryrpt_TEST
(p_recordset OUT sys_refcursor,
--p_airportid IN VARCHAR2,
p_startdate IN DATE,
p_enddate IN DATE)

is

begin
--SET ONCOUNT ON it is sql
--creating temporary table
execute immediate 'create table TMP_EVENTS_MON
(
gatename VARCHAR2(15) null,
runwayname VARCHAR2(32) null,
adflag CHAR(1) null,
dir INTEGER null ,
actualtime DATE null,
date_part VARCHAR2(15) null

)';


--populating temporary table
INSERT INTO TMP_EVENTS_MON(gatename,runwayname,adflag,dir,actualtime,date_part)
SELECT gatename,runwayname,adflag,dir,actualtime,
(case when (to_number(to_char(actualtime,'hh24mi'))<=2200 and to_number(to_char(actualtime,'hh24mi'))>=600) then 'day' else
'night'end) date_part
from oper o,ganout g
where adflag in ('A','D')and
( trunc(o.actualtime) between trunc(p_startdate) and trunc(p_enddate)) and
--aircraftcategory in ('J','B','R') and
--airportid = 'EGLL' and
o.opnum = g.opnum ;


OPEN p_recordset FOR

select gatename,runwayname,adflag,dir,actualtime,date_part
from TMP_EVENTS_MON;


--prompt drop temporary table if it exists
execute immediate 'drop table TMP_EVENTS_MON';

--SET NOCOUNT OFF
END;

/

regards nat