暴露集成 API

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 3：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 4：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

{
  "_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"
    }
  ]
}

源示例 3：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 4：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

{
  "_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"
    }
  ]
}

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

select id as
"_id"
,
       name,
       status,
       team as
"{}team"

  from action_items_with_team_jv
order by name, id

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

{
    "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": [
      ⋰
    ]
}

源示例 3：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 4：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 5：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 6：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 7：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

where id = :id

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

$ 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/"
        }
    ]
}

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

&{TEAM_MEMBER_NOT_FOUND}.

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

&{TEAM_MAX_ONE_LEAD}.

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 3：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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;

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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;

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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;

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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;

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 3：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

begin

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

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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;

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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;

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

select mime_type, file_contents
  from attachments
 where id = :id

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

begin

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

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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;

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

$ 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}

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 3：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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;

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

TREAT(… AS JSON_
SPECIFIC_TYPE
)

源示例 3：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

-- 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;

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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;

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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;

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

-- 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;

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

-- 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;

源示例 3：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

-- 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;

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

-- 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;

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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;

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 3：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 4：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

-- 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 '$')))
loop
    p(json_value(k.team_member,'$.name')||','||
      json_value(k.team_member,'$.role'));
end loop;  

源示例 5：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 6：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

-- 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;

源示例 7：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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 '$')))
    loop
        p(json_value(k.team_member,'$.name')||','||
          json_value(k.team_member,'$.role'));
    end loop;
    -- Delete team member Zelda using SQL
    select json_transform(l_actionitem,
                remove '$.team[*]?(@.name == "Zelda" && @.role == "MEMBER")')
      into l_actionitem;
    p(json_serialize(l_actionitem pretty));
    -- 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;
    p(json_serialize(l_actionitem pretty));
end;

源示例 8：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

{
  "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"
    }
  ]
}

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

create json relational duality view
action_items_dv
 as

action_items

@insert @update @delete

{
  _id: id,
  name: name,
  status: status,
  team:
action_item_team_members

@insert @update @delete

  {
    team_assignment_id: id,
    role: role,

staff

@unnest

    {
      staff_id: id,
      name: name
    }
  }
};

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

-- Retrieve 3 action item JSON docs by _id value
select data
  from action_items_dv
 where json_value(data,'$._id') in (14, 25, 27)

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

{
  "_id": 14,
  "name": "End of Year Party",
  "status": "OPEN",
  "team": [
    {
      "team_assignment_id": 23,
      "role": "MEMBER",
      "staff_id": 4,
      "name": "David"
    },
    {
      "team_assignment_id": 24,
      "role": "LEAD",
      "staff_id": 5,
      "name": "Georgia"
    },
    {
      "team_assignment_id": 25,
      "role": "MEMBER",
      "staff_id": 13,
      "name": "Jane"
    }
  ],
  "_metadata": {
    "etag": "5891809CF5691D5400D4141A65123976",
    "asof": "000025C52A64CB47"
  }
}

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

declare
    l_json json :=
           json(q'~
            {
              "name": "Buy Talulah's Retirement Gift",
              "team": [
                {
                  "role": "LEAD",
                  "name": "Abigail"
                },
                {
                  "role": "MEMBER",
                  "name": "Chaz"
                }
              ]
            }
       ~');
begin

insert into action_items_dv(data)
    values (l_json)
    returning data into l_json
;
    dbms_output.put_line(json_serialize(l_json pretty));
end;

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

{
  "_id" : 58,
  "name" : "Buy Talulah's Retirement Gift",
  "status": "OPEN",
  "team" :
  [
    {
      "team_assignment_id" : 127,
      "role" : "MEMBER",
      "staff_id" : 23,
      "name" : "Chaz"
    },
    {
      "team_assignment_id" : 128,
      "role" : "LEAD",
      "staff_id" : 18,
      "name" : "Abigail"
    }
  ],
  "_metadata" :
  {
    "etag" : "48A8D3D393B6653A5D2B0359EDAD2D21",
    "asof" : "000025C52A6F11EE"
  }
}

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

select ai.name as action_name, s.name, tm.role
  from action_item_team_members tm
  join action_items ai
    on ai.id = tm.action_id
  join staff s
    on s.id = tm.user_id
 where ai.id = 58
 order by tm.role, s.name

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

ACTION_NAME                    NAME      ROLE
-----------------------------  --------  ------
Buy Talulah's Retirement Gift  Abigail   LEAD
Buy Talulah's Retirement Gift  Chaz      MEMBER

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

declare
    l_json json :=
           -- Modified original JSON document
           -- to swap Chaz with Usha
           json(q'~
            {
              "_id" : 58,
              "name" : "Buy Talulah's Retirement Gift",
              "team" :
              [
                {
                  "role" : "MEMBER",
                  "name" : "Usha"
                },
                {
                  "team_assignment_id" : 128,
                  "role" : "LEAD",
                  "staff_id" : 18,
                  "name" : "Abigail"
                }
              ],
              "_metadata" :
              {
                "etag" : "48A8D3D393B6653A5D2B0359EDAD2D21",
                "asof" : "000025C52A6F11EE"
              }
            }
       ~');
begin
    update action_items_dv
       set data =  l_json
     where json_value(data,'$._id') = 58
    returning data into l_json;
    dbms_output.put_line(json_serialize(l_json pretty));
end;

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

ORA-42699: Cannot update JSON Relational Duality View 'ACTION_ITEMS_DV':
  The ETAG of document with ID 'FB03C13B00' in the database did not match
  the ETAG passed in.

源示例 3：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

"_metadata" : {
   "
etag
" : "
48A8D3D393B6653A5D2B0359EDAD2D21
",
   "asof" : "000025C52A6F11EE"
}

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

ORA-42699: Cannot update JSON Relational Duality View 'ACTION_ITEMS_DV':
  The ETAG of document with ID 'FB03C13B00' in the database did not match
  the ETAG passed in.

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

WHERE RESID =
l_resid_var_raw

源示例 3：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

WHERE JSON_VALUE(data,'$._id') =
l_id_value

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

{
    "name": "New Product Promotion Deck",
    "team": [
        {
            "role": "LEAD",
            "name": "Usha"
        },
        {"name": "Leopoldo"}
    ]
}

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

ORA-40937: Cannot insert into table 'STAFF' in JSON Relational Duality View
           'ACTION_ITEMS_DV': Missing INSERT annotation

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

{
    "name": "New Product Pitch Deck",
    "team": [
        {"name": "David"},
        {"name": "Georgia"},
        {"name": "Jane"},
        {"name": "Georgia"}
    ]
}

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

-- in package actions_items_dv_api
procedure save(
    p_action_item in out json,
    p_operation   in     t_operation)
is
begin
    case p_operation
        when c_operation_insert then

insert into action_items_dv
(data)
            values (p_action_item)
            returning data into p_action_item;
        when c_operation_update then

update action_items_dv

               set data = p_action_item
             where json_value(data,'$."_id"')
                   = json_value(p_action_item,'$."_id"');
        when c_operation_delete then

delete from action_items_dv

             where json_value(data,'$."_id"')
                   = json_value(p_action_item,'$."_id"');
    end case;
end save;

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

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

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

-- Ensure role values are legal
for k in (select team_member
            from json_table(p_action_item,'$.team[*]'
                 columns (team_member json path '$')))
loop
    app_common.report_if_error(
        action_items_common.is_valid_role(
            json_value(k.team_member,'$.role')));
end loop;

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

function get_object(
    p_id in number)
return json
is
    l_ret json;
begin
    app_common.report_if_error(
        action_items_common.ensure_action_exists(p_id));
    select
data

      into l_ret
      from
action_items_dv

      where json_value(data,'$."_id"') = p_id;
    return l_ret;
end get_object;

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

package body action_items_dv_api as

    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 json,
        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(
                    json_value(p_action_item,'$."_id"')));
        end if;
        if p_operation in (c_operation_insert,c_operation_update) then
            -- Ensure role values are legal
            for k in (select team_member
                        from json_table(p_action_item,'$.team[*]'
                             columns (team_member json path '$')))
            loop
                app_common.report_if_error(
                    action_items_common.is_valid_role(
                        json_value(k.team_member,'$.role')));
            end loop;
            -- Default role to member if not supplied
            select json_transform(p_action_item,
                        set '$.team[*]?(@.role == null ||
                                         !exists(@.role)).role' = 'MEMBER')
            into p_action_item;
        end if;
    end before_save;
    ------------------------------------
    procedure save(
        p_action_item in out json,
        p_operation   in     t_operation)
    is
    begin
        case p_operation
            when c_operation_insert then
                insert into action_items_dv(data)
                values (p_action_item)
                returning data into p_action_item;
            when c_operation_update then
                update action_items_dv
                   set data = p_action_item
                 where json_value(data,'$."_id"')
                       = json_value(p_action_item,'$."_id"');
            when c_operation_delete then
                delete from action_items_dv
                 where json_value(data,'$."_id"')
                       = json_value(p_action_item,'$."_id"');
        end case;
    end save;
    ------------------------------------
    procedure after_save(
        p_action_item in json,
        p_operation   in t_operation)
    is
        l_error varchar2(4000);
        l_id number := json_value(p_action_item,'$."_id"');
    begin
        if p_operation in (c_operation_insert,c_operation_update) then
            app_common.report_if_error(
                action_items_common.no_duplicate_team_members(l_id));
            app_common.report_if_error(
                action_items_common.one_lead_per_action_team(l_id));
        end if;
    end after_save;
    ------------------------------------
    function get_object(
        p_id in number)
    return json
    is
        l_ret json;
    begin
        app_common.report_if_error(
            action_items_common.ensure_action_exists(p_id));
        select data
          into l_ret
          from action_items_dv
          where json_value(data,'$."_id"') = p_id;
        return l_ret;
    end get_object;
    ------------------------------------
    function get_objects(
        p_search in varchar2,
        p_offset in number default null,
        p_limit  in number default null)
    return json
    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 json_value(data,'$."_id"') id,
                         count(*) over() as total_count
                    from action_items_dv
                   where upper(json_value(data,'$.name'))
                         like upper('%'||p_search||'%')
                   order by upper(json_value(data,'$.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(json_object_t(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.to_json;
    end get_objects;
    ------------------------------------
    function insert_object(
        p_object in json)
    return json
    is
        l_object json := p_object;
    begin
        before_save(l_object,c_operation_insert);
        save(l_object,c_operation_insert);
        after_save(l_object,c_operation_insert);
        return get_object(json_value(l_object,'$."_id"'));
    end insert_object;
    ------------------------------------
    function update_object(
        p_object in json)
    return json
    is
        l_object json := p_object;
    begin
        before_save(l_object,c_operation_update);
        save(l_object,c_operation_update);
        after_save(l_object,c_operation_update);
        return get_object(json_value(l_object,'$."_id"'));
    end update_object;
    ------------------------------------
    procedure delete_object(
        p_id in number)
    is
        l_id_only_object json_object_t := json_object_t();
        l_object json;
    begin
        l_id_only_object.put('_id',p_id);
        l_object := l_id_only_object.to_json;
        save(l_object,c_operation_delete);
    end delete_object;
end action_items_dv_api;

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

-- in package action_items_api
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;

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

-- in package action_items_api
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;

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

-- in package action_items_api
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;

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

-- in package action_items_api
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;

源示例 1：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

-- in package action_items_api
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;

源示例 2：代码按官方示例保留，便于复制到 SQL Workshop、ORDS Handler 或命令行中核对。

-- in package action_items_api
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;