No Code API Integration
APEX REST Data Sources, introduced in APEX 18.1, let you consume external REST APIs and use them as data sources for reports, charts, and forms without writing any PL/SQL. Define the REST endpoint once in Shared Components, and APEX handles the HTTP communication, JSON parsing, pagination, and caching automatically.
Creating a REST Data Source
Navigate to Shared Components then REST Data Sources and click Create. Provide the endpoint URL, and APEX will discover the response structure by making a test request. For a public API:
URL: https://jsonplaceholder.typicode.com/posts. APEX automatically detects the JSON array structure and creates columns for each field (userId, id, title, body). You can then use this REST Data Source as the source for any report region just like a local table or view.
Authentication
For APIs that require authentication, configure Web Credentials in Shared Components. APEX supports Basic Authentication, OAuth2 Client Credentials, OAuth2 Authorization Code, API Key (as header or query parameter), and HTTP Header Token. Create the credential once and reference it across multiple REST Data Sources:
Navigate to Shared Components, then Web Credentials, then Create. Select the authentication type, enter the credentials, and save. Then in your REST Data Source, select the web credential under the Authentication tab.
Server-Side Pagination
For APIs that return paginated results, configure the pagination parameters in the REST Data Source definition. APEX supports multiple pagination styles including page number and size, offset and limit, cursor-based, and next URL link. Configure these under the REST Data Source’s Operations tab, and APEX will automatically request subsequent pages as users scroll through the report.
Synchronization for Offline and Performance
REST Data Sources support local synchronization, which caches the API data in a local table. This improves performance by reducing API calls and provides offline resilience if the external API is temporarily unavailable. Enable synchronization in the REST Data Source definition and set a refresh interval. APEX creates and manages the local cache table automatically.
Transforming Data
If the API response structure does not match what your report needs, add a Row Selector to point to the correct array within the JSON response, and use column mappings to rename or filter fields. For complex transformations, you can add a PL/SQL Post-Processing function that receives the raw response and returns the transformed data.