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.

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


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:

 l_blob blob;
 l_clob clob;
 select blob_value into l_blob from rest_data;
 l_clob := wwv_flow_utilities.blob_to_clob(l_blob);

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>');






RSS feed for comments on this post. TrackBack URI

  1. […] Anton Nielsen provided a great tip for troubleshooting apex_web_service in apex_web_service.make_rest_request not working with POST. […]

  2. I want to thank you immensly for you help, I was able to implement what I needed.
    Have a beautiful day, If I can send you $, I would love to know that I bought you a cold beer in gratitude. rmander.ca@gmail.com

  3. You Rock!!!

  4. Thanks for the great post

  5. Hi Christoph, your post helped me a lot and saved me a lot of time. I owe you a beer at KScope18. See you there,
    Thanks, Dick

  6. could you please add more info how to handle json object after parsing it in shared example :

    l_blob blob;
    l_clob clob;
    select blob_value into l_blob from rest_data;
    l_clob := wwv_flow_utilities.blob_to_clob(l_blob);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Entries and comments feeds.

%d bloggers like this: