CODEWITHSUNDEEP
oracle-databaseplsql
Evgeniy175
Evgeniy175

Reputation: 334

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

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?

Upvotes: 3

Views: 8768

Answers (1)

Kacper
Kacper

Reputation: 4818

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;

Upvotes: 7

Related Questions