Back to the Basics: Passing a Null value to Dynamic SQL
Posted by decipherinfosys on February 19, 2009
In one of our previous blog post, we had covered how we can use SELECT clause with dynamic sql using execute immediate.
In this blog post, we will see how we can pass in a null value in dynamic sql. At times, we come across a situation where we have to update a column with a value to null. If it is a regular update statement, then it is not an issue. But problem with dynamic sql is that literal NULL is not allowed in dynamic sql in the USING clause. Let’s set up an example to see the issue first and then the resolution. Let us assume that we have a table calleed test with two VARCHAR columns col1 and col2. And it has these records in it:
SQL> select col1, col2 from test;
Run the following PL/SQL block to update col2 to NULL values using dynamic sql.
EXECUTE IMMEDIATE ‘UPDATE TEST SET COL2 = :1’ USING NULL;
Since literal NULL is not allowed in USING clause, we get following error upon execution.
ERROR at line 2:
ORA-06550: line 2, column 57:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 2, column 5:
PL/SQL: Statement ignored
Now let us re-issue the same PL/SQL block but use a variable.
EXECUTE IMMEDIATE ‘UPDATE TEST SET COL2 = :1’ USING v_col2;
If we query data again, we will see successful update of col2 with null values.
SQL> select col1,col2 from test;
This is one of the ways of passing in a null value to dynamic SQL statement.