oracle plsql concanting a variable to a string

Question

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;

HamoriZ · Accepted Answer · 2011-03-16 09:42:45Z

2

'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 ''

answered Mar 16, 2011 at 9:42

HamoriZ

2,45821 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HamoriZ Over a year ago

What is the content of the p_loc? Is it DALLAS (p_loc='DALLAS') or 'DALLAS' (p_loc='''DALLAS'''). If it is DALLAS, then 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 ||''' )'; should work. Inside of the string '' means '.

Bhagwat Over a year ago

THANKS iT IS WORKING FINE CAN U PLEASE EXPLAIN WHY IT SHOULD BE THAT wAY

Julfcar Ali · Accepted Answer · 2021-09-08 13:12:06Z

0

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...

answered Sep 8, 2021 at 13:12

Julfcar Ali

12 bronze badges

Collectives™ on Stack Overflow

oracle plsql concanting a variable to a string

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related