Parameters and Binds for Your Oracle REST Data Services

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.

You don’t need to manually create triggers and sequences anymore – the database can do that for you in 12c and higher.

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:

WizTools REST Client and a POST to ORDS — The json attribute names need to match your input binds for the SQL in your module.

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 bind parameter name needs to MATCH the :bind in your SQL code.

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.

The JSON attribute names aren’t an accident – they’re my parameter names.

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

sql developer table data — TOTALLY RANDOM employee names and salaries.

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.

Share on Facebook

5 Comments

Stephen Robb 2 years ago Reply

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.
Stephen Robb 2 years ago Reply

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.
- thatjeffsmith Post Author 2 years ago
  
  Declare a local BLOB, and init it to the :body and reference that in your two calls.
  
  You can only refer to :body once.
- Stephen Robb 2 years ago
  
  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;
- thatjeffsmith Post Author 2 years ago
  
  l_body should be a BLOB, not a CLOB
  
  if you want a CLOB, then use :body_text vs :body