Dyanamic Query by Passing a String as Conditon for checking

This is my procedure:

PROCEDURE SP_SALARYADVANCE_RPT_TEST
  (
    txtExtraQuery IN STRING,
    refcur OUT sys_refcursor )
OPEN refcur FOR
    SELECT DISTINCT( SAL.ADVANC开发者_JAVA百科ENO ), SALARYADVANCEID, SAL.TRANSDATE, SAL.APPROVEDREMARKS, SAL.APPROVEDAMOUNT, SAL.PAYRECAMOUNT, EMP.EMPLOYEENAME, EMP.EMPLOYEECODE
      FROM HRM_SALARYADVANCE SAL, HRM_EMPLOYEE EMP, HRM_EMPLOYEEDEPARTMENTS DEPTS
      WHERE SAL.EMPLOYEEID     = EMP.EMPLOYEEID AND
        SAL.EMPLOYEEID     = DEPTS.EMPLOYEEID AND
        DEPTS.DEPARTMENTID = txtdeptid || txtextraquery; 

Here my problem is I am sending AND SAL.STATUS in (1,2,3) as parameter This 1,2,3 may change i wish to pass it from frontend itself.

While excuting this query I am getting and error of

ORA-01722: invalid number

I'm guessing that your "parameter" AND SAL.STATUS in (1, 2, 3) is put into txtExtraQuery. Of course, you can't concatenate that to your existing query like you did, because then you'd concatenate txtdeptid with txtextraquery. And that doesn't have any meaning. That's why you get an ORA-01722 error, when comparing that with DEPTS.DEPARTMENTID

What you want to do is run an EXECUTE IMMEDIATE command. Check out these links:

• http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm
• http://www.dba-oracle.com/t_execute_dynamic_pl_sql_procedures.htm

But on the other hand, I think you should re-design your procedure. This doesn't look very nice. Why not pass a VARRAY or some other UDT to your procedure?