Re: plsql & decode

Maybe this example can help.

set serveroutput on size 1000000

CREATE OR REPLACE PROCEDURE aProcedure
(par_OrderBy IN VARCHAR2
,par_DescAsc IN VARCHAR2
)
IS
TYPE aRefCursor IS REF CURSOR;
aCursor aRefCursor;
aQuery VARCHAR2(1000);
aTableName user_tables.table_name%TYPE;
BEGIN
    OPEN aCursor FOR 'select table_name from user_tables order by '||par_orderby||' '||par_DescAsc;
--

• or
• aQuery := ...
• OPEN aCursor FOR aQuery;
  --
  LOOP FETCH aCursor INTO aTableName; EXIT WHEN aCursor%NOTFOUND; --
  • do the rest -- dbms_output.put_line(aTableName); END LOOP; CLOSE aCursor; EXCEPTION WHEN OTHERS THEN IF aCursor%ISOPEN THEN CLOSE aCursor; END IF; END; /

execute aProcedure('table_name','asc');
execute aProcedure('table_name','desc');

"tony" <ttigger4321_at_hotmail.com> wrote in message news:3cfcda84.99032413_at_news.dal.ca...

> Hi.  I have declared the following cursor in a plsql procedure:
>
> Procedure tbl_list (pcode IN VARCHAR2) is
>
> CURSOR get_info_C is
> SELECT
> code, desc
> FROM codetable
> WHERE 0=0
> ORDER by DECODE (pcode, 'code', code, 'desc', desc, swvstyp_code);
>
> I would like to also pass either desc or asc from a href.  Seems like
> I can't do another decode.  Any idea how to get around this?
>
> many thanks
> cheers,
> tony
> 902.494.1021 w
> 902.461.4550 h