Re: Dynamic Sorts in Pl/Sql

From: Mike Krolewski <mkrolewski_at_rii.com>
Date: Fri, 19 Jan 2001 00:08:36 GMT
Message-ID: <9480hv$698$1@nnrp1.deja.com>

In article <947quk$ev$1_at_news2.isis.unc.edu>,   "Patrick Casey" <patrick_casey_at_unc.edu> wrote:
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_0098_01C08174.AF644F30
> Content-Type: text/plain;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> We are trying to create a procedure that will be able to sort fields =
> dynamically. We want the same fields for each query, but at some
 times =
> we want it sorted by one field, and sometimes by another.
>
> We created the following procedure:
> PROCEDURE testSort
> (in_sort_field IN VARCHAR2,
> in_sort_order IN VARCHAR2 DEFAULT 'asc',
> out_cursor OUT pjcCursor)
> IS
> BEGIN
> if in_sort_order =3D 'asc' then
> OPEN out_cursor FOR
> SELECT cat_itm_id, cat_itm_nm FROM ITP_CATALOG_ITEM
> ORDER BY in_sort_field ASC;
> else
> open out_cursor FOR
> select cat_itm_id, cat_itm_nm FROM itp_catalog_item
> order by in_sort_field DESC;
> end if;
> END;
>
> It allows us to pass in a field name to sort by. Now, it works fine
 on =
> ASC, but it never works on DESC. We think this is because Oracle
 really =
> sees this query as
>
> OPEN out_cursor FOR
> SELECT cat_itm_id FROM ITP_CATALOG_ITEM
> ORDER BY 'in_sort_field' ASC; <-- notice quotes around field name
>
> It seems to ignore everything following.....Now, I did try to create
 a =
> procedure that had the sort field hardcoded, with quotes around the
 name =
> and it would not sort either. So, that seems to be the problem, the =
> quotes.
>
> Does anyone have any guesses as to how we can make this work????
>
> --=20
>
> Thanks.
>
> Patrick Casey
> Applications Analyst Programmer II
> UNC - Chapel Hill
> patrick_casey_at_unc.edu
> 919.962.0549
>

You are not allow to redefine the query on the fly.

There are two ways: dbms_sql package OR native dynamic SQL...

You can however use dbms_sql package to construct the sql statement and then execute it. This has been the mechanism for a while and is the most compatible if that is an issue.

Native dynamic SQL : Chapter 10 of 8i PL/SQL user guide and reference

You can extend this example to construct the sql_statement with the appropriate field for the order by clause. Note: also there is syntax for binding variables ( input and output ).

Example from manual....

As the following example shows, you can fetch rows from the result set of a dynamic multi-row query into a record:

DECLARE
    TYPE EmpCurTyp IS REF CURSOR;

    emp_cv EmpCurTyp;
    emp_rec emp%ROWTYPE;
    sql_stmt VARCHAR2(200);

    my_job VARCHAR2(15) := ’CLERK’;
BEGIN
    sql_stmt := ’SELECT * FROM emp WHERE job = :j’;     OPEN emp_cv FOR sql_stmt USING my_job;     LOOP

        FETCH emp_cv INTO emp_rec;
        EXIT WHEN emp_cv%NOTFOUND;
        -- process record

    END LOOP;
    CLOSE emp_cv;
END;

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rii.com
              Usual disclaimers


Sent via Deja.com
http://www.deja.com/

Received on Thu Jan 18 2001 - 18:08:36 CST