err in function

[prodadmin]

Can some one help me in correcting this ..


**************************************************
FUNCTION FN_AVAILABLE
( P_USER_ID in USERS.MEMBER%TYPE
,P_PASSWORD in MEMBERS.password%TYPE
,P_RESERVED_CODER_ID in USERS.MEMBER%TYPE
,P_JOB_ID in CODING_JOBS.JOB_ID%TYPE
,P_COMPANY_ID in CODING_JOBS.COMPANY_ID%TYPE
)
RETURN RECORDSET IS
Result RECORDSET;
BEGIN
L_MT_ID := PA_CD.FN_MID( P_USER_ID);
if PA_wcj.f_VMt( L_MT_ID, P_PASSWORD) then

OPEN Result FOR
select CODING_JOB_ID, -- *** first errors ***
PATH
from CODING_JOBS
where CODING_STAGE_ID in (2,6)
and ((RESERVED_CODER_ID=" " OR (P_RESERVED_CODER_ID = RESERVED_CODER_ID)) --** second error **
and ((COMPANY_ID=0) OR (P_COMPANY_ID = COMPANY_ID))
and ((JOB_ID=0) OR (P_JOB_ID = CODING_JOB_ID));

end if;

return( Result);

END FN_AVAILABLE;

Line # = 219 Column # = 5 Error Text = PL/SQL: SQL Statement ignored
Line # = 223 Column # = 31 Error Text = PL/SQL: ORA-00907: missing right parenthesis

[DaPi]

First error - "OPEN x FOR" x must be a cursor.
http://www.csee.umbc.edu/help/oracle..._ora.htm#40414 and preceding pages.

Second error - unpaired () on line you indicate. Just like the error message says! Why complicate the counting by using unecessary () pairs? Try:

and (RESERVED_CODER_ID=" " OR P_RESERVED_CODER_ID = RESERVED_CODER_ID) 
and (COMPANY_ID=0 OR P_COMPANY_ID = COMPANY_ID)
and (JOB_ID=0 OR P_JOB_ID = CODING_JOB_ID);

[prodadmin]

Thanks ..i will try this

also i am getting new error after correcting this :

ora-00604: error occured at recursive SQL Leevle 1
ora-01400 : cannnot insert NULL into (sys.obj$"."NAME"