HomeContact

Oracle APEX API Custom json

By Shady Nagy
Published in Oracle
July 19, 2021
2 min read
Oracle APEX API Custom json

Table Of Contents

01
Introduction
02
Sending Custom JSON Responses in APEX APIs
03
Pagination and Sorting
04
Additional Enhancements
05
Conclusion
06
Further Reading

Introduction

Oracle Application Express (APEX) is a low-code development platform that empowers developers to create scalable, secure enterprise applications with world-class features that can be deployed anywhere. With APEX, you can quickly develop and deploy apps that address real-world problems and provide immediate value, without needing expertise in a wide array of technologies. Focus on problem-solving, and let APEX handle the rest.

Sending Custom JSON Responses in APEX APIs

In this section, we will discuss how to create a custom JSON response using Oracle APEX.

Step 1: Create the API handler

  • Create a new API handler and choose the PL/SQL type.
  • Add the following code to the handler:
DECLARE
employees_cursor SYS_REFCURSOR;
BEGIN
OPEN employees_cursor FOR
SELECT empno "$uri",
rn,
empno,
ename,
job,
hiredate,
mgr,
sal,
comm,
deptno
FROM (SELECT emp.*, ROW_NUMBER () OVER (ORDER BY empno) rn FROM emp)
tmp;
APEX_JSON.open_object;
APEX_JSON.write ('emplyees', employees_cursor);
APEX_JSON.write ('message', 'Success');
APEX_JSON.close_object;
END;

This code block declares a cursor, retrieves employee data, and creates a custom JSON object containing an array of employees and a success message.

Step 2: Test the API with Postman

Using a tool like Postman, you can test the API and see the custom JSON response:

{
"emplyees": [
{
"$uri": 7369,
"RN": 1,
"EMPNO": 7369,
"ENAME": "SMITH",
"JOB": "CLERK",
"HIREDATE": "1980-12-17T00:00:00Z",
"MGR": 7902,
"SAL": 800,
"DEPTNO": 20
},
...
],
"message": "Success"
}

With these steps, you can now customize the API result as needed.

Pagination and Sorting

To make your API even more powerful, you can add pagination and sorting options. This will allow users to request a specific range of data and sort it according to their preferences.

Step 1: Modify the API handler

Update the PL/SQL code in the API handler to include pagination and sorting parameters:

DECLARE
employees_cursor SYS_REFCURSOR;
p_offset NUMBER := :OFFSET;
p_limit NUMBER := :LIMIT;
p_sort_column VARCHAR2(255) := :SORT_COLUMN;
p_sort_order VARCHAR2(4) := :SORT_ORDER;
BEGIN
IF p_sort_column IS NULL THEN
p_sort_column := 'empno';
END IF;
IF p_sort_order IS NULL THEN
p_sort_order := 'ASC';
END IF;
OPEN employees_cursor FOR
SELECT *
FROM (SELECT emp.*, ROW_NUMBER () OVER (ORDER BY empno) rn FROM emp)
tmp
ORDER BY CASE
WHEN p_sort_column = 'empno' AND p_sort_order = 'ASC' THEN
empno
END ASC,
CASE
WHEN p_sort_column = 'empno' AND p_sort_order = 'DESC' THEN
empno
END DESC
OFFSET p_offset ROWS FETCH NEXT p_limit ROWS ONLY;
APEX_JSON.open_object;
APEX_JSON.write ('emplyees', employees_cursor);
APEX_JSON.write ('message', 'Success');
APEX_JSON.close_object;
END;

This updated code adds pagination and sorting options, with default values for the offset, limit, sort column, and sort order.

Step 2: Add the Pagination and Sorting Parameters

To utilize these new features, you’ll need to add the OFFSET, LIMIT, SORT_COLUMN, and SORT_ORDER parameters to your API request. For example:

https://your-apex-instance.com/ords/workspace/api/employees?OFFSET=0&LIMIT=10&SORT_COLUMN=empno&SORT_ORDER=ASC

This request would return the first 10 employees, sorted by their employee number in ascending order.

Additional Enhancements

  1. Error handling: Implement error handling in your API to ensure that your users receive meaningful error messages when something goes wrong.

  2. Search and filtering: Allow users to search for specific employees or filter the data based on specific criteria, such as job title, department, or hire date.

  3. Authentication and authorization: Secure your API by implementing authentication and authorization mechanisms, such as OAuth 2.0, to control access to your API resources.

  4. Caching: Implement caching to improve the performance of your API, especially for large datasets or complex queries.

  5. Rate limiting: Implement rate limiting to prevent abuse of your API and ensure fair usage by all clients.

By adding these enhancements, you can create a more powerful and versatile API that caters to a wide range of user needs.

Conclusion

Oracle APEX is a powerful low-code development platform that enables developers to create enterprise applications quickly and efficiently. By following the steps in this tutorial, you can create custom JSON responses in APEX APIs to tailor the results to your specific needs. Happy coding!

Further Reading

To learn more about Oracle APEX and expand your knowledge, consider exploring the following resources:

  1. Oracle APEX Documentation - Official documentation covering all aspects of Oracle APEX.
  2. Oracle APEX Blog - Official blog with articles, announcements, and best practices for APEX developers.

With these resources, you can further enhance your APEX skills and create even more powerful applications to meet the needs of your organization.

Feedback and Questions:

We’d love to hear your feedback on this tutorial! If you have any questions or suggestions for improvement, please don’t hesitate to reach out. You can leave a comment below, or you can contact us through the following channels:

  1. Email: shady@shadynagy.com
  2. Twitter: @ShadyNagy_
  3. LinkedIn: Shady Nagy

We’ll do our best to address any questions or concerns you may have. We look forward to hearing from you and helping you make the most of Oracle APEX API Custom json!


Tags

#Oracle#Apex#Oracle Apex#sql#API#restful

Share


Previous Article
Install Oracle APEX
Shady Nagy

Shady Nagy

Software Innovation Architect

Topics

AI
Angular
dotnet
GatsbyJS
Github
Linux
MS SQL
Oracle

Related Posts

Updating Oracle Database Configuration After Changing the Server IP Address
Updating Oracle Database Configuration After Changing the Server IP Address
May 16, 2024
2 min

Quick Links

Contact Us

Social Media