PL/SQL: EXECUTE IMMEDIATE with condition variable

Question

I have a function in PL/SQL written like this

IF p_class IS NOT NULL
THEN
v_conditions := ' WHERE class = :p_class '
ELSE
v_conditions := ' WHERE 1 = :p_class '
p_class := 1
END IF;

I want to execute query with given p_class:

v_query := v_query || v_conditions;
EXECUTE IMMEDIATE v_query USING p_class

Now I want to check if my p_class is not exists, I won't have v_conditions in my query. Is there any better way instead of using WHERE 1 = :p_class?

I try to refactor my function to check if p_class is null, I won't have to add v_conditions to query, but I get an error:

bind variable does not exists.

I also try v_conditions := ' WHERE class = ' || p_class but I'm afraid that it will get SQL injection so I didn't use it.

Answer 1

You can also do it in a single statement, no IF needed:

DECLARE
  l_ename VARCHAR2(100);
  l_statement VARCHAR2(500) := 'SELECT COUNT(*) FROM emp WHERE ename = :ename or :ename IS NULL';
  l_result NUMBER;
BEGIN
  l_ename := 'KING';
  --l_ename := NULL
  EXECUTE IMMEDIATE l_statement INTO l_result USING l_ename, l_ename;
  dbms_output.put_line(l_result);
END;
/

--update-- if there are multiple parameters and the check of the NULL value has performance consequences then you can try the following. execute a 1=1 first if the parameter value is null. This will be evaluated and yield true so the next condition will be ignored.

DECLARE
  l_ename VARCHAR2(100);
  l_job VARCHAR2(100);
  l_statement VARCHAR2(500) := 'SELECT COUNT(*) FROM emp WHERE 1 = 1 ';
  l_condition VARCHAR2(100);
  l_result NUMBER;
BEGIN
  l_ename := NULL;
  l_job := 'CLERK';
  IF l_ename IS NULL THEN
    l_statement := l_statement ||' AND (1 = 1 OR 1 = :p1) ';
  ELSE
    l_statement := l_statement ||' AND ename = :p1 ';
  END IF;
  IF l_job IS NULL THEN
    l_statement := l_statement ||' AND (1 = 1 OR 1 = :p2) ';
  ELSE
    l_statement := l_statement ||' AND job = :p2 ';
  END IF;
    
  dbms_output.put_line(l_statement);
  EXECUTE IMMEDIATE l_statement INTO l_result USING l_ename, l_job;
  dbms_output.put_line(l_result);
END;
/