Parameters and Binds for Your Oracle REST Data Services

Update: This post was refreshed on October 14, 2021.

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. You should do something, more like THIS.

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 Identity Clause in the table definition.

CREATE TABLE "SIMPLE_EMP" 
   (	"ID" NUMBER(*,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER  NOCYCLE  NOT NULL ENABLE, 
	"NAME" VARCHAR2(256 BYTE) NOT NULL ENABLE, 
	"SALARY" NUMBER(9,2) NOT NULL ENABLE, 
	"HIRE_DATE" TIMESTAMP (6), 
	 CONSTRAINT "SIMPLE_EMP_PK" PRIMARY KEY ("ID"));
 
REM INSERTING INTO SIMPLE_EMP
SET DEFINE OFF;
INSERT INTO SIMPLE_EMP (NAME,SALARY,HIRE_DATE) VALUES ('Mike',188,to_timestamp('21-JAN-17 09.29.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO SIMPLE_EMP (NAME,SALARY,HIRE_DATE) VALUES ('Joel',1,to_timestamp('19-JUN-17 09.29.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO SIMPLE_EMP (NAME,SALARY,HIRE_DATE) VALUES ('Shaq',1,to_timestamp('19-JUN-17 09.29.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO SIMPLE_EMP (NAME,SALARY,HIRE_DATE) VALUES ('Bob',1,to_timestamp('19-JUN-17 09.29.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO SIMPLE_EMP (NAME,SALARY,HIRE_DATE) VALUES ('Bart',0,to_timestamp('20-JUN-17 09.29.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO SIMPLE_EMP (NAME,SALARY,HIRE_DATE) VALUES ('David',1,to_timestamp('19-JUN-17 09.29.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO SIMPLE_EMP (NAME,SALARY,HIRE_DATE) VALUES ('Rene',1,to_timestamp('19-JUN-17 09.29.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO SIMPLE_EMP (NAME,SALARY,HIRE_DATE) VALUES ('Andres',1,to_timestamp('19-JUN-17 09.29.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO SIMPLE_EMP (NAME,SALARY,HIRE_DATE) VALUES ('Andre',1,to_timestamp('19-JUN-17 09.29.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO SIMPLE_EMP (NAME,SALARY,HIRE_DATE) VALUES ('Bobby',1,to_timestamp('19-JUN-17 09.29.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'));
COMMIT;

Now, I want to execute this stored procedure via HTTP(S).

In this post, to do that, we’re going to create a POST (PLSQL) Handler on a REST Module Template, and we’ll write the code required to kick off that PL/SQL.

I already have a module for questions I get from email/web/wherever, I’m just going to create a new TEMPLATE (URI) on that module. I’ll call it ‘rpc/addemp.’

The database code behind this service is VERY simple. I’m just calling the stored procedure.

BEGIN
    ADD_EMP(P_EMP_NAME     => :P_EMP_NAME,   -- can be read directly off of POST BODY
            P_EMP_SALARY   => :P_EMP_SALARY, -- can be read directly off of POST BODY
            P_OUT_ID       => :NEW_EMPID, -- will require an ords parameter (OUT)
            P_OUT_TOTAL    => :TOTAL);    -- will require an ords parameter (OUT)
COMMIT;
 :status_code := 201;
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:

The json attribute names need to match your input binds for the SQL in your module.

These top level POST request JSON attributes get referenced via corresponding :binds in our handler PL/SQL Block. So I don’t need to create those.

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 define POST handler parameters for those two items. If you’re using the PL/SQL API, they’re going to be the p_bind_variable_name bits.

In our REST Web IDE, parameters are defined alongside our Handler code.

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.

Two parameters, that represent our two OUT values from calling the stored procedure, and we’re going to put BOTH of those on the Body of the Response. Note, we can NAME the parameter anything we want, but how we name it will determine how it will be seen in the response.

The :BINDs must be named exactly as you’re using them in your PL/SQL block.

Ok, let’s make the call, and see our response.

The JSON attribute names aren’t an accident – they’re my PL/SQL IN parameters.

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

TOTALLY RANDOM employee names and salaries.

Why write any code, at all?

This post was originally published in 2017, BEFORE the AUTO feature existed. Well, now it does. So I can enable my procedure, and then just call it. No code to write.

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

Parameters and Binds for your RESTful Services

How does the data get out?

Why write any code, at all?

5 Comments

Write A Comment Cancel Reply

How does the data get out?

Why write any code, at all?

Related Posts

5 Comments

Write A Comment Cancel Reply