Oracle plsql binding variable to string

below is a program written in oracle plsql, when this program is executed, setting the P_LOC value to 'DALLAS' it gives an error like -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;
+3
source share
1 answer

'SELECT DEPTNO FROM DEPT WHERE LOC = ' ' ' || p_loc || ' ' ' )';

Since Dallas is a string, you need to treat it as a string: "DALLAS" and not as DALLAS. Inside the line you should use ''

+2
source

All Articles