Home » SQL & PL/SQL » SQL & PL/SQL » Oracle-Should I use temp table or ref cursor (Oracle 11g)
Oracle-Should I use temp table or ref cursor [message #626966] Wed, 05 November 2014 17:21 Go to next message
sormita
Messages: 1
Registered: November 2014
Junior Member
I have the following scenario for which i will have to write a stored procedure:

Header table containing invoice_ID and invoice_line_ID
Address Line table containing invoice_line_id and 'Ship_From' and 'Ship_To' corresponding to each invoice_line_ID in header table. 3.Address header table containing invoice_ID and 'Ship_From' and 'Ship_To' corresponding to each invoice_id in header table.
The cases are such that not always all 'Ship_From' and 'Ship_To' information will be present in the Address Line table. In that case the information needs to be selected from Address Header table.

So i will write a case structure and two joins : 1. That will join Header table and Address Line table 2. That will join Header table and Address Header table. with the condition to do the second join in case entire information for a particular invoice_line_id is not available in line table.

My question here is where should i store the information? I will use a cursor to perform the above case structure. But should i use a ref cursor or a temp table in this case?

Please note that my customer is not liking the idea of extra database objects in the database so i might have to delete the temp table after i am done displaying. I need help on that as well as to is there any alternative to temp table or whether ref cursor take up extra space on the database or not.

Report message to a moderator

Re: Oracle-Should I use temp table or ref cursor [message #626967 is a reply to message #626966] Wed, 05 November 2014 17:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Re: Oracle-Should I use temp table or ref cursor [message #626970 is a reply to message #626966] Thu, 06 November 2014 00:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Generally speaking, the best wayy to return a result set to a client program is to use a (ref) cursor.
A cursor does not take any space neither in the database nor in the memory (but some few bytes for this later).

Report message to a moderator

Re: Oracle-Should I use temp table or ref cursor [message #626977 is a reply to message #626966] Thu, 06 November 2014 01:24 Go to previous messageGo to next message
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
Senior Member
I think your relational analysis may be faulty. What are the primary keys and foreign keys in each table?

Report message to a moderator

Re: Oracle-Should I use temp table or ref cursor [message #627302 is a reply to message #626977] Mon, 10 November 2014 14:39 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Oracle (most of the time) doesn't need temp tables. The query you are using to fill the temp table simply put in the from clause of the select of the form

select a.col1
from
(select col1,col2
from mytable) a,
mytable2 b
where a.col2 = b.col3;

Report message to a moderator

Re: Oracle-Should I use temp table or ref cursor [message #627323 is a reply to message #627302] Mon, 10 November 2014 17:12 Go to previous message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
Quote:
My question here is where should i store the information?

What are you going to do with the information after you have it? Can you show us an example of your procedure code?

If the gathered data is the basis for other processing within your procedure why not just act on each record in your Cursor (eg; Cursor For LOOP). If it is a LOT of data, then reading it sequentially from the database one row at a time would be pretty slow. I would suggest using a Collection and Bulk Collecting groups of records into the Collection and then loop through those records in the collection. Notice that I say "groups of records" not "all records". This means you need to use the LIMIT clause on your BULK COLLECT so you can keep the record set in your collection manageable without eating up all of the available memory on the database server. If you have never worked with collections, take a look at these articles by Steven Feuerstein:
http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html
http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html

Craig...

Report message to a moderator

Previous Topic: log_table
Next Topic: Buffer size error from External table
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 23 04:58:04 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.