Hello Everyone
I have a situation where we need to use User defined data Type and we don't use Auto REST feature. We are not sure how the return type of UDT in fuction or OUT from Procedure can be handle by ORDS. Return from cursor works fine with RESULTSET parameter. I am assuming AUTO pl/sql have some features that I am not aware of.
Here is an example.
We have following Types created.
CREATE OR REPLACE TYPE emp_det_obj AS OBJECT
(
empno NUMBER (4),
ename VARCHAR2 (10 BYTE),
job VARCHAR2 (9 BYTE),
sal NUMBER (7, 2)
);
/
create or replace type emp_det_tab is table of emp_det_obj;
/
create or replace function emp_details return emp_det_tab is
l_emp_det_tab emp_det_tab;
And Procedure as
CREATE OR REPLACE PROCEDURE proc_emp_details (p_emp_det OUT emp_det_tab)
IS
--l_emp_det_tab emp_det_tab;
BEGIN
SELECT emp_det_obj (empno => e.empno,
ename => e.ename,
job => e.job,
sal => e.sal)
BULK COLLECT INTO p_emp_det
FROM emp e;
--return l_emp_det_tab; — when used as function
end proc_emp_details;
/
BEGIN
ords_admin.enable_schema (p_enabled => TRUE,
p_schema => 'my_schema',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'abc3',
p_auto_rest_auth => FALSE);
ORDS_admin.define_module (p_schema => 'my_schema',
p_module_name => 'testprcudt',
p_base_path => 'testprcudt/',
p_items_per_page => 0);
ORDS_admin.define_template (p_schema => 'my_schema',
p_module_name => 'testprcudt',
p_pattern => 'proc_emp_details');
ORDS_admin.define_handler (p_schema => 'my_schema',
p_module_name => 'testprcudt',
p_pattern => 'proc_emp_details',
p_method => 'POST',
p_source_type => ORDS.source_type_plsql,
p_source => '
BEGIN
proc_emp_details(:l_emp_det_tab);
END;' ,
p_items_per_page => 0);
ORDS_admin.define_parameter (p_schema => 'my_schema',
p_module_name => 'testprcudt',
p_pattern => 'proc_emp_details',
p_method => 'POST',
p_name => 'l_emp_det_tab',
p_bind_variable_name => 'l_emp_det_tab',
p_source_type => 'RESPONSE',
p_param_type => 'RESULTSET', --- What param type should be used here? Document shows only STRING, INT, DOUBLE, BOOLEAN, LONG, TIMESTAMP (not even RESULTSET, perhaps comes with new document)
p_access_method => 'OUT');
COMMIT;
END;
/
This does not work. How can we have function/procedure that output UDT to be shown using ORDS? Also if there are nested UDT will it be different way to handle?
Thanks,
Rajan