I have the query inside ' ' :

X:=NULL;
v_sql:='insert into users(id) values(NVL('||X||',NULL))';

The result I got is:

insert into users(id) values(NVL(,NULL))

Why the null value is skipped? And how can I fix this?

I have the query inside ' ' :

X:=NULL;
v_sql:='insert into users(id) values(NVL('||X||',NULL))';

The result I got is:

insert into users(id) values(NVL(,NULL))

Why the null value is skipped? And how can I fix this?

• oracle-database
• plsql
• oracle-apex

Share Improve this question Follow edited Nov 16, 2024 at 20:38 jps 22.7k1616 gold badges8888 silver badges107107 bronze badges asked Nov 16, 2024 at 17:24 mmll llmmmmll llmm 1111 bronze badge

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

How to fix this ? Avoid it by not contatening. It's not good practice. Always try to use bind variables and then you won't run into such issues. For your use case, no dynamic sql is needed. This works too and is a lot simpler:

DECLARE
    l_x VARCHAR2(1);
BEGIN
    l_x := NULL;
    INSERT INTO users ( id ) VALUES ( nvl(l_x,NULL) );
END;
/

PL/SQL procedure successfully completed.

If EXECUTE IMMEDIATE is needed (for some other reason you're not mentioning) , then use it with bind variables:

DECLARE
    l_x VARCHAR2(1);
    l_sql VARCHAR2(100);
BEGIN
    l_x := NULL;
    l_sql  := q'!INSERT INTO users ( id ) VALUES ( nvl(:x,NULL) )!';
    EXECUTE IMMEDIATE l_sql USING l_x;
END;
/

PL/SQL procedure successfully completed.

Actually, it is not skipped but it causes the concatenation to fail silently.

1. X is NULL.
   

2. When you do '|| X ||', the NULL value of X is not treated as a string but as a missing value.
   

3. String concatenation with NULL ('|| NULL ||') results in the entire concatenation expression becoming NULL.
   
   Thus, the v_sql assignment evaluates to NULL, and you see NVL(,NULL) in your result
   
   If you want the literal text NULL to appear in the SQL query when X is NULL, you need to explicitly handle this substitution. For example:
   

   v_sql := 'insert into users(id) values(NVL(' || CASE WHEN X IS NULL THEN 'NULL' ELSE X END || ', NULL))';