How to create temporary tables in Stored Procedures.
840557Feb 17 2011 — edited Feb 18 2011Hi
I am new to oracle, I have a requirement where I need to execute a query in a loop for different values in where condition. Here I need to save the results of the query of each iteration. After completion of loop I need to pass the results to front-end. I have done a lot of research for temporary table concept in oracle, but I ended up with no solution except headache. Every one is showing how to create temporary tables in general but not in stored procedure.
I badly need the concept of temporary tables, or is there any alternate way to store the temporary results. my procedure will look like this.
create or replace
procedure uspMatchCode(parWord varchar2, p_recorderSet out types.cursor_type)
as
parCnt smallint;
begin
parcnt := 0;
select count(1) into parCnt from ...............;
if parcnt > 0 then
Open p_recorderSet For
select field1, field2, field3, ........ from table1, table2 , table2 where <<conditions>>;
else
---- here I want to create a temporary table and store the result form for loop into temp table.
CREATE GLOBAL TEMPORARY TABLE my_temp_table (column1 NUMBER, column2 NUMBER) ON COMMIT DELETE ROWS;
FOR parCnt in 0..3
loop
insert into my_temp_table select field1, field2, field3, ........ from table1, table2 , table2 where <<conditions>>;
end loop;
Open p_recorderSet For
Select * from <<temp table>>;
end if;
end;
Any help would be great to save me out of the problem.
Thanks
Kiran.