Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Rod McLure
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic