31

I created a function in oracle that inserts records in specific tables and return an output according to what occurs within the function. e.g (ins_rec return number)

How do I call this function and see its output in sql plus

MrCodeWeaver
  • 425
  • 10
  • 22
maher
  • 391
  • 1
  • 4
  • 4

3 Answers3

38
declare
  x number;
begin
  x := myfunc(myargs);
end;

Alternatively:

select myfunc(myargs) from dual;
GriffeyDog
  • 8,186
  • 3
  • 22
  • 34
  • 1
    `select myfunc(myargs) from dual;` worked for me. I had issues with the `delcare` version. – claptimes Jul 11 '14 at 14:34
  • I have a function that calls a procedure. If I use this method to call the function, I get a DML write exception. If I use the "exec dbms_output...." call, it works without errors – user1747935 Aug 04 '15 at 20:00
  • 4
    You can't use a function that _modifies data_ in a `select` statement. – GriffeyDog Aug 04 '15 at 20:30
  • 1
    @Alex78191 `select` statements are meant to be non-DML, i.e. perform no updates to data. The error you get would be `ORA-14551: cannot perform a DML operation inside a query`. – GriffeyDog Nov 27 '18 at 15:58
12

One option would be:

SET SERVEROUTPUT ON

EXEC DBMS_OUTPUT.PUT_LINE(your_fn_name(your_fn_arguments));
cagcowboy
  • 30,012
  • 11
  • 69
  • 93
5

As another answer already said, call select myfunc(:y) from dual; , but you might find declaring and setting a variable in sqlplus a little tricky:

sql> var y number

sql> begin
  2  select 7 into :y from dual;
  3  end;
  4  /

PL/SQL procedure successfully completed.

sql> print :y

         Y
----------
         7

sql> select myfunc(:y) from dual;
CoderSteve
  • 629
  • 5
  • 16