aspose file tools*
The moose likes Oracle/OAS and the fly likes Stored procedure to write to temporary table, return result set from table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Stored procedure to write to temporary table, return result set from table" Watch "Stored procedure to write to temporary table, return result set from table" New topic
Author

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

Rod McLure
Greenhorn

Joined: Mar 04, 2010
Posts: 24
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

Joined: Aug 22, 2010
Posts: 3610
    
  60

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

Joined: Mar 04, 2010
Posts: 24
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

Joined: Aug 22, 2010
Posts: 3610
    
  60

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Stored procedure to write to temporary table, return result set from table