Re: Ref Cursor in Oracle Stored Procure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 22 Jul 2001 06:47:55 GMT
Message-ID: <9gfmm402fgc@drn.newsguy.com>

In article <4a1d2952.0106152157.3581b2cf_at_posting.google.com>, sunnysearch_at_yahoo.com says...
>
>Hello there,
> Can any body help me in use of ref cursor in oracle stored
>procedure.
>I am using oracle ref cursor in stored procedure/function which will
>return a ref cursor to calling program.
> I am not getting, how to use 'alias' in order by clause in a query
>which is used in ref cursor.
> I have ref cursor in stored procedure as :
>
>
>open refCurs for
> select FirstName, Lastname, Average*sin(degree)*sqrt(memo) X
> From UserData
> where FirstName like 'Su%'
> ORDER BY
> X*0.5 - 5*(sqrt(X+(X-5));
>
> where refcurs is object of ref cursor and usedata is a oracle
> table containg above listed fields.
>
>Here I do not want to use formula for X in order by clause as it will
>be repeated in order by clause. Instead I want to use alias 'X' to
>order the result.

use an inline view:

open refCurs for

     select * 
       from ( select FirstName, Lastname, Average*sin(degree)*sqrt(memo) X
		From UserData 
	       where FirstName like 'Su%' )
       ORDER BY X*0.5 - 5*(sqrt(X+(X-5));

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 

Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle Corp Received on Sun Jul 22 2001 - 01:47:55 CDT