below is the program written in oracle plsql when this program is run by giving P_LOC value as DALLAS it gives error as -904 ORA-00904: "DALLAS": invalid identifier please provide a solution
create or replace
PROCEDURE PR_EMP_LST1
( P_LOC IN VARCHAR2
, P_MIN_SAL IN NUMBER
, P_MAX_SAL IN NUMBER
, P_REF_CUR OUT SYS_REFCURSOR
) AS
TYPE TY_LST_REFCURSOR IS REF CURSOR ;
cur_emp_lst TY_LST_REFCURSOR;
v_inv_query VARCHAR2(2000);
V_USG_CL VARCHAR2(200);
BEGIN
IF P_LOC IS NULL AND p_min_sal IS NULL AND p_max_sal IS NULL THEN
v_inv_query := SELECT ENAME
FROM EMP ;
ELSE
v_inv_query := SELECT ENAME
FROM EMP WHERE ;
IF P_LOC IS NULL AND p_min_sal IS NOT NULL AND p_max_sal IS NOT NULL THEN
v_inv_query :=v_inv_query || SAL BETWEEN
||p_min_sal
|| AND
|| p_max_sal;
-- v_inv_query :=v_inv_query || SAL BETWEEN :1 AND :2 ;
dbms_output.put_line( 2 );
-- V_USG_CL:= USING ||p_min_sal|| , || p_max_sal;
ELSIF P_LOC IS NOT NULL AND p_min_sal IS NOT NULL AND p_max_sal IS NOT NULL THEN
dbms_output.put_line( 1 );
v_inv_query :=v_inv_query|| SAL BETWEEN
||p_min_sal || AND || p_max_sal
|| AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC=
|| p_loc
|| ) ;
dbms_output.put_line( 2 );
ELSIF P_LOC IS NOT NULL AND p_min_sal IS NULL AND p_max_sal IS NOT NULL THEN
v_inv_query :=v_inv_query|| SAL <=
||p_max_sal
|| AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC=
|| p_loc
|| ) ;
ELSIF P_LOC IS NOT NULL AND p_min_sal IS NOT NULL AND p_max_sal IS NULL THEN
v_inv_query :=v_inv_query|| SAL >=
||p_min_sal
|| AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC=
||p_loc
|| ) ;
ELSIF P_LOC IS NULL AND p_min_sal IS NULL AND p_max_sal IS NOT NULL THEN
v_inv_query :=v_inv_query|| SAL <=
||p_max_sal;
ELSIF P_LOC IS NULL AND p_min_sal IS NOT NULL AND p_max_sal IS NULL THEN
v_inv_query :=v_inv_query|| SAL >=
|| p_min_sal;
ELSIF P_LOC IS NOT NULL AND p_min_sal IS NULL AND p_max_sal IS NULL THEN
v_inv_query :=v_inv_query|| DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC=
||p_loc
|| ) ;
END IF;
END IF;
dbms_output.put_line( 3 );
dbms_output.put_line(v_inv_query );
OPEN cur_emp_lst FOR v_inv_query ;
dbms_output.put_line( 4 );
P_REF_CUR:=cur_emp_lst;
END PR_EMP_LST1;