Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Carlos.

Asked: January 05, 2004 - 11:43 am UTC

Last updated: January 07, 2004 - 4:12 pm UTC

Version: 7.3.4

Viewed 1000+ times

You Asked

I'm declaring a Cursor in a stored procedure and I want that the cursor return me the
rows in the order depending on a parameter.
For example:

For Valued Stock Method:

cursor c_m is
select id,qty
from t_stock
where qty>0
order by id P_METHOD;

being P_METHOD='desc' if the Valued Stock Method is LIFO or
P_METHOD='asc' if the Method is FIFO.

The message it displays me is:

PLS-00103: Encountered the symbol "p_method" when expecting one of
the following:
. ( , * @ % & - + ; / for mod rem an exponent (**) asc desc


Is it possible to do it?

Thanks a lot,

Carlos


and we said...

order by decode( p_method, 'asc', 1, -1 ) * id


OR, you would have to use a ref cursor:


if ( p_method = 'desc' )
then
open c_m for select id,qty
from t_stock
where qty>0
order by id DESC;
else
open c_m for select id,qty
from t_stock
where qty>0
order by id ASC;
end if;

But since I cannot see an index being used to present the data "sorted" anyway (the range scan on qty>0 will not have id sorted if the index is on (qty,id) so it'll sort at the end anyway and if the index is on id,qty -- it will have to search the entire index.... so thats not a good solution either) the decode method should be "just fine"

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Good indeed

Carlos, January 07, 2004 - 4:12 pm UTC

Very smart the idea of using decode

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library