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](https://stackoverflow.com/a/40360471/230471), 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, Aug 01 '18 at 15:29

hotfix · Answer 1 · 2018-08-01T12:37:42.487

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 01 '18 at 12:37

answered Aug 01 '18 at 12:20

hotfix

3,328
17
30

thanks for immediate answer, but that produces me same error as described, in Toad. What else could be wrong ? – Lucy82 Aug 01 '18 at 12:22
how do you call that block? i have no errors by executing it in a database – hotfix Aug 01 '18 at 12:25
@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. – Lucy82 Aug 01 '18 at 12:30
@Lucy82 try to replace someVar with `:someVar` and x with `:NameofYourVariable` – hotfix Aug 01 '18 at 12:32
no that doesn't work. And I can't find nowhere how to do that in SSRS for Oracle. – Lucy82 Aug 01 '18 at 12:35
and someVar is actually my report parameter which is being passed to Oracle, so I don't need to declare it. – Lucy82 Aug 01 '18 at 12:36
what about the output parameter? – hotfix Aug 01 '18 at 12:38
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/177190/discussion-between-hotfix-and-lucy82). – hotfix Aug 01 '18 at 12:39
im not sure why I would need this. All I need is only to change 2nd paramter value in report when first paramter value is 'Yes'. There are plenty of examples for that in SSRS threads, but none work with Oracle. At least for Reporting Services version 10. – Lucy82 Aug 01 '18 at 12:41
The answer as posted is valid PL/SQL assuming the two host variables `:someVar` and `:var` are defined, so the problem must be in Toad or SSRS (neither of which I have, so I can't advise on those). However, you wouldn't use a `select from dual` to copy one variable to another, so I am wondering if what you are actually trying to do is return a cursor. – William Robertson Aug 01 '18 at 15:23
@hotfix, this compiles in Oracle but nothing happens in SSRS until I add a field to Dataset. But when I do that I receive error : **The report parameter ‘var’ has a DefaultValue or a ValidValue that depends on the report parameter “var”. Forward dependencies are not valid.** So I'm still stuck... – Lucy82 Aug 02 '18 at 05:04

Barbaros Özhan · Answer 2 · 2018-08-01T15:25:09.430

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 01 '18 at 15:25

answered Aug 01 '18 at 15:16

Barbaros Özhan

39,060
9
21
45

score 0 · Answer 3 · answered Aug 01 '18 at 20:22

0

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

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

do?

answered Aug 01 '18 at 20:22

Littlefoot

78,293
10
26
46

Oracle - If variable is null

3 Answers3