I have a package that contains a number of stored procedures that return data via a strongly type refcursor which I'd like to expose via ORDS.
I've enable autorest on the package and can see it in USER_ORDS_OBJECTS.
SQL> SELECT
2 parsing_schema,
3 parsing_object,
4 object_alias,
5 type,
6 status
7 FROM
8 user_ords_objects
9 WHERE
10 status = 'ENABLED'
11 AND
12 type = 'PACKAGE';
PARSING_SCHEMA PARSING_OBJECT OBJECT_ALIAS TYPE STATUS
--------------- --------------- --------------- --------------- ---------------
SCOTT PCK_REPORTING pck_reporting PACKAGE ENABLED
I made an educated guess based on autorest'd objects like tables that the base URL should look something like this: http://my_ords_host:my_ords_port/ords/scott/pck_reporting
So naturally I would assume that to access my sproc (we'll call it my_sproc), it would just be a case of adding it to the end of the URL http://my_ords_host:my_ords_port/ords/scott/pck_reporting/my_sproc
This doesn't work and instead you get a 404. I even tried to append my_sproc's strongly type refcursor parameter name (PROCEDURE my_sproc (p_output IN OUT rpt_strong_refcur)) i.e. http://my_ords_host:my_ords_port/ords/scott/pck_reporting/my_sproc/:p_output which also yielded a 404.
Why do I want to do this? I'm trying to avoid (read: feeling lazy) having to create a module to wrap the packaged stored sproc (see code fragment below):
BEGIN
ords.define_module(
p_module_name => 'wrapper',
p_base_path => 'wrapper/',
p_items_per_page => 0
);
ords.define_template(
p_module_name => 'wrapper',
p_pattern => 'my_sproc/'
);
ords.define_handler(
p_module_name => 'wrapper',
p_pattern => 'my_sproc/',
p_method => 'POST',
p_source_type => ords.source_type_plsql,
p_source => q'[BEGIN
:ct := 'application/json';
pck_reporting.my_sproc(:rc);
END;]'
,
p_items_per_page => 0
);
ords.define_parameter(
p_module_name => 'wrapper',
p_pattern => 'my_sproc/',
p_method => 'POST',
p_name => 'Content-Type',
p_bind_variable_name => 'ct',
p_source_type => 'HEADER',
p_access_method => 'OUT'
);
ords.define_parameter(
p_module_name => 'wrapper',
p_pattern => 'my_sproc/',
p_method => 'POST',
p_name => 'rc',
p_bind_variable_name => 'rc',
p_source_type => 'RESPONSE',
p_access_method => 'OUT',
p_param_type => 'RESULTSET'
);
COMMIT;
END;
/
After running the above script and performing a POST request against http://my_ords_host:my_ords_port/ords/scott/wrapper/my_sproc/ yields a JSON payload.
Update: This was for ORDS 3.0.9. So at the moment Kiran's answer is correct. As soon as the capability land in the next ORDS update I will either update this post or accept Jeff's answer.