Re: PL/SQL - Replace field name with parameter

You can't do this with the approach you're currently taking. You're gonna have to build a dynamic sql query string and "execute immediate".

See the section on that in the PL/SQL Manual.

Another option would be to reconsider the design of this table if you're open to changes on that level. A bit more normalized perhaps?

Cheers!

mandoswork_at_hotmail.com (PaulM) wrote in message news:<fc844f16.0408240245.5a369566_at_posting.google.com>...
> Hi
>
> I'm trying to call a cursor in PL/SQL function and replace one of the
> field names in the select statement with a parameter that is passed to
> the function. The script is below:
>
> CREATE OR REPLACE function fnc_get_week (p_reg_key IN number,
> p_week_num IN number)
> RETURN date IS
> l_date date;
>
> cursor cur_week is
> select date_27 --<<I want to replace '27' with p_week_num>>>
> from registers_1
> where reg_key = p_reg_key;
>
> BEGIN
>
> open cur_week;
> fetch cur_week into l_date;
> close cur_week;
>
>
> RETURN l_date;
>
> END fnc_get_week;
> /
>
> Could anyone let me know what the syntax is to replace the number 27
> above with the value passed into the function thru the p_week_num
> parameter.
>
> Any help would be greatly appreciated.
>
> Thanks
>
> Paul
Received on Tue Aug 24 2004 - 09:30:22 CDT