Home » SQL & PL/SQL » SQL & PL/SQL » Sorting a cursor in stored procedure
Sorting a cursor in stored procedure [message #36048] Thu, 01 November 2001 08:35 Go to next message
CARLOS
Messages: 24
Registered: February 2000
Junior Member
Dear experts,

I've created a cursor in the stored procedure in the following way,
...
Open vCursor for
SELECT * FROM tableA
....

The question is, I want to know, if I want to provide the user the flexibility of sorting this cursor by
different column, how can I sort this cursor?

It seems that I can't sort like the following
SELECT * FROM vCursor<----NOT a table
ORDER BY (xyz)<----column name that user desired.

do anyone get any idea? Pls advise!!!!

----------------------------------------------------------------------
Re: Sorting a cursor in stored procedure [message #36049 is a reply to message #36048] Thu, 01 November 2001 08:56 Go to previous messageGo to next message
Sudhakar Atmakuru
Messages: 58
Registered: May 2001
Member
Pass the sorting key as an input parameter to the stored procedure and use it in the SELECT statement of the CURSOR declaration. Hope it works and resolve your problem.
Good luck :)

----------------------------------------------------------------------
Re: Sorting a cursor in stored procedure [message #36052 is a reply to message #36048] Thu, 01 November 2001 09:06 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
open vCursor for 'SELECT * FROM tableA order by ' || p_order_by_parameter;

----------------------------------------------------------------------
Re: Sorting a cursor in stored procedure [message #36059 is a reply to message #36049] Thu, 01 November 2001 15:36 Go to previous messageGo to next message
CARLOS
Messages: 24
Registered: February 2000
Junior Member
Thx,

It does help! ^^

----------------------------------------------------------------------
Re: Sorting a cursor in stored procedure [message #36068 is a reply to message #36048] Fri, 02 November 2001 05:50 Go to previous messageGo to next message
neel
Messages: 12
Registered: November 2001
Junior Member
Hi Carlos,

I am trying a similar kind of procedure, can u post ur procedure on the forum, so that i can get some idea.

Thanks
Neel

----------------------------------------------------------------------
Re: Sorting a cursor in stored procedure [message #36080 is a reply to message #36068] Fri, 02 November 2001 16:27 Go to previous message
CARLOS
Messages: 24
Registered: February 2000
Junior Member
Sure! But have to wait till next Monday when I'm back to office OK?

----------------------------------------------------------------------
Previous Topic: Re: usage of temporary table in pl/sql procedure
Next Topic: what is the fastest way to update a big table (50000rows*20columns)
Goto Forum:
  


Current Time: Mon Oct 26 14:53:04 CDT 2020