Commiting only a part of transaction in remote db

[ramesh_putta]

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;

[tombest]

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