
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.
In this section, we will discuss how to create a custom JSON response using Oracle APEX.
DECLAREemployees_cursor SYS_REFCURSOR;BEGINOPEN employees_cursor FORSELECT empno "$uri",rn,empno,ename,job,hiredate,mgr,sal,comm,deptnoFROM (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.
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.
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.
Update the PL/SQL code in the API handler to include pagination and sorting parameters:
DECLAREemployees_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;BEGINIF p_sort_column IS NULL THENp_sort_column := 'empno';END IF;IF p_sort_order IS NULL THENp_sort_order := 'ASC';END IF;OPEN employees_cursor FORSELECT *FROM (SELECT emp.*, ROW_NUMBER () OVER (ORDER BY empno) rn FROM emp)tmpORDER BY CASEWHEN p_sort_column = 'empno' AND p_sort_order = 'ASC' THENempnoEND ASC,CASEWHEN p_sort_column = 'empno' AND p_sort_order = 'DESC' THENempnoEND DESCOFFSET 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.
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.
Error handling: Implement error handling in your API to ensure that your users receive meaningful error messages when something goes wrong.
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.
Authentication and authorization: Secure your API by implementing authentication and authorization mechanisms, such as OAuth 2.0, to control access to your API resources.
Caching: Implement caching to improve the performance of your API, especially for large datasets or complex queries.
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.
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!
To learn more about Oracle APEX and expand your knowledge, consider exploring the following resources:
With these resources, you can further enhance your APEX skills and create even more powerful applications to meet the needs of your organization.
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:
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!
Quick Links
Legal Stuff