Exposing APIs for Integration

Expose REST APIs for external integrations while reusing business logic and securing access.

A PL/SQL package provides an application programming interface (API) for any solution in your workspace. However, when external systems need to integrate with your app, you need to provide them a REST API instead. It's a web service that another program can call over the network, and uses JavaScript Object Notation (JSON) as a simple way to send and receive data.

Some integrations only need to read data, while others may require creating, updating, or deleting it. If you let an outside system change your application data, you must apply the same validation and defaulting business logic used in your web user interface to keep bad data out of your system of record. In either case, you need to ensure only intended clients can access your services.

This section's example code helps you witness how two key Oracle 26ai features can cut the effort to expose APIs in half.

• Reviewing Action Items API Requirements
  Review why an external system needs REST APIs to integrate with your APEX app.
• Reviewing REST API Basics
  Review the HTTP, JSON, SQL, and PL/SQL basics behind REST APIs.
• Planning Your ORDS Service Module
  Plan your ORDS service module schema alias, URL templates, and handlers.
• Using SQL Developer Web REST Designer
  Use SQL Developer Web REST Designer to create and navigate ORDS service definitions.
• Creating and Testing Read-Only APIs
  Create and test read-only REST APIs for collections and single rows.
• Enforcing Business Logic in REST APIs
  Apply the same business rules to REST API changes that your APEX pages use.
• Controlling REST API Behavior Completely
  For complete control over your ORDS REST APIs, create a full set of PL/SQL handlers.
• Layering Packages for Modularity
  Layer PL/SQL packages so pages and REST APIs share checks while handlers stay simple.
• Simplifying REST APIs in Oracle 26ai
  Simplify REST API development in Oracle 26ai with JSON type and duality views.
• Securing APIs with Role-Based Access Control
  Secure REST APIs with roles and OAuth 2.0 clients for authorized partner access.

Review why an external system needs REST APIs to integrate with your APEX app.

An external support ticketing system needs to integrate with your existing application managing action items and the team of staff members assigned to work on each one. Review the app's functionality and how the external tool needs to integrate with it.

• Exploring Action Item Teams Example
  Explore an action items app that manages teams and enforces team membership rules.
• Understanding the Integration Scenario
  Understand how an external ticketing system uses REST APIs to manage action items in your APEX app.

Review the HTTP, JSON, SQL, and PL/SQL basics behind REST APIs.

• Reviewing HTTP Methods for REST APIs
  Review how HTTP methods map to REST operations on business entities.
• Inspecting JSON Syntax
  Review JSON objects, arrays, and value types used in REST API payloads.
• Constructing JSON in SQL
  Use JSON_OBJECT and JSON_ARRAYAGG SQL functions to construct JSON from relational data.
• Working with JSON in PL/SQL
  Use PL/SQL JSON types to parse, build, and serialize JSON data.

Plan your ORDS service module schema alias, URL templates, and handlers.

Oracle REST Data Services (ORDS) lets you define web APIs using SQL and PL/SQL. Using Oracle 26ai's multilingual engine (MLE), you can also define them with server-side JavaScript.

• Choosing a Schema Alias
  Choose a schema alias to define the leading segment of your ORDS service URLs.
• Identifying URL Templates in a Module
  Define URL templates in a module to identify the service endpoints clients call.
• Choosing GET Handlers to Read Data
  Use GET handlers to expose read-only access for an entity collection and a single entity.
• Creating a View for GET Handlers
  Create a view that returns action item data with nested team members for GET handlers.

Use SQL Developer Web REST Designer to create and navigate ORDS service definitions.

Access SQL Developer Web directly from App Builder to define your ORDS modules, templates, and handlers.

• Accessing SQL Developer Web REST Designer
  Open SQL Developer Web from App Builder to use its REST API designer.
• Navigating ORDS Service Definitions
  Navigate from modules to templates to handlers in SQL Developer Web REST Designer.

Create and test read-only REST APIs for collections and single rows.

When your use case calls for reading data, you can build REST APIs using SQL queries. A Collection Query handler lets clients filter, sort, and page through the results, while a Collection Item handler returns a single row. You can test your REST APIs using curl on the command line or tools with a graphical interface like Postman.

• Defining a Read-Only Collection Query
  Define a Collection Query GET handler that returns action items with nested team data.
• Testing Handler with curl or Postman
  Test a GET endpoint with curl and jq or with Postman to inspect the JSON response.
• Filtering/Paging Collection Query Endpoints
  Use query string parameters to filter and page through Collection Query results.
• Configuring a Collection Item Handler
  Define a Collection Item GET handler that returns one action item, given its unique id.

Apply the same business rules to REST API changes that your APEX pages use.

If you let an outside system change your application data, you must apply the same validation and defaulting business logic used in your web user interface to keep bad data out of your system of record.

• Reviewing Rules for Data Changes
  Review the defaulting and validation rules the example app enforces for action item teams.
• Defining Text Messages for Errors
  Define Text Messages for translatable errors your pages and PL/SQL can share.
• Using Text Messages in Pages and PL/SQL
  Use Text Message substitution syntax in pages and APEX_LANG.GET_MESSAGE in PL/SQL.
• Handling Errors in a Common Way
  Centralize error lookup, reporting, and logging so pages and APIs handle errors consistently.
• Coding Business Rules for Reuse
  Put shared business rules in a package so APEX pages and REST APIs can reuse the same logic.
• Experiencing Validation Failure in UI
  Show validation failures in the UI using the same shared business logic your REST APIs use.
• Receiving Validation Error in REST API
  REST API clients see the same validation errors that end users see in the app.

For complete control over your ORDS REST APIs, create a full set of PL/SQL handlers.

• Providing Complete Set of ORDS Handlers
  Understand the full set of operations your REST API can implement for maximum control.
• Using Handler Bind Variables
  Use handler bind variables to read URL values, request bodies, parameters, and headers.
• Keeping PL/SQL Handlers Maintainable
  Keep ORDS PL/SQL handlers small by delegating REST logic to package procedures.
• Defining Explicit Handler Parameters
  Define explicit handler parameters for request headers, query strings, and other inputs.
• Reusing Common REST API Handler Code
  Reuse common REST handler code to send JSON responses, errors, and optional debug details.
• Reading and Writing Binary Content
  Read binary content with a Media Resource handler and store uploads using the :body bind variable.

Layer PL/SQL packages so pages and REST APIs share checks while handlers stay simple.

Layer your PL/SQL packages with care. This keeps ORDS handlers simple and lets pages and REST APIs run the same checks, no matter who sends the data.

• Studying API Handler Package Layers
  Study how ORDS handlers call a REST layer that delegates to shared core API logic.
• Examining Core Action Item API Logic
  The ACTION_ITEMS_API package is the heart of the Action Items REST API.

Simplify REST API development in Oracle 26ai with JSON type and duality views.

If you use Oracle 26ai, creating REST APIs for your applications is dramatically easier thanks to significant new JSON capabilities in this release.

• Using JSON Type and Duality Views
  Use JSON type and duality views to work with relational data as JSON.
• Exploring ORDS AutoREST for Duality View
  Expose a JSON relational duality view quickly by enabling ORDS AutoREST access.
• Configuring ORDS Handlers for 26ai API
  Set up ORDS handlers to call an alternate API that uses 26ai JSON type and a duality view.
• Seeing How Duality View Simplifies Logic
  Compare the 26ai duality view API with the 19c version to see how much code it removes.

Secure REST APIs with roles and OAuth 2.0 clients for authorized partner access.

For production application integration use, secure any REST APIs you create with role-based access control. Then, issue any partner application a secure OAuth 2.0 client for authenticated access to the APIs you authorize them to use.

• Defining ORDS Role and Privileges
  Define ORDS roles and privileges to control access to protected REST API modules.
• Issuing OAuth Client for Authentication
  Issue an OAuth client so an external system can authenticate and access authorized APIs.
• Using REST API with an OAuth Client
  Use an OAuth client to get a bearer token and call authorized REST APIs.

Explore an action items app that manages teams and enforces team membership rules.

The figure below shows a page from your app that all staff use daily. It shows a two-tab navigation structure with a tab to edit Staff members and a tab to edit Action Items. On the Action Items tab, the user can list, create, edit, and delete action teams of staff members assigned to work on an action item. The Website Redesign Project being edited in the figure has team members Levi, Opal, David, and Georgia. Levi's role is Lead, and the others' role is Member.

The simple underlying data model appears below. Just three tables: ACTION_ITEMS, STAFF, and ACTION_ITEM_TEAM_MEMBERS. The entity relationship diagram shows that an action item has one or more team members, each of whom is a staff member.

Understand how an external ticketing system uses REST APIs to manage action items in your APEX app.

Your support team uses an open-source ticketing system outside your APEX environment. When a customer escalates a ticket that requires development work, that system uses a REST API to create an action item in your app. It assigns the support agent as the lead and adds key engineers as team members. If the ticket changes, the system updates the action item. If the ticket closes without requiring development work, the system deletes it.

The diagram illustrates the REST API interaction between the external ticketing system and the action item system built with APEX.

Review how HTTP methods map to REST operations on business entities.

The Hypertext Transfer Protocol (HTTP) defines methods that state a client's purpose in requesting a server URL. In RESTful web services, the four methods below map to the Create, Read, Update, and Delete (CRUD) operations on resources. Each resource is a business entity like an Action Item.

Review JSON objects, arrays, and value types used in REST API payloads.

For example, the object below represents a team member named Georgia. The keys are name, role, user_id, and active. The values "Georgia" and "LEAD" are strings, while 5 is a number and true is a boolean (true or false) value.

{
  "name": "Georgia",
  "role": "LEAD",
  "user_id": 5,
  "active": true
}

The order of the properties in a JSON object is not significant, so the following object is the same logical object as above, even though their two text representations are not exactly equal.

{
  "user_id": 5,
  "role": "LEAD",
  "name": "Georgia",
  "active": true
}

["apple","banana","cherry"]

[
  { "name": "Georgia", "role": "LEAD",   "user_id": 5, "active": true},
  { "name": "Karl",    "role": "MEMBER", "user_id": 8, "active": true}
]

In JSON, a value can be one of the types below.

For better readability, use any spaces, tabs, or line breaks you need. Parsers ignore this whitespace.

Use JSON_OBJECT and JSON_ARRAYAGG SQL functions to construct JSON from relational data.

For example, the following query retrieves one JSON object per ACTION_ITEMS row. Notice you can include a column as is, with a default property name, or explicitly specify a column's property name using the value keyword. Study the team member name, user_id, and role. For consistency, it's fine to use value even if you choose the same lowercase version of the column name that would have been the default.

select json_object(
          '_id' value ai.id,
          ai.name,
          ai.status,
          'team' value (select json_arrayagg(
                                  json_object(
                                     'team_member_id' value tm.id,
                                     'name'           value s.name,
                                     'user_id'        value tm.user_id,
                                     'role'           value tm.role))
                          from action_item_team_members tm
                          join staff s
                            on tm.user_id = s.id
                         where tm.action_id = ai.id))
  from action_items ai

{
  "_id": 14,
  "name": "End of Year Party",
  "status: "OPEN",
  "team": [
    {
      "team_member_id": 23,
      "name": "David",
      "user_id": 4,
      "role": "MEMBER"
    },
    {
      "team_member_id": 24,
      "name": "Georgia",
      "user_id": 5,
      "role": "LEAD"
    },
    {
      "team_member_id": 25,
      "name": "Jane",
      "user_id": 13,
      "role": "MEMBER"
    }
  ]
}

select json_object(
          '_id' value ai.id,
          ai.name,
          ai.status,
          'team' value (select json_arrayagg(
                                  json_object(
                                     'team_member_id' value tm.id,
                                     'name'           value s.name,
                                     'user_id'        value tm.user_id,
                                     'role'           value tm.role)
                                  order by tm.role, s.name)
                          from action_item_team_members tm
                          join staff s
                            on tm.user_id = s.id
                         where tm.action_id = ai.id))
  from action_items ai

{
  "_id": 14,
  "name": "End of Year Party",
  "status": "OPEN",
  "team": [
    {
      "team_member_id": 24,
      "name": "Georgia",
      "user_id": 5,
      "role": "LEAD"
    },
    {
      "team_member_id": 23,
      "name": "David",
      "user_id": 4,
      "role": "MEMBER"
    },
    {
      "team_member_id": 25,
      "name": "Jane",
      "user_id": 13,
      "role": "MEMBER"
    }
  ]
}

Use PL/SQL JSON types to parse, build, and serialize JSON data.

• Parsing JSON in PL/SQL
  Parse JSON text into PL/SQL JSON objects and arrays, then access their values by type.
• Treating an Element More Specifically
  Use TREAT to work with a generic JSON element as a more specific PL/SQL JSON type.
• Constructing JSON in PL/SQL
  Build JSON objects and arrays in PL/SQL by adding properties and array elements.
• Serializing JSON in PL/SQL
  Serialize PL/SQL JSON objects to text with STRINGIFY or TO_CLOB, depending on size.

Choose a schema alias to define the leading segment of your ORDS service URLs.

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

Define URL templates in a module to identify the service endpoints clients call.

You specify service endpoints using templates to identify the app-specific part of the URL a client uses. Then, you configure a handler for one or more HTTP methods for each template. Before you define templates, you first create a module to contain them. You can name the module anything you like, but say you call it v0.

https://example.com/ords/cloudcompanion/v0/actionitems

https://example.com/ords/cloudcompanion/v0/actionitems/1234

Use GET handlers to expose read-only access for an entity collection and a single entity.

Start by assuming your integration requires read-only access to action items. The HTTP GET method is the appropriate one to handle this case. The figure below shows the v0 ORDS module, with its two URL path templates, and a GET method handler for each template.

Create a view that returns action item data with nested team members for GET handlers.

Start by defining a view the /actionitems template's GET handlers can use. This gives a meaningful name like ACTION_ITEMS_WITH_TEAM_JV to the query that retrieves the exact action item data you want the handler to return. Select top-level ACTION_ITEMS columns you want to include, then use a subquery with JSON_ARRAYAGG and JSON_OBJECT as shown below to include the JSON array of nested ACTION_ITEM_TEAM_MEMBERS data. Notice how the subquery joins this table with STAFF to include the staff member's name in the result. Your ORDS GET handlers can now reference this query to simplify their definition.

create view action_items_with_team_jv as
select ai.id,
       ai.name,
       ai.status,
       (select json_arrayagg(
                 json_object(
                   'team_member_id' value tm.id,
                   'name'           value s.name,
                   'user_id'        value tm.user_id,
                   'role'           value tm.role
                 )
               )
          from action_item_team_members tm
          join staff s on tm.user_id = s.id
         where tm.action_id = ai.id
       ) as team
from action_items ai

Open SQL Developer Web from App Builder to use its REST API designer.

After enabling your workspace schema for web API development, as shown below, launch SQL Developer Web any time from the SQL Workshop menu.

When launched from App Builder, SQL Developer Web is connected to your workspace schema. As shown below, access the REST API designer using the REST entry on the "hamburger" menu.

Navigate from modules to templates to handlers in SQL Developer Web REST Designer.

When you access SQL Developer Web's REST Designer, as shown below, you start on the landing page. The dashboard at the top shows the count of your modules and other information. Click on the Modules tile to see the module list.

The Modules list displays a tile for each, with summary information. From here you can create a new module, or use the three-vertical-dots menu as shown below to access an existing module's list of Templates.

The Templates list displays a tile for each, with the service URL and a button to quickly copy it to the clipboard. From here, you can create a new template or use the three-vertical-dots menu as shown below to access an existing template's list of Handlers.

The Handlers list displays a tile for each. Use the three-vertical-dots menu as shown below to view or edit the handler's details. Also notice the "breadcrumb" bar with a hierarchy of links you can click to return to the v0 module's list of templates, the Modules list, or the REST designer's landing page.

Define a Collection Query GET handler that returns action items with nested team data.

select id as "_id",
       name,
       status,
       team as "{}team"
  from action_items_with_team_jv
order by name, id

Once defined, your collection query handler for the v0 module's /actionitems template is ready to test out. Click the Copy to Clipboard button as shown below to paste its URL into your favorite testing tool.

Test a GET endpoint with curl and jq or with Postman to inspect the JSON response.

$curl https://example.com/ords/cloudcompanion/v0/actionitems

JSON parsers ignore whitespace, so as shown below, ORDS returns data without extra spaces or newlines. On your terminal, the JSON wraps across many lines.

Pipe curl output into jq using | to format it. It indents and color-codes the result for easier reading.

$ curl https://example.com/ords/cloudcompanion/v0/actionitems | jq

Postman is another popular tool developers use for experimenting with web APIs. As shown below, pick the HTTP method and enter the URL, then click (Send) to see the nicely-formatted response.

Use query string parameters to filter and page through Collection Query results.

$ curl https://example.com/⋯/v0/actionitems?q={"name":{"$like":"%Party%"}}

The response looks like the following if just one action item in the system matches.

{
    "items": [
        {
            "_id": 14,
            "name": "End of Year Party",
            "status": "OPEN",
            "team": [
                {
                    "team_member_id": 23,
                    "name": "David",
                    "user_id": 4,
                    "role": "MEMBER"
                },
                {
                    "team_member_id": 24,
                    "name": "Georgia",
                    "user_id": 5,
                    "role": "LEAD"
                },
                {
                    "team_member_id": 25,
                    "name": "Jane",
                    "user_id": 13,
                    "role": "MEMBER"
                }
            ]
        }
    ],
    "hasMore": false,
    "limit": 25,
    "offset": 0,
    "count": 1,
    "links": [
      ⋰
    ]
}

{
       ⋮
    "hasMore": false,
    "limit": 25,
    "offset": 0,
    "count": 1,
       ⋮
}

$ curl https://example.com/⋯/v0/actionitems?limit=10

$ curl https://example.com/⋯/v0/actionitems?offset=10&limit=10

$ curl https://⋯/v0/actionitems?limit=5&q={"name":{"$like":"Feature:%"}}

$ curl https://⋯/v0/actionitems?offset=5&limit=5&q={"name":{"$like":"Feature:%"}}

Define a Collection Item GET handler that returns one action item, given its unique id.

Define the ORDS template handler for the GET method on the /actionitems/:id template using the Source Type of Collection Item. This handler type's SQL query returns a single row by referencing a value provided in the URL. The :id in the template defines an implicit variable. As shown below, your query uses its value with bind variable notation. Then, select the top-level attributes you want to include in the result.

select id as "_id",
       name,
       status,
       team as "{}team"
  from action_items_with_team_jv
 where id = :id

Once defined, your collection item handler for the v0 module's /actionitems/:id template is ready to test out. Click the Copy to Clipboard button as shown below to paste its URL into your favorite testing tool.

$ curl https://example.com/ords/cloudcompanion/v0/actionitems/14 | jq
{
    "id": 14,
    "name": "End of Year Party",
    "status": "OPEN",
    "team": [
        {
            "team_member_id": 23,
            "name": "David",
            "user_id": 4,
            "role": "MEMBER"
        },
        {
            "team_member_id": 24,
            "name": "Georgia",
            "user_id": 5,
            "role": "LEAD"
        },
        {
            "team_member_id": 25,
            "name": "Jane",
            "user_id": 13,
            "role": "MEMBER"
        }
    ],
    "links": [
        {
            "rel": "collection",
            "href": "https://example.com/ords/cloudcompanion/v0/actionitems/"
        }
    ]
}

Review the defaulting and validation rules the example app enforces for action item teams.

Define Text Messages for translatable errors your pages and PL/SQL can share.

&{TEAM_MEMBER_NOT_FOUND}.

In PL/SQL code, GET_MESSAGE in the APEX_LANG package gets a string by its static ID in the user's language. If the message includes named tokens, pass an APEX_T_VARCHAR2 string list with each token name followed by its string value.

l_message := apex_lang.get_message(
                p_name   => 'TEAM_MEMBER_NOT_FOUND',
                p_params => apex_t_varchar2('name',l_team_member.name));

Use Text Message substitution syntax in pages and APEX_LANG.GET_MESSAGE in PL/SQL.

&{TEAM_MAX_ONE_LEAD}.

&{TEAM_MAX_ONE_LEAD name="&P3_NAME."}.

In PL/SQL code, GET_MESSAGE in the APEX_LANG package gets a string by its unique name. If the message includes named tokens, pass an APEX_T_VARCHAR2 string list with each token name followed by its string value.

l_message := apex_lang.get_message(
                p_name   => 'TEAM_MEMBER_NOT_FOUND',
                p_params => apex_t_varchar2('name',l_team_member.name));

Centralize error lookup, reporting, and logging so pages and APIs handle errors consistently.

package app_common is

    c_workspace constant varchar2(40) := 'COMPANION';
    c_app_id    constant number       := 1001;

    -----------------------------------------------------------
    -- Return error message with indicated key
    -----------------------------------------------------------
    function error_message(
        p_message_key in varchar2,
        p_params      in apex_t_varchar2 default null)
        return           varchar2;
    -----------------------------------------------------------
    -- Report error message with indicated behavior if non-null
    -----------------------------------------------------------
    procedure report_if_error(
        p_error_message in varchar2);
    -----------------------------------------------------------
    -- Report error message with indicated message key
    -----------------------------------------------------------
    procedure report_error_key(
        p_error_message_key in varchar2,
        p_params            in apex_t_varchar2 default null);
end app_common;

The APP_COMMON package body appears below. Notice its is_apex_call that detects whether the error is being reported while running in the context of an APEX application or not. The report_if_error procedure uses this to decide whether to add the error to the APEX error stack using APEX_ERROR.ADD_ERROR, or whether to raise an exception containing the error message instead. The code uses APEX_LANG.GET_MESSAGE to get the error message by Static ID in the user's current language.

package body app_common is
    --------------------------------------------
    function is_apex_call
    return boolean
    is
    begin
        return apex_application.g_flow_id is not null;
    end is_apex_call;
    --------------------------------------------
    function error_message(
        p_message_key in varchar2,
        p_params      in apex_t_varchar2 default null)
        return           varchar2
    is
    begin
        apex_util.set_workspace(c_workspace);
        return apex_lang.get_message(
                p_name           => p_message_key,
                p_params         => p_params,
                p_application_id => c_app_id);
    end error_message;
    --------------------------------------------
    procedure report_if_error(
        p_error_message in varchar2)
    is
        l_error varchar2(4000);
    begin
        if p_error_message is not null then
            if is_apex_call then
                apex_error.add_error (
                    p_message          => p_error_message,
                    p_display_location => apex_error.c_on_error_page);
            else
                raise_application_error(-20001,p_error_message);
            end if;
        end if;
    end report_if_error;
    -----------------------------------------------
    procedure report_error_key(
        p_error_message_key in varchar2,
        p_params            in apex_t_varchar2 default null)
    is
    begin
        report_if_error(
            p_error_message => error_message(p_error_message_key,p_params));
    end report_error_key;
end app_common;

Put shared business rules in a package so APEX pages and REST APIs can reuse the same logic.

package action_items_common is
    -----------------------------------------------------------------------
    -- Return true if the action item with provide id exists
    -----------------------------------------------------------------------
    function action_exists(
        p_action_id in number)
    return boolean;
    -----------------------------------------------------------------------
    -- Ensure id passed in is null, or return error message
    -----------------------------------------------------------------------
    function ensure_null_id_for_new_action(
        p_id   in number)
        return    varchar2;
    -----------------------------------------------------------------------
    -- Ensure action item exists, or return error message
    -----------------------------------------------------------------------
    function ensure_action_exists(
        p_action_id in number)
    return varchar2;
    -----------------------------------------------------------------------
    -- Ensure action item has exactly one Lead, or return error message
    -----------------------------------------------------------------------
    function one_lead_per_action_team(
        p_action_id in number)
    return varchar2;
    -----------------------------------------------------------------------
    -- Ensure action item has no duplicate members, or return error message
    -----------------------------------------------------------------------
    function no_duplicate_team_members(
        p_action_id in number)
    return varchar2;
    -----------------------------------------------------------------------
    -- Ensure role value is legal, or return error message
    -----------------------------------------------------------------------
    function is_valid_role(
        p_role_value in varchar2)
        return          varchar2;
end action_items_common;

package body action_items_common is
    ------------------------------------------------------------
    -- Constant for error message keys
    ------------------------------------------------------------
    c_msg_team_must_have_lead   constant varchar2(25) := 'TEAM_MUST_HAVE_A_LEAD';
    c_msg_team_max_one_lead     constant varchar2(25) := 'TEAM_MAX_ONE_LEAD';
    c_msg_team_no_duplicates    constant varchar2(25) := 'TEAM_NO_DUPE_MEMBERS';
    c_msg_action_item_not_found constant varchar2(25) := 'ACTION_ITEM_NOT_FOUND';
    c_msg_illegal_role_value    constant varchar2(25) := 'INVALID_ROLE_TYPE';
    c_msg_no_id_for_new_action  constant varchar2(25) := 'ID_UNEXPECTED_FOR_INSERT';

    c_no_error                  constant varchar2(1)  := null;
    ------------------------------------
    function ensure_null_id_for_new_action(
        p_id   in number)
        return    varchar2
    is
    begin
        if p_id is not null then
            return app_common.error_message(c_msg_no_id_for_new_action);
        end if;
        return c_no_error;
    end ensure_null_id_for_new_action;
    ------------------------------------
    function ensure_action_exists(
        p_action_id in number)
    return varchar2
    is
    begin
        if not action_exists(p_action_id) then
            return app_common.error_message(c_msg_action_item_not_found,
                    apex_t_varchar2('id',p_action_id));
        end if;
        return c_no_error;
    end ensure_action_exists;
    ------------------------------------
    function action_exists(
        p_action_id in number)
    return boolean
    is
    begin
        for j in (select null
                    from action_items
                   where id = p_action_id)
        loop
            return true;
        end loop;
        return false;
    end action_exists;
    ------------------------------------
    function one_lead_per_action_team(
        p_action_id in number)
        return         varchar2
    is
        l_lead_count number;
    begin
        -- Ensure the action id still exists since it might
        -- have been deleted. If it exists, perform the check
        -- on the children rows.
        for j in (select id
                    from action_items
                   where id = p_action_id) loop
            select count(*)
            into l_lead_count
            from action_item_team_members
            where action_id = p_action_id
            and role = 'LEAD';
            if l_lead_count != 1 then
                return app_common.error_message(
                      case
                            when l_lead_count < 1
                            then c_msg_team_must_have_lead
                            else c_msg_team_max_one_lead
                        end);
            end if;
        end loop;
        return c_no_error;
    end;

    ------------------------------------
    function no_duplicate_team_members(
        p_action_id in number)
        return         varchar2
    is
    begin
        -- Ensure the action id still exists since it might
        -- have been deleted. If it exists, perform the check
        -- on the children rows.
        for j in (select id
                    from action_items
                   where id = p_action_id) loop
            for k in (select user_id, count(*)
                        from action_item_team_members
                       where action_id = p_action_id
                       group by user_id
                       having count(*) > 1
                       fetch first row only) loop
                return app_common.error_message(c_msg_team_no_duplicates);
                exit;
            end loop;
        end loop;
        return c_no_error;
    end;

    ------------------------------------
    function is_valid_role(
        p_role_value in varchar2)
        return          varchar2
    is
    begin
        if p_role_value not in ('MEMBER','LEAD') then
            return app_common.error_message(
                    c_msg_illegal_role_value,
                    apex_t_varchar2('value',p_role_value));
        end if;
        return c_no_error;
    end is_valid_role;
end action_items_common;

Show validation failures in the UI using the same shared business logic your REST APIs use.

After you set up the shared Action Item logic in the ACTION_ITEMS_COMMON package, app pages use it declaratively through Invoke API page processes or validations. The figure below shows what users see when saving an action item team with two leads. In the figure, the user clicked (Apply Changes) on the Action Item edit page for the Website Redesign Project. The Action Team grid below shows both Levi and Opal as having the role of Lead. The error notification area appears with an orange triangle containing an exclamation point and the title "1 error has occurred". The application specific error appears below that as a bulleted message: "Only one Lead per action team".

REST API clients see the same validation errors that end users see in the app.

Your REST APIs URL template handlers call common Action Item logic in the ACTION_ITEMS_COMMON package. If a REST API client tries to create a new action item with duplicate team members, the call fails. The behavior is the same for curl, Postman, or another system.

As shown below, HTTP error 400 signals an application-specific failure, and the JSON response explains why. By using shared logic and error handling, REST clients see the same messages app users see for the same invalid data. The figure shows the result of using the Postman REST API testing tool to POST a request to the endpoint:

https://example.com/ords/cloudcompanion/v1/actionitems

The example request's body contains the following JSON payload where both Levi and Opal team members have the role of LEAD.

{
  "name": "Website Redesign Project",
  "team": [
     {"role":"LEAD",  "name":"Levi"},
     {"role":"LEAD",  "name":"Opal"},
     {"role":"MEMBER","name":"David"},
     {"role":"MEMBER","name":"Georgia"},
  ]
}

{"$error":"Only one lead per action team"} 

Understand the full set of operations your REST API can implement for maximum control.

Use handler bind variables to read URL values, request bodies, parameters, and headers.

Your handler can use bind variables to access any of the following information in the incoming request:

• URL path variables – like :id from /v1/actionitems/:id template
• Implicit parameters like:
  • Request body – using :body (BLOB) or :body_text (CLOB)
• Handler parameters you configure explicitly:
  • Query string parameters – e.g. /v1/actionitems?skip=5&pagesize=5
  • HTTP Headers – e.g. :debug to reference a header named app$debug
  • Request payload properties

Keep ORDS PL/SQL handlers small by delegating REST logic to package procedures.

begin
   action_items_api_rest.update_object(:id,:body_text,:debug);
end;

The figure shows the v1 ORDS module's actionsitems/:id template's PUT handler PL/SQL block explained above in context in the SQL Developer Web REST Designer. Notice the :debug listed in the Handler Parameters section and :body_text in the Implicit Parameters sections on the right.

Define explicit handler parameters for request headers, query strings, and other inputs.

The figure below shows the Parameters tab of the PL/SQL PUT handler's details page. Notice how it defines the bind variable name debug for an incoming HTTP Header named app$debug. Use this tab to create any explicit parameters your handler needs, defining a parameter name, bind variable name, source type, access method, and data type for each one.

Reuse common REST handler code to send JSON responses, errors, and optional debug details.

package app_common_rest as
    --------------------------------------------------------------
    -- Set debug mode for error reporting
    --------------------------------------------------------------
    procedure set_debug(
        p_value in varchar2);
    --------------------------------------------------------------
    -- Commit and write out JSON response
    --------------------------------------------------------------
    procedure commit_and_send_response(
        p_json json_element_t default null);
    --------------------------------------------------------------
    -- Handle error by sending HTTP error with $error payload
    --------------------------------------------------------------
    procedure handle_error(
        p_error_message   in varchar2,
        p_http_error_code in number default 400);
end app_common_rest;

If the g_debug_mode flag is enable by a call to set_debug, then notice also that the handle_error adds an additional debug$error property whose value is a JSON array containing the lines of the PL/SQL call stack info from format_error_stack and format_error_backtrace in the dbms_utility package.

create or replace package body app_common_rest as
    g_debug_mode boolean := false;
    ------------------------------------
    procedure set_debug(
        p_value in varchar2)
    is
    begin
        if upper(p_value) = 'TRUE' then
            g_debug_mode := true;
        end if;
    end set_debug;
    ------------------------------------
    procedure commit_and_send_response(
        p_json in json_element_t default null)
    is
        l_has_json boolean := p_json is not null;
    begin
        commit;
        owa_util.mime_header('application/json', l_has_json);
        if p_json is not null then
            htp.p(p_json.stringify);
        else
            owa_util.status_line(204, 'No content', true);
        end if;
    end;
    --------------------------------------------------------------
    -- Handle error by sending HTTP 400 with $error payload
    --------------------------------------------------------------
    procedure handle_error(
        p_error_message   in varchar2,
        p_http_error_code in number default 400)
    is
        l_json_err json_object_t := json_object_t();
        l_json_arr json_array_t;
    begin
        owa_util.mime_header('application/json', p_error_message is null);
        owa_util.status_line(p_http_error_code, 'Error', true);
        if p_error_message is not null then
            l_json_err.put('$error',
                           regexp_replace(p_error_message,'^ORA-[0-9]+:\s'));
            if g_debug_mode then
                l_json_arr := json_array_t();
                for j in (select rownum, column_value
                            from table(apex_string.split(
                                dbms_utility.format_error_stack()
                                || chr(10)
                                || dbms_utility.format_error_backtrace())))
                loop
                    if j.column_value is not null then
                        l_json_arr.append(j.column_value);
                    end if;
                end loop;
                l_json_err.put('debug$error', l_json_arr);
            end if;
            htp.p(l_json_err.stringify);
        end if;
        rollback;
    end;
end app_common_rest;

Read binary content with a Media Resource handler and store uploads using the :body bind variable.

select mime_type, file_contents
  from attachments
 where id = :id

begin
   your_package.insert_attachment(
       p_mime_type     => :content_type,
       p_file_contents => :body);
   commit;
end;

Study how ORDS handlers call a REST layer that delegates to shared core API logic.

The package specification for ACTION_ITEMS_API_REST appears below.

package action_items_api_rest as
    ------------------------------------
    procedure get_objects(
        p_search in varchar2,
        p_offset in number   default 0,
        p_limit  in number   default 10,
        p_debug  in varchar2 default null);
    ------------------------------------
    procedure get_object(
        p_id in number,
        p_debug  in varchar2 default null);
    ------------------------------------
    procedure insert_object(
        p_object in clob,
        p_debug  in varchar2 default null);
    ------------------------------------
    procedure update_object(
        p_id     in number,
        p_object in clob,
        p_debug  in varchar2 default null);
    ------------------------------------
    procedure delete_object(
        p_id     in number,
        p_debug  in varchar2 default null);
end action_items_api_rest;

As shown below, the ACTION_ITEMS_API_REST package is a REST-specific layer on top of the ACTIONS_ITEM_API package that contains the core of the API logic. Its simple procedures essentially convert the incoming JSON CLOB payloads into a JSON_OBJECT_T types to pass to the core Action Items API package. Then they use the common functionality in APP_COMMON_REST to write out the response or error details. This layering keeps the ORDS handler PL/SQL blocks extremely simple.

Notice in the diagram how the ACTION_ITEMS_API package reuses application-wide common code from APP_COMMON and Action Item specific common validation logic from ACTION_ITEMS_COMMON. This layering maximizes reuse so your application pages and REST APIs apply the same checks to modified data, regardless of how the users or external systems submit it.

The ACTION_ITEMS_API package is the heart of the Action Items REST API.

Each procedure uses other interesting private helper routines whole roles are important to understand before digging into the full code of the package body.

• Constructing or Querying an Action Item
  Compare programmatic and SQL-based ways to build action item JSON.
• Retrieving Action Items with Paging
  Retrieve matching action items in a predictable order and return paged JSON results.
• Modifying Action Items with Pre/Post Logic
  Coordinate JSON parsing, business logic, and table changes for action item CRUD operations.
• Inspecting the Core Action Item API Body
  Inspect the full core API package body that coordinates JSON parsing, data changes, and validation.

Use JSON type and duality views to work with relational data as JSON.

Oracle 26ai's JSON type further simplifies JSON data processing in SQL and PL/SQL. Its JSON Relational Duality Views let you define a declarative JSON structure for one or more related tables using a simplified syntax. This JSON view of the underlying relational data model can be read-only if desired, but you can also define it to support inserting, updating, and deleting data through the same view.

• Storing and Accessing JSON Type Data
  Oracle 26ai JSON type to store, query, and transform JSON, with easy access from JSON_OBJECT_T when needed.
• Defining JSON Relational Duality View
  Define a JSON relational duality view that maps related table data into a single JSON document.
• Querying JSON Relational Duality View
  Query a JSON relational duality view to retrieve related data as JSON documents.
• Modifying JSON Using a Duality View
  Modify related table data by inserting, updating, or deleting JSON documents through a duality view.
• Flexing the Duality of Duality Views
  Work with the same data as JSON documents or relational rows, with changes reflected in both.
• Preventing Lost Updates with ETAG
  A duality view document's ETAG value prevents a stale update from overwriting newer changes.
• Comparing _id and RESID in Duality View
  Use RESID as an alternative document identifier for duality view updates and deletes.

Expose a JSON relational duality view quickly by enabling ORDS AutoREST access.

Once you've created a JSON Relational Duality View, the fastest way to expose it to REST clients is to enable AutoREST access on it using SQL Developer Web.

• Enabling AutoREST for a Duality View
  Enable AutoREST on a duality view and choose the object alias clients use in the URL.
• Exercising Duality View AutoREST Endpoint
  Test an AutoREST duality view endpoint to see the database defaults and checks it enforces.
• Enforcing More Complex Business Logic
  Combine PL/SQL handlers with Duality Views to apply custom logic with much simpler code.

Set up ORDS handlers to call an alternate API that uses 26ai JSON type and a duality view.

The v2 module has the same template and handlers as the v1 module. However, its handlers use 26ai JSON type and Duality Views to simplify the implementation. The diagram shows the module, its templates, and their handlers using the segment of the REST API URL they contribute. The v2 module has an /actionitems template with GET and POST handlers. Its /actionitems/:id template has GET, PUT, and DELETE handlers.

The ACTION_ITEMS_DV_API_REST accepts the CLOB JSON payload and constructs a JSON type to pass to the main ACTION_ITEMS_DV_API package. The diagram below shows the similar package layering the v1 module uses.

Compare the 26ai duality view API with the 19c version to see how much code it removes.

• Saving JSON Using SQL Without Parsing
  Save JSON directly through a duality view without parsing it into PL/SQL records.
• Transforming JSON Declaratively in SQL
  Use JSON_TRANSFORM to add, change, or remove JSON elements declaratively in SQL.
• Processing and Accessing JSON Easily
  Process nested JSON values directly with JSON_TABLE and JSON_VALUE.
• Querying Predefined JSON Structure
  Query the duality view’s DATA column to return JSON in the required structure.
• Inspecting the Core 26ai Action Item API
  Inspect the 26ai API package that saves JSON through a duality view.

Define ORDS roles and privileges to control access to protected REST API modules.

Define an ORDS Role in SQL Developer Web to name to a group of access privileges. Use the Security > Roles menu option from the SQL Developer Web REST Designer. For example, create a role named External Application Integration to which you can associate the set of privileges required for an external system to integrate with your Action Items application REST API endpoints.

Next, define a privilege to manage access to a specific set of modules or URL patterns. For example, you can create a privilege named com.example.ACTION_ITEMS_PRIVILEGE with display label Action Items Privilege. As shown below, you configure the privilege to protect the modules v0, v1, and v2 created in this section.

To grant this privilege to the External Application Integration role created above, switch to the Roles tab and shuttle it into the Selected Roles list as shown below. If you create many different privileges to protect different modules or URL patterns, you can grant all the appropriate privileges in this same way to External Application Integration role.

Issue an OAuth client so an external system can authenticate and access authorized APIs.

When an external app like the Support Ticketing System needs to access your REST APIs, issue it an OAuth 2.0 client. This acts as its "ticket" for secure access. ORDS generates a client id and secret, which you share securely. The third-party client uses these credentials to get a bearer token and includes it in the Authorization header of every REST API call. The role you assign to the OAuth client controls which APIs they can access, based on the privileges granted to that role. Create a distinct OAuth client for each external system that needs access.

Use the Security > OAuth Clients menu in the SQL Developer Web REST Designer to access the list of existing clients. Then, click (Create OAuth Client) to add a new one. Choose the Grant Type of client credentials (CLIENT_CRED), give it a name and description like Support Ticketing System, and enter a support email. On the Roles tab, associate the new client to External Application Integration role to grant it all privileges the role provides.

As shown below, when you finish the process by clicking (Create), ORDS assigns a random client id and client secret. You get one chance to see the client credential, so store it in your password vault for safe keeping. As shown below, the new OAuth client appears in the list.

Your new client appears in the OAuth Clients list in SQL Developer Web's REST Designer as shown below. Options on the three-vertical-dots menu let you revoke the secret if needed, or rotate it should it get lost or you want to change it.

Use an OAuth client to get a bearer token and call authorized REST APIs.

To use an OAuth client, the third party system starts by acquiring a bearer token using the client id and client secret provisioned for their integration. You can test the scenario on the command line using curl. Notice that the URL shares the same base as your REST APIs up to an including the schema alias (e.g. cloudcompanion). Then it ends with the /oauth/token path segments. The ‑‑data flag makes curl send a POST request with Content‑Type header set to application/x‑www‑form‑urlencoded and a request body containing grant_type=client_credentials. If the client id and secret are valid, the token endpoint returns a JSON response containing the bearer-type access token. It also includes the number of seconds that the token is valid. In the example below, the token expires in 3600 seconds, or 1 hour.

$ curl --user "QvUVx6Gp8P02dTb0C-II1A..:szUul--kEJ1Te-kS454_tQ.." \
--data "grant_type=client_credentials" \
https://example.com/ords/cloudcompanion/oauth/token

{"access_token":"SNVDe2Tjough44I_X6tmXg","token_type":"bearer","expires_in":3600}

Once the system gets its token, for an hour it can send that token in the Authorization header of any REST API request it makes to the APIs their OAuth client's role authorizes them to use. For example, using curl this access looks like this. Notice the word Bearer, followed by a space, precedes the token value in the header.

$ curl -H "Authorization: Bearer SNVDe2Tjough44I_X6tmXg"  \
https://example.com/ords/cloudcompanion/v1/actionitems/14

{
    "code": "Unauthorized",
    "message": "Unauthorized",
    "type": "tag:oracle.com,2020:error/Unauthorized",
    "instance": "tag:oracle.com,2020:ecid/636fea296204b162106f0cdb5d7e22bc"
}

Parse JSON text into PL/SQL JSON objects and arrays, then access their values by type.

PL/SQL offers native types JSON_OBJECT_T and JSON_ARRAY_T for working programmatically with JSON data. Create an instance by passing JSON text in a VARCHAR2 or CLOB to the type constructor function. Then as shown below you can access property values using getter functions, depending on the data type expected, and access array elements using the get() function.

declare
    l_actionitem json_object_t;
    l_team       json_array_t;
    l_member     json_object_t;
    -----------------------------
    function get_object(
        p_array in json_array_t,
        p_index in pls_integer)
        return     json_object_t
    is
    begin
        return treat(p_array.get(p_index) as json_object_t);
    end get_object;
begin
   l_actionitem := json_object_t(q'~
                    {
                      "name": "Practice Using JSON in PL/SQL",
                      "team": [
                        {"role": "LEAD","name": "Georgia"},
                        {"name": "Usha","role": "MEMBER"}
                      ]
                    }
                    ~');
   l_team   := l_actionitem.get_array('team');
   l_member := treat(l_team.get(0) as json_object_t);
   -- All three print out "Georgia"
   dbms_output.put_line(l_member.get_string('name'));
   dbms_output.put_line(treat(l_team.get(0) as json_object_t).get_string('name'));
   dbms_output.put_line(get_object(l_team,0).get_string('name'));
end;

ORA-30625: method dispatch on NULL SELF argument is disallowed

Use TREAT to work with a generic JSON element as a more specific PL/SQL JSON type.

The generic type JSON_ELEMENT_T is the "grandparent" in the PL/SQL family of JSON types. A JSON array can hold scalars, objects, or other arrays. In the tree below, if type B is indented inside type A, then you can say B is a more specific kind of A.

JSON_ELEMENT_T
├── JSON_SCALAR_T
│   ├── JSON_NUMBER_T
│   ├── JSON_STRING_T
│   └── JSON_BOOLEAN_T
├── JSON_OBJECT_T
└── JSON_ARRAY_T

TREAT(… AS JSON_SPECIFIC_TYPE)

-- Get object with index p_index in an array of objects
function get_object(
    p_array in json_array_t,
    p_index in pls_integer)
    return     json_object_t
is
begin
    return treat(p_array.get(p_index) as json_object_t);
end get_object;

Build JSON objects and arrays in PL/SQL by adding properties and array elements.

You can construct JSON programmatically property by property, starting with empty JSON objects and arrays. As shown below, notice that the code first builds the team member array's contents, then puts the array in the containing object with a team property name.

declare
    l_actionitem     json_object_t := json_object_t();
    l_team           json_array_t  := json_array_t();
    l_member_usha    json_object_t;
    l_member_georgia json_object_t;
begin
   l_actionitem.put('name', 'Practice Using JSON in PL/SQL');
   l_member_georgia := json_object_t();
   l_member_georgia.put('role', 'LEAD');
   l_member_georgia.put('name', 'Georgia');
   l_team.append(l_member_georgia);
   l_member_usha := json_object_t();
   l_member_usha.put('name', 'Usha');
   l_member_usha.put('role', 'MEMBER');
   l_team.append(l_member_usha);
   -- append all array elements before adding array to object
   l_actionitem.put('team', l_team);
   -- Both print out "Georgia"
   dbms_output.put_line(l_member_georgia.get_string('name'));
   dbms_output.put_line(treat(l_team.get(0) as json_object_t).get_string('name'));
end;

ORA-30625: method dispatch on NULL SELF argument is disallowed

Serialize PL/SQL JSON objects to text with STRINGIFY or TO_CLOB, depending on size.

The following example containing a JSON object with a property having a value of length 32767 shows how TO_CLOB works correctly. However, uncommenting the call to STRINGIFY produces an error since the JSON object's text representation exceeds 32767 characters.

declare
    l_value varchar2(32767) := lpad('y',32767,'x');
    l_json  json_object_t   := json_object_t();
    -------------------------------------------------------
    procedure p(p_clob in clob)
    is
        l_offset integer := 1;
        l_chunk  varchar2(32767);
    begin
        while l_offset <= dbms_lob.getlength(p_clob) loop
        l_chunk := dbms_lob.substr(p_clob, 32767, l_offset);
        dbms_output.put_line(l_chunk);
        l_offset := l_offset + 32767;
        end loop;
    end p;
begin
  l_json.put('name',l_value);
  -- Gives ORA-40478: output value too large
  -- p(l_json.stringify);
  p(l_json.to_clob);
end;

ORA-30625: method dispatch on NULL SELF argument is disallowed

Compare programmatic and SQL-based ways to build action item JSON.

For educational purposes, the GET_OBJECT procedure shows two different ways to return the JSON representation of an action item. As shown below, if the c_get_object_approach constant equals c_approach_programmatic then it calls GET_OBJECT_PROGRAMMATICALLY. If the constant is c_approach_sql instead, then it calls GET_OBJECT_USING_SQL.

-- in package action_items_api
function get_object(
    p_id in number)
return json_object_t
is
    c_get_object_approach constant t_get_object_approach := c_approach_sql;
    l_ret json_object_t;
begin
    case c_get_object_approach
        when c_approach_programmatic then
            l_ret := get_object_programmatically(p_id);
        when c_approach_sql then
            l_ret := json_object_t(get_object_using_sql(p_id));
    end case;
    return l_ret;
end;

GET_OBJECT_PROGRAMMATICALLY shown below selects the data from ACTION_ITEMS and ACTION_ITEMS_TEAM_MEMBERS tables into appropriate PL/SQL record structures. Then it uses the PL/SQL JSON_OBJECT_T and JSON_ARRAY_T types to create the action item JSON property by property. Notice that the code first builds up the contents of the JSON array, and then adds the array to the action item JSON object with a property name of team at the end.

-- in package action_items_api
function get_object_programmatically(
    p_id in number)
return json_object_t
is
    l_action_item_rec action_items%rowtype;
    l_action_item_obj json_object_t := json_object_t();
    l_team_members_arr json_array_t := json_array_t();
    l_team_members     t_action_item_team_members := t_action_item_team_members();
    l_team_member_obj  json_object_t;
    l_team_member_name staff.name%type;
begin
    app_common.report_if_error(
        action_items_common.ensure_action_exists(p_id));
    select *
      into l_action_item_rec
      from action_items
     where id = p_id;
    l_action_item_obj.put('_id',l_action_item_rec.id);
    l_action_item_obj.put('name',l_action_item_rec.name);
    l_action_item_obj.put('status',l_action_item_rec.status);
    select *
      bulk collect into l_team_members
      from action_item_team_members
     where action_id = p_id;
    for j in 1..l_team_members.count loop
        l_team_member_obj := json_object_t();
        l_team_member_obj.put('team_assignment_id',l_team_members(j).id);
        l_team_member_obj.put('role',l_team_members(j).role);
        l_team_member_obj.put('staff_id',l_team_members(j).user_id);
        -- Lookup staff name using id
        select name
          into l_team_member_name
          from staff
         where id = l_team_members(j).user_id;
        l_team_member_obj.put('name',l_team_member_name);
        -- Append team member object to the team array
        l_team_members_arr.append(l_team_member_obj);
    end loop;
    l_action_item_obj.put('team',l_team_members_arr);
    return l_action_item_obj;
end get_object_programmatically;

As shown below, GET_OBJECT_USING_SQL uses the JSON_OBJECT and JSON_ARRAYAGG functions to create the action item JSON object with its nested team members. Notice the RETURNING CLOB clauses on the array function and on the outer use of JSON_OBJECT to ensure the JSON construction is not limited to 4000 characters.

-- in package action_items_api
function get_object_using_sql(
    p_id in number)
return clob
is
    l_action_item_json_clob clob;
begin
    app_common.report_if_error(
        action_items_common.ensure_action_exists(p_id));
    select json_object(
      '_id' value ai.id,
      'name' value ai.name,
      'status' value ai.status,
      'team' value (
        select json_arrayagg(
          json_object(
            'team_assignment_id' value tm.id,
            'role' value tm.role,
            'staff_id' value tm.user_id,
            'name' value s.name
          )
        returning clob /* json_arrayagg */)
        from action_item_team_members tm
        left join staff s on tm.user_id = s.id
        where tm.action_id = ai.id
      )
    returning clob /* json_object */)
    into l_action_item_json_clob
    from action_items ai
    where ai.id = p_id;
    return l_action_item_json_clob;
end get_object_using_sql;

Retrieve matching action items in a predictable order and return paged JSON results.

-- in package action_items_api
function get_objects(
    p_search in varchar2,
    p_offset in number default null,
    p_limit  in number default null)
return json_object_t
is
    l_offset pls_integer := nvl(p_offset,0);
    l_limit  pls_integer := nvl(p_limit,25);
    l_ret json_object_t := json_object_t();
    l_items json_array_t := json_array_t();
    l_row_count  pls_integer := 0;
    l_total_rows pls_integer := 0;
begin
    for j in (select id, count(*) over() as total_count
                from action_items
               where upper(name) like upper('%'||p_search||'%')
               order by upper(name)
               offset l_offset rows
               fetch first l_limit rows only)
    loop
        if l_total_rows = 0 then
            l_total_rows := j.total_count;
        end if;
        l_row_count := l_row_count + 1;
        l_items.append(get_object(j.id));
    end loop;
    l_ret.put('items',l_items);
    l_ret.put('hasMore', l_offset + l_row_count < l_total_rows);
    l_ret.put('totalRows',l_total_rows);
    return l_ret;
end get_objects;

Coordinate JSON parsing, business logic, and table changes for action item CRUD operations.

• Populating PL/SQL Records from JSON
  Parse action item JSON into PL/SQL records for the parent row and team members.
• Saving Action Item Data to Tables
  Save action item records and team members with insert, update, or delete logic.
• Defaulting and Validating Pre/Post-Save
  Apply defaults before saving, then validate saved parent and child rows before commit.
• Coordinating the API Helper Code
  Coordinate JSON parsing, pre-save logic, table changes, post-save checks, and response generation.

Inspect the full core API package body that coordinates JSON parsing, data changes, and validation.

The full code of the ACTION_ITEMS_API package body appears below.

create or replace package body action_items_api as

    subtype t_get_object_approach is pls_integer range 1..2;
    c_approach_programmatic constant t_get_object_approach := 1;
    c_approach_sql          constant t_get_object_approach := 2;

    subtype t_operation is pls_integer range 1..3;
    c_operation_insert      constant t_operation := 1;
    c_operation_update      constant t_operation := 2;
    c_operation_delete      constant t_operation := 3;

    ------------------------------------
    procedure before_save(
        p_action_item  in out action_items%rowtype,
        p_action_item_team_members in out t_action_item_team_members,
        p_operation    in     t_operation)
    is
    begin
        if p_operation = c_operation_insert then
            -- Providing an ID with insert not allowed
            app_common.report_if_error(
                action_items_common.ensure_null_id_for_new_action(p_action_item.id));
        end if;
        if p_operation in (c_operation_insert,c_operation_update) then
            -- Default role to member if not supplied
            for j in 1..p_action_item_team_members.count loop
                if p_action_item_team_members(j).role is null then
                    p_action_item_team_members(j).role := 'MEMBER';
                else
                    -- Check role value is legal
                    app_common.report_if_error(
                        action_items_common.is_valid_role(p_action_item_team_members(j).role));
                end if;
            end loop;
        end if;
    end before_save;
    ------------------------------------
    procedure save(
        p_action_item              in out action_items%rowtype,
        p_action_item_team_members in out t_action_item_team_members,
        p_operation                in     t_operation)
    is
        l_new_ids                apex_t_number;
        l_action_item_member_ids apex_t_number := apex_t_number();
    begin
        case p_operation
            when c_operation_insert then
                -- insert the action item
                insert into action_items(name)
                values (p_action_item.name)
                returning id into p_action_item.id;
                -- Set foreign key attribute for new child rows
                for i in 1..p_action_item_team_members.count loop
                    p_action_item_team_members(i).action_id := p_action_item.id;
                end loop;
                -- insert all the team members
                forall i in 1..p_action_item_team_members.count
                    insert into action_item_team_members values p_action_item_team_members(i)
                    returning id bulk collect into l_new_ids;
                -- Populate the system-assigned id
                for i in 1..p_action_item_team_members.count loop
                    p_action_item_team_members(i).id := l_new_ids(i);
                end loop;
            when c_operation_update then
                update action_items
                set name = p_action_item.name,
                    status = p_action_item.status
                where id = p_action_item.id;
                -- Insert/update child rows that need inserting, populating assigned id
                for i in 1..p_action_item_team_members.count loop
                    if p_action_item_team_members(i).id is null then
                        -- id is null? Needs an insert
                        insert into action_item_team_members
                        values p_action_item_team_members(i)
                        returning id into p_action_item_team_members(i).id;
                    else
                        -- id is not null, needs an update
                        update action_item_team_members
                        set role = p_action_item_team_members(i).role,
                            user_id = p_action_item_team_members(i).user_id
                        where id = p_action_item_team_members(i).id
                          and action_id = p_action_item_team_members(i).action_id;
                    end if;
                    -- track which ids we updated and inserted so we can delete others
                    l_action_item_member_ids.extend;
                    l_action_item_member_ids(l_action_item_member_ids.count)
                        := p_action_item_team_members(i).id;
                end loop;
                -- Delete any existing team members who are not among the updates
                delete from action_item_team_members
                where action_id = p_action_item.id
                and id not in (select column_value from table(l_action_item_member_ids));
            when c_operation_delete then
                delete from action_items
                 where id = p_action_item.id;
        end case;
    end save;
    ------------------------------------
    procedure after_save(
        p_action_item              in action_items%rowtype,
        p_action_item_team_members in t_action_item_team_members,
        p_operation                in t_operation)
    is
    begin
        if p_operation in (c_operation_insert,c_operation_update) then
            app_common.report_if_error(
                action_items_common.no_duplicate_team_members(p_action_item.id));
            app_common.report_if_error(
                action_items_common.one_lead_per_action_team(p_action_item.id));
        end if;
    end after_save;
    ------------------------------------
    procedure action_item_json_to_record(
        p_object        in json_object_t,
        p_action_item  out action_items%rowtype,
        p_action_item_team_members out t_action_item_team_members)
    is
        l_team_members_arr json_array_t;
        l_team_member_obj  json_object_t;
        l_team_member_rec  action_item_team_members%rowtype;
        l_team_member_name staff.name%type;
    begin
        p_action_item.id     := p_object.get_number('_id');
        p_action_item.name   := p_object.get_string('name');
        p_action_item.status := p_object.get_string('status');
        p_action_item_team_members := t_action_item_team_members();
        l_team_members_arr := p_object.get_array('team');
        for j in 0..l_team_members_arr.get_size - 1 loop
            -- Fill in foreign key attribute from parent
            l_team_member_rec := null;
            l_team_member_rec.action_id := p_action_item.id;
            l_team_member_obj := treat(l_team_members_arr.get(j) as json_object_t);
            l_team_member_rec.id      := l_team_member_obj.get_number('team_assignment_id');
            l_team_member_rec.user_id := l_team_member_obj.get_string('staff_id');
            -- if user_id is null, try to use name to look it up since name is unique
            if l_team_member_rec.user_id is null then
                l_team_member_name := l_team_member_obj.get_string('name');
                if l_team_member_name is not null then
                    begin
                        select id
                          into l_team_member_rec.user_id
                          from staff
                         where name = l_team_member_name;
                    exception
                        when no_data_found then
                            app_common.report_error_key(
                                'TEAM_MEMBER_NOT_FOUND',
                                apex_t_varchar2('name',l_team_member_name));
                    end;
                end if;
            end if;
            l_team_member_rec.role := substr(l_team_member_obj.get_string('role'),1,6);
            p_action_item_team_members.extend;
            p_action_item_team_members(p_action_item_team_members.count) := l_team_member_rec;
        end loop;
    end action_item_json_to_record;
    ------------------------------------
    function get_object_using_sql(
        p_id in number)
    return clob
    is
        l_action_item_json_clob clob;
    begin
        app_common.report_if_error(
            action_items_common.ensure_action_exists(p_id));
        select json_object(
          '_id' value ai.id,
          'name' value ai.name,
          'status' value ai.status,
          'team' value (
            select json_arrayagg(
              json_object(
                'team_assignment_id' value tm.id,
                'role' value tm.role,
                'staff_id' value tm.user_id,
                'name' value s.name
              )
            returning clob /* json_arrayagg */)
            from action_item_team_members tm
            left join staff s on tm.user_id = s.id
            where tm.action_id = ai.id
          )
        returning clob /* json_object */)
        into l_action_item_json_clob
        from action_items ai
        where ai.id = p_id;
        return l_action_item_json_clob;
    end get_object_using_sql;
    ------------------------------------
    function get_object_programmatically(
        p_id in number)
    return json_object_t
    is
        l_action_item_rec action_items%rowtype;
        l_action_item_obj json_object_t := json_object_t();
        l_team_members_arr json_array_t := json_array_t();
        l_team_members     t_action_item_team_members := t_action_item_team_members();
        l_team_member_obj  json_object_t;
        l_team_member_name staff.name%type;
    begin
        app_common.report_if_error(
            action_items_common.ensure_action_exists(p_id));
        select *
          into l_action_item_rec
          from action_items
         where id = p_id;
        l_action_item_obj.put('_id',l_action_item_rec.id);
        l_action_item_obj.put('name',l_action_item_rec.name);
        l_action_item_obj.put('status',l_action_item_rec.status);
        select *
          bulk collect into l_team_members
          from action_item_team_members
         where action_id = p_id;
        for j in 1..l_team_members.count loop
            l_team_member_obj := json_object_t();
            l_team_member_obj.put('team_assignment_id',l_team_members(j).id);
            l_team_member_obj.put('role',l_team_members(j).role);
            l_team_member_obj.put('staff_id',l_team_members(j).user_id);
            -- Lookup staff name using id
            select name
              into l_team_member_name
              from staff
             where id = l_team_members(j).user_id;
            l_team_member_obj.put('name',l_team_member_name);
            -- Append team member object to the team array
            l_team_members_arr.append(l_team_member_obj);
        end loop;
        l_action_item_obj.put('team',l_team_members_arr);
        return l_action_item_obj;
    end get_object_programmatically;
    ------------------------------------
    function get_object(
        p_id in number)
    return json_object_t
    is
        c_get_object_approach   constant t_get_object_approach := c_approach_sql;
        l_ret json_object_t;
    begin
        case c_get_object_approach
            when c_approach_programmatic then
                l_ret := get_object_programmatically(p_id);
            when c_approach_sql then
                l_ret := json_object_t(get_object_using_sql(p_id));
        end case;
        return l_ret;
    end;
    ------------------------------------
    function get_objects(
        p_search in varchar2,
        p_offset in number default null,
        p_limit  in number default null)
    return json_object_t
    is
        l_offset pls_integer := nvl(p_offset,0);
        l_limit  pls_integer := nvl(p_limit,25);
        l_ret json_object_t := json_object_t();
        l_items json_array_t := json_array_t();
        l_row_count  pls_integer := 0;
        l_total_rows pls_integer := 0;
    begin
        for j in (select id, count(*) over() as total_count
                    from action_items
                   where upper(name) like upper('%'||p_search||'%')
                   order by upper(name)
                   offset l_offset rows
                   fetch first l_limit rows only)
        loop
            if l_total_rows = 0 then
                l_total_rows := j.total_count;
            end if;
            l_row_count := l_row_count + 1;
            l_items.append(get_object(j.id));
        end loop;
        l_ret.put('items',l_items);
        l_ret.put('hasMore', l_offset + l_row_count < l_total_rows);
        l_ret.put('totalRows',l_total_rows);
        return l_ret;
    end get_objects;
    ------------------------------------
    function insert_object(
        p_object in json_object_t)
    return json_object_t
    is
        l_action_item_rec action_items%rowtype;
        l_action_item_team_members t_action_item_team_members;
    begin
        action_item_json_to_record(
            p_object                   => p_object,
            p_action_item              => l_action_item_rec,
            p_action_item_team_members => l_action_item_team_members);
        before_save(l_action_item_rec,l_action_item_team_members,c_operation_insert);
        save(l_action_item_rec,l_action_item_team_members,c_operation_insert);
        after_save(l_action_item_rec,l_action_item_team_members,c_operation_insert);
        return get_object(l_action_item_rec.id);
    end insert_object;
    ------------------------------------
    function update_object(
        p_object in json_object_t)
    return json_object_t
    is
        l_action_item_rec action_items%rowtype;
        l_action_item_team_members t_action_item_team_members;
    begin
        action_item_json_to_record(
            p_object                   => p_object,
            p_action_item              => l_action_item_rec,
            p_action_item_team_members => l_action_item_team_members);
        before_save(l_action_item_rec,l_action_item_team_members,c_operation_update);
        save(l_action_item_rec,l_action_item_team_members,c_operation_update);
        after_save(l_action_item_rec,l_action_item_team_members,c_operation_update);
        return get_object(l_action_item_rec.id);
    end update_object;
    ------------------------------------
    procedure delete_object(
        p_id in number)
    is
        l_action_item_rec action_items%rowtype;
        l_action_item_team_members t_action_item_team_members;
    begin
        l_action_item_rec.id := p_id;
        before_save(l_action_item_rec,l_action_item_team_members,c_operation_delete);
        save(l_action_item_rec,l_action_item_team_members,c_operation_delete);
        after_save(l_action_item_rec,l_action_item_team_members,c_operation_delete);
    end delete_object;
end action_items_api;

Oracle 26ai JSON type to store, query, and transform JSON, with easy access from JSON_OBJECT_T when needed.

l_actionitem json :=
             json(q'~
                {
                  "name": "Practice Using JSON in PL/SQL",
                  "team": [
                    {"name": "Georgia","role": "LEAD"},
                    {"name": "Quinn"},
                    {"name": "Zelda"}
                  ]
                }
              ~');

-- Default role to member if not supplied
select
   json_transform(l_actionitem,
      set '$.team[*]?(@.role == null || !exists(@.role)).role'
           = 'MEMBER')
    into l_actionitem;

-- Print out the name of the LEAD team member
p(json_value(l_actionitem,'$.team[*]?(@.role == "LEAD").name'));

-- Process each team member object in a loop
for k in (select team_member
            from json_table(l_actionitem,'$.team[*]'
                    columns (team_member json path '

-- Delete team member Zelda using SQL
select json_transform(l_actionitem,
            remove '$.team[*]?(@.name == "Zelda" && @.role == "MEMBER")')
  into l_actionitem;

-- Delete team member Quinn using PL/SQL JSON_OBJECT_T
l_actionitem_obj := json_object_t(l_actionitem);
l_teammembers    := l_actionitem_obj.get_array('team');
-- iterate backwards for deleting
for j in reverse 0..l_teammembers.get_size - 1  loop
    if get_object(l_teammembers,j).get_string('name') = 'Quinn' then
        l_teammembers.remove(j);
    end if;
end loop;
-- Show interoperability between JSON and JSON_OBJECT_T
l_actionitem := l_actionitem_obj.to_json;

declare
    l_actionitem json :=
                 json(q'~
                    {
                      "name": "Practice Using JSON in PL/SQL",
                      "team": [
                        {"name": "Georgia","role": "LEAD"},
                        {"name": "Quinn"},
                        {"name": "Zelda"}
                      ]
                    }
                  ~');
    l_actionitem_obj json_object_t;
    l_teammembers    json_array_t;
    -----------------------------
    function get_object(
        p_array in json_array_t,
        p_index in pls_integer)
        return     json_object_t
    is
    begin
        return treat(p_array.get(p_index) as json_object_t);
    end get_object;
    -----------------------------
    procedure p(p_str in varchar)
    is
    begin
        dbms_output.put_line(p_str);
    end p;
begin
    -- Default role to member if not supplied
    select json_transform(l_actionitem,
                set '$.team[*]?(@.role == null
                                ||!exists(@.role))
                                .role' = 'MEMBER')
    into l_actionitem;
    p(json_serialize(l_actionitem pretty));
    -- Print out the name of the LEAD team member
    p(json_value(l_actionitem,'$.team[*]?(@.role == "LEAD").name'));
    -- Process each team member object in a loop
    for k in (select team_member
                from json_table(l_actionitem,'$.team[*]'
                        columns (team_member json path '

{
  "name" : "Practice Using JSON in PL/SQL",
  "team" :
  [
    {
      "name" : "Georgia",
      "role" : "LEAD"
    },
    {
      "name" : "Quinn",
      "role" : "MEMBER"
    },
    {
      "name" : "Zelda",
      "role" : "MEMBER"
    }
  ]
}
Georgia
Georgia,LEAD
Quinn,MEMBER
Zelda,MEMBER
{
  "name" : "Practice Using JSON in PL/SQL",
  "team" :
  [
    {
      "name" : "Georgia",
      "role" : "LEAD"
    },
    {
      "name" : "Quinn",
      "role" : "MEMBER"
    }
  ]
}
{
  "name" : "Practice Using JSON in PL/SQL",
  "team" :
  [
    {
      "name" : "Georgia",
      "role" : "LEAD"
    }
  ]
}