Function problem - ORA-00907

From: Mando <mandoswork_at_hotmail.com>
Date: 28 Sep 2006 01:52:35 -0700
Message-ID: <1159433555.076202.127740@m73g2000cwd.googlegroups.com>

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 Received on Thu Sep 28 2006 - 03:52:35 CDT