x

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, 2009 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, 2009 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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

SQL Server Central

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

Topics:

x715
x408
x50

asked: Dec 12, 2009 at 08:43 PM

Seen: 1478 times

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