Re: Function problem - ORA-00907

From: sybrandb <sybrandb_at_gmail.com>
Date: 28 Sep 2006 04:46:15 -0700
Message-ID: <1159443975.372417.134890@m7g2000cwm.googlegroups.com>

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

What I see is
- AFAIK you need to
  execute immediate 'select * '||

                               'from emp'||
                               'where empno=10'

- You are not escaping single quotes by using an extra single quote everywhere
- You are not using bind variables do you have defined them in the declaration section of your procedure
- Consequently your procedure is unscalable - The general resolution method for problems like this is to take the statement out of the procedure and have sql*plus churn it. That will provide you with the exact line, and it is also much faster than Usenet.

-- 
Sybrand Bakker
Senior Oracle DBA

Received on Thu Sep 28 2006 - 06:46:15 CDT