Why won't oracle sql developer recognise my if statement

Question

I am using oracle SQL developer and am trying to run a query with an if statement in it.

I have defined a variable and assigned a value to it.

define
var buyer varchar2(1)

/* define variable values */ 
exec :buyer := 'Yep'

if :buyer = 'Yep' then 
begin
dbms_output.put_line('Yep');
else 
dbms_output.put_line('Nope');
end;

However, when I run the script, I get an error message stating that IF is an unknown command.

Error starting at line : 8 in command -
if :buyer = 'Yep' then
Error report -
Unknown Command

Any advice is greatly appreciated.

Because you can't use if outside of a PL/SQL block.

user330315
– user330315

2014-12-04 15:46:37 +00:00
Commented Dec 4, 2014 at 15:46 — user330315
– user330315, Commented Dec 4, 2014 at 15:46

Maki · Accepted Answer · 2014-12-04 16:08:36Z

1

You have to define a DECLARE-BEGIN-END block to run IF-ELSIF-ELSE-END conditions. Based on your sample code, I'm assuming this is what you would like to do.

DECLARE
  buyer VARCHAR2(3) := 'Yep';
BEGIN
  IF ( buyer = 'Yep' ) THEN
    dbms_output.put_line('Yep' );
  ELSE
    dbms_output.put_line('Nope');
  END IF;
END;
/

You could run this as a script in sql developer. HTH!

answered Dec 4, 2014 at 16:08

Maki

1,0101 gold badge9 silver badges13 bronze badges

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

Comments

Falco · Accepted Answer · 2014-12-04 15:54:27Z

1

Look up the PLSQL Syntax:

DECLARE
  variable type;
BEGIN
  IF variable = 'test'
  THEN
    DBMS_OUTPUT.PUT_LINE('J');
  ELSE
    DBMS_OUTPUT.PUT_LINE('N');
  END IF;
END;
/

answered Dec 4, 2014 at 15:54

Falco

3,46626 silver badges29 bronze badges

1 Comment

Squirmster Over a year ago

Thanks for the help both. That's cleared that up... :)

Collectives™ on Stack Overflow

Why won't oracle sql developer recognise my if statement

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related