How to insert a NULL value in PL/SQL Procedure?

For example table Foobars,

CREATE TABLE Foobars(
   foo NUMBER,
   bar NUMBER
);

How to insert a NULL value from a variable in dynamic PL/SQL procedure?

DECLARE
   var_foo  NUMBER := 69;
   var_bar  NUMBER := NULL;
   l_insert_statement VARCHAR2(128);
BEGIN
   -- Assembles insert statement
   l_insert_statement :=
      'INSERT INTO Foobars VALUES (' ||
         var_foo || ', ' ||
         var_bar ||
      ');';
   -- Executes insert statement
   EXECUTE immediate (l_insert_statement);
END;
/

The query returns ORA-00936 at the "EXECUTE immediate…" line: missing expression.

>Solution :

Don’t pass variables in dynamic SQL using string concatenation, pass them as bind variables (and remove the ; statement terminator in the dynamic SQL string):

DECLARE
   var_foo  NUMBER := 69;
   var_bar  NUMBER := NULL;
   l_insert_statement VARCHAR2(128);
BEGIN
   -- Assembles insert statement
   l_insert_statement := 'INSERT INTO Foobars VALUES (:1, :2)';
   -- Executes insert statement
   EXECUTE immediate l_insert_statement USING var_foo, var_bar;
END;
/

Or, in this case, you don’t need dynamic SQL:

DECLARE
   var_foo  NUMBER := 69;
   var_bar  NUMBER := NULL;
BEGIN
   INSERT INTO Foobars VALUES (var_foo, var_bar);
END;
/

And don’t need PL/SQL and can just use a simple SQL statement:

INSERT INTO Foobars VALUES (69, NULL);

db<>fiddle here