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

Question

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.

MT0 · Accepted Answer · 2021-11-08 09:40:37Z

3

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

edited Nov 8, 2021 at 9:40

answered Nov 8, 2021 at 9:36

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Littlefoot · Accepted Answer · 2021-11-08 09:34:56Z

2

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;

answered Nov 8, 2021 at 9:34

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related