Mostly Making oracle easy, mostly:
Search for:
In ORDS

Parameters and Binds for your RESTful Services

3 Mins Read

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.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

5 Comments

  1. Stephen Robb 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.

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

      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

      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;

Write A Comment