How to test an Oracle function that contains DML in PLSQL Developer?

Question

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

Answer 1

adding

pragma autonomous_transaction

to the function in the declare block allows it to be selected from dual

select find_person(arguments) from dual;

Since the DML in the function is simply for logging the arguments passed in to the function, it is an acceptable use of autonomous_transaction, but otherwise should be avoided

Answer 2

pragma_autonomous_transaction is one way.

but to test with out effecting your original database, there are few open source tools to test your SQL / PLSQLs like DBUNIT, utPLSQL etc.

these are unit testing tools for SQL and plsql

Answer 3

The test screen in PLSQL developer has two sections. In the upper part you will find the code you've shown in your question. The code that the test function has generated has replaced the variables of your function with bind variables: :as_surname, :as_given etc. In the lower part of the screen you can enter values for these parameters and view the value of the result.

Answer 4

I haven't worked with xmltype, but documentation gives the following option:

dbms_output.put_line(result.getStringVal());

Answer 5

change "result" to ":result" and click on the little arrow thingy in the top left corner of the variables grid. It should add "result" as a bind varibale and you can specify its type.

In your case the best options are clob or PL/SQL string.

And your script could look like so:

declare
  result xmltype;
begin
  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);
  if result is null then
    :result := null;
  else
    :result := result.GetClobVal();
  end if;
end;

As you can see, it is basically what PL/SQL Dev has created for you, except for the handling of how to return the xmltype in a way that PL/SQL Dev understands.

if you want to return a resultset, you can return cursors:

begin
  ...
  open :someCursor for 
    select 1 from ...;
  ...

You have to change the type of "someCursor" in the variables grid to "cursor" or it is not going to work.

Answer 6

SQL Developer works pretty well with DBMS_OUTPUT .. it has a separate tab for it alongside the script output, results etc. just click the little "enable output" button and use DBMS_Output.Put_Line(result); in your code.