CODEWITHSUNDEEP
oracle10gdynamic-sql
Bhagwat
Bhagwat

Reputation: 71

oracle plsql concanting a variable to a string

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;

Upvotes: 3

Views: 10741

Answers (2)

Julfcar Ali
Julfcar Ali

Reputation: 1

P_LOC parameter is string type, the parameter value should be enclosed within single quotes. In existing dynamic SQL condition, the single quotes are not present.

Please replace below condition with new condition.

Existing Condition: || ' AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC= ' || p_loc ||' )';

New Condition: || q'[ AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC= ']' || p_loc || q'[' )]';

Thank you...

Upvotes: 0

HamoriZ
HamoriZ

Reputation: 2438

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

As Dallas is string, you need to handle it as string: 'DALLAS' and not as DALLAS. Inside of string you have to use ''

Upvotes: 2

Related Questions