8-12. Passing NULL Values to Dynamic SQL
Problem
You want to pass a NULL value to a dynamic query that you are using. For example, you want to query the EMPLOYEES table for all records that have a NULL MANAGER_ID value.
Solution
Create an uninitialized variable, and place it into the USING clause. In this example, a dynamic query is written and executed using native dynamic SQL. The dynamic query will retrieve all employees who do not currently have a manager assigned to their record. To retrieve the records that are required, the WHERE clause needs to filter the selection so that only records containing a NULL MANAGER_ID value are returned.
DECLARE
TYPE cur_type IS REF CURSOR;
cur cur_type;
null_value CHAR(1);
sql_string VARCHAR2(150);
emp_rec employees%ROWTYPE;
BEGIN
sql_string := 'SELECT * ' ||
'FROM EMPLOYEES ' ||
'WHERE MANAGER_ID IS :null_val';
OPEN cur FOR sql_string USING null_value;
LOOP
FETCH cur INTO emp_rec;
DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name ||
' - ' || emp_rec.email);
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur;
END;
In this solution, the bind variable :null_val has an uninitialized variable value substituted in its place. This will cause the query to evaluate the bind variable as a NULL value. All records that reside within the EMPLOYEES table and do not have a MANAGER_ID assigned to them should be printed by the DBMS_OUTPUT package.
How It Works
It is not possible to simply pass a NULL value using native dynamic SQL. At least, you cannot pass a NULL as a literal. However, oftentimes it is useful to initialize a bind variable to null.
An uninitialized variable in PL/SQL inherently has the value of NULL. Hence, if you do not initialize a variable, then it will contain a NULL value. Passing an uninitialized variable via the EXECUTE IMMEDIATE statement will have the same effect as substituting a NULL value for a bind variable.