How should I temporarily store data within a PL/SQL procedure?

Question

I am very new to PL/SQL. I have data in an initial table, named 'FLEX_PANEL_INSPECTIONS' that I am attempting to summarise in a second table, named 'PANEL_STATUS_2' using a PL/SQL procedure. However, due to the nature of the data, I have had to write a case statement in order to correctly summarise the data from FLEX_PANEL_INSPECTIONS. I have therefore created a third, intermediate table to bridge the two (named 'PANEL_STATUS_1') since the case statement will not allow columns in the group by clause which specifically order the data (to the extent of my knowledge - I get an error when I try and do this). I do not want to be storing data in the intermediate table - is there any way that I can either make it temporary (i.e. exist only while the procedure runs so that data from 'PANEL_STATUS_1' is not retained); create a view within the procedure, or remove the need for the intermediate table altogether?

Any help or criticism of my mistakes / misunderstanding of PL/SQL would be greatly appreciated. Here is the code I have written:

create or replace procedure PANEL_STATUS_PROCEDURE (panel_lot_id in number) as

begin

--Populate intermediate table with information about the status of the panels.
insert into PANEL_STATUS_1 (FLEX_LOT_ID, FLEX_PANEL_DMX, FLEX_PANEL_STATUS)   
select FLEX_LOT_ID, FLEX_PANEL_DMX,

--Sum the status values of the 4 panel inspections. A panel passes if and only if this sum = 4. 
case sum (FLEX_PANEL_STATUS)
    when 4 then 1
    else 0

end as new_panel_status

from FLEX_PANEL_INSPECTIONS
where FLEX_LOT_ID = panel_lot_id
group by FLEX_LOT_ID, FLEX_PANEL_DMX;

--Add information about the machine ID and the upload time to this table.
insert into PANEL_STATUS_2 (FLEX_LOT_ID, FLEX_PANEL_DMX, FLEX_PANEL_STATUS, MACHINE_ID, UPLOAD_TIME)
select distinct PANEL_STATUS_1.*, MACHINE_ID, UPLOAD_TIME
from PANEL_STATUS_1, FLEX_PANEL_INSPECTIONS

where (FLEX_PANEL_INSPECTIONS.FLEX_LOT_ID = PANEL_STATUS_1.FLEX_LOT_ID
       and FLEX_PANEL_INSPECTIONS.FLEX_PANEL_DMX = PANEL_STATUS_1.FLEX_PANEL_DMX)

and FLEX_PANEL_INSPECTIONS.FLEX_LOT_ID = panel_lot_id;

end PANEL_STATUS_PROCEDURE;
/

Answer 1

You can create your temp table as

create global temporary table gtt_panel_status
( column datatype ... )
on commit [delete|preserve] rows;

(specifying either delete or preserve in the on commit clause).

However you usually don't need a temp table. You might try a with clause (CTE), or else an inline view along lines of select x, y, z from (select your subquery here).

Edit: actually looking at your query some more, I think what you a actually need is an analytic sum, i.e. a total without aggregating. For example, something like this:

create or replace procedure panel_status_procedure
    ( panel_lot_id in number )
as
begin
    -- Add information about the machine ID and the upload time to this table.
    insert into panel_status_2
         ( flex_lot_id
         , flex_panel_dmx
         , flex_panel_status
         , machine_id
         , upload_time )
    select distinct
           flex_lot_id
         , flex_panel_dmx
         , case sum(flex_panel_status) over (partition by flex_lot_id, flex_panel_dmx)
               when 4 then 1
               else 0
           end
         , machine_id
         , upload_time
    from   flex_panel_inspections pi
    where  pi.flex_lot_id = panel_lot_id;

end panel_status_procedure;