Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to create temporary tables in Stored Procedures.

840557Feb 17 2011 — edited Feb 18 2011
Hi

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.
This post has been answered by earth on Feb 18 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 18 2011
Added on Feb 17 2011
16 comments
36,960 views