How to view return value of Oracle stored function containing DML in PLSQL Developer?

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:

  -- Non-scalar parameters require additional processing 
  result xmltype;
  -- 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);

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

more ▼

asked Dec 12, 2009 at 08:43 PM in Default

avatar image

21 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first


pragma autonomous_transaction

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

select find_person(arguments) from dual;

more ▼

answered Dec 12, 2009 at 10:59 PM

avatar image

21 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 12, 2009 at 08:43 PM

Seen: 1840 times

Last Updated: Dec 12, 2009 at 08:43 PM

Copyright 2018 Redgate Software. Privacy Policy