Parameters and Binds for your RESTful 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.

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.