Re: cursor :can you pass order-by as parameter?

On Wed, 31 Mar 2004 16:03:40 GMT, mike <burnunit_at_DROPTHIS.hotmail.com> wrote:

>Hi, I'm relatively new to ORACLE forms - I hope this makes sense :-)
>
>Looking at : http://www.unix.org.ua/orelly/oracle/prog2/ch06_10.htm#SQL2-
>CH-6-SECT-10.2
>they state:
>
>"The most common place to use a parameter in a cursor is in the WHERE
>clause, but you can make reference to it anywhere in the SELECT
>statement, as shown here: "
>
>DECLARE
> CURSOR joke_cur (category_in VARCHAR2)
> IS
> SELECT name, category_in, last_used_date
> FROM joke
> WHERE category = UPPER (category_in);
>
>
>and this works fine. BUT if I want to dynamically order the results like
>this :
>
>DECLARE
> CURSOR joke_cur (category_in VARCHAR2, sort_key_in VARCHAR2)
> IS
> SELECT name, category_in, last_used_date
> FROM joke
> WHERE category = UPPER (category_in)
> ORDER BY sort_key_in;
>
>
>I would get back exactly the same results as the first example - the
>order by clause is ignored.
>
>Does anyone know how to dynamically specify the order by clause within a
>cursor?
>
>Thanks,
>
>Michael.

You will have to use dynamic sql, and probably end up with an unscalable application.
In Query mode in Forms it used to be possible to hit the & key to specify extra conditions.

--
Sybrand Bakker, Senior Oracle DBA