How to view return value of stored function containing DML?

[jwc]

Selecting the return value of an Oracle stored function that doesn't contain DML can be done by simply selecting the function:

select function_name() from dual;

If the function contains DML (in this case some inserts to log the arguments passed to the function), the above query is not allowed. (ORA-14551)

How can I select/view the return value of this function?

if I choose "test" in plsql developer, plsqldev produces something like:

declare
  -- Non-scalar parameters require additional processing
  result xmltype;
begin
  -- Call the function
  result := find_person(as_surname => :as_surname,
                       as_given => :as_given,
                       ad_birth_date_from => :ad_birth_date_from,
                       ad_birth_date_to => :ad_birth_date_to,
                       as_gender => :as_gender);
end;

How can I view the value of the "result" variable?

select result from dual;

inside the begin/end block produces

ORA-06550: PLS-00428: an INTO clause
is expected in this SELECT statement

 

[Marco Kalter]

You can change the PL/SQL Block of the Test Window like this:

begin
  -- Call the function
  :result := find_person(as_surname => :as_surname,
                         as_given => :as_given,
                         ad_birth_date_from => :ad_birth_date_from,
                         ad_birth_date_to => :ad_birth_date_to,
                         as_gender => :as_gender).getclobval;
end;

Note that the local 'result' variable is removed, that the :result variable is now a bind variable, and that the function's xmltype result is converted to a CLOB with the getclobval function. Now you can declare the :result variable as a Temporary CLOB to retrieve and view the XML text.