Building REST APIs | SQL Developer | POST & DELETE

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