In previous blog we have seen how we can enable REST services in database schema using ORDS. Also we have created RESTfull API to get the customer details.
Previous Blog: Building REST APIs using SQL Developer & ORDS
In this blog we will build the APIs – POST Method to insert a record & Delete Method – delete record in database using REST Services.
Building RESTfull API – POST Method
Check the connection: Go to Rest Development Tab & Click on connect.

Step 1: Create New Module
Provide the module name & URI prefix. Check the Publish check box. Pagination is set to 5 for testing purpose. Click on next.

Step 2: Provide URI Pattern here. Click on next.

Step 3: Select the method handler as ‘POST‘

Open the POST method, go to SQL Worksheet. Enter the PL/SQL Block.


DECLARE ln_cust_id CUSTOMERS.CUSTOMER_ID%TYPE; BEGIN Insert into CUSTOMERS (EMAIL_ADDRESS,FULL_NAME) values (:EMAIL_ADDRESS,:FULL_NAME) RETURNING CUSTOMER_ID INTO ln_cust_id; commit; :string_out := 'Record Inserted. Note Customer Id:'|| ln_cust_id ; EXCEPTION WHEN OTHERS THEN :string_out := 'Something is missing!'; :status := 400; END;
Setup Parameters:
Adding the Input & Output parameters required for the POST Method.

Checking the details here:

Upload Module


Module is uploaded. Now test the API to post the customer details.
Testing Rest API
Providing input parameters as EMAIL_ADDRESS & FULL_NAME. Check on send.


Customer got created.

Building RESTfull API – Delete Method
Create a new handler in existing module.

Add handler like this in existing module.

After creating the handler, open the Delete handler & edit the SQL Worksheet.

DECLARE ln_cust_id CUSTOMERS.CUSTOMER_ID%TYPE; BEGIN DELETE FROM CUSTOMERS WHERE upper(EMAIL_ADDRESS) = upper(:EMAIL_ADDRESS) RETURNING CUSTOMER_ID INTO ln_cust_id; commit; :string_out := 'Record Deleted. Customer Email:'|| ln_cust_id ; EXCEPTION WHEN OTHERS THEN :string_out := 'Something is missing!'; :status := 400; END;
Setup Parameters
Adding the Input & Output parameters required for the Delete Method.


Testing Rest API
Records got deleted from the database table.

- Testing APIs using Python: Calling Oracle Database REST APIs using Python
- Securing REST APIs: Securing Oracle Database REST APIs using BasicAuth
Thanks!
Happy Learning! Your feedback would be appreciated!
Follow @shobhitsinghIN
3 thoughts on “Building REST APIs | SQL Developer | POST & DELETE”