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

Question

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 : 

Answer 1

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>

Answer 2

Your objective is to have dynamic predicate in a query, based on input parameters. This is a frequent task.

The most important think is to realize, that you should always use bind variables for production queries, i.e. ommit construction such as WHERE name = 'David' || TEMP_WHERE.

There are three options in PL/SQL

Use IF - ELSE

This is the simplest option where for each combination of the input parameter there is one IF/ELSE branch

declare
 l_person_id  int;
 l_name varchar2(10) := 'David';
 l_age int := 28;
 add_temp_where int := 1; 
begin
  if add_temp_where = 0 then
    select id into l_person_id from person where name = l_name;
  else
    select id into l_person_id from person where name = l_name and age = l_age;
  end if;  
  dbms_output.put_line(l_person_id);    
end;
/

This will work in your case with one optional parameter, but is not practicable with more parameters. For 3 parameters you will need 8 branches.

Use OR to Disable Predicates

This option use only one static statement and use the OR logic to disable unwanted parameters.

Note that I'm using the parameter add_temp_where and int 0,1 to be able to use it in SQL.

declare
 l_person_id  int;
 l_name varchar2(10) := 'David';
 l_age int := 28;
 add_temp_where int := 1; 
begin
  select id into l_person_id from person where name = l_name and (add_temp_where = 0 or age = l_age);
  dbms_output.put_line(l_person_id);    
end;
/

So basically if add_temp_where = 0 than the predicate age = l_age is ignored due to shortcut evaluation.

This option works fine in case that for all options the query returns similar number of rows (technically the same execution plan is used).

It fails badly in case that for one option the whole table is returned and for other ony some small part (via index).

In such case you need to use dynamic SQL.

Dynamic SQL

You use the same trick as above with OR, so you generate following SQL strings

-- for  add_temp_where = 1
select id  from person where name = :1 and age = :2
-- for  add_temp_where = 0
select id  from person where name = :1 and (0=0 or age = :2)'

Note that both statement have two bind variables, so they can be used in one EXECUTE IMMEDIATE as follows:

declare
 l_person_id  int;
 l_name varchar2(10) := 'David';
 l_age int := 28;
 add_temp_where BOOLEAN := true;
 --
 l_sql1 varchar2(1000) := 'select id  from person where name = :1 and age = :2';
 l_sql2 varchar2(1000) := 'select id  from person where name = :1 and (0=0 or age = :2)'; 
begin
  execute immediate l_sql1 into l_person_id using l_name, l_age;
  dbms_output.put_line(l_person_id); 
  execute immediate l_sql2 into l_person_id using l_name, l_age;
  dbms_output.put_line(l_person_id);   
end;
/

Both statements are independent and can have therefore different execution plans, which solves the problem of the second option.

More info and credits to this option here and here