Oracle - If variable is null

Question

I need to return some dummy select when my variable matches string. This is what I tried:

begin
  if :someVar = 'Yes' then
  select 1 from dual;

  end if;
end;
/

I've tried declaring a variable for INTO clause, but I allways receive error "PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following..".

Can somebody show what should I change to get desired select ? I'm beginner in Oracle, and I need this for SSRS reports, for setting parameter default values (based on another parameter - which is someVar in my question).

I'm not sure what you mean by return some dummy select. Do you need to return a cursor, or return the value of a variable, or just set a host variable's value e.g. if :x = 'Y' then :z := 123;? — William Robertson
– William Robertson, Commented Aug 1, 2018 at 15:29

hotfix · Accepted Answer · 2018-08-01 12:37:42Z

1

you have to add an into :<variable>

begin
  if :someVar = 'Yes' then
    select 1 into :var from dual;

  end if;
end;
/

:var should be you varibale in ssrs

edited Aug 1, 2018 at 12:37

answered Aug 1, 2018 at 12:20

hotfix

3,39624 silver badges36 bronze badges

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

11 Comments

Lucy82 Over a year ago

thanks for immediate answer, but that produces me same error as described, in Toad. What else could be wrong ?

hotfix Over a year ago

how do you call that block? i have no errors by executing it in a database

Lucy82 Over a year ago

@Thanks, that was the problem indeed. But looks like SSRS report still doesn't work with this syntax, so I didn't solve anything yet.

hotfix Over a year ago

@Lucy82 try to replace someVar with :someVar and x with :NameofYourVariable

Lucy82 Over a year ago

no that doesn't work. And I can't find nowhere how to do that in SSRS for Oracle.

|

Barbaros Özhan · Accepted Answer · 2018-08-01 15:25:09Z

0

You don't need an IF statement, but use a DECODE statement inside a DUMMY SELECT as in the following code( It seems your second parameter is an integer as 0 / 1 ) :

begin
  select decode(:someVar,'Yes',1,0) into :someVar2 from dual;
end;
/

edited Aug 1, 2018 at 15:25

answered Aug 1, 2018 at 15:16

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Comments

Littlefoot · Accepted Answer · 2018-08-01 20:22:59Z

0

Why involving SELECT & DUAL at all? Wouldn't a simple

:someVar2 := case when :someVar = 'Yes' then 1
                  else 0
             end;

do?

answered Aug 1, 2018 at 20:22

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Collectives™ on Stack Overflow

Oracle - If variable is null

3 Answers 3

11 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

11 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related