How do I get the return value from a function in Oracle using Toad

Question

How do I find out in Toad what the return value of a function is?

I'm running something like this code:

declare r number;
begin
    r:= packagename.functionname(paraname);
end;

I can't work out how to get "r" returned to the data grid, some posts suggest using DBMS output but nothing is written to it when I run the code.

The function performs updates, commits, calls other functions and has cursors in it.

Metametabel

Answer 1

begin
    dbms_output.put_line(packagename.functionname(paraname));
end;

You'll need to turn output on before running this. To do that, select the "DBMS Output" tab at the bottom of the editor, then click the leftmost button under the tab (it should depict a red circle, with the tooltip "Turn Output On" (if it's a green circle, output is already on)).

The results of the query will be written to the "DBMS Output" window, not the "Data Grid" (you may have to wait a few seconds for the polling to pick up the results). If you use a user-defined type or a ref cursor, this will be insufficient and you'll need to process the results in the anonymous block before writing them out.

Chitkara

Also you can turn on "Automatically poll for output after execution" in the same area at the bottom. – Tareyn

Answer 2

How about just:

select packagename.functionname(paraname) from dual; 

Finish

Tried it but get: "ORA-14552 cannot perform a DDL, commit or rollback inside a query or DML." Edited post to provide more info. – Metametabel

Answer 3

The answer from Klaus of

select packagename.functionname(paraname) from dual;

worked for me but it only returned a cursor. This displayed as (CURSOR) in the data grid in Toad. Once I double clicked on that, it opened a new windows with a data grid for that cursor and I saw my results.

Antifebrile

This is more like a comment than a new answer to the question – Boswall