Streamline Your Workflow – Creating a Restful API in Oracle APEX

Oracle REST Data Services (ORDS) is a powerful Java-based tool that allows developers with SQL and database skills to create high-performance REST APIs for a variety of Oracle databases. With ORDS, developers can easily develop and deploy RESTful web services for Oracle Database, Oracle Database 12c JSON Document store, and Oracle NoSQL Database, allowing applications to efficiently access and manipulate data. ORDS provides a comprehensive set of features, including security, caching, pagination, and more, making it an ideal tool for building scalable and secure web services.

Creating a RESTAPI in Oracle APEX without Authentication: 

A Resource Module that enables you to access information about Users is named oracle.apex-app.cb and the base path value for the service is /apex-app/ .

Steps for creating Module:

• Go to the Dashboard in that click, SQL Workshop.
• Under SQL Workshop click, RESTful Services.
• You will find the RESTful Data Services Wizard.
• There you will find list of option in the left panel, select Modules.
• Fill the Module name, Base Path and then click, Create Module at the top right corner.

Example:

Module Name: oracle.apex-app.cb

Base Path: /apex-app/

Steps for creating Template:

Under oracle.apex-app.cb Module as created above, click Create Template on right corner at bottom.

• After, you’ll navigate to Template Wizard.
• You can specify the URI Template under module base path.
• Once created, click Create Template in the right-side corner, as shown below.

Example:

URI Template: apex-app

Steps for creating Handler:

Under oracle.apex-app.cb/apex-app Template as created above, click Create Handler on right corner at bottom.

• After, you’ll navigate to Handler Wizard.
• Select HTTP Get method from the list option given.
• Under you find Source type choose Query.
• In format option choose the file format as per your requirements.
• In the below you will find the Source to write a query to fetch data from oracle database table to shown in the API call and then click, Create Handler at the top right corner.

Example:

Method: GET

Source Type: Query

Format: JSON

Source: Select * from tb_uam_users

Using the below link, you can achieve the API Response from browser:

Creating a RESTAPI in Oracle APEX with Authentication

Steps for creating Privilege:

• In addition to the above steps, you need to create Privilege for Authenticating Rest API.
• Under the RESTful Data Services Wizard, you will find list of option in the left panel, select Privilege.
• Next to Create Privilege, once the Privilege page is viewed, there you can find the ORDS Privileges Definition as shown below and fill the Privilege name, Title.
• Under you can see Roles, where you can choose the roles and apply it in the RESTful services then, followed by you can see Protected Modules, where you can choose the modules and apply it in the oracle.apex-app.cb.
• Click, Create Privilege at the top right corner as shown below.

Example:

Name: APEX-Authentication

Title: APEX-App Auth

Role: Restful Services

Protected Modules: oracle.apex-app.cb

Using the below link, you can achieve the API Response from browser, before you access into the API Response, you should Authenticate through Oracle Apex Credential and it will navigate to API Response Page:

Username: <apex-username>

Password: <apex-password>

Now, you’ll get an API Response as shown below,

Please feel free to reach Cittabase for more information. Visit our blogs for more topics on Oracle APEX.