Thanks for the question, Arthur.
Asked: July 30, 2016 - 9:54 pm UTC
Last updated: August 01, 2016 - 4:30 pm UTC
Version: 11.2.0.4
Viewed 1000+ times
You Asked
Hi Tom,
This question is about the named parameter syntax.
I've successfully called functions using this method:
v_order_id := CREATE_CUSTOMER_ORDER (
p_customer_id => p_customer_id,
p_total_amount => p_total_amount,
p_authorization_code => p_author_code,
p_transaction_id => p_trans_id,
p_credit_card_id => p_credit_card_id,
p_confirm => p_confirm);
However, in the example below I want to call a procedure, and return a REF CURSOR. I cannot seem to figure out how to do it, nor find any examples of this.
package_name.procedure_name (
p_rank => 5,
p_score => v_score,
p_data => :data)
p_data is defined as IN OUT and a REF CURSOR........
Can you help me with the syntax to perform this?
Thank you,
Arthur
and Chris said...
REF CURSOR isn't a type in itself. To pass these, you can either:
- Use the predefined sys_refcursor type
- Define your own type of REF CURSOR
For example, the following defined the parameter as a sys_refcursor. If you want to access the values in SQL*Plus / SQL Dev, pass a refcursor variable:
create or replace procedure p (cur in out sys_refcursor) is
begin
open cur for
select * from dual;
end p;
/
var c refcursor;
exec p(cur => :c);
print :c
C
-
DUMMY
-----
X
Or you could define a ref cursor type and declare a variable using it. Then get the results using PL/SQL:
declare
type t is ref cursor;
cur t;
l dual%rowtype;
begin
p(cur => cur);
fetch cur into l;
dbms_output.put_line(l.dummy);
end;
/
X
For further reading, see:
https://community.oracle.com/thread/888365 http://docs.oracle.com/database/121/LNPLS/static.htm#LNPLS00605