CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldeveloper
this-Mathieu
this-Mathieu

Reputation: 121

Oracle Query - Missing Defines

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

enter image description here

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

Upvotes: 8

Views: 16650

Answers (5)

Tony BenBrahim
Tony BenBrahim

Reputation: 7290

In my case, I had bind OUT parameters that I tried to access before calling CallableStatement::execute().

Upvotes: 0

v.sheldeshov
v.sheldeshov

Reputation: 188

I've just got similar case.

After a short research the bug was found. Where was a code

DECLARE
VAR001 NUMBER := NULL;
...
SELECT *
...
AND FIELD001 = VAR001

I have forgoten to set VAR001

Upvotes: 0

Dzmitry Lahoda
Dzmitry Lahoda

Reputation: 939

I had the same error message. My DB column was 32 chars(VARCHAR2(32 CHAR)) and declared variable for filter was 64 chars(szId varchar2(32) := :Id;). I copied and pasted some value as input variable. For some reason I got white space in the end. So I got 33 chars and mentioned error. I made my declaration of 32 chars and started to get more meaningful error.

I your case your DB column is VARCHAR2(20 BYTE) and declare is whatever varchar2(20) := :bananas;. I may appear that byte is not char in total size.

Upvotes: 0

Don Pedro
Don Pedro

Reputation: 31

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.

Upvotes: 3

WW.
WW.

Reputation: 24281

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

Upvotes: 1

Related Questions