Oracle and PL/SQL Recipes: A Problem-Solution Approach

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