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 Script:
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;
Your REST service must be concluding its session with an auto-commit, where your other client doesn't do so.
Your code doesn't have an exception handler, so if your second SQL fails, there is nothing to rollback the first SQL except the client itself, outside the procedure. Some clients may be configured for auto-commit, others not. Therein is your difference. It is not safe to let stateless APIs like REST calls and Web-based calls create transactions that are left open by your procedures. Always ensure you do an explicit commit or rollback before returning control, and that includes in error conditions.
So, if you want a failure in the second SQL to always rollback the first SQL, you need to program that into the procedure itself. Look at the very end:
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;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END save_pr;