Home » SQL & PL/SQL » SQL & PL/SQL » Cursor advice
Cursor advice [message #202350] Thu, 09 November 2006 04:32 Go to next message
Tom Dunne
Messages: 13
Registered: August 2006
Location: Dublin, Ireland
Junior Member
I am looking for some general advice on how to combine a number of select statements into a cursor.

I am using a reporting application that will only take data back from an Oracle package via a recordset, it won't accept individual return values, so I have to use a cursor in my package.

What I have is as follows:

Package body

proc 1
proc 2
proc 3
proc 4

What I was thinking of doing was taking the SELECT from each proc and combining them into one large select for the cursor using UNION:

OPEN myCursor FOR


All selects return the same number of columns, so the UNION will work. Each select returns one row - they each do a count.

I am just wondering if there is a more efficient way of doing this?
Re: Cursor advice [message #202353 is a reply to message #202350] Thu, 09 November 2006 04:46 Go to previous messageGo to next message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
UNION ALL will be slightly more efficient than UNION as it won't do a distinct on the results.

Without seeing the queries, there's no real way to comment on this.
Re: Cursor advice [message #202360 is a reply to message #202353] Thu, 09 November 2006 06:05 Go to previous message
Messages: 20848
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As you are about to use all procedures, could you - instead of packages and procedures - use that large UNION query in the reporting application you mentioned?

For example, if it was Reports Builder, its query (written your way) would look very neat - a few SELECT statements and UNIONs between them.
The same query written my way would probably look awful - bunch of SELECT statements, all of them probably having several tables and a complicated WHERE clause). But that could, actually, do the job.

Or perhaps not?
Previous Topic: Table Audit - INSERT with Particular Value on Past date
Next Topic: ORA12545 - Connect failed because target host or obejct does not exist
Goto Forum:

Current Time: Fri Oct 21 06:22:43 CDT 2016

Total time taken to generate the page: 0.25609 seconds