Commiting only a part of transaction in remote db
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Commiting only a part of transaction in remote db

  1. #1
    Join Date
    Jul 2003
    Posts
    3

    Lightbulb 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;

  2. #2
    Join Date
    Jul 2003
    Posts
    59
    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
  •  



Click Here to Expand Forum to Full Width