SQL & PL/SQL

We have two tables named XXTB_PAYMENT_REQUEST and XXTB_PR_DISTRIBUTION. We use a PLSQL Procedure to insert/update data into these tables. This Procedure is exposed as an ORDS service, allowing it to be invoked by other applications as a Rest API.

The problem we are facing is that when the API is executed, if the procedure fails to insert data into the second table (XXTB_PR_DISTRIBUTION), it does not roll back the data that was already inserted into the XXTB_PAYMENT_REQUEST table. However, when I run the procedure locally from my SQL Developer as a procedure call, it works fine and considers the call as one single transaction.

What should be done to ensure that the API also considers the call as one single transaction?

Setup:

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;

Test Scripts:

Successfully inserts the data into both tables:

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;

This call encounters an error and fails to insert data into both tables due to the Cost Center value exceeding the maximum allowed length.

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 Setup:

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;