Integrating Remote Data

The fastest way to work with data in Oracle APEX is to use tables or views in the local database. But many applications also need to integrate with remote data sources.

APEX makes it easy to work with remote Oracle and MySQL databases, REST APIs that follow common patterns such as Fusion Applications, ORDS, and OData services, as well as any other REST API. To simplify working with a set of REST APIs that use custom conventions, create a custom REST Data Source Adapter plug-in to encapsulate their rules and automate their use in your apps.

• Using Remote Oracle and MySQL Data
  Oracle REST Data Services' REST-Enabled SQL feature lets authenticated remote applications access data in an Oracle database or Oracle Cloud MySQL database system.
• Configuring Secure Web Credentials
  Use APEX Web Credentials to securely store the authentication details required for REST-Enabled SQL services and other REST API access.
• Using REST APIs as Data Sources
  To work with data from REST APIs, use a REST Data Source. Each one you create can support one or more standard REST operations to GET, PUT, POST, PATCH, or DELETE data from a REST API whose endpoint URL you configure.
• Invoking a REST Operation Declaratively
  Use the Invoke API page process to call a REST Data Source operation with no code.
• Using REST APIs in Code
  Sometimes your applications need to work with REST APIs in code. Using REST Data Sources is the maximally-declarative approach. In contrast, the MAKE_REST_REQUEST function in the APEX_WEB_SERVICE package provides additional control but requires writing more code.
• Extra Fusion Apps, ORDS, OData Features
  When related REST APIs follow common conventions for data filtering, sorting, attribute selection, object modification, and error reporting, Oracle APEX can provide powerful additional low-code functionality to your apps.
• Additional REST Data Source Features
  REST Data Sources can cache responses, synchronize reference data, add computed columns, apply declarative filtering and sorting, combine local and remote data, and support custom adapter plug-ins.
• Debugging REST API Errors
  If you work with REST APIs, one of them might return an error at runtime. Use APEX's built-in debug tracing facility to diagnose the problem and identify a fix.

Oracle REST Data Services' REST-Enabled SQL feature lets authenticated remote applications access data in an Oracle database or Oracle Cloud MySQL database system.

In Oracle APEX, you can reference a REST-Enabled SQL service by providing the endpoint URL and authentication credentials. Then you can use data in the remote database in any APEX region just like a local table. Oracle APEX handles all the details of interacting with the remote database.

• Referencing a REST-Enabled SQL Service
  Define a REST-Enabled SQL reference to work with the data in a REST-Enabled SQL Service.
• REST-Enabled SQL Service URL
  The format of the URL clients like APEX need to work with your REST-Enabled SQL Service depends on where its running.
• Using a REST-Enabled SQL Service
  Once you've created a REST-Enabled SQL Service reference at workspace level, you can use it almost anywhere you can configure a data source.

Use APEX Web Credentials to securely store the authentication details required for REST-Enabled SQL services and other REST API access.

Once you have saved a credential secret, it is encrypted and there is no way to retrieve it in clear text. APEX supports many different kinds of stored credentials, including:

Basic Authentication and OAuth2 can optionally delegate to a securely stored credential defined in the Oracle database as well.

To work with data from REST APIs, use a REST Data Source. Each one you create can support one or more standard REST operations to GET, PUT, POST, PATCH, or DELETE data from a REST API whose endpoint URL you configure.

At runtime, APEX automatically uses the web credential you associate with it to authenticate access. If the service supports returning data in pages, you can choose from a number of common pagination strategies.

You can specify various kinds of parameters at the service or operation level to get data into and out of an API call. To simplify using the data the API returns in your pages, you specify Data Profile columns that let APEX treat the data as a rowset. In many cases, App Builder can discover these column descriptions automatically, which you can then further adjust to fit your requirements.

Once you've defined a REST Data Source, you can use it almost anywhere in your application. This means in any APEX region, List of Values, or other dynamic list use cases. You do this by choosing a source Location of REST Source. When useful, you can easily augment the data retrieved from the REST API to perform additional filtering, ordering, and joins to combine remote data with local data. To improve performance, you can take advantage of various caching options as well as local synchronization of less-frequently-changing data on a schedule you define.

If your REST Data Source supports appropriate operations enabling insert, updates, and deletes, your pages can also use it to create and modify data as well. Some API suites define convention-based enhancements for filtering, sorting, and modifying data. APEX natively supports these features for Oracle Fusion Applications, Oracle REST Data Services, and OData. For other situations, you can create a custom REST adapter plug-in to provide the same seamless, extended data access for any other API suite you may need to integrate with.

• Using a Simple HTTP REST Data Source
  The Simple HTTP service is the most generic and flexible REST Data Source type APEX offers. By understanding its capabilities and how to configure them, you can confidently integrate with any necessary API.
• Service URL Uses Remote Server
  When working with REST APIs, you often use several endpoints from the same remote server. These service URLs share a base URL, including the domain name and any common path segments. Only the trailing part of the Service URL differs.
• Data Profile Helps Turn JSON Into Rows
  Each REST Data Source includes a Data Profile. Its details let APEX automatically convert a REST API's JSON response into rows like a database query returns.
• Retrieving Remote Data Page by Page
  To configure pagination behavior on your Simple HTTP REST Data Source, use the Settings tab on the edit page.
• REST Operations and Database Actions
  You can define one or more operations for each REST Data Source. APEX queries, inserts, updates, or deletes rows using the operation whose Database Action is appropriate to each task. It uses the operation's HTTP Method when sending the HTTP request to the REST API endpoint URL.
• Configuring a Request Body Template
  Your operations for Insert row and Update row need to send data to the REST API in the request body.
• Supply and Return Values with Parameters
  Just as PL/SQL procedures can define IN, OUT, and IN/OUT parameters, so can REST Data Sources. Define a parameter at the operation level if it's specific to that action, or at the data source level if it's relevant to all operations.

Use the Invoke API page process to call a REST Data Source operation with no code.

As shown below, set the API Type to REST Source, pick a REST Data Source, and choose an operation. Then configure parameter values to provide input values or map output values to page items. Your workflows can do the same with the Invoke API activity configuring parameters using workflow variables.

As shown below, the Onboard New Employee page process type is Invoke API. It is configured to invoke the Onboard New Employee operation of the Employee Onboarding REST Data Source.

Indented below the Onboard New Employee page process, notice the Parameters heading. Each updatable operation parameter appears there. As shown below, you can source the value of a parameter like P_SAL from a page item like P57_SAL.

When useful, as shown below you can also provide a parameter's value using an expression. For example, as shown below the value of the P_HIREDATE parameter is the result of calling an application-specific helper function json_date(). It accepts the name of a page item or workflow version variable and returns a date value for substitution into a JSON payload. Its result is the unquoted four characters null if the item's value is null or the double-quoted date in the ISO 8601 format. Many REST APIs expect their date values in this standard 2025-06-28T13:25:32Z format.

create or replace function json_date(
    p_item in varchar2)
    return    varchar2
is
    l_value varchar2(255) := apex_session_state.get_timestamp(p_item);
begin
    return case
            when l_value is null then 'null'
            else
                apex_string.format(
                    '"%s"',
                    to_char(
                        apex_session_state.get_timestamp(p_item),
                        apex_json.c_date_iso8601))
           end;
end;

As shown below, the Employee Onboarding REST Data Source has just the one Onboard New Employee operation the page invokes. If a REST API you're integrating with returns no response payload, or you have no need to reference values from it, then no Data Profile is needed. Notice that this REST Data Source has zero (0) Data Profile columns. It defines a single, static HTTP Header type parameter to send the Content-Type header with the value application/json.

Notice the operation parameters below corresponding to each value the Request Body Template requires.

{
    "job"     :"#P_JOB#"
   ,"mgr"     : #P_MGR!NULL#
   ,"sal"     : #P_SAL!NULL#
   ,"comm"    : #P_COMM!NULL#
   ,"empno"   : #P_EMPNO!NULL#
   ,"ename"   :"#P_ENAME#"
   ,"deptno"  : #P_DEPTNO!NULL#
   ,"hiredate": #P_HIREDATE!RAW#
}

Sometimes your applications need to work with REST APIs in code. Using REST Data Sources is the maximally-declarative approach. In contrast, the MAKE_REST_REQUEST function in the APEX_WEB_SERVICE package provides additional control but requires writing more code.

• Processing REST Source Rows in Code
  Use the APEX_EXEC package to process rows from REST Data Sources programmatically.
• Invoking a REST Operation Programmatically
  You can invoke a REST Data Source operation programmatically, treating it like a function call instead of a row set.
• Calling REST APIs Without a Data Source
  Use the APEX_WEB_SERVICE package to call a REST API that requires a binary payload or returns a binary response. Binary payloads require this approach. You can also use the package for general REST calls, but then you must code many tasks that a REST Data Source handles declaratively.

When related REST APIs follow common conventions for data filtering, sorting, attribute selection, object modification, and error reporting, Oracle APEX can provide powerful additional low-code functionality to your apps.

Their goal is to make integrating remote data as easy as working with a local database table. When you create a REST Data Source using one of these types, the wizard automatically discovers all supported operations and Data Profile columns based on understanding their extended custom metadata. At runtime, when a region uses one of these REST Data Sources, the APEX engine dynamically adds any necessary request parameters and builds request body payloads. No need to define any of the common parameters or worry about maintaining request body templates.

APEX also automatically delegates filtering and sorting to the backend system when using these types of REST Data Sources. This is important for optimized application performance because it leverages backend indexes to quickly return only relevant rows to the APEX application. By default, Simple HTTP data sources must retrieve all rows to the APEX engine database to then perform filtering and sorting there instead.

REST Data Sources can cache responses, synchronize reference data, add computed columns, apply declarative filtering and sorting, combine local and remote data, and support custom adapter plug-ins.

Finally, if you work with REST APIs that follow a set of conventions APEX doesn't support natively, you can create a custom plug-in to add a new REST Data Source type that lets your teammates automatically benefit from all the declarative smarts you build into it.

• Caching a GET Operation's Response
  To improve performance and reduce unnecessary HTTP calls, you can enable server-side caching for selected REST Data Source GET operations.
• Synchronizing Data Locally
  Use local data synchronization to cache less-frequently changing REST Data Source data for faster List of Values, lookup, and region access.
• Adding Computed Data Profile Columns
  Add computed columns to enrich response payload data, then use their values in regions, Lists of Values, or programmatic code.
• Configuring Default Filtering and Sorting
  Configure default filter and sort expressions wherever a REST Data Source type supports them.
• Filter, Sort, Join, or Aggregate Remote Data
  Complement APEX’s JSON row and column extraction with SQL to filter, sort, join, or aggregate remote REST API data as needed.
• Creating a Custom REST Source Plug-in
  If you work with REST APIs that follow a set of conventions APEX doesn't support natively, create a custom REST Data Source plug-in.

If you work with REST APIs, one of them might return an error at runtime. Use APEX's built-in debug tracing facility to diagnose the problem and identify a fix.

The figure below shows an Interactive Report that uses a REST Data Source based on the Fusion Applications Sales Opportunities REST API. The user chooses Actions > Data > Sort to configure their preferred sorting order. Notice that they choose the Assignment Mode column for the third sorting column. When they click (Apply) to refresh the interactive report with the data sorted in this new way, they receive a runtime error.

ORA-20999: REST Data Source returned an HTTP error: HTTP 400: Bad request

When running your application from App Builder, by default, a developer toolbar appears at the bottom of the page. Notice when an error occurs that it displays a warning icon. By choosing Debug > Current Debug Level > Info, you can enable debug tracing. When you set the debug level, the current page refreshes to capture a debug trace. While debug tracing is enabled, each subsequent HTTP request the browser makes to your APEX application produces a distinct debug trace log entry. To browse the latest debug trace entries, choose Debug > View Debug from the developer toolbar.

As shown below, the debug viewer opens in a new window and displays recent debug request traces in an Interactive Report. By default they should be sorted descending by Timestamp so the most recent entries are first. Notice the viewer's Interactive Report region already has an application id and page id filter applied to focus your attention on the most likely trace entries you want to see. However, you can use standard features of the Interactive Report to disable a filter or remove it if you need to see a more complete set of trace log entries.

In addition to the Timestamp column, the Path Info can also help identify the right request to examine in more detail. Path Info will be show for an initial page render, accept REQUESTVAL/BUTTONNAME for a page submit, and one of ajax, ajax plugin, or ajax process for partial page requests that APEX components make to refresh a section of a page. In this case when the user applies a new sort condition the Interactive Report native page item plug-in does a partial refresh so Path Info is ajax plugin. The figure highlights the most recent request debug trace record. Click on the view identifier link to drill down to inspect the trace detail details for that request.

The debug viewer for a single request shows the complete logging detail. The Info level of debug tracing is usually enough to understand most problems. On occasion, when you require additional detail, choose a more granular debug trace level. A timeline accompanies the page to help understand at what point during the request work is performed that takes time and where errors occur. As shown below, errors display as red dots on the timeline. Hover over a dot to see an error message summary, and click a dot to navigate to that line of the debug trace log.

fetch_adfbc: HTTP 400 - The attribute AssignmentMode is not sortable

making GET request to https://server/crmRestApi/resources/11.13.18.05/opportunities
?onlyData=true
&fields=TargetPartyName,Revenue,CurrencyCode,WinProb,PrimaryContactPartyName,...
&orderBy=WinProb:desc,Revenue:desc,AssignmentMode:asc
&limit=500, using request body:

:
&orderBy=WinProb:desc,Revenue:desc,AssignmentMode:asc
:

The error message clearly explains that the server cannot sort on this attribute, so you've identified the problem. To remedy the situation, you just need to adjust one setting on the ASSIGNMENTMODE column in the Interactive Report to disable sorting. As shown below, you do this with a single click on the Enable Users To > Sort switch after selecting the column in the Rendering tab.

After diagnosing the problem using the debug trace log, and adjusting the page to avoid the issue in the future, as shown below, you can see the end user no longer has Assignment Mode available in the list of columns they can sort on. You can disable the debug tracing using the developer toolbar until the next time it proves useful. The figure shows the end user Interactive Report Action > Sort dialog where the end user configures the attributes to user to order the results.

Define a REST-Enabled SQL reference to work with the data in a REST-Enabled SQL Service.

A REST-Enabled SQL reference is a workspace-level definition. Once created, you can use it in any application.As shown below, the Create REST Enabled SQL Service wizard in Workspace Utilities lets you assign a meaningful name to the endpoint.

As shown below, you can enter new authentication credentials on the second step of the wizard, or choose a credential you might have defined previously. Most REST-Enabled SQL services use basic authentication over HTTPS. This includes ones on-premises and in Autonomous AI Database.

When you click (Create) APEX validates the endpoint URL and authentication to confirm everything is working. As shown below, if all is well, you'll see a confirmation to that effect.

The format of the URL clients like APEX need to work with your REST-Enabled SQL Service depends on where its running.

https://domainname/ords/your_schema_alias

ocid1.databasetoolsconnection.oc1.eu-frankfurt-1.xxxxxx

https://sql.dbtools.yourregion.oci.oraclecloud.com/20201005/ords/connectionocid

Once you've created a REST-Enabled SQL Service reference at workspace level, you can use it almost anywhere you can configure a data source.

You can use a REST-Enabled SQL Service reference in any APEX region, List of Values, or other dynamic list use cases. You do this by choosing a source Location of REST-Enabled SQL. For example, as shown below, the Employees Interactive Report region uses the EMP table in the Remote Oracle Database defined above.

All the Source > Type options are available against a remote Oracle database, so you can also use a SQL Statement and Function Body Returning SQL Query as well. For a remote Oracle database, all read and write operations are supported. In contrast, remote MySQL databases are read-only. While you can display MySQL data in any APEX region including Form and Interactive Grid, the latter two regions' automatic DML page processes are not yet supported.

The Simple HTTP service is the most generic and flexible REST Data Source type APEX offers. By understanding its capabilities and how to configure them, you can confidently integrate with any necessary API.

If the service you need to use supports a GET method, then the Create REST Data Source wizard gives you a head start by automatically discovering the data profile columns based on the JSON response payload. If not, you can always create and configure the REST Data Source manually.

https://example.com/ords/cloudcompanion/emp

$ curl -L https://example.com/ords/cloudcompanion/emp

{
  "items": [
    {
      "empno": 7839,
      "ename": "KING",
      "job": "PRESIDENT",
      "mgr": null,
      "hiredate": "1981-11-17T00:00:00Z",
      "sal": 5000,
      "comm": null,
      "deptno": 10,
      "links": [
        {
          "rel": "self",
          "href": "https://example.com/ords/cloudcompanion/emp/7839"
        }
      ]
    },
    ⋮
  ],
  "hasMore": false,
  "limit": 25,
  "offset": 0,
  "count": 14,
  "links": [
    {
      "rel": "self",
      "href": "https://example.com/ords/cloudcompanion/emp/"
    },
    ⋮
  ]
}

Before defining a REST Data Source for the endpoint, consult the documentation for the service to understand whether it supports retrieving data in pages. This service supports using the limit query string parameter to set a page size, and the offset parameter to indicate a number of rows to skip.

$ curl -L "https://example.com/ords/cloudcompanion/emp?limit=2"

$ curl -L "https://example.com/ords/cloudcompanion/emp?offset=2&limit=2"

To create a REST Data Source to work with this API in your APEX application, as shown below, just use Create REST Data Source wizard from the Shared Components > REST Data Sources page. To get started, all you need to enter is the endpoint URL and proceed to the subsequent wizard steps.

When working with REST APIs, you often use several endpoints from the same remote server. These service URLs share a base URL, including the domain name and any common path segments. Only the trailing part of the Service URL differs.

https://example.com/ords/cloudcompanion/emp

https://example.com/ords/cloudcompanion/dept

APEX supports this common usage pattern by storing the base URL in a Remote Server definition. When you create a new REST Data Source, APEX notices if the endpoint URL matches the base URL of an existing Remote Server. If not, it helps you define a new Remote Server to reuse later. As shown below, APEX detects the Employees service endpoint URL matches an existing example.com Remote Server, and assigns the trailing emp portion of the URL to the Service URL Path field.

At runtime, APEX concatenates a REST Data Source's Remote Server Base URL and its Service URL Path to determine the full URL to use. APEX keeps these concepts separate to simplify another common requirement of REST integration. In your development environment, your application's REST Data Sources may work against a set of test endpoints. It is highly likely that in your production environment the application will need to work against the live endpoints that will reside on different production domains. APEX lets you set the base URL for each Remote Server differently in each environment, making it trivially easy to have your application talk to the live REST endpoints in production. The different Remote Server base URL values stay "sticky" to each environment, so when you update your production environment with a new version of the application, your REST Data Sources continue to interact with the correct production REST APIs.

Each REST Data Source includes a Data Profile. Its details let APEX automatically convert a REST API's JSON response into rows like a database query returns.

If your endpoint supports GET, APEX can analyze a response payload to discover a starting set of Data Profile columns. As shown below, after this discovery step, the wizard displays the data the service returns in rows and columns. It uses the suggested Data Profile column information in the process.

To peek at the JSON response APEX analyzed, click on the Response Body tab. As shown below, the Employees service returns a top-level items array of JSON objects containing employee information. Each employee JSON object contains a nested links array.

On the Data Profile tab, as shown below, you see APEX identified the items property as the Row Selector. This JSON path expression identifies the root JSON structure to convert into rows. The table below lists each discovered data profile column's name, data type, and JSON selector. APEX uses these details to extract each column's value from the JSON payload. Notice the LINKS column is of type ARRAY. The REL and HREF columns are part of this nested links array. You can click the "x" in the Remove column to omit any columns you don't want, or you can edit the Data Profile later in App Builder.

For a column like HIREDATE if automatic discovery picks the wrong data type, you can adjust it later in the REST Data Source edit page. For example, say the hiredate property in the remote REST service is a value with only date and time information. If it does not include sub-second precision or a time zone, as shown below you can first edit the Data Profile columns, then edit the specific HIREDATE Data Profile column to adjust the data type to DATE and change its format mask to the one for an ISO 8601 standard date/time used by this REST API: YYYY-MM-DD"T"HH24:MI:SS"Z"

To configure pagination behavior on your Simple HTTP REST Data Source, use the Settings tab on the edit page.

As shown below, choose an appropriate Pagination Type from the list. This identifies the basic strategy your REST API uses to retrieve data page by page. Then configure the specific names your REST API uses in the different roles of your selected pagination strategy. This example Employees service supports specifying a page size with a limit parameter and a fetch offset. The hasMore property value of true lets APEX know there are more rows to retrieve.

After configuring this information, APEX automatically uses the configured pagination strategy whenever you use this REST Data Source in regions that let users page through rows. For example, as shown below, an Interactive Report can use the Employees (Simple HTTP) REST Data Source by setting the Location property of the region to REST Source. Notice the Data Profile column names appear as the region's Columns in the rendering tree.

making GET request to https://example.com/ords/cloudcompanion/emp/?offset=5&limit=5

making GET request to https://example.com/ords/cloudcompanion/emp/?offset=5&limit=5

You can define one or more operations for each REST Data Source. APEX queries, inserts, updates, or deletes rows using the operation whose Database Action is appropriate to each task. It uses the operation's HTTP Method when sending the HTTP request to the REST API endpoint URL.

As shown below, a Simple HTTP REST Data Source you create with automatic discovery starts with a GET operation for the Fetch Rows database action. Until you assign a more descriptive operation name, APEX uses its method as an alternative name. Defined this way, the REST Data Source fully supports read-only use in any region or List of Values.

However, to modify data with a Form or Interactive Grid you need to add additional Fetch single row, Insert row, Update row, and Delete row operations as shown below.

For example, to enforce lost update protection the Automatic DML page processes for Form and Interactive Grid regions use the Fetch single row database action to find a row to display by primary key. As shown below, the GET operation for this Fetch single row database action defines an additional URL Pattern to add onto the REST Source Base URL.

It's very useful that Simple HTTP REST Data Source operations can reference the case-sensitive Selector name of primary key columns as virtual parameters in the URL pattern. The lowercase empno name is the JSON selector for the primary key EMPNO column in this REST Data Source's Data Profile. So notice below the URL Pattern references the primary key value of the current row being modified using the :empno bind variable notation. The PUT and DELETE operations do the same.

As shown below, the P51_EMPNO page item in the Employee form region enables the Primary Key switch. Page Designer should infer the setting automatically from the Data Profile of the REST Data Source the Form region references. But it's always good to double-check. This ensures that the appropriate REST Data Source operation URL referencing the value of the :empno primary key data profile column selector as an implicit parameter will use the correct value.

making GET request to https://example.com/ords/cloudcompanion/emp/7369

Your operations for Insert row and Update row need to send data to the REST API in the request body.

As shown below, you configure the contents of this payload using the operation's Request Body Template. It can contain a mix of JSON syntax and #NAME# substitutions. The names of region data source columns are available to reference automatically, as well as any parameters of type Request Body you might need to define. Notice the column name substitutions are not surrounded by double quotes. At runtime APEX replaces the substitutions with appropriately escaped values that contain the double quotes if needed. If the REST API you work with requires passing a null value using the JSON null keyword instead of empty double quotes, then reference the substitution like #COMM!NULL# using the !NULL modifier.

The figure shows the Request Body Template for the Employees PUT operation. It's a JSON object template including a mix of JSON properties and data profile column substitutions.

#PAYLOAD!RAW#

The PAYLOAD parameter will appear in any context where you use the REST Data Source and you can assign a dynamic value to the parameter using a PL/SQL expression. The !RAW modifier asks APEX to treat the value of the parameter verbatim, avoiding the normal JSON escaping it performs when substituting parameter or data source column values.

Just as PL/SQL procedures can define IN, OUT, and IN/OUT parameters, so can REST Data Sources. Define a parameter at the operation level if it's specific to that action, or at the data source level if it's relevant to all operations.

You can configure a default value for each one, mark it as required if relevant, and indicate if the value is static. Any parameter whose value is not static can be set on the page or List of Values where you use the REST Data Source so different usages can pass in appropriate values. In Page Designer, they appear beneath a Parameters node indented inside the region in the rendering tree. The table below describes the most common parameter types.

For example, to send a Content-Type header with the fixed value application/json, use an HTTP Header type parameter with IN direction marked as static.

In contrast, for an HTTP header or query string parameter related to authentication and authorization, define an appropriate Web Credential of the appropriate type instead. Then reference that web credential in your REST Data Source. This ensures security-related values are obfuscated in debug logs and that the credential secret is securely stored and handled.

Use the APEX_EXEC package to process rows from REST Data Sources programmatically.

If the REST Data Source defines operations supporting all Database Action values, then you can both query and modify data. Using appropriate package functions, you open a "context" for working with rows. When querying rows, you iterate through the results in a loop. When modifying rows you add rows to the context and then execute the DML operations for those rows. When done, you close the context to release its resources.

• Querying REST Source Rows in Code
  Use OPEN_REST_SOURCE_QUERY in the APEX_EXEC package to query rows from a REST Data Source. It automatically executes the operation with the Fetch rows Database Action.
• Modifying REST Source Rows in Code
  Use OPEN_REST_SOURCE_DML_CONTEXT in the APEX_EXEC package to modify a row from a REST Data Source.

You can invoke a REST Data Source operation programmatically, treating it like a function call instead of a row set.

https://example.com/ords/cloudcompanion/orders/singleBookCreate

{
   "isbn": "978‑1565926912",
   "quantity": 1
}

{
    "orderNumber": 12345,
    "estimatedDelivery": "2025-07-06T00:00:00"
}

After configuring a REST Data Source with appropriate Data Profile columns and a POST operation having the required Request Body Template, you invoke it programmatically using the EXECUTE_REST_SOURCE procedure in the APEX_EXEC package.

• Configuring REST Source for Invocation
  When a REST API has no GET method or OpenAPI description to automatically discover, you can define the operations and data profile columns manually.
• Invoking REST Operation in Code
  Use EXECUTE_REST_SOURCE in the APEX_EXEC package to invoke a REST operation in code.

Use the APEX_WEB_SERVICE package to call a REST API that requires a binary payload or returns a binary response. Binary payloads require this approach. You can also use the package for general REST calls, but then you must code many tasks that a REST Data Source handles declaratively.

For more information, see MAKE_REST_REQUEST Function in Oracle APEX API Reference.

For easier comparison, the example below calls the same Single Book Order endpoint without using a REST Data Source.

declare
    c_endpoint_url constant varchar2(200) :=
       'https://example.com/ords/cloudcompanion/orders/singleBookCreate';
    l_new_order_number      number;
    l_estimated_delivery    date;
    l_request               json_object_t;
    l_response_payload      clob;
    l_response              json_object_t;
begin
    -- Build the request body JSON
    l_request := json_object_t();
    l_request.put('isbn','978‑1565926912');
    l_request.put('quantity',1);
    -- POST the JSON request body to the REST API endpoint URL
    l_response_payload :=
        apex_web_service.make_rest_request(
            p_http_method          => 'POST',
            p_url                  => c_endpoint_url,
            p_body                 => l_request.to_clob,
            p_credential_static_id => null /* Non-null for auth case */,
            p_token_url            => null /* Non-null for OAuth */);
    -- If HTTP status code indicates a success and response is not empty
    if apex_web_service.g_status_code = 200
       and l_response_payload is not null
       and dbms_lob.getlength(l_response_payload) > 0
    then
        -- Work with the response body as JSON
        l_response := json_object_t(l_response_payload);
        -- Reference the values from the response JSON
        l_new_order_number   := l_response.get_number('orderNumber');
        l_estimated_delivery := l_response.get_date('estimatedDelivery');
        :P55_SUCCESS_MESSAGE := apex_string.format(
                                   'New Order %s should deliver %s',
                                   l_new_order_number,
                                   to_char(l_estimated_delivery,
                                           'DD-MON-YYYY'));
    end if;
end;

To improve performance and reduce unnecessary HTTP calls, you can enable server-side caching for selected REST Data Source GET operations.

This lets your application reuse previously retrieved results instead of re-fetching them from the remote service. Caching options let you control whether results are shared across all users, stored per user, or scoped just to a specific session. You can also configure when cached content becomes invalid using a simple duration in minutes or a more advanced DBMS_SCHEDULER calendaring expression. For example, you might cache responses until the top of the hour or midnight, or keep them for 15 minutes. Setting the invalidation to 0 means the data is cached only during the current page execution. That can be useful if multiple regions on the same page use the same REST Data Source. APEX manages this caching entirely on the server, so it does not rely on the browser's cache.

Use local data synchronization to cache less-frequently changing REST Data Source data for faster List of Values, lookup, and region access.

For less-frequently changing remote data, APEX can automatically create and manage a local cache table for a REST Data Source. You can configure a synchronization schedule, or call the SYNCHRONIZE_DATA procedure in the APEX_REST_SOURCE_SYNC package to refresh the data on-demand. Then enable the List of Values Use Synchronization Table? switch or a region's Use Local Table switch in the REST Synchronization section. When this option is enabled, APEX automatically uses the locally cached data instead of requesting the data over REST on each access.

Add computed columns to enrich response payload data, then use their values in regions, Lists of Values, or programmatic code.

{
   ⋮
  "firstName": "Jordan",
  "lastName": "Jones"
   ⋮
}

FIRST_NAME||' '||LAST_NAME

Other types of computed columns include Lookup and SQL Query (return single value). Once defined, the computed column is a read-only value you can reference anywhere you use the REST Data Source throughout your application.

Configure default filter and sort expressions wherever a REST Data Source type supports them.

For example, the Oracle Cloud Applications, ORDS, and OData REST Data Source types all support their own declarative filter and order by syntax. By enabling the External Filter and Order By switch on a region or List of Value, you can specify either or both using an appropriately structured clause.

StatusCode = 'OPEN' and WinProb >= 75

WinProb:desc,PrimaryContactPartyName:asc

For more information on external filter and order by syntax for Fusion Applications REST APIs, see REST Data Source Runtime Features for Oracle Cloud SaaS Apps and BOSS REST Data Source Runtime Features in Oracle APEX App Builder User’s Guide.

When configuring the same features for ORDS or OData REST Data Source, check with the documentation of those services to understand the filtering and sorting syntax they expect.

Complement APEX’s JSON row and column extraction with SQL to filter, sort, join, or aggregate remote REST API data as needed.

• Understanding the JSON_TABLE Query
  The APEX engine turns a REST Data Source's JSON response payload into rows and column using the Data Profile information.
• Locally Filtering and Sorting Remote Data
  If the REST API you use doesn't let you filter or sort the data, you can do it in the local APEX database instead. Since these follow-up actions occur after the APEX engine retrieves the JSON response from the REST API, it's known as Local Post-Processing.
• Using Custom SQL Involving Remote Data
  Set a region or LOV's Local Post-Processing to SQL Query to query remote data from the #APEX$SOURCE_DATA# inline view.

If you work with REST APIs that follow a set of conventions APEX doesn't support natively, create a custom REST Data Source plug-in.

This kind of extension adds a new REST Data Source type to the list in the Create REST Data Source wizard. By choosing your new kind of data source, your teammates can automatically benefit from all the declarative smarts you build into it.

Creating a REST Data Source plug-in requires writing PL/SQL code that implements one or more contract procedures the APEX engine defines. The high-level contracts this type of plug-in can implement are:

You can find a basic, working example of a REST Source plug-in to study and experiment with on the Oracle APEX GitHub repository. After choosing the APEX release you're using, see the plugins/rest-source subdirectory. It implements the Capabilities, Discover, and Fetch contracts.

Use OPEN_REST_SOURCE_QUERY in the APEX_EXEC package to query rows from a REST Data Source. It automatically executes the operation with the Fetch rows Database Action.

To define the data source columns whose values you want to retrieve, use a variable of type APEX_EXEC.T_COLUMNS and call ADD_COLUMN to add one or more column names to it. Then pass it as the p_columns argument value.

To set operation parameters, use a variable of type APEX_EXEC.T_PARAMETERS, call ADD_PARAMETER to add one or more parameters to the list, then pass it as the p_parameters value. Use a while loop with NEXT_ROW as the loop condition to iterate through the results. When done, or when an exception occurs, call CLOSE to clean up context resources.

A simple example that retrieves and iterates over all rows from the Employees (Simple HTTP) REST Data Source is below. Notice the code references the REST Data Source using its Static ID employees_simple_http.

declare
    l_params   apex_exec.t_parameters;
    l_cols     apex_exec.t_columns;
    l_ctx      apex_exec.t_context;
    l_empno    number;
    l_ename    varchar2(255);
    l_hiredate date;
    l_empinfo  apex_t_varchar2;
begin
    apex_exec.add_column(
        p_columns     => l_cols,
        p_column_name => 'EMPNO');
    apex_exec.add_column(
        p_columns     => l_cols,
        p_column_name => 'ENAME');
    apex_exec.add_column(
        p_columns     => l_cols,
        p_column_name => 'HIREDATE');
    apex_exec.add_parameter(
        p_parameters => l_params,
        p_name       => 'MY_PARAM',
        p_value      => 1234);
    l_ctx := apex_exec.open_rest_source_query(
                p_static_id  => 'employees_simple_http',
                p_columns    => l_cols,
                p_parameters => l_params);
    while apex_exec.next_row(l_ctx) loop
        l_empno    := apex_exec.get_number(l_ctx,'EMPNO');
        l_ename    := apex_exec.get_varchar2(l_ctx,'ENAME');
        l_hiredate := apex_exec.get_date(l_ctx,'HIREDATE');
        -- Do something here with current row values
    end loop;
    apex_exec.close(l_ctx);
exception
    when others then
        apex_exec.close(l_ctx);
        raise;
end;

For more information, see OPEN_REST_SOURCE_QUERY Function in Oracle APEX API Reference.

Be aware that by default Simple HTTP REST Data Sources perform ordering and filtering in the APEX database after retrieving all rows from the REST API. Other convention-based REST Data Source types automatically delegate filtering, sorting, and pagination to the REST API for an optimized data exchange. APEX offers these smarter REST Data Source types for Fusion Applications, Oracle REST Data Services, and OData.

Use OPEN_REST_SOURCE_DML_CONTEXT in the APEX_EXEC package to modify a row from a REST Data Source.

To define the data source columns whose values you want to work with, use a variable of type APEX_EXEC.T_COLUMNS and call ADD_COLUMN to add one or more column names to it. Make sure to pass the p_data_type parameter of each column, too, as well as passing true for p_is_primary_key on at least one of the columns. Then pass this column list as the p_columns parameter value in your call to open the DML context.

To supply parameter values, use a variable of type APEX_EXEC.T_PARAMETERS, call ADD_PARAMETER to add one or more parameters to it, then pass the parameters variable as the value of p_parameters.

Once you create a "context" to work with rows, call the ADD_DML_ROW procedure to add a new current row to the context, passing a constant of type APEX_EXEC.T_DML_OPERATION as the value of the p_operation parameter. To set the column values on the current row, call SET_VALUE. To process the row you added to the context, call EXECUTE_DML. When done, or when an exception occurs, call CLOSE to clean up context resources.

For more information, see OPEN_REST_SOURCE_DML_CONTEXT Function in Oracle APEX API Reference.

• Inserting a REST Source Row in Code
  Review an example of inserting data using a REST Data Source.
• Updating a REST Source Row in Code
  Review an example of updating a row using a REST Data Source.
• Deleting a REST Source Row in Code
  Review an example of deleting a row from a REST Data Source.

When a REST API has no GET method or OpenAPI description to automatically discover, you can define the operations and data profile columns manually.

After entering the endpoint URL in the wizard, on a following wizard step clicking the (Create REST Source Manually) button creates the data source you can adjust further in the edit pages.

When you create a REST Data Source manually, the wizard creates placeholder operations and data profile columns. Start by adjusting the Data Profile. You can delete the third placeholder data profile column, and edit the two existing ones to adjust the column name, data type, and JSON Selector as appropriate for the current API. As shown below, to access the two values in the response payload the Data Profiles columns are updated to reflect a numeric ORDER_NUMBER column with selector orderNumber and a date column ESTIMATED_DELIVERY.

YYYY-MM-DD"T"HH24:MI:SS

{
   "isbn"    : "#P_ISBN#",
   "quantity":  #P_QUANTITY#
}

You can also include two Data Profile Column type parameters to access the API response payload values as out parameters.

After configuring Data Profile columns and POST operation, the Single Book Order REST Data Source appears below. Some REST APIs expect the Content-Type header with the value application/json so you can add an appropriate static Header-type parameter to send when needed. Since the service returns a single JSON object response instead of an array, notice as well that Pagination Type is set to No Pagination.

Use EXECUTE_REST_SOURCE in the APEX_EXEC package to invoke a REST operation in code.

To supply parameter values, use a variable of type APEX_EXEC.T_PARAMETERS, call ADD_PARAMETER to add one or more parameters to it, then pass the parameters variable as the value of p_parameters. After invoking the REST Data Source operation, use GET_PARAMETER_VARCHAR2 or GET_PARAMETER_CLOB to retrieve the out parameters by name.

A simple example of invoking the Simple Book Create operation of the Single Book Order REST Data Source appears below. Notice it uses the Static ID of the data source and the operation.

declare
    l_params             apex_exec.t_parameters;
    l_new_order_number   number;
    l_estimated_delivery date;
begin
    -- Set inbound parameter values
    apex_exec.add_parameter(
        p_parameters => l_params,
        p_name       => 'P_ISBN',
        p_value      => '978‑1565926912');
    apex_exec.add_parameter(
        p_parameters => l_params,
        p_name       => 'P_QUANTITY',
        p_value      => 1);
    -- Invoke using static ids of data source and operation
    apex_exec.execute_rest_source(
        p_static_id           => 'single_book_order',
        p_operation_static_id => 'single_book_create',
        p_parameters          => l_params);
    -- Reference the values from the response as out parameters
    l_new_order_number       := apex_exec.get_parameter_varchar2(
                                   l_params,
                                   'ORDER_NUMBER');
    l_estimated_delivery := to_date(apex_exec.get_parameter_varchar2(
                                    l_params,
                                    'ESTIMATED_DELIVERY'));
    :P55_SUCCESS_MESSAGE := apex_string.format(
                               'New Order %s should deliver %s',
                               l_new_order_number,
                               to_char(l_estimated_delivery,
                                       'DD-MON-YYYY'));
end;

The APEX engine turns a REST Data Source's JSON response payload into rows and column using the Data Profile information.

It dynamically produces a SELECT statement using the JSON_TABLE() operator to map JSON data into relational query results. For example, in a REST Data Source based on an Employees REST API, assume its Data Profile defines three columns:

{
    "items": [
        {
            "empno": 7839,
            "ename": "KING",
            "job": "PRESIDENT",
            "mgr": null,
            "hiredate": "1981-11-17T00:00:00Z",
            "sal": 5000,
            "comm": null,
            "deptno": 10
        },
        ⋮
        {
            "empno": 7788,
            "ename": "SCOTT",
            "job": "ANALYST",
            "mgr": 7566,
            "hiredate": "1982-12-09T00:00:00Z",
            "sal": 3000,
            "comm": null,
            "deptno": 20
        }
    ]
}

select "EMPNO",
       "ENAME",
       "HIREDATE"
from (
    select *
    from (
    /* Cast extracted data to declared data type here */
    select to_number("EMPNO")                    as "EMPNO",
           "ENAME"                               as "ENAME",
           to_date("HIREDATE",
                   'YYYY-MM-DD"T"HH24:MI:SS"Z"') as "HIREDATE"
        from
            /* Use the row selector and column selectors here */
            json_table ( response_payload format json,'$."items"[*]'
                columns (
                    "EMPNO"    varchar2 ( 4000 ) path '$."empno"',
                    "ENAME"    varchar2 ( 4000 ) path '$."ename"',
                    "HIREDATE" varchar2 ( 4000 ) path '$."hiredate"'
                )
            )
    )
)

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
      7839 KING       17-NOV-81
      7698 BLAKE      01-MAY-81
      7782 CLARK      09-JUN-81
      7566 JONES      02-APR-81
      7788 SCOTT      09-DEC-82

If the REST API you use doesn't let you filter or sort the data, you can do it in the local APEX database instead. Since these follow-up actions occur after the APEX engine retrieves the JSON response from the REST API, it's known as Local Post-Processing.

When you set this property of your region or List of Values to Where/Order By Clause, then additional Where Clause and Order by Type properties appear. If you choose a Static Value type of order by clause, then can just type it in yourself. Otherwise the order by clause is determined by the Order by Item you configure.

The WHERE clause and ORDER BY clauses you configure for Local Post-Processing are added to the outermost SQL query in the APEX engine's JSON_TABLE() statement. As a consequence, these clauses can only reference column names that are included in that outermost SELECT list. If you need to filter on or order by a column that you didn't initially include in a region, add the additional column and leave it hidden.

HIREDATE > DATE'1981-10-31'

HIREDATE DESC

select "EMPNO",
       "ENAME",
       "HIREDATE"
from (
    /* Rest of APEX JSON_TABLE() query omitted for brevity */
)
where HIREDATE > DATE'1981-10-31'
order by HIREDATE DESC

This additional configuration results in retrieving the original set of Employees from the remote REST API, but then displaying only those in the desired hire date range, ordered as indicated.

Set a region or LOV's Local Post-Processing to SQL Query to query remote data from the #APEX$SOURCE_DATA# inline view.

select EMPNO,
       ENAME,
       HIREDATE
  from #APEX$SOURCE_DATA#

But you can edit this SQL statement to add additional WHERE clauses, JOIN clauses, additional SELECT list columns or expressions, or really anything that produces a resulting valid SELECT statement.

select r.EMPNO,
       r.ENAME,
       r.HIREDATE,
       c.NAME as COURSE_NAME,
       t.STATUS
  from #APEX$SOURCE_DATA# r
  join EMP_TRAINING t
    on r.EMPNO = t.EMPNO
  join EMP_COURSE c
    on t.COURSE_ID = c.ID
 where t.STATUS in ('COMPLETED','CERTIFIED')

This means your Local Post-Processing SQL statement can also compute aggregates, rename columns, or simply do whatever your use case requires. If you need to dynamically construct your SQL statement, you can use the PL/SQL Function Body Returning SQL Query type instead.

Review an example of inserting data using a REST Data Source.

A simple example that inserts two employees using the Employees (Simple HTTP) REST Data Source is below. Notice the code references the REST Data Source using its Static ID employees_simple_http.

declare
    l_cols     apex_exec.t_columns;
    l_dml_ctx  apex_exec.t_context;
begin
    apex_exec.add_column(
        p_columns        => l_cols,
        p_column_name    => 'EMPNO',
        p_data_type      => apex_exec.c_data_type_number,
        p_is_primary_key => true);
    apex_exec.add_column(
        p_columns        => l_cols,
        p_column_name    => 'MGR',
        p_data_type      => apex_exec.c_data_type_number);
    apex_exec.add_column(
        p_columns        => l_cols,
        p_column_name    => 'SAL',
        p_data_type      => apex_exec.c_data_type_number);
    apex_exec.add_column(
        p_columns        => l_cols,
        p_column_name    => 'COMM',
        p_data_type      => apex_exec.c_data_type_number);
    apex_exec.add_column(
        p_columns        => l_cols,
        p_column_name    => 'DEPTNO',
        p_data_type      => apex_exec.c_data_type_number);
    apex_exec.add_column(
        p_columns     => l_cols,
        p_column_name => 'ENAME',
        p_data_type   => apex_exec.c_data_type_varchar2);
    apex_exec.add_column(
        p_columns     => l_cols,
        p_column_name => 'JOB',
        p_data_type   => apex_exec.c_data_type_varchar2);
    apex_exec.add_column(
        p_columns     => l_cols,
        p_column_name => 'HIREDATE',
        p_data_type   => apex_exec.c_data_type_date);
    l_dml_ctx := apex_exec.open_rest_source_dml_context(
                    p_static_id  => 'employees_simple_http',
                    p_columns    => l_cols);
    -- Insert LUCY
    apex_exec.add_dml_row(l_dml_ctx,apex_exec.c_dml_operation_insert);
    apex_exec.set_value(l_dml_ctx,'EMPNO',1234);
    apex_exec.set_value(l_dml_ctx,'ENAME','LUCY');
    apex_exec.set_value(l_dml_ctx,'HIREDATE',date '2025-06-27');
    -- Insert LEO
    apex_exec.add_dml_row(l_dml_ctx,apex_exec.c_dml_operation_insert);
    apex_exec.set_value(l_dml_ctx,'EMPNO',1235);
    apex_exec.set_value(l_dml_ctx,'ENAME','LEO');
    apex_exec.set_value(l_dml_ctx,'HIREDATE',date '2025-05-14');
    -- Process the rows in the DML context
    apex_exec.execute_dml(l_dml_ctx);
    apex_exec.close(l_dml_ctx);
exception
    when others then
        apex_exec.close(l_dml_ctx);
        raise;
end;

Review an example of updating a row using a REST Data Source.

Then it proceeds to update any values that need to be changed. Finally, it calls EXECUTE_DML to process the DML context. It closes both query and DML contexts to release resources when completed or in case of an exception.

declare
    l_params   apex_exec.t_parameters;
    l_cols     apex_exec.t_columns;
    l_ctx      apex_exec.t_context;
    l_dml_ctx  apex_exec.t_context;
begin
    apex_exec.add_column(
        p_columns        => l_cols,
        p_column_name    => 'EMPNO',
        p_data_type      => apex_exec.c_data_type_number,
        p_is_primary_key => true);
    apex_exec.add_column(
        p_columns        => l_cols,
        p_column_name    => 'MGR',
        p_data_type      => apex_exec.c_data_type_number);
    apex_exec.add_column(
        p_columns        => l_cols,
        p_column_name    => 'SAL',
        p_data_type      => apex_exec.c_data_type_number);
    apex_exec.add_column(
        p_columns        => l_cols,
        p_column_name    => 'COMM',
        p_data_type      => apex_exec.c_data_type_number);
    apex_exec.add_column(
        p_columns        => l_cols,
        p_column_name    => 'DEPTNO',
        p_data_type      => apex_exec.c_data_type_number);
    apex_exec.add_column(
        p_columns     => l_cols,
        p_column_name => 'ENAME',
        p_data_type   => apex_exec.c_data_type_varchar2);
    apex_exec.add_column(
        p_columns     => l_cols,
        p_column_name => 'JOB',
        p_data_type   => apex_exec.c_data_type_varchar2);
    apex_exec.add_column(
        p_columns     => l_cols,
        p_column_name => 'HIREDATE',
        p_data_type   => apex_exec.c_data_type_date);
    apex_exec.add_parameter(
        p_parameters => l_params,
        p_name       => 'empno_update',
        p_value      => 7788);
    -- Add parameter used by employee_by_pk_simple_http
    -- Fetch rows operation
    apex_exec.add_parameter(
        p_parameters => l_params,
        p_name       => 'empno_getone',
        p_value      => 7788);
    l_ctx := apex_exec.open_rest_source_query(
                p_static_id  => 'employee_by_pk_simple_http',
                p_columns    => l_cols,
                p_parameters => l_params);
    if apex_exec.next_row(l_ctx) then
        l_dml_ctx := apex_exec.open_rest_source_dml_context(
                        p_static_id             => 'employees_simple_http',
                        p_columns               => l_cols,
                        p_lost_update_detection => apex_exec.c_lost_update_implicit);
        -- Update SCOTT
        apex_exec.add_dml_row(l_dml_ctx,apex_exec.c_dml_operation_update);
        -- Checksum used for lost-update protection
        apex_exec.set_row_version_checksum(
            p_context   => l_dml_ctx,
            p_checksum  => apex_exec.get_row_version_checksum( p_context => l_ctx ));
        apex_exec.set_values(
            p_context         => l_dml_ctx,
            p_source_context  => l_ctx );
        -- Update the hiredate of the SCOTT employee
        apex_exec.set_value(l_dml_ctx,'HIREDATE',date'2024-07-14');
        -- Process the DML context
        apex_exec.execute_dml(l_dml_ctx);
    end if;
    apex_exec.close(l_ctx);
    apex_exec.close(l_dml_ctx);
exception
    when others then
        apex_exec.close(l_ctx);
        apex_exec.close(l_dml_ctx);
        raise;
end;

The Employee by PK (Simple HTTP) REST Data Source, with Static ID employee_by_pk_simple_http has just a single GET operation with Database Action of Fetch rows. As shown below, it's configured to use the URL Pattern parameter empno_getone in the URL to retrieve a single employee.

making GET request to https://example.com/ords/cloudcompanion/emp/7788

{
    "empno": 7788,
    "ename": "SCOTT",
    "job": "ANALYST",
    "mgr": 7566,
    "hiredate": "1982-12-09T00:00:00Z",
    "sal": 3000,
    "comm": null,
    "deptno": 20,
    "links": [
        {
            "rel": "self",
            "href": "https://example.com/ords/cloudcompanion/emp/7788"
        },
        ⋮
    ]
}

This means the Employee by PK (Simple HTTP) REST Data Source needs a Data Profile that caters for this different, single-row response. As shown below, the Data Profile for this REST Data Source omits the Row Selector and enables the Contains Single Row switch.

Review an example of deleting a row from a REST Data Source.

A simple example that deletes one employee from the Employees (Simple HTTP) REST Data Source is below. Notice the code references the REST Data Source using its Static ID employees_simple_http and that it only needs to define the primary key column since the corresponding operation for the Delete row database action has no payload containing attribute values.

declare
    l_cols     apex_exec.t_columns;
    l_dml_ctx  apex_exec.t_context;
begin
    apex_exec.add_column(
        p_columns        => l_cols,
        p_column_name    => 'EMPNO',
        p_data_type      => apex_exec.c_data_type_number,
        p_is_primary_key => true);
    l_dml_ctx := apex_exec.open_rest_source_dml_context(
                    p_static_id  => 'employees_simple_http',
                    p_columns    => l_cols);
    -- Delete SMITH
    apex_exec.add_dml_row(l_dml_ctx,apex_exec.c_dml_operation_delete);
    apex_exec.set_value(l_dml_ctx,'EMPNO',7369);
    -- Process the DML context
    apex_exec.execute_dml(l_dml_ctx);
    apex_exec.close(l_dml_ctx);
exception
    when others then
        apex_exec.close(l_dml_ctx);
        raise;
end;