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.


Thanks!

Happy Learning! Your feedback would be appreciated!

3 thoughts on “Building REST APIs | SQL Developer | POST & DELETE

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 )

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s