String concatenation select query is not giving result pl/sql script

January 26, 2022

I am trying to construct query dynamically but after string concatenation the select statement not producing any result in pl/sql.

Please help me on this

DECLARE
  person_id NUMBER;
BEGIN
  DECLARE
    age_where VARCHAR2(100 CHAR);
    TEMP_WHERE     VARCHAR2(100 CHAR) := '';
    add_temp_where BOOLEAN            := true;
    
  begin
    age_where := q'[ and age=28]';
    
    IF(ADD_TEMP_WHERE) THEN
      TEMP_WHERE := age_where;
    END IF;
    
  SELECT id INTO person_id FROM PERSON WHERE name = 'David' || TEMP_WHERE ;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('no data');
  END;
  DBMS_OUTPUT.PUT_LINE('result : ' || person_id);
END;

Table entries

ID      NAME        AGE     ADDRESS     SALARY
-----------------------------------------------
1       David       28      PURAM       30000
2       Vimal       30      MARUR       20000

Output:

anonymous block completed
no data
result :

>Solution :

You’ll need dynamic SQL for that.

SQL> DECLARE
  2     person_id       NUMBER;
  3     age_where       VARCHAR2 (100 CHAR);
  4     TEMP_WHERE      VARCHAR2 (100 CHAR) := '';
  5     add_temp_where  BOOLEAN := TRUE;
  6     l_str           VARCHAR2 (400);
  7  BEGIN
  8     age_where := q'[ and age=28]';
  9
 10     IF (ADD_TEMP_WHERE)
 11     THEN
 12        TEMP_WHERE := age_where;
 13     END IF;
 14
 15     l_str := q'[SELECT id FROM PERSON WHERE name = 'David']' || TEMP_WHERE;
 16
 17     EXECUTE IMMEDIATE l_str
 18        INTO person_id;
 19
 20     DBMS_OUTPUT.PUT_LINE ('result : ' || person_id);
 21  END;
 22  /
result : 1

PL/SQL procedure successfully completed.

SQL>