Oracle Query - Missing Defines

Question

I created a very simple table:

CREATE TABLE TMP ("ID" VARCHAR2(20 BYTE));

Then tried to do this:

DECLARE
  whatever varchar2(20) := :bananas;
BEGIN
  MERGE INTO tmp t USING 
    (SELECT whatever AS this_id FROM DUAL) d 
  ON (t.id = d.this_id) 
    WHEN NOT MATCHED THEN 
      INSERT (id) VALUES (d.this_id);
END;

And then enter binds

And get this error:

Error starting at line : 1 in command -
DECLARE
  whatever varchar2(20) := :bananas;
BEGIN
  MERGE INTO tmp2 t USING 
    (SELECT whatever AS this_id FROM DUAL) d 
  ON (t.id = d.this_id) 
    WHEN NOT MATCHED THEN 
       INSERT (id) VALUES (d.this_id);
END;
Error report -
Missing defines

I've had no luck figuring out what it wants. If I replace ':bananas' with a value like 'a' it works, but not when I use a variable and bind the value. Anyone know whats wrong with my query? Thanks.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE   11.2.0.4.0  Production"
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

edit: I've just noticed that the error is not preventing the data from being merged correctly... The error is still concerning though

Solution 1

I would probable skip the DECLARE section all together and use everywhere :bananas instead of whatever. After BEGIN put :bananas := :bananas; and you'll get no error. Good luck.

Solution 2

Try adding a slash after the statement on a line of its own. Then highlight the whole block and press F5.