APEX REST POST

March 22, 2016 at 6:51 pm | Posted in Oracle Developement | 9 Comments

I’ve been struggling to get a RESTful POST request working through APEX. After some time of trial and error I finally got a working example going.

For this test I created a table to store the REST data:

create table rest_data (description varchar2(100), blob_value blob);

APEX Setup:

From the SQL Workshop menu, select RESTful Services. Click the Create button to create a new service, and configure it by simply giving it a name (cmr) and a URI Template (test/) (don’t forget the trailing slash).

Set the Resource Handler method to POST and Source Type to PL/SQL. Now the tricky part is about accessing the header variables and the payload of the POST request.

Header variables come across as bind variables, and can thus easily be used in PL/SQL. The payload, comes across as a BLOB, and is a little sensitive to handling.

 

The PL/SQL source then simply inserts into the table.

declare
 l_blob blob := :body;
begin
 insert into rest_data(description,blob_val) values (:description, l_blob);
end;

Note that assigning the :body bind variable is done in the declaration of the code. There is currently some odd behavior that if you assign it any other way, the blob length is zero.

After clicking Create Module, navigate edit the new POST handler. I set the Requires Secure Access to No, so I don’t have to mess with HTTPS.

 

Now add a parameter for the header variable. I added one for description, by clicking the Create Parameter button. Note that bind variable names are case sensitive.

That’s it. To test the service you can use a browser plugin like Postman.

The URL to test will be something like this: http://yourhost:port/ords/workspace/test/

Set your REST test client up with a DESCRIPTION header variable, and Content-Type as application/javascript.

The body of the request can be any sample JSON string.

Sending this POST request should insert a row into the rest_data table with the description and the JSON BLOB.

Eventually you’ll want to convert the JSON BLOB into a CLOB so you can process it with APEX_JSON. You can use  wwv_flow_utilities.blob_to_clob() to easily make that conversion:

declare
 l_blob blob;
 l_clob clob;
begin
 select blob_value into l_blob from rest_data;
 l_clob := wwv_flow_utilities.blob_to_clob(l_blob);
 apex_json.parse(l_clob);
end;

In order to formulate a response, you need to create an OUT parameter that either returns a header variable, or the response body.

Then add PL/SQL to the source to populate the response bind variable:

:response := 'Receipt OK.';

You can also print directly to the response page by using htp.p:

htp.p('<h3>Receipt OK.</h3>');