Having trouble creating procedure.

[olichap]

Hello all,

I'm a recent convert from SQL Server to Oracle 8.1 and am having some trouble creating a stored procedure. My procedure code will be below; hopefully someone can point out where I am going wrong. Before that though, I have a question about temp tables in Oracle.

In the query below I am creating a temp table into which I insert my required data. When the query runs my table is generated and everything is OK. I notice that the temp table is created in the database as if it is a standard table. In SQL Server the temp table is thrown out (supposedly) when the connection is closed. Does Oracle drop the table on its own or do I need to explicitly do so?

Here is the procedure:

create procedure sp_AllEvents as

begin
drop table tmp_events;

create global temporary table tmp_events as
select
p.patient_id,
p.pt_lname || ', ' || p.pt_fname as PatName,
p.pt_sex,
p.pt_dob,
pe.event_id,
pe.event_type,
pe.event_desc,
pe.event_status,
pe.event_start_date as StartDate,
pe.event_end_date as EndDate,
pe.provider_id,
pe.pcp_id,
pe.elig_id as RefToProv,
pe.facility_id
FROM pper_patient p, pper_event pe
where p.patient_id = pe.patient_id;

insert into tmp_events
select
p.patient_id,
p.pt_lname || ', ' || p.pt_fname as PatName,
p.pt_sex,
p.pt_dob,
pe.event_id,
pe.event_type,
pe.event_desc,
pe.event_status,
pe.event_start_date as StartDate,
pe.event_end_date as EndDate,
pe.provider_id,
pe.pcp_id,
pe.elig_id as RefToProv,
pe.facility_id
FROM pper_patient p, pper_event pe
where p.patient_id = pe.patient_id

union all

select
p.patient_id,
p.pt_lname || ', ' || p.pt_fname as PatName,
p.pt_sex,
p.pt_dob,
r.referral_id as event_id,
rd.ref_type as event_type,
r.referral_desc as event_desc,
rd.ref_status as event_status,
rd.valid_from as StartDate,
rd.valid_to as EndDate,
r.ref_from_prov_id as provider_id,
r.pcp_id,
r.ref_to_id as RefToProv,
rd.rule_id as facility_id
FROM pper_patient p, pper_referral r, pper_referral_detail rd
where p.patient_id = r.patient_id and r.referral_id = rd.referral_id
order by patname, event_id;

--drop table tmp_events;
end;

Thanks for any assistance,

Oliver

 

[rcurva]

you are executing a DDL inside a procedure, and that is not allowed. You can only do that when you use dynamic SQL.

For ORacle 8i;
execute immediate

For lower version;
DBMS_SQL Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[avictora]

This is not like MS SQL Server where the scope of the temporary table gets over once the stored procedure is executed. In Oracle the temporary table you create will become a permanent one and it has to be explicitly handled. Again you have to make sure whether this SP will be run concurrently in which case you might again face problems(like another user inserting rows at the same time or the table getting dropped by another user).

 

[Guest_imported]

Hai,
Just make sure whether you need a session based temporary table or transaction based. Based on this, the table will be dropped either at the end of the session or at the end of the transaction.
Thanks,
Double H

 

[rtefft]

An additional thought - if your temp table is going to have a lot of rows in in, try truncating it and then deleting it. Truncate empties the table immediately (regardless of its size) by resetting the data pointer. When you drop it, the table will be empty and there won't be long delays while the rows are cleared out. ____________________________
Rich Tefft
PL/SQL Programmer

 

[KODAKALLA]

Pl/sql works on early binding,means the objects refererences and the privileges will be checked at compiled time. Hence if your program has to compile without errors,the tables which you are creating in the pl/sql block should exists, but until unless it runs, the table doesnot exists.

Hence,as mentioned in the earlier post, you have to go for
native dynamic sql in 8i and dbms_sql package before 8i.

Cheers,
Giridhar