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

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.

CodePudding user response：

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

CodePudding user response：

Problem is terminating semi-colon in l_insert_statement; remove it

No : ');';
Yes: ')';

However, there's nothing dynamic in code you use so I'd suggest you to use simply

begin
  insert into foobars values (var_foo, var_bar);
end;