Re: plsql & decode

Perfect! Thanks.

On Tue, 4 Jun 2002 18:09:51 +0200, "Richard Spee" <rhpspee_at_wxs.nl> wrote:

>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
>
>

cheers,
tony
902.494.1021 w
902.461.4550 h Received on Wed Jun 05 2002 - 10:23:54 CDT