我们有两个名为XXTB_PAYMENT_REQUEST和XXTB_PR_DISTRIBUTION的表。我们使用PLSQL过程来插入/更新这些表中的数据。此过程公开为一个ORDS服务，允许其他应用程序将其作为Rest API调用。
我们面临的问题是，当执行API时，如果过程未能将数据插入到第二个表（XXTB_PR_DISTRIBUTION）中，它不会回滚已经插入到XXTB_PAYMENT_REQUEST表中的数据。但是，当我在SQL Developer中以过程调用的形式在本地运行该过程时，它可以正常工作，并将调用视为一个事务。
应该做些什么来确保API也将调用视为单个事务？
设置：

CREATE TABLE xxtb_payment_request (
    pr_number VARCHAR2(5),
    org_name  VARCHAR2(10),
    amount    NUMBER,
    PRIMARY KEY ( pr_number )
);

CREATE TABLE xxtb_pr_distribution (
    pr_number   VARCHAR2(5)
        CONSTRAINT fk_prnum_distr
            REFERENCES xxtb_payment_request,
    line_number NUMBER,
    cost_center VARCHAR2(5),
    PRIMARY KEY ( pr_number,
                  line_number )
);

CREATE OR REPLACE PROCEDURE save_pr (
    p_pr_number_in   IN VARCHAR2,
    p_org_name_in    IN VARCHAR2,
    p_amount_in      IN NUMBER,
    p_cost_center_in IN VARCHAR2,
    p_line_number_in IN NUMBER
) AS
BEGIN
    MERGE INTO xxtb_payment_request xpr
    USING (
        SELECT
            p_pr_number_in AS pr_number_in,
            p_org_name_in  AS org_name_in,
            p_amount_in    AS amount_in
        FROM
            dual
    ) p_params ON ( xpr.pr_number = p_params.pr_number_in )
    WHEN MATCHED THEN UPDATE
    SET xpr.org_name = p_params.org_name_in,
        xpr.amount = p_params.amount_in
    WHEN NOT MATCHED THEN
    INSERT (
        pr_number,
        org_name,
        amount )
    VALUES
        ( p_params.pr_number_in,
          p_params.org_name_in,
          p_params.amount_in );

    MERGE INTO xxtb_pr_distribution xpd
    USING (
        SELECT
            p_pr_number_in   AS pr_number_in,
            p_line_number_in AS line_number_in,
            p_cost_center_in AS cost_center_in
        FROM
            dual
    ) d_params ON ( xpd.pr_number = d_params.pr_number_in
                    AND xpd.line_number = d_params.line_number_in )
    WHEN MATCHED THEN UPDATE
    SET xpd.cost_center = d_params.cost_center_in
    WHEN NOT MATCHED THEN
    INSERT (
        pr_number,
        line_number,
        cost_center )
    VALUES
        ( d_params.pr_number_in,
          d_params.line_number_in,
          d_params.cost_center_in );

    COMMIT;
END save_pr;

测试脚本：
成功地将数据插入到两个表中：

BEGIN
    save_pr(p_pr_number_in => 'PR102', 
            p_org_name_in => 'ORG102', 
            p_amount_in => 1000, 
            p_cost_center_in => 'CC102', 
            p_line_number_in => 1
    );
END;

型
由于成本中心值超过了允许的最大长度，此调用遇到错误，无法将数据插入到两个表中。

BEGIN
    save_pr(p_pr_number_in => 'PR103', 
            p_org_name_in => 'ORG103', 
            p_amount_in => 1000, 
            p_cost_center_in => 'CC1033', 
            p_line_number_in => 1
    );
END;

型
ORDS设置：

BEGIN
ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'DB_USER',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'db_user',
      p_auto_rest_auth      => FALSE);    

ORDS.DEFINE_MODULE(
      p_module_name    => 'test_payment_request',
      p_base_path      => '/prtest/',
      p_items_per_page =>  25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);    
      
ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'test_payment_request',
      p_pattern        => 'savePayReq',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
ORDS.DEFINE_HANDLER(
      p_module_name    => 'test_payment_request',
      p_pattern        => 'savePayReq',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_items_per_page =>  0,
      p_mimes_allowed  => 'application/json',
      p_comments       => NULL,
      p_source         => 
'begin

save_pr(    p_pr_number_in => :pr_number_in, 
            p_org_name_in => :org_name_in, 
            p_amount_in => :amount_in, 
            p_cost_center_in => :cost_center_in, 
            p_line_number_in => :line_number_in
    );      

:return_status := ''SUCCESS'';
:return_message := ''Saved successfully.'';

EXCEPTION
    WHEN OTHERS THEN
        :status := 400;
        :return_status := ''ERROR'';
        :return_message := ''System response: '' || sqlerrm;

end;'
      
END;

型