I have a very simple, and not very elegant stored procedure.
CREATE OR REPLACE PROCEDURE ADD_EMP ( P_EMP_NAME IN SIMPLE_EMP.name%TYPE, P_EMP_SALARY IN SIMPLE_EMP.SALARY%TYPE, P_OUT_ID OUT SIMPLE_EMP.id%TYPE, P_OUT_TOTAL OUT INTEGER ) AS BEGIN INSERT INTO SIMPLE_EMP (name, SALARY) VALUES (P_EMP_NAME, P_EMP_SALARY) RETURN id INTO P_OUT_ID; SELECT COUNT(id) INTO P_OUT_TOTAL FROM SIMPLE_EMP; EXCEPTION WHEN OTHERS THEN HTP.print(SQLERRM); END;
Sidebar: Like, I’m not a professional developer. The WHEN OTHERS THEN bit…that’s BAD CODE, don’t do that. I’m trying to talk about something else though, so don’t pay attention to that.
This stored procedure takes in a name and salary. It inserts a record to my table, and returns the new employee’s ID and the new total number of employees in my company.
My app needs to know what the new employee’s ID is so it can go find it. The ID is generated by a 12c Identity Clause in the table definition.

Now, I want to execute this stored procedure via REST.
Let’s create a RESTful Service via ORDS.
I want a new module, and I’m calling it ‘rpc.’ I’ll have a single template in there called ‘AddEmp’, and it’s going to have a single POST Handler.
The database code behind this service is VERY simple. I’m just calling a stored procedure.
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;
P_EMP_NAME and P_EMP_SALARY binds are important. To get the data to my stored procedure, I’m going to POST up some JSON. That looks like this:
Ok, so that gets the data in.
How does the data get out?
Let’s look at the code behind the module again. We’re calling ADD_EMP and we’re passing the 2 outputs to these binds:
- :NEW_EMPID
- :TOTAL
We need to add/define module handler parameters for those two things. If you’re using the PL/SQL API, they’re going to be the p_bind_variable_name bits.
I really like using our new REST module editor in the database tree though, so I’ll toggle to the parameters page.

The Name of the parameter determines how the JSON is constructed when returned.
Before we run this, a few things I glossed over:
- I set the mime type to ‘application/json’ – bc I want my output to be interpreted as JSON and not HTML for example
- You’ll need v4.2 to do this in the DB tree, otherwise you can use the REST Development panel and your ‘sql developer’ ORDS user
Ok, let’s make the call.

And it did the work, we got the output, but I’m paranoid, let’s go check out table.

P.S. This is about to get easier.
Think, AUTO REST end points for your stored procedures. No need to create a module and POST handlers. And we’ll grab the outputs automatically for you. Something to look forward to in a future ORDS release.
5 Comments
Thanks, that did the trick. I appreciate your help and patience. Once I knew body_text existed I searched for it and found the very clear Oracle documentation, including the fact that the bind variable can only be used once.
Thanks again.
Hi
I have been using the ORDS UI within APEX to manage my Restful services. I am really stuck on one handler though. It’s receiving a JSON block in a pre-defined message format. I can get the JSON data from the message and add it to a table in the database using an INSERT statement, but when I try to use the same bind variable in a call to a stored procedure nothing (literally nothing) happens when the data is received.
The call to the procedure works if I run this manually in SQL Developer, but when the handler is called from outside, the procedure is not called
begin
INSERT INTO InboundMessages (MessageSource, MessageBody, MessageReceivedTime) VALUES (‘ABC’, :body, CURRENT_TIMESTAMP);
/*
PROCESSINBOUNDMESSAGE(
p_MsgSource => ‘ABC’,
p_MsgBody => :body);
*/
—
:status := 200;
—
exception
when others then
:status := 400;
end;
THis works, but if I comment out the INSERT and uncomment the procedure call, nothing happens.
Declare a local BLOB, and init it to the :body and reference that in your two calls.
You can only refer to :body once.
Thanks, I appreciate the quick reply. Apologies if I’m missing something simple.
I’ve done that, and it’s working when run from SQL Developer, but still not from incoming data via ORDS:
declare
l_Result number := 200;
l_Body clob := :body;
begin
l_Result := PROCESSINBOUNDMESSAGE(
p_MsgSource => ‘ABC’,
p_MsgBody => l_Body);
—
:status := l_Result;
—
exception
when others then
:status := 400;
end;
l_body should be a BLOB, not a CLOB
if you want a CLOB, then use :body_text vs :body