x
login about faq Site discussion (meta-askssc)

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:

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
more ▼

asked Dec 12 '09 at 08:43 PM in Default

jcraig11 gravatar image

jcraig11
21 1 1 1

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

1 answer: sort voted first

adding

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 '09 at 10:59 PM

jcraig11 gravatar image

jcraig11
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

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x598
x340
x50

asked: Dec 12 '09 at 08:43 PM

Seen: 969 times

Last Updated: Dec 12 '09 at 08:43 PM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.