Oracle APEX API Custom json

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!