Stored procedure to write to temporary table, return result set from table

[Rod McLure]

Hi,
I am trying to write my first stored procedure something like
Loop through the results returned from a cursor
as I read each row from the cursor I need to manipulate the data and store the result in a temporary table
then when I have finished with all the data in the cursor I will return the data from the temporary table as a result set
using a Ref cursor
I cannot find an example on the internet for the syntax
of creating a temp table inside a stored procedure

any help woukd be appreciated
Thanks,
Rod

[Martin Vashko]

In Oracle, you don't create temporary tables "on the fly". Oracle supports global temporary tables only. This table would be permanently present in your schema, like any other "normal" table. Each session then sees its own contents of a temporary table. Furthermore, there are two kinds of this table, one preserves rows across commits, the other drops all rows when you commit; so make sure you don't have an autocommit on in the second case. The details are here.

However, Oracle can usually support pretty complex SQL queries, sometimes much more complex than other databases. If you manage to do all of the manipulations in pure-SQL, and formulate the query right, it will probably be faster than any stored procedure. Oracle can actually create a hidden temporary table on its own if it feels like it, but it will likely still be faster than a stored procedure.

So, investigate the pure-SQL approach first. You might find the subquery factoring (WITH) clause useful.

[Rod McLure]

Thank you!, I think I get it now.
I will see how far I can get with just SQL, I am trying to replicate the logic in a legacy report(Cobol), so I am thinking I will need the looping, if statements etc that a stored procedure would allow me.
Rod

[Martin Vashko]

In SQL, you essentially manipulate data in sets. This is different from the procedural languages and the transition between them is not easy; comparable to transition from procedural to functional languages perhaps.

Loops are sometimes used when you need to do operations over neighboring rows. In SQL, Analytic functions can help in this a lot. They are incredibly powerful.