Systems Engineering and RDBMS

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;

COL1       COL2
———- ———-
Oracle     10g
SQLServer  2008

Run the following PL/SQL block to update col2 to NULL values using dynamic sql.

BEGIN
EXECUTE IMMEDIATE ‘UPDATE TEST SET COL2 = :1’ USING NULL;
END;
/

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.

DECLARE
v_col2 CHAR(1);
BEGIN
EXECUTE IMMEDIATE ‘UPDATE TEST SET COL2 = :1’ USING v_col2;
END;
/

If we query data again, we will see successful update of col2 with null values.

SQL> select col1,col2 from test;
COL1       COL2
———- ———-
Oracle
SQLServer

This is one of the ways of passing in a null value to dynamic SQL statement.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: