Oracle Query - Missing Definitions

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 type 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 could not figure out what he wants. If I replace ": bananas" with the value "a", this works, but not when I use the variable and bind its value. Does anyone know what happened to my request? 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 just noticed that the error does not interfere with the proper data merging ... The error is still related to the fact that

Answer 1

Try adding a slash after the statement on a separate line. Then highlight the entire block and press F5.

Answer 2

I would probably skip the DECLARE section all together and use everywhere: bananas instead of anything. After BEGIN: bananas: =: bananas; and you will not get any error. Good luck.

Answer 3

I had the same error message. My DB column had 32 characters ( VARCHAR2(32 CHAR) ), and the declared variable for the filter was 64 characters ( szId varchar2(32) := :Id; ). I copied and pasted some value as an input variable. For some reason, I got a space at the end. Thus, I received 33 characters and noted an error. I made a 32 character expression and started getting a more meaningful error.

In your case, your database column is VARCHAR2(20 BYTE) and whatever varchar2(20) := :bananas; declared whatever varchar2(20) := :bananas; . It may seem that byte not char in total size.