Re: Function problem - ORA-00907

Mando wrote:
> Can anyone tell me where the problem is with the function below. It
> compiles fine but I get the following error when I try to call it e.g.
>
> ERROR at line 1:
> ORA-00907: missing right parenthesis
> ORA-06512: at "DEV.FNC_EMA_REG_HOURS", line 12
>
> <the line 12 error is in the execute immediate statement
>
> CREATE OR REPLACE FUNCTION FNC_EMA_REG_HOURS(p_person_code IN NUMBER,
> p_week_num IN NUMBER, p_column IN VARCHAR2)
>
> RETURN NUMBER IS
>
> l_reg_hours NUMBER;
> v_coll VARCHAR2(2);
> v_pcode NUMBER;
> v_week NUMBER;
> v_sql_string VARCHAR2(1000);
>
> BEGIN
>
> v_coll := p_column;
> v_week := p_week_num;
> v_pcode := p_person_code;
> EXECUTE IMMEDIATE
>
> 'SELECT SUM(RM.DURATION) - SUM(DECODE
> SR.DURATION_08,0,AA.DURATION,'',AA.DURATION,SR.DURATION_08))
>
> FROM
> STUDENT_REGISTER SR,
> ADDIT_PERSONAL_DETAILS_03 APD,
> REGISTERS_MAIN RM,
> REGISTERS_1 R1,
> ALL_DATES_2 AD,
> (SELECT PERSON_CODE, REG_KEY,DURATION FROM FES.LCC_AUTH_ABSENCE WHERE
> WEEK_NUMBER = 8) AA
>
> WHERE
> SR.PERSON_CODE = APD.PERSON_CODE
> AND AD.EBS_WEEK_NUM = 8
> AND AD.DAY_NUM = 5
> AND AD.SESSION_CODE = RM.SESSION_CODE
> AND APD.PERSON_CODE = 555555
> AND RM.REG_KEY = SR.REG_KEY
> AND RM.SESSION_CODE = ''06/07''
> AND SR.REG_PRG_TYPE = ''A''
> AND RM.END_DATE >= AD.WEEK_DATE
> AND RM.REG_KEY = R1.REG_KEY
> AND SUBSTR(R1.ACTIVE_DATES,8,1) <> ''0''
> AND SR.REG_KEY = AA.REG_KEY (+)
> AND SR.PERSON_CODE = AA.PERSON_CODE (+)
> GROUP BY SR.PERSON_CODE'
>
> INTO l_reg_hours;
>
> RETURN l_reg_hours;
>
> End FNC_EMA_REG_HOURS;
>
> Any help much appreciated

>From what I can see, the line:

'SELECT SUM(RM.DURATION) - SUM(DECODE should be:

'SELECT SUM(RM.DURATION) - SUM(DECODE( HTH -g Received on Thu Sep 28 2006 - 07:50:55 CDT