-
Commiting only a part of transaction in remote db
Commiting only a part of transaction in remote db
Hi,
I have a procedure SLT_PROV_LOG on a remote db and I am trying to
call this from another procedure SLT_PROV_UPD_SERVICE_ORDER_ID which is in my local DB.
I wanted to commit only the transactions happened through SLT_PROV_LOG irrespective of the status of other transactions.
The Oracle env is 8.1.7
thanks
Ramesh
========================================================================
-- PROCEDURE: SLT_PROV_LOG
--
-- PURPOSE: INSERTS VALUES INTO SLTPROV_LOG FOR LOGS AND ERROR MESSAGES
--
-- ========================================================================
PROCEDURE SLT_PROV_LOG ( p_CustomerRef IN VARCHAR2,
p_AccountNum IN VARCHAR2,
p_ProductID IN NUMBER,
p_ProductSeq IN NUMBER,
p_CreatedDate IN DATE,
p_Remarks IN VARCHAR2 ) IS
BEGIN
INSERT INTO SLTPROV_LOG
VALUES ( p_CustomerRef,
p_AccountNum,
p_ProductID,
p_ProductSeq,
p_CreatedDate,
p_Remarks );
commit;
END SLT_PROV_LOG;
-----------------------------------------------
-- ========================================================================
--
-- PROCEDURE: SLT_PROV_UPD_SERVICE_ORDER_ID
--
-- PURPOSE: STORES THE SERVICE ORDER ID GENERATED BY CLARITY ONCE CLARITY
-- HAS VALIDATED THE SERVICE ORDER
--
-- ========================================================================
PROCEDURE SLT_PROV_UPD_SERVICE_ORDER_ID (p_CustomerRef IN VARCHAR2,
p_ProductID IN NUMBER,
p_ProductSeq IN NUMBER,
p_ServiceOrderID IN VARCHAR2,
p_CommitFlag IN VARCHAR2,
p_Results OUT VARCHAR2) IS
------------------------------------------------------------------------
-- Variables
------------------------------------------------------------------------
UPDATE_SER_ORD_FAILED EXCEPTION;
PRAGMA EXCEPTION_INIT(UPDATE_SER_ORD_FAILED, -20013);
BEGIN
IF (SLT_PROV_IS_TRACE_ON() = 'Y') THEN
SLT_PROV_LOG( p_CustomerRef,
NULL,
p_ProductID,
p_ProductSeq,
sysdate,
'SLTPROV.SLT_PROV_UPD_ORDER_STATUS: ' ||
'p_ServiceOrderID='|| p_ServiceOrderID);
END IF;
p_Results := NULL;
BEGIN
SLT_PROV_VALIDATE(p_CustomerRef, NULL, p_ProductID, p_ProductSeq, FALSE);
BEGIN
UPDATE custhasproduct
SET supplier_order_num = p_ServiceOrderID
WHERE customer_ref = p_CustomerRef
AND product_id = p_ProductID
AND product_seq = p_ProductSeq;
p_Results := 'OK';
EXCEPTION
WHEN OTHERS THEN
RAISE UPDATE_SER_ORD_FAILED;
END;
EXCEPTION
WHEN UPDATE_SER_ORD_FAILED THEN
p_Results := SQLCODE;
SLT_PROV_LOG( p_CustomerRef,
NULL,
p_ProductID,
p_ProductSeq,
sysdate,
'SLTPROV Update Service Order Id' ||SQLERRM||
'CustomerRef='|| p_CustomerRef ||' ProductID='|| p_ProductID ||' ProductSeq='|| p_ProductSeq);
RAISE_APPLICATION_ERROR(SQLCODE,'SLTPROV.SLT_PROV_GET_PRODUCT_QUANTITY: get product quantity'||SQLERRM);
WHEN OTHERS THEN
p_Results := SQLCODE;
SLT_PROV_LOG( p_CustomerRef,
NULL,
p_ProductID,
p_ProductSeq,
sysdate,
'SLTPROV Update Service Order Id' ||SQLERRM||
'CustomerRef='|| p_CustomerRef ||' ProductID='|| p_ProductID ||' ProductSeq='|| p_ProductSeq);
RAISE_APPLICATION_ERROR(SQLCODE,'SLTPROV.SLT_PROV_GET_PRODUCT_QUANTITY: get product quantity'||SQLERRM);
END;
IF (p_CommitFlag = 'Y') THEN
BEGIN
COMMIT;
END;
END IF;
END SLT_PROV_UPD_SERVICE_ORDER_ID;
-
Check out autonomous transactions. That lets a stored proc commit/rollback independent of the transaction that was active in the calling proc.
See:
http://download-east.oracle.com/docs..._ora.htm#28551
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|