Oracle DB: how to store function result into variable inside procedure

Question

Good day. I have a function:

create function get_n(search tt.pp%type)
  return number
  is rc number;
begin
  select count(*)
  into rc
  from tt
  where tt.pp=search;

  return (rc);
end;
/

and i can get result as

variable qwe number;

begin
  select get_n('sample')
    into :qwe
    from dual;
end;

print qwe;

So, it's successfully works. But by parts: i can't exec line with print at execution of other (PLS-00103: Encountered the symbol "PRINT"...). And it's really strange.

I try to get result from function in anonymous block and print it:

declare
  qwe number;
begin
  select get_n('sample')
    into :qwe
    from dual;
  dbms_output.put_line(qwe);
exception
  when others
    then dbms_output.put_line(sqlerrm);
end;
/

And it's not print anything. Why?

And yep, i can just := the value of the function, but why this doesn't works? — Evgeniy175
– Evgeniy175, Commented Dec 18, 2016 at 18:04
What's wrong with using the much simpler qwe := get_n('sample')? — user330315
– user330315, Commented Dec 18, 2016 at 22:40
@a_horse_with_no_name all is great with this, just want to understand what is wrong in this case — Evgeniy175
– Evgeniy175, Commented Dec 18, 2016 at 22:42

Kacper · Accepted Answer · 2016-12-19 08:20:19Z

7

Problem is :. Following code should work:

declare
  qwe number;
begin
  select get_n('sample')
    into qwe
    from dual;
  dbms_output.put_line(qwe);
exception
  when others
    then dbms_output.put_line(sqlerrm);
end;
/

: means variable that need to be binded not variable inside PL/SQL block.
And in case of first block you're missing / after PL/SQL block what causes compiler reads print as part of PL/SQL not SQLplus script:

variable qwe number;

begin
  select get_n('sample')
    into :qwe
    from dual;
end;
/

print qwe;

edited Dec 19, 2016 at 8:20

answered Dec 18, 2016 at 18:20

Kacper

4,8182 gold badges23 silver badges35 bronze badges

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

Collectives™ on Stack Overflow

Oracle DB: how to store function result into variable inside procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related