Yesterday I talked about how to grab the output returned from your stored procedure when executing via Oracle REST Data Services.
The example was:
INSERT a new record into a table. Return the new employee ID and the total number of employees in the table.
So we just needed 2 parameters for our handler to catch the two integers returned, easy-peasy.
But, what if we were going to return the entire record?
So let’s write the stored procedure first, again not using my PL/SQL as anything other than the most basic of examples…
CREATE OR REPLACE PROCEDURE ADD_EMP2 ( P_EMP_NAME IN SIMPLE_EMP.name%TYPE, P_EMP_SALARY IN SIMPLE_EMP.SALARY%TYPE, P_OUT_EMP OUT sys_refcursor, P_OUT_TOTAL OUT INTEGER ) AS NEW_ID INTEGER; BEGIN INSERT INTO SIMPLE_EMP (name, SALARY) VALUES (P_EMP_NAME, P_EMP_SALARY) RETURN id INTO NEW_ID; OPEN P_OUT_EMP FOR SELECT * FROM SIMPLE_EMP WHERE ID = NEW_ID; SELECT COUNT(id) INTO P_OUT_TOTAL FROM SIMPLE_EMP; EXCEPTION WHEN OTHERS THEN HTP.print(SQLERRM); END;
Now we need to create our RESTful Services Template (AddGetEmp) and Handler (POST).
So the URI Module is ‘/rpc’ and the URI Pattern is ‘AddGetEmp’
The code behind the RESTful Service is very basic, same as before, except now we need to ‘catch’ the ID and refcursor.
BEGIN ADD_EMP2(P_EMP_NAME => :P_EMP_NAME, P_EMP_SALARY => :P_EMP_SALARY, P_OUT_EMP => :NEW_RECORD, P_OUT_TOTAL => :TOTAL); COMMIT; END;
And now we need to define the parameters, one for :NEW_RECORD, and one for :TOTAL.
Now we SAVE the definition – don’t forget this step or your testing will result in some very nice and frustrating 404’s (also not forgetting to check the ‘publish’ box when creating the module to begin with!)
Now I’ll right click to export my Module to PL/SQL and share the entire thing with you:
-- Generated by Oracle SQL Developer REST Data Services 4.2.0.17.065.2202 -- Exported REST Definitions from ORDS Schema Version 3.0.9.348.07.16 -- Schema: HR Date: Fri Mar 10 09:37:59 EST 2017 -- BEGIN ORDS.ENABLE_SCHEMA( p_enabled => TRUE, p_schema => 'HR', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'peeps', p_auto_rest_auth => FALSE); ORDS.DEFINE_MODULE( p_module_name => 'rpc', p_base_path => '/rpc/', p_items_per_page => 25, p_status => 'PUBLISHED'); ORDS.DEFINE_TEMPLATE( p_module_name => 'rpc', p_pattern => 'AddEmp', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'rpc', p_pattern => 'AddEmp', p_method => 'POST', p_source_type => 'plsql/block', p_items_per_page => 0, p_mimes_allowed => 'application/json', p_source => 'declare -- new_empID NUMBER; BEGIN ADD_EMP(P_EMP_NAME => :P_EMP_NAME, P_EMP_SALARY => :P_EMP_SALARY, P_OUT_ID => :NEW_EMPID, P_OUT_TOTAL => :TOTAL); commit; END;' ); ORDS.DEFINE_PARAMETER( p_module_name => 'rpc', p_pattern => 'AddEmp', p_method => 'POST', p_name => 'ID', p_bind_variable_name => 'new_empID', p_source_type => 'RESPONSE', p_param_type => 'INT', p_access_method => 'OUT'); ORDS.DEFINE_PARAMETER( p_module_name => 'rpc', p_pattern => 'AddEmp', p_method => 'POST', p_name => 'NumofEmps', p_bind_variable_name => 'TOTAL', p_source_type => 'RESPONSE', p_param_type => 'INT', p_access_method => 'OUT'); ORDS.DEFINE_TEMPLATE( p_module_name => 'rpc', p_pattern => 'AddGetEmp', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'rpc', p_pattern => 'AddGetEmp', p_method => 'POST', p_source_type => 'plsql/block', p_items_per_page => 0, p_mimes_allowed => 'application/json', p_source => 'begin ADD_EMP2(P_EMP_NAME => :P_EMP_NAME, P_EMP_SALARY => :P_EMP_SALARY, P_OUT_EMP => :NEW_RECORD, P_OUT_TOTAL => :TOTAL); commit; END;' ); ORDS.DEFINE_PARAMETER( p_module_name => 'rpc', p_pattern => 'AddGetEmp', p_method => 'POST', p_name => 'EMP', p_bind_variable_name => 'NEW_RECORD', p_source_type => 'RESPONSE', p_param_type => 'RESULTSET', p_access_method => 'OUT'); ORDS.DEFINE_PARAMETER( p_module_name => 'rpc', p_pattern => 'AddGetEmp', p_method => 'POST', p_name => 'NumOfEmps', p_bind_variable_name => 'TOTAL', p_source_type => 'RESPONSE', p_param_type => 'INT', p_access_method => 'OUT'); COMMIT; END;
The $LINK Trick Works Here, Too
Remember when we talked about using $notation to indicate to ORDS we were working with links?
In that example, we were using GET Handlers with SQL statements.
Today, someone asked about POST and PL/SQL – could they still use this ‘trick’ to generate a link?
The answer is, ‘Yes.’
One way to go about it is this – have an OUT parameter for your RESTful Service POST Handler that is of type ‘RESULTSET’ – what we’ve been talking about here. And again, we’re going to have a refcursor. But, we’re going to ‘inject’ an extra column into the SQL driving our cursor. Instead of a SELECT * FROM for P_OUT_EMP, we’re going to have…
OPEN P_OUT_EMP FOR SELECT id, name, salary, '/stuff/link' "$goto" FROM SIMPLE_EMP WHERE ID = NEW_ID;
Now, when our store procedure output is returned on the POST Response Body, we get –
{
"EMP": [
{
"id": 1,
"name": "JEFF",
"salary": 2500,
"links": [
{
"rel": "goto",
"href": "http://localhost:8080/stuff/link"
}
]
}
],
"NumOfEmps": 1
}
37 Comments
hi ,
i tried the above steps but i faced error
PLS-00306: wrong number or types of arguments in call to ‘ADD_EMP2’
any help please
It’s possible my 4+ year old example is riddled with bugs, you have my apologies in advance. I’ll try to get this updated if necessary tomorrow/ASAP.
hello ,
i tried it via postman and its work 🙂
error appear in sql developer
thanks for your effort and help
Hi Jeff, is there a way to use the POST (with parameters In the body) in order to obtain a result similar to the one obtained with GET method ? I need to obtain something like the result of “select * from emp where empno = “. Thank you very much. The more details, the better, I’m new to ORDS and APEX.
Are you calling this POST from APEX?
Otherwise APEX isn’t really in play, and I’ll just talk about the ORDS component.
Take a look at this post, I use a POST to run a sql statement, using a :bind, to get the query result back in a JSON response. The very first example.
I blog quite often and I seriously appreciate your information. This article has truly peaked my interest.
I’m going to bookmark your website and keep checking for new details about once a week.
I subscribed to your Feed as well.
Information is useful, But i have problem using this response in oracle Apex 20.1 using rest datsource.. Can you please point me any example of using this response in Oracle APEX, I get Error:ORA-06592:CASE not found while executing.
Thanks Jeff.
Using what response, exactly?
Also, best to ask APEX questions to the APEX team.
I am using
SQL Developer : Version 4.1.4.21 .
Extension: Oracle SQL Developer – RESTful Services Administration oracle.sqldeveloper.rest 12.2.0.21.21
Unable to export all source code using right click on modules.
what update should I do ?
your sqldev is many years old – start with that. today’s version is 20.2
This site was… how do I say it? Relevant!! Finally
I’ve found something which helped me. Thank you!
Awesome post.
Hi Jeff,
Thanks for wonderful articles you have.
I have a situation where we need to use User defined Type and I tried your method and it won’t work unless Auto REST is enabled. We don’t want to enable Auto REST in our case.
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 => ‘MA_REVIEW_STG’,
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’,
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?
Thanks,
Rajan
is there a way to use resultset to return an array of type. I have a piplined functions and in high concurrency, there’e a win in cpu with static cursors. i can’t do simple select with cursor i have to do pl/sql code to check parameters…
Can you give me any kind of example of what you’re looking to post up and the kind of response you want, as well as some sample pl/sql code you’ll be using?
Hi Jeff,
unfortanly i can’t call a pipelined function from pl/sql bloc. So my get webservice can’t be pl/sql one. If i do the check of parameters in pipelined function like this
CREATE or replace FUNCTION employees_piped (in_deptno NUMBER)
RETURN employees_ntt PIPELINED AS
custom_exception EXCEPTION;
BEGIN
if(in_deptno > 100) THEN
RAISE custom_exception;
— return;
END IF;
FOR r IN (SELECT * FROM employees where department_id = in_deptno) LOOP
PIPE ROW (employees_ot(
r.employee_id, r.first_name, r.last_name,
r.email, r.phone_number, r.hire_date,
r.job_id, r.salary, r.commission_pct,
r.manager_id, r.department_id));
END LOOP;
RETURN;
Exception
WHEN custom_exception then
owa_util.status_line (400, ‘invalid parameters’, false);
owa_util.http_header_close;
htp.p(‘invalid parameter’);
END employees_piped;
/
this code raise error 500
the get service type is sql with select …table(function)…
so how to do check parameters if i call pipelined function
if i do with sys_refcursor i can modify the type of the get to pl/sql code, do the check and call a function who return sys_refcursor…
is there a real gain of using pipelined over then sys_refcursor with relatively small dataset returned by webservices. (static cursor vs dynamic) i have a webservices that can have a 2000 simultanious call .
Sorry, you’ve lost me…I’m not sure what you’re trying to achieve with your service. Start there.
Also when you say you get a 500..that only means that ORDS has encountered a ORA or PLS error, check your ords output log or turn on debug mode, so you can see what’s going wrong and fix it.
i write this document to explain this case
https://docs.google.com/document/d/1kyb8aPO4CEQAGIqtkEb6jiPO5dduI-M5MFI0NvYc5PY/edit?usp=sharing
Fantastic blog! Do you have any tips and hints for aspiring writers?
I’m hoping to start my own site soon but I’m a little lost on everything.
Would you propose starting with a free platform like WordPress or
go for a paid option? There are so many options out there
that I’m completely confused .. Any ideas? Many thanks!
Really helpful…! and perfect solution
Hello Jeff,
My Oracle stored procedure is being integrated with java backend service using ORDS.
The problem I am facing is, I have some set of input parameters for my stored procedure which has traditional naming convention like
p_firstname
p_lastname
p_postcode
etc.
But the Java service is having them as(as they don’t follow these naming conventions)
forename
surname
postcode
Due to this I am facing problem as I am not able to capture the input from them due to different parameter names.
And ofcourse, we cannot use positioned notation instead of named notation because input is in json.
So it has to be key-value thing. So it has to be like
“forename”:”Dennis”
“surname”:”Roche”
“postcode”:”BD12BN”
Can you suggest anything to handle this without breaking the input parameter naming convention at my end?
I don’t understand the problem I guess. Isn’t there code that interfaces your java app with the REST api? Can that code not handle going from p_firstname to forename?
So it has to be key-value thing. So it has to be like
“forename”:”Dennis”
“surname”:”Roche”
“postcode”:”BD12BN”
WHAT has to be that way? The response from ORDS or the POST body on a execute?
Thanks Jeff.
“WHAT has to be that way? The response from ORDS or the POST body on a execute?”
The POST body on a execute has to be that way.
“forename”:”Dennis”
“surname”:”Roche”
“postcode”:”BD12BN”.. This is the POST body and my Oracle SP has IN parameters like p_firstname, p_lastname, p_postcode…
“ Isn’t there code that interfaces your java app with the REST api? Can that code not handle going from p_firstname to forename?”
Sorry, I couldn’t understand this part. Is this something that you are thinking that should be there at my end(Oracle) or Java end?
I can possibly create a package or a wrapper procedure but that would run into the same issue as my wrapper procedure would be like:
CREATE OR REPLACE PROCEDURE prc_test_ords_wrapper (
forename VARCHAR2,
surname VARCHAR2,
postcode VARCHAR2
) AS
BEGIN
prc_get_ords_org(p_first_name => forename, p_surname => surname, p_postcode => postcode);
END;
So it is like somewhere I am bound to use their parameter name(due to key-value thing in POST body for my proc) which would break my naming convention.
That’s my question all about.
How to use PLSQL table with ORDS. when using 11gR2 database. I tried creating a REST service PL/SQL block based and wrote a package procedure with PL/SQL table (array) one parameter.when i call it in REST handler i get error wrong type argument.
Following is the code
create or replace PACKAGE PKG1 AS
TYPE vc_arr is table of varchar2(30) index by BINARY_INTEGER;
PROCEDURE INS_C
(
P_NAME IN vc_arr
);
/* TODO enter package declarations (types, exceptions, methods etc) here */
END PKG1;
create or replace PACKAGE body PKG1 AS
PROCEDURE INS_C
(
P_NAME IN vc_arr
) AS
BEGIN
FOR I IN 1..p_name.count LOOP
INSERT INTO contact (
name,
phone,
column2
) VALUES (
p_name(i),
null
,null
);
end loop;
END INS_C;
end;
REST handler code :
begin
pkg1.ins_c(:p_name);
end;
I answered your question on the forums.
But with auto feature, it was pretty easy.
Hi Jeff
Is there any way if we are using htp.p to return formatted JSON to ORDS to use the $link functionality for links?
From my simple tests it looks like it will not be interpreted on the way out.
Thanks,
Adrian
Hi Jeff,
I forgot to mention that I use PL/Sql to gererate my json Data using GET. I think the correct Question is how to make Pagination using Pl/sql Source Typ for GET method?
Regards
Pierre
You’d have to code it yourself…we don’t have this built into the service.
Hi Jeff,
Thanks for the useful informations. It help me a lot. Ords is great.
… Do you know how to make Pagination with Resulset? Is there a Build-in method to paginate my Service, when I use Resulset?
Regards
Pierre.
Hi Jeff,
Thank again. I will check that.
I have another question is about how to handle null and empty value. When the Service give me something like that
{
empno:1,
ename:null
}
How to hide ename when value is null or empty?
Regards
Pierre
You can’t…not unless you code your service to check for ename, and only it it’s not null, to include it in your query..but that’s a lot of work. You can however replace the null using a NVL() function call in your SQL code, or just have your application handle it.
Hi Jeff,
“You can’t…not unless you code your service to check for ename, and only it it’s not null, to include it in your query.”
How to make that? I have tried , but not works.
Regards
Pierre
I started on your answer here. I’ll work on the all or nothing solution for you later today.
Hi Jeff,
thank for the Useful Information. It help me. I have another Question. How to use Resulset to make nesting? Like so for example
{
EMP: [
{
id: 1,
name: 1,
salary: 900,
dept: deptnodata: [
{
DEPTNO: 1,
DEPTNAME: ‘hallo’
},
{
DEPTNO: 2,
DEPTNAME: ‘hallob’
}
]
},
{
id: 832635,
name: 2,
salary: 900,
dept: deptnodata: [
{
DEPTNO: 40,
DEPTNAME: ‘halloc’
},
{
DEPTNO: 144,
DEPTNAME: ‘hallod’
}
]
}
]
}
Thanks
Pierre
Here’s a SQL solution, or one idea on how to do it.
Hi Jeff,
Thank for the Answers. Is that method better that using APEX_JSON API? Because it is possible to create nested json with apex_json. What is the best method? I ask because we have more than 10000 Data for this Service.
Thanks
Pierre
Better? No idea.
Try both, test the execution plans. Test with different pagesizes.