--- title: Oracle APEX Support for Hierarchical REST Source Data Profiles description: Explore the new Oracle APEX 24.1 features that support hierarchical JSON data in REST Data Sources, enhancing your data handling & reporting capabilities. image: https://content.dsp.co.uk/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles.png --- [![DSP From Ground to Cloud](https://content.dsp.co.uk/hubfs/Logos/DSP%20From%20Ground%20to%20Cloud/DSP-Logo-Colour-From-Ground-to-Cloud.svg "DSP From Ground to Cloud")](https://dsp.co.uk) [![DSP From Ground to Cloud](https://content.dsp.co.uk/hs-fs/hubfs/2023/Logos/DSP/DSP-Logo-2019-White-600px.png?width=70&height=72&name=DSP-Logo-2019-White-600px.png "DSP From Ground to Cloud")](https://dsp.co.uk) - Services - Consulting Services - [Oracle](https://www.dsp.co.uk/oracle-consultancy) - [Microsoft](https://www.dsp.co.uk/sql-server-consultancy) - [Google](https://www.dsp.co.uk/google-cloud-consultancy) - [Architecture](https://www.dsp.co.uk/solution-design-and-architecture) - Managed Services - [Oracle Managed Services](https://www.dsp.co.uk/oracle-managed-services) - [SQL Server Managed Services](https://www.dsp.co.uk/sql-server-managed-services) - [OCI Managed Services](https://www.dsp.co.uk/oracle-cloud-infrastructure-managed-services) - [Azure Managed Services](https://www.dsp.co.uk/azure-managed-services) - [On Premise Apps Managed Services](https://www.dsp.co.uk/application-managed-services) - [Cloud Apps Managed Services](https://www.dsp.co.uk/oracle-cloud-applications-services) - Licensing and SAM - [Oracle Licensing](https://www.dsp.co.uk/oracle-licensing-services) - [Software Asset Management](https://www.dsp.co.uk/oracle-software-asset-management-sam) - Application Services - [Application Development](https://www.dsp.co.uk/software-development) - [Application Modernisation](https://www.dsp.co.uk/legacy-software-modernisation) - [Oracle Forms to Oracle APEX](https://www.dsp.co.uk/oracle-forms-to-oracle-apex) - [Application Extensions](https://www.dsp.co.uk/software-customisation) - [Archiving Solutions](https://www.dsp.co.uk/application-archiving-solution) - [Application Hosting](https://www.dsp.co.uk/oracle-apex-hosting-services) - [Development Training](https://www.dsp.co.uk/oracle-apex-training) - Data Science - [AI Strategy](https://www.dsp.co.uk/ai-strategy) - [Responsible AI and Governance](https://www.dsp.co.uk/responsible-ai) - [Agentic AI](https://www.dsp.co.uk/agentic-ai) - [Data Management for AI](https://www.dsp.co.uk/data-management) - [Power BI](https://www.dsp.co.uk/power-bi-services) - [Microsoft Fabric](https://www.dsp.co.uk/microsoft-fabric-services) - [Machine Learning](https://www.dsp.co.uk/oci-machine-learning) - Cloud Migration and Management - [Cloud Readiness and Design](https://www.dsp.co.uk/oracle-cloud-readiness-assessment) - [Migration to Fusion and Cloud Applications](https://www.dsp.co.uk/oracle-cloud-application-modernisation) - [Migration to Cloud Infrastructure](https://www.dsp.co.uk/oracle-cloud-migration) - [Private Cloud](https://www.dsp.co.uk/oracle-private-cloud-services) - [Database@ Hyperscaler](https://www.dsp.co.uk/multicloud-management) - [FinOps and Optimisation](https://www.dsp.co.uk/optimise-oracle-cloud-infrastructure) - Integration and APIs - [API Management](https://www.dsp.co.uk/api-development-services) - [GoldenGate](https://www.dsp.co.uk/oracle-goldengate-services) - Disaster Recovery and Security - [Disaster Recovery](https://www.dsp.co.uk/oracle-database-disaster-recovery-solutions) - [Security Compliance](https://www.dsp.co.uk/data-security) - [Cloud Security](https://www.dsp.co.uk/cloud-security)   ![Oracle Partner, Microsoft Partner and Google Partner](https://content.dsp.co.uk/hubfs/logo-2021/Partner-logos/multiple-vendor/rectangle-2022/Partner-logos-rectangle-Nov21.svg) #### Getting to know us - [About us](https://www.dsp.co.uk/about) - [What we do](https://www.dsp.co.uk/what-we-do) - [Leadership](https://www.dsp.co.uk/dsp-product-leads) - [Careers](https://www.dsp.co.uk/careers) - Technologies - Oracle Cloud Infrastructure - [Oracle Cloud Infrastructure](https://www.dsp.co.uk/oracle-cloud-infrastructure-services) - [IaaS](https://www.dsp.co.uk/iaas) - [PaaS](https://www.dsp.co.uk/paas-security) - Oracle Database - [Oracle Database](https://www.dsp.co.uk/oracle-database-consulting) - [Autonomous Database](https://www.dsp.co.uk/oracle-autonomous-database-services) - [MySQL and Heatwave](https://www.dsp.co.uk/mysql-services) - [Oracle APEX](https://www.dsp.co.uk/oracle-apex-services) - Oracle Engineered Systems - [Oracle Exadata](https://www.dsp.co.uk/exadata-database-service-consultancy) - [Oracle Database Appliance](https://www.dsp.co.uk/oracle-database-appliance) - Oracle Applications - [Oracle Fusion and Cloud Applications](https://www.dsp.co.uk/oracle-fusion-services) - [Oracle E-Business Suite](https://www.dsp.co.uk/oracle-ebs-services) - [JD Edwards](https://www.dsp.co.uk/jd-edwards-services) - [Peoplesoft](https://www.dsp.co.uk/oracle-peoplesoft-services) - [Hyperion](https://www.dsp.co.uk/oracle-hyperion-services) - Microsoft - [SQL Server](https://www.dsp.co.uk/sql-server-consultancy) - [Microsoft Azure](https://www.dsp.co.uk/microsoft-azure-services) - [Power BI](https://www.dsp.co.uk/power-bi-services) - [Microsoft Fabric](https://www.dsp.co.uk/microsoft-fabric-services) - Google - [Google Cloud Platform (GCP)](https://www.dsp.co.uk/google-cloud-partner) - Multicloud and Database Hyperscaler - [Oracle Database@Azure](https://www.dsp.co.uk/oracle-databaseazure-partner) - [Oracle Database@GCP](https://www.dsp.co.uk/google-cloud-machine-learning) - [Oracle Database@AWS](https://www.dsp.co.uk/amazon-web-services-aws) - [Oracle Azure Interconnect](https://www.dsp.co.uk/oracle-microsoft-multicloud) - Open Source Database - [PostgreSQL](https://www.dsp.co.uk/postgresql-services) - [MySQL](https://www.dsp.co.uk/mysql-services) - [MariaDB](https://www.dsp.co.uk/migration-from-mariadb-to-mysql) #### Managed Services - [Oracle Database](https://www.dsp.co.uk/oracle-managed-services) - [SQL Server](https://www.dsp.co.uk/sql-server-managed-services) - [MySQL](https://www.dsp.co.uk/mysql-support) #### Application Services - [Oracle Applications](https://www.dsp.co.uk/oracle-application-cloud-migration) - [Oracle APEX](https://www.dsp.co.uk/oracle-apex-services) - [Microsoft](https://www.dsp.co.uk/microsoft-partnership) - [ISV Services](https://www.dsp.co.uk/isv-support-and-services) #### Cloud Services - [Oracle Cloud Infrastructure](https://www.dsp.co.uk/oracle-cloud-services) - [Microsoft Azure](https://www.dsp.co.uk/microsoft-azure-services) - [Google Cloud Platform](https://www.dsp.co.uk/gcp-database) - [Multi-cloud](https://www.dsp.co.uk/oracle-microsoft-multicloud) #### Data Science - [Machine Learning](https://www.dsp.co.uk/machine-learning-consulting-services) - [Business Intelligence](https://www.dsp.co.uk/business-intelligence-services) - [Artificial Intelligence](https://www.dsp.co.uk/artificial-intelligence-consulting-services) #### Consulting Services - [Oracle](https://www.dsp.co.uk/oracle-consultancy) - [Microsoft](https://www.dsp.co.uk/sql-server-consultancy) - [Google](https://www.dsp.co.uk/google-cloud-consultancy) - [Cloud Migration](https://www.dsp.co.uk/cloud-migrations) #### Technology Solutions - [Engineered Systems](https://www.dsp.co.uk/oracle-engineered-systems-partner) - [Data Security](https://www.dsp.co.uk/data-security) - [Database Architecture](https://www.dsp.co.uk/solution-design-and-architecture) - [Disaster Recovery](https://www.dsp.co.uk/database-disaster-recovery) - Industries - [Finance](https://www.dsp.co.uk/finance) - [Healthcare](https://www.dsp.co.uk/healthcare-it-services) - [Travel and Transport](https://www.dsp.co.uk/database-solutions-for-travel-and-transport) - [Higher Education](https://www.dsp.co.uk/higher-education) - [Public Sector](https://www.dsp.co.uk/public-sector) - [Manufacturing](https://www.dsp.co.uk/manufacturing) - [Retail](https://www.dsp.co.uk/retail) - [Software Vendors](https://www.dsp.co.uk/isv-support-and-services) ### Recent case study: #### Oracle EBS Cloud Deployment Consolidating and Migrating assets into Oracle Cloud Infrastructure. [**![Oracle EBS Cloud Deployment](https://content.dsp.co.uk/hs-fs/hubfs/stonewater-logo%20(1).png?width=250&name=stonewater-logo%20(1).png)**](https://www.dsp.co.uk/stonewater)   #### Most Visited Pages - [Oracle Licensing](https://www.dsp.co.uk/oracle-licensing) - [Oracle Cloud Calculator](https://www.oracle-cloud-calculator.com/) - [Oracle Cloud Migration](https://www.dsp.co.uk/oracle-cloud-migration) - [Oracle Exadata Services](https://www.dsp.co.uk/oracle-exadata-services) - [Artificial Intelligence Services](https://www.dsp.co.uk/artificial-intelligence-consulting-services) #### - - [SQL Server Support](https://www.dsp.co.uk/sql-server-support) - [Oracle Support](https://www.dsp.co.uk/oracle-support) - [Google Cloud Consultancy](https://www.dsp.co.uk/google-cloud-consultancy) - [New Application Development](https://www.dsp.co.uk/oracle-apex-services) - [Azure Virtual Desktop](https://www.dsp.co.uk/azure-virtual-desktop) - Resources - [Gartner ](https://www.dsp.co.uk/complimentary-gartner-download) - [ISG](https://www.dsp.co.uk/isg-oracle-report) - [Blogs](https://www.dsp.co.uk/blogs) - [Case Studies](https://www.dsp.co.uk/case-studies) - [Testimonials](https://www.dsp.co.uk/testimonials) - [Events](https://www.dsp.co.uk/events) - [Webinars](https://www.dsp.co.uk/webinars) - [Insights](https://www.dsp.co.uk/insights) - [Technical Resources](https://www.dsp.co.uk/technical-resources) ### DSP-Explorer acquires leading Oracle Applications Managed Services Provider, Claremont, to further extend its data management capabilities.   [![Read More](https://no-cache.hubspot.com/cta/default/3321273/c7e13c9c-65e1-46e7-8541-8da86a4fc305.png)](https://cta-redirect.hubspot.com/cta/redirect/3321273/c7e13c9c-65e1-46e7-8541-8da86a4fc305) #### Resources - [Blogs](https://www.dsp.co.uk/blogs) - [Webinars](https://www.dsp.co.uk/webinars) - [Case Studies](https://www.dsp.co.uk/case-studies) - [Testimonials](https://www.dsp.co.uk/testimonials) - About - [About DSP](https://www.dsp.co.uk/about) - [What We Do](https://www.dsp.co.uk/what-we-do) - [DSP Group Board ](https://www.dsp.co.uk/dsp-group-board) - [Careers](https://www.dsp.co.uk/careers) - [Project Harar](https://www.dsp.co.uk/project-harar) - [Customer Area](https://www.dsp.co.uk/contact-us) - [Customer Support Portal](https://dspgroup-ism.ivanticloud.com/) - [Customer Resources](https://www.dsp.co.uk/welcome) - [Contact us](https://www.dsp.co.uk/contact-us) # Oracle APEX Support for Hierarchical REST Source Data Profiles [ Philip Ratcliffe ](https://content.dsp.co.uk/author/philip-ratcliffe) 27-Mar-2025 11:10:02 Oracle APEX Support for Hierarchical REST Source Data Profiles 7:32 ### Contents [31-Jul-2025 09:41:30 Quick SQL - Enhancements and Generative AI Modelling ](https://content.dsp.co.uk/quick-sql-enhancements-and-generative-ai-modelling) [23-Aug-2023 14:24:34 Exporting Custom Interactive Reports via REST ](https://content.dsp.co.uk/exporting-custom-interactive-reports-via-rest) [12-Mar-2026 13:54:10 Tips for making the most out of APEX's REST Data Sources ](https://content.dsp.co.uk/tips-for-making-the-most-out-of-apexs-rest-data-sources) Until recently, [Oracle APEX](https://www.dsp.co.uk/oracle-apex-services), via functionality such as REST Data Sources, could only extract a flat JSON structure. From release 24.1, however, APEX can now understand and transform more complex hierarchical or nested JSON responses. In this blog, I will demonstrate some of this new functionality and its use, for example, in Interactive Reports.   ## Hierarchical REST Data Source The image below illustrates a snippet of JSON output from an Oracle REST Data Service for department-related data that incorporates nested arrays. Not including the highest-level array (departments), there are three further arrays in the JSON, two at the first level down (employees and locations) and one at the second level down (customers). [![Oracle APEX Support for Hierarchical REST Source Data Profiles](https://content.dsp.co.uk/hs-fs/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%201.png?width=389&height=681&name=Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%201.png)](https://content.dsp.co.uk/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%201.png) Up until APEX release 23.2, a REST Data Source would only be able to extract the highest-level array (departments). Any lower-level arrays would not be extracted. ## Creating the REST Data Source The procedure for creating a REST Data Source in your APEX application is unchanged. Access the Create wizard via Shared Components > Data Sources > REST Data Source and click the Create button. The wizard, too, is unchanged. As you Discover the Data profile, it isn't immediately clear that APEX is doing anything other than extracting a flat structure, as previously, only the five non-array attributes associated with departments are listed: [![Oracle APEX Support for Hierarchical REST Source Data Profiles](https://content.dsp.co.uk/hs-fs/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%202.png?width=1681&height=397&name=Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%202.png)](https://content.dsp.co.uk/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%202.png) Clicking the Data Profile tab in Discover provides more clues that changes are afoot. More columns are listed here, including some identified with an 'array' type: [![Oracle APEX Support for Hierarchical REST Source Data Profiles](https://content.dsp.co.uk/hs-fs/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2031.png?width=1630&height=618&name=Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2031.png)](https://content.dsp.co.uk/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2031.png) Once you have created the data source in the wizard, click the Create REST Data Source button.   ## View the REST Data Source Once your REST Data Source has been created, click on the link under the REST Source Name column in the REST Data Source page to view and edit the definition for it. On the definition page, clicking the 'Edit Data Profile' provides more evidence of changes. Here, you can see that some columns are defined as array types, and others have parent columns associated with those array-type columns. [![Oracle APEX Support for Hierarchical REST Source Data Profiles](https://content.dsp.co.uk/hs-fs/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%204.png?width=911&height=293&name=Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%204.png)](https://content.dsp.co.uk/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%204.png)   ## Create an IR Based on a Hierarchical REST Source Now, we can utilise the defined REST Data Source in our application. Create a new page based on an Interactive Report for which the Data Source is the REST Data Source we have just defined. Once the page and report have been created, in the report's region in page designer, you will find that initially, by default, the report is defined with seven columns, including two first-level array types (EMPLOYEES and LOCATIONS). ![Oracle APEX Support for Hierarchical REST Source Data Profiles Image 5](https://content.dsp.co.uk/hs-fs/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%205.png?width=772&height=718&name=Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%205.png) Let's run the report. Not good. We get a 'character string buffer too small' error. [![Oracle APEX Support for Hierarchical REST Source Data Profiles](https://content.dsp.co.uk/hs-fs/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%206.png?width=573&height=195&name=Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%206.png)](https://content.dsp.co.uk/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%206.png) I noticed that, in the report definition, all the columns, including the two array columns (employees and locations), are defined as 'Plain Text' types. I potentially have a lot of data within the 'employees' array. I set the 'employees' column as a hidden column and reran the report. Thankfully the report runs successfully, but it's displaying the 'locations' array column data as a JSON-format string. [![Oracle APEX Support for Hierarchical REST Source Data Profiles](https://content.dsp.co.uk/hs-fs/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%207.png?width=1894&height=421&name=Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%207.png)](https://content.dsp.co.uk/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%207.png) Essentially, in our REST Data Source definition, APEX has identified these columns as arrays, but we haven't described how we want to deal with these array columns, so they are just being returned as plain text in JSON format. In Page Designer, under the report's region properties, there is a new attribute, 'Nested Rows', under 'Data Profile'. This attribute has defaulted to 'None'. [![Oracle APEX Support for Hierarchical REST Source Data Profiles](https://content.dsp.co.uk/hs-fs/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%208.png?width=445&height=278&name=Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%208.png)](https://content.dsp.co.uk/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%208.png) If, for this attribute, we select '3. Locations', on selection, both array type columns (employees and locations) are removed as columns from the report definition. Instead, the report includes columns for each of the attributes included in the locations JSON array. At this stage, columns are not created for each attribute included in the other arrays in the JSON (employees, customers). [![Oracle APEX Support for Hierarchical REST Source Data Profiles](https://content.dsp.co.uk/hs-fs/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%209.png?width=268&height=342&name=Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%209.png)](https://content.dsp.co.uk/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%209.png) Let's run the report again, and once it's run, use the feature of the IR (Actions > Columns) to ensure all columns are included in the report. Now, we can see that APEX is successfully interpreting the individually nested columns. Notice, though, that we are now displaying a report row for each nested location, no longer one for each department. That's not a great problem – we can use one of the features of the IR, Control Breaks, to overcome that. [![Oracle APEX Support for Hierarchical REST Source Data Profiles](https://content.dsp.co.uk/hs-fs/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2010.png?width=1879&height=426&name=Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2010.png)](https://content.dsp.co.uk/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2010.png) It is worth noting that only one Nested Rows column can be selected at a time in the IR report definition. If the 'employees' column is selected rather than the 'locations' column, then only the columns of the employees' nested row will be included in the report and not those for 'locations'. However, all columns of all parents will also become available as part of the flat table structure. Therefore, if 'customers' is selected as the Nested Rows column, all the nested columns of employees and customers (but not locations) become available. [![Oracle APEX Support for Hierarchical REST Source Data Profiles](https://content.dsp.co.uk/hs-fs/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2011.png?width=605&height=609&name=Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2011.png)](https://content.dsp.co.uk/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2011.png)   ## Post-Processing SQL By using the pre-existing Local Post Processing property of the IR report definition, we can perform calculations on individual line items. I set my Nested Row to be 'employees' and entered the following code snippet into the report's Local Post Processing property. [![Oracle APEX Support for Hierarchical REST Source Data Profiles](https://content.dsp.co.uk/hs-fs/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2012.png?width=427&height=147&name=Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2012.png)](https://content.dsp.co.uk/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2012.png) I made some basic IR format changes and then ran the report, which now shows one row per department with calculated employee-related data (employee count and salary total) determined by the post-processing SQL. [![Oracle APEX Support for Hierarchical REST Source Data Profiles](https://content.dsp.co.uk/hs-fs/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2013.png?width=1889&height=362&name=Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2013.png)](https://content.dsp.co.uk/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2013.png) ## Programmatic Support Before APEX release 24.1, the APEX_EXEC package provided functions and procedures to process REST Data Sources programmatically. Since release 24.1, the package has been extended to accommodate nested JSON array columns. This includes the addition of new functions and procedures, including (but not limited to): - ADD_DML_ARRAY_ROW procedure - CLOSE_ARRAY procedure - NEXT_ARRAY_ROW function - OPEN_ARRAY procedure - SET_ARRAY_CURRENT_ROW procedure Or extending existing functions and procedures to cater for array columns, for example: - ADD_COLUMN procedure - OPEN_REST_SOURCE_QUERY function For example, the following snippet makes use of the APEX_EXEC package in the following ways: - OPEN_REST_SOURCE_QUERY – an existing function that opens a REST Source Query context, in this case, the 'departments' REST Data Source. - NEXT_ROW - the existing function to advance the cursor of the opened query context by one row. - OPEN_ARRAY – the new procedure to enter the array within the provided array column and move the cursor to before the first row. - NEXT_ARRAY_ROW – the new function advances the array cursor by one row. - CLOSE_ARRAY – the new procedure closes the current array and returns the cursor to the parent element. - CLOSE – The existing procedure closes the query context. [![Oracle APEX Support for Hierarchical REST Source Data Profiles](https://content.dsp.co.uk/hs-fs/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2014.png?width=829&height=531&name=Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2014.png)](https://content.dsp.co.uk/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2014.png) This way, the code processes each department and every location associated with each department. Execution of the procedure generates the following output: [![Oracle APEX Support for Hierarchical REST Source Data Profiles](https://content.dsp.co.uk/hs-fs/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2016.png?width=403&height=145&name=Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2016.png)](https://content.dsp.co.uk/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles%20Image%2016.png)   ## Summary Since APEX release 24.1, Oracle has greatly enhanced APEX REST Data Sources by enabling support for hierarchical (nested) JSON. Every APEX component that supports REST Data Source now also supports array columns within the data profile, including: - Classic Report, Interactive Report and Interactive Grid - Map Regions and JET Charts - Calendar - Region Plug-Ins - Automations - Shared Lists Of Values - Search Configurations - Report Queries With similarly extended programmatic support, this is a welcome enhancement to APEX REST Data Source's capabilities. For more information, check out our [Oracle APEX Services](https://www.dsp.co.uk/oracle-apex-services), and if you liked this blog, check out our other [APEX blogs here](https://content.dsp.co.uk/apex). [Contact us](https://www.dsp.co.uk/contact-us) today and one of our expert developers will be in touch. [Oracle](https://content.dsp.co.uk/topic/oracle) [JSON](https://content.dsp.co.uk/topic/json) [APEX](https://content.dsp.co.uk/topic/apex) [Oracle APEX](https://content.dsp.co.uk/topic/oracle-apex) [REST](https://content.dsp.co.uk/topic/rest) [31-Jul-2025 09:41:30 Quick SQL - Enhancements and Generative AI Modelling ](https://content.dsp.co.uk/quick-sql-enhancements-and-generative-ai-modelling) [23-Aug-2023 14:24:34 Exporting Custom Interactive Reports via REST ](https://content.dsp.co.uk/exporting-custom-interactive-reports-via-rest) [12-Mar-2026 13:54:10 Tips for making the most out of APEX's REST Data Sources ](https://content.dsp.co.uk/tips-for-making-the-most-out-of-apexs-rest-data-sources) [Previous Which Oracle Engineered System is right for you? ](https://content.dsp.co.uk/which-oracle-engineered-system-is-right-for-you) [Next Improving Customer Service with AI-Powered Sentiment Analysis ](https://content.dsp.co.uk/improving-customer-service-with-ai-powered-sentiment-analysis) #### Contact Sales Enquiries [+44 (0) 203 880 1686](tel:+4420%2038801686) Technical Support +44 (0) 330 058 8367 [enquiries@dsp.co.uk](mailto:enquiries@dsp.co.uk) #### Follow us #### Office Locations [**London (Head Office)**](https://www.google.com/maps/place/Fora+-+Chancery+House/@51.5174484,-0.1139686,198m/data=!3m2!1e3!5s0x48761adea956397d:0xb7585122cbe86fc0!4m6!3m5!1s0x48761b4b75bf77fb:0x97c4f0ad56af6d23!8m2!3d51.5174586!4d-0.1129066!16s%2Fg%2F11h_cy7xk5?entry=ttu&g_ep=EgoyMDI1MTAwOC4wIKXMDSoASAFQAw%3D%3D) [Chancery House](https://www.google.com/maps/place/Fora+-+Chancery+House/@51.5174484,-0.1139686,19z/data=!3m1!5s0x48761adea956397d:0xb7585122cbe86fc0!4m6!3m5!1s0x48761b4b75bf77fb:0x97c4f0ad56af6d23!8m2!3d51.5174586!4d-0.1129066!16s%2Fg%2F11h_cy7xk5?entry=ttu&g_ep=EgoyMDI1MDkyOS4wIKXMDSoASAFQAw%3D%3D) [53-64 Chancery Lane](https://www.google.com/maps/place/Fora+-+Chancery+House/@51.5174484,-0.1139686,19z/data=!3m1!5s0x48761adea956397d:0xb7585122cbe86fc0!4m6!3m5!1s0x48761b4b75bf77fb:0x97c4f0ad56af6d23!8m2!3d51.5174586!4d-0.1129066!16s%2Fg%2F11h_cy7xk5?entry=ttu&g_ep=EgoyMDI1MDkyOS4wIKXMDSoASAFQAw%3D%3D) [London](https://www.google.com/maps/place/Fora+-+Chancery+House/@51.5174484,-0.1139686,19z/data=!3m1!5s0x48761adea956397d:0xb7585122cbe86fc0!4m6!3m5!1s0x48761b4b75bf77fb:0x97c4f0ad56af6d23!8m2!3d51.5174586!4d-0.1129066!16s%2Fg%2F11h_cy7xk5?entry=ttu&g_ep=EgoyMDI1MDkyOS4wIKXMDSoASAFQAw%3D%3D) [WC2A 1QS](https://www.google.com/maps/place/Fora+-+Chancery+House/@51.5174484,-0.1139686,19z/data=!3m1!5s0x48761adea956397d:0xb7585122cbe86fc0!4m6!3m5!1s0x48761b4b75bf77fb:0x97c4f0ad56af6d23!8m2!3d51.5174586!4d-0.1129066!16s%2Fg%2F11h_cy7xk5?entry=ttu&g_ep=EgoyMDI1MDkyOS4wIKXMDSoASAFQAw%3D%3D) [**Leeds** Richmond House, Lawnswood Business Park, Leeds, LS16 6QY](https://www.google.com/maps/dir/53.8405223,-1.6062773/DSP-Explorer/@53.8404788,-1.6061783,17z/data=!4m9!4m8!1m1!4e1!1m5!1m1!1s0x4879591e5d204ac9:0x4e41b926d72adca!2m2!1d-1.6064847!2d53.8404842) [**Derby**](https://www.google.com/maps/place/Cubo+Pride+Park/@52.9142877,-1.4558952,331m/data=!3m2!1e3!5s0x4879f10728c1eea1:0x877f85530d38bf6!4m15!1m8!3m7!1s0x4879f1072b0e2ebb:0x7c717035ff049b12!2sPride+Pl,+Derby+DE24+8QR!3b1!8m2!3d52.913692!4d-1.4543743!16s%2Fg%2F1th7mf0r!3m5!1s0x4879f16809382b33:0x6fccc05b8500d970!8m2!3d52.914146!4d-1.4544105!16s%2Fg%2F11w4j8v8z2?entry=ttu&g_ep=EgoyMDI1MDYyMi4wIKXMDSoASAFQAw%3D%3D) [Cubo Pride Park,](https://www.google.com/maps/place/Cubo+Pride+Park/@52.9142877,-1.4558952,331m/data=!3m2!1e3!5s0x4879f10728c1eea1:0x877f85530d38bf6!4m15!1m8!3m7!1s0x4879f1072b0e2ebb:0x7c717035ff049b12!2sPride+Pl,+Derby+DE24+8QR!3b1!8m2!3d52.913692!4d-1.4543743!16s%2Fg%2F1th7mf0r!3m5!1s0x4879f16809382b33:0x6fccc05b8500d970!8m2!3d52.914146!4d-1.4544105!16s%2Fg%2F11w4j8v8z2?entry=ttu&g_ep=EgoyMDI1MDYyMi4wIKXMDSoASAFQAw%3D%3D) [1 Pride Place](https://www.google.com/maps/place/Cubo+Pride+Park/@52.9142877,-1.4558952,331m/data=!3m2!1e3!5s0x4879f10728c1eea1:0x877f85530d38bf6!4m15!1m8!3m7!1s0x4879f1072b0e2ebb:0x7c717035ff049b12!2sPride+Pl,+Derby+DE24+8QR!3b1!8m2!3d52.913692!4d-1.4543743!16s%2Fg%2F1th7mf0r!3m5!1s0x4879f16809382b33:0x6fccc05b8500d970!8m2!3d52.914146!4d-1.4544105!16s%2Fg%2F11w4j8v8z2?entry=ttu&g_ep=EgoyMDI1MDYyMi4wIKXMDSoASAFQAw%3D%3D) [Derby](https://www.google.com/maps/place/Cubo+Pride+Park/@52.9142877,-1.4558952,331m/data=!3m2!1e3!5s0x4879f10728c1eea1:0x877f85530d38bf6!4m15!1m8!3m7!1s0x4879f1072b0e2ebb:0x7c717035ff049b12!2sPride+Pl,+Derby+DE24+8QR!3b1!8m2!3d52.913692!4d-1.4543743!16s%2Fg%2F1th7mf0r!3m5!1s0x4879f16809382b33:0x6fccc05b8500d970!8m2!3d52.914146!4d-1.4544105!16s%2Fg%2F11w4j8v8z2?entry=ttu&g_ep=EgoyMDI1MDYyMi4wIKXMDSoASAFQAw%3D%3D) [DE24 8QR](https://www.google.com/maps/place/Cubo+Pride+Park/@52.9142877,-1.4558952,331m/data=!3m2!1e3!5s0x4879f10728c1eea1:0x877f85530d38bf6!4m15!1m8!3m7!1s0x4879f1072b0e2ebb:0x7c717035ff049b12!2sPride+Pl,+Derby+DE24+8QR!3b1!8m2!3d52.913692!4d-1.4543743!16s%2Fg%2F1th7mf0r!3m5!1s0x4879f16809382b33:0x6fccc05b8500d970!8m2!3d52.914146!4d-1.4544105!16s%2Fg%2F11w4j8v8z2?entry=ttu&g_ep=EgoyMDI1MDYyMi4wIKXMDSoASAFQAw%3D%3D) ** [Dublin](https://www.google.com/maps/search/Inniscarra,+Main+Street,+Rathcoole,+Dublin/@53.2810032,-6.4764674,17z/data=!3m1!4b1?entry=ttu)** [Inniscarra,](https://www.google.com/maps/search/Inniscarra,+Main+Street,+Rathcoole,+Dublin/@53.2810032,-6.4764674,17z/data=!3m1!4b1?entry=ttu) [Main Street,](https://www.google.com/maps/search/Inniscarra,+Main+Street,+Rathcoole,+Dublin/@53.2810032,-6.4764674,17z/data=!3m1!4b1?entry=ttu) [Rathcoole,](https://www.google.com/maps/search/Inniscarra,+Main+Street,+Rathcoole,+Dublin/@53.2810032,-6.4764674,17z/data=!3m1!4b1?entry=ttu) [Dublin](https://www.google.com/maps/search/Inniscarra,+Main+Street,+Rathcoole,+Dublin/@53.2810032,-6.4764674,17z/data=!3m1!4b1?entry=ttu) #### About Us DSP is a Data Management and Cloud Platform MSP that delivers enterprise grade support & consulting services for Oracle, Microsoft and Multi-Cloud technologies. ![Oracle Partner](https://content.dsp.co.uk/hubfs/logo-2021/Partner-logos/Oracle-logos/Colour/o-prtnr-cmyk-250.png) ![Microsoft Partner](https://content.dsp.co.uk/hubfs/Microsoft%20partner%20logos%20-%20July%202023/mic-sol-part-colour@200x.png) ![Google Cloud Partner](https://content.dsp.co.uk/hubfs/logo-2021/Partner-logos/GCP-Logos/Google-Cloud-Partner%20(1).svg) Registered Office: 30 City Road, London, EC1Y 2AB. Company Registration Number: 03898451 ```json { "@context" : "https://schema.org", "@type" : "BlogPosting", "dateModified" : "2024-10-01", "datePublished" : "2024-10-01", "headline" : "Oracle APEX Support for Hierarchical REST Source Data Profiles", "image" : [ "https://www.dsp.co.uk/hubfs/Oracle%20APEX%20Support%20for%20Hierarchical%20REST%20Source%20Data%20Profiles.png" ], "mainEntityOfPage" : { "@id" : "https://content.dsp.co.uk/oracle-apex-support-for-hierarchical-rest-source-data-profiles", "@type" : "WebPage" } } ```