Re: Dynamic SQL in Stored Procedure

Hi - check the dbms_sql package - you can do wondera with it! Ian

On Fri, 30 Jan 1998 10:40:57 +0100, michael ringbo <mri_at_dde.dk> wrote:

>Hi Mark
>
>Don't know if this works for you, but how about trying:
>order by decode(var1,'A',colA,colB)
>
>Michael Ringbo
>mri_at_dde.dk
>
>Mark Aurit wrote:
>
>> Im using the Oracle Web Server, and I want to give the customer the
>> ability choose a sort; in other words, affect the ORDER BY. Their
>> request will be passed to a stored procedure, and Im wondering how
>> (and if) I can change the SQL dynamically. I could do something like
>> IF var1 = 'A' THEN
>> .. do some cursor stuff and issue a SELECT..
>> ORDER BY colA
>> ELSE
>> ORDER BY colB
>> END IF;
>> But I hate the idea of all that code Ill have to duplicate. Can I say
>> something like:
>> IF var1 = 'A' then
>> var2 := 'emp_id';
>> else
>> var2 := 'city';
>> end if;
>> Followed by:
>> .. cursor stuff and a SELECT ...
>> ORDER BY var2
>>
>> Or some such construct?
>>
>> Any ideas greatly appreciated.
>> Mark_Aurit @ mail.northgrum.com
>
>
>
Received on Sat Jan 31 1998 - 00:00:00 CST