Re: PL/SQL - Replace field name with parameter

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

Paul, normally to manipulate the SQL statement you want to execute at run time you have to use dynamic SQL. Pull out your pl/sql manual and look up "execute immediate". The dbms_sql package provides access to building more complex dynamic SQL statements.

v_sql := 'SELECT col1, '||p_parmcol1||', col4 '||....

HTH -- Mark D Powell -- Received on Tue Aug 24 2004 - 09:20:30 CDT