Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2004
    Posts
    10

    Arrow Unanswered: Trigger with commit/rollback using autonomous_transaction

    Help!

    I've search the forums for examples of using pragma autonomous_transaction. I've used it in my own code but I can't seem to get it to work.

    I have two tables:
    Code:
    CREATE TABLE NewCallData
    (
      CDRRef VARCHAR2(100),
      CLI VARCHAR2(15),
      DDI VARCHAR2(15),
      TLI VARCHAR2(15),
      SupplierID INTEGER,
      DateTimeStamp DATE,
      Duration INTEGER,
      Result VARCHAR2(100)
    );
    
    CREATE TABLE CallPayments 
    (
      CallPaymentPK INTEGER NOT NULL PRIMARY KEY USING INDEX TABLESPACE BILLINGINDX,
      ProductFK VARCHAR2(60) NOT NULL REFERENCES CORE.Products (ProductPK),
      SupplierFK INTEGER NOT NULL REFERENCES CORE.Suppliers (SupplierPK),
      DateStamp DATE NOT NULL CHECK (TO_CHAR(DateStamp, 'HH24MISS') = '000000'),
      TimeBandFK INTEGER NOT NULL REFERENCES TimeBands (TimeBandPK),
      CLICat VARCHAR2(20) NOT NULL,
      CLISubCat VARCHAR2(20) NOT NULL,
      DDICat VARCHAR2(20) NOT NULL,
      DDISubCat VARCHAR2(20) NOT NULL,
      TLICat VARCHAR2(20) NOT NULL,
      TLISubCat VARCHAR2(20) NOT NULL,
      Units NUMBER(9, 4) NOT NULL CHECK (Units >= 0),
      Items NUMBER(9, 4) NOT NULL,
      Cost NUMBER(9, 4) NOT NULL
    );
    CREATE SEQUENCE sCallPayments INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE;
    What I want to do is when I insert rows into the NewCallData table a trigger will attempt to insert or update rows in the CallPayments table. If the inserts/updates failed I want the inserts/updates to CallPayments to be rolled back and the Result field in the NewCallData table to be updated with the reason that it failed.

    Here's my trigger:
    Code:
    CREATE OR REPLACE TRIGGER AddNewCallData
      BEFORE INSERT ON NewCallData
      FOR EACH ROW
    DECLARE
      PRAGMA AUTONOMOUS_TRANSACTION;
      vResult VARCHAR2(100);
      
    BEGIN
      
      vResult := AddCall(:new.CLI, 
                         :new.DDI, 
                         :new.TLI, 
                         :new.SupplierID, 
                         :new.DateTimeStamp,
                         :new.Duration,
                         :new.CDRRef);
      
      IF (vResult = 'SUCCESS') THEN
        COMMIT;
      ELSE
        ROLLBACK;
      END IF;
      
      :new.Result := vResult;                     
      
    END;
    The AddCall Function does all the work of inserting/updating the rows in the CallPayments table and returns with the result and it does not have any commit or rollback statements within it.

    When trying to insert a row into NewCallData I get the following error:
    Code:
    INSERT INTO NewCallData VALUES (NULL, '01373123456', '08702250000', '01373654321', 13, SYSDATE, 60, NULL);
    ERROR: ORA-02074: cannot ROLLBACK in a distributed transaction
    ORA-06512: at "BILLING.ADDNEWCALLDATA", line 18
    ORA-04088: error during execution of trigger 'BILLING.ADDNEWCALLDATA'
    Does anybody know why this doesn't work or if there is another solution to the problem?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You don't need the autonomous transaction, commit and rollback statements. - or even the varieble. All you need is this:
    Code:
    CREATE OR REPLACE TRIGGER AddNewCallData
      BEFORE INSERT ON NewCallData
      FOR EACH ROW
    BEGIN
      
      :new.Result := AddCall(:new.CLI, 
                         :new.DDI, 
                         :new.TLI, 
                         :new.SupplierID, 
                         :new.DateTimeStamp,
                         :new.Duration,
                         :new.CDRRef);
     
    END;
    If the inserts/updates to CallPayments failed in AddCall then they didn't happen, so what is there to roll back?

  3. #3
    Join Date
    Sep 2004
    Posts
    10
    I understand what your saying but I'm still a little concerned that if there are 2 inserts, the first one suceeds and the second one fails then the data will be inconsistant. I need the first one to be rolled back if the subsequent inserts fail. For each row thats inserted into NewCallData, the AddCall function may insert or update 1, 2, 3, or more rows.

    AddCall is a bit complicated and recursively calls itself but I'll post it any way so you can see whats happening. I've also tried putting autonomous_transaction in the AddCal function and doing the rollbacks and commits in there but I still get the same error message.

    Code:
    CREATE OR REPLACE FUNCTION AddCall (CLI IN VARCHAR2, 
                                        DDI IN VARCHAR2, 
                                        TLI IN VARCHAR2,
                                        SupplierID IN INTEGER,
                                        DateTimeStamp IN DATE,
                                        Duration IN NUMBER,
                                        CDRRef IN OUT VARCHAR2,
                                        vSection IN INTEGER DEFAULT 1,
                                        vPercentage IN NUMBER DEFAULT 1,
                                        vMinimumDuration IN NUMBER DEFAULT 0) RETURN VARCHAR2 IS
    
      vTimeBand INTEGER;
      vTimeBandFrom INTEGER;
      vTimeBandTo INTEGER;
      vCLICat VARCHAR2(15);
      vCLISubCat VARCHAR2(15);
      vDDICat VARCHAR2(15);
      vDDISubCat VARCHAR2(15);
      vTLICat VARCHAR2(15);
      vTLISubCat VARCHAR2(15);
      vSectionDuration INTEGER;
      vOverlapDuration INTEGER;
      vNextSectionDateTime DATE;
      vSectionPercentage NUMBER(9, 4);
      vRateUnitCharge NUMBER(9, 4);
      vRateMinUnits NUMBER(9, 4);
      vRateItemCharge NUMBER(9, 4);
      vRateMinCharge NUMBER(9, 4);
      vCost NUMBER(9, 4);
      vUpdateRow INTEGER;
      vRecursiveResult VARCHAR2(100);
      
    BEGIN
    
      -- IGNORE ZERO DURATION CDRS
      IF (Duration = 0) THEN
        RETURN 'SUCCESS';
      END IF;
    
    
      -- IF A CDR REF IS GIVEN THEN USE THAT, OTHERWISE GENERATE ONE FROM THE DATA SUPPLIED
      IF (CDRRef IS NULL) THEN
          CDRRef := CLI || '-' || 
                    DDI || '-' || 
                    TLI || '-' || 
                    SupplierID || '-' || 
                    TO_CHAR(DateTimeStamp, 'YYYYMMDDHH24MISS') || '-' ||
                    TO_CHAR(Duration);
      END IF;
      
      
      -- APPEND SECTION ID TO REF IF GREATER THAN 1
      IF (vSection > 1) THEN
        IF (INSTR(CDRRef, '~', -1, 1) > 0) THEN
          CDRRef := SUBSTR(CDRRef, 1, INSTR(CDRRef, '~', -1, 1) - 1) || '~' || TO_CHAR(vSection);
        ELSE
          CDRRef := CDRRef || '~' || TO_CHAR(vSection);
        END IF;
      END IF;
        
      
      -- DELETE CALL LOGS OLDER THAN 3 MONTHS
      IF (MOD(TO_NUMBER(TO_CHAR(SYSDATE, 'HH24MISS')), 100) = 0) THEN
        DELETE FROM CallPaymentLog WHERE DateStamp < SYSDATE - 90;
      END IF;
    
    
      -- GET TIME BAND FOR CALL
      BEGIN
        SELECT TB.TimeBandPK, TBI.FromTime, TBI.ToTime 
          INTO vTimeBand, vTimeBandFrom, vTimeBandTo
          FROM TimeBands TB, TimeBandItems TBI
         WHERE TB.TimeBandPK = TBI.TimeBandFK
           AND TB.SupplierFK = SupplierID
           AND TBI.DayOfWeek = SUBSTR(TO_CHAR(DateTimeStamp, 'DAY'), 1, 3)
           AND TO_NUMBER(TO_CHAR(DateTimeStamp, 'HH24MISS')) BETWEEN FromTime AND ToTime;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          --ROLLBACK;
          RETURN 'NO TIME BAND';
        
        WHEN TOO_MANY_ROWS THEN
          --ROLLBACK;
          RETURN 'DUPLICATE TIME BAND MATCH';
          
        END;
      
         
         
      -- CALCULATE DURATION OVERLAP
      IF (TO_NUMBER(TO_CHAR(DateTimeStamp, 'HH24MISS')) + Duration > vTimeBandTo) THEN
        vOverlapDuration := (TO_NUMBER(TO_CHAR(DateTimeStamp, 'HH24MISS')) + Duration) - vTimeBandTo;
        vSectionDuration := Duration - vOverlapDuration;
        vNextSectionDateTime := DateTimeStamp + ((vSectionDuration + 1) / 86400);
      ELSE
        vOverlapDuration := 0;
        vSectionDuration := Duration;
        vNextSectionDateTime := DateTimeStamp;
      END IF;  
      
      
      -- CALCULATE CALL PERCENTAGE
      vSectionPercentage := vSectionDuration / (vSectionDuration + vOverlapDuration) * vPercentage;
         
         
      -- GET CLI CATEGORIES
      BEGIN
        SELECT CategoryFK, SubCategoryFK
          INTO vCLICat, vCLISubCat
          FROM (SELECT CategoryFK, SubCategoryFK
                  FROM SubCategoryPrefixes
                 WHERE CategoryType = 'CLI'
                   AND CLI LIKE CLIPrefix || '%'
                   AND DDI LIKE DDIPrefix || '%'
                   AND TLI LIKE TLIPrefix || '%'
                 ORDER BY LENGTH(CLIPrefix) ASC, LENGTH(DDIPrefix) ASC, LENGTH(TLIPrefix) ASC)
         WHERE ROWNUM = 1;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          --ROLLBACK;
          RETURN 'NO CLI CATEGORY';
    
      END;
       
    
      -- GET DDI CATEGORIES
      BEGIN
        SELECT CategoryFK, SubCategoryFK
          INTO vDDICat, vDDISubCat
          FROM (SELECT CategoryFK, SubCategoryFK
                  FROM SubCategoryPrefixes
                 WHERE CategoryType = 'DDI'
                   AND CLI LIKE CLIPrefix || '%'
                   AND DDI LIKE DDIPrefix || '%'
                   AND TLI LIKE TLIPrefix || '%'
                 ORDER BY LENGTH(DDIPrefix) ASC, LENGTH(TLIPrefix) ASC, LENGTH(CLIPrefix) ASC)
         WHERE ROWNUM = 1;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          --ROLLBACK;
          RETURN 'NO DDI CATEGORY';
          
      END;     
    
    
      -- GET TLI CATEGORIES
      BEGIN
        SELECT CategoryFK, SubCategoryFK
          INTO vTLICat, vTLISubCat
          FROM (SELECT CategoryFK, SubCategoryFK
                  FROM SubCategoryPrefixes
                 WHERE CategoryType = 'TLI'
                   AND CLI LIKE CLIPrefix || '%'
                   AND DDI LIKE DDIPrefix || '%'
                   AND TLI LIKE TLIPrefix || '%'
                 ORDER BY LENGTH(TLIPrefix) ASC, LENGTH(CLIPrefix) ASC, LENGTH(DDIPrefix) ASC)
         WHERE ROWNUM = 1;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          --ROLLBACK;
          RETURN 'NO TLI CATEGORY';
          
      END;     
    
    
      -- GET WHOLESALE RATE THAT MATCHES THIS CALL TYPE
      BEGIN
        SELECT UnitCharge, MinUnits, ItemCharge, MinCharge
          INTO vRateUnitCharge, vRateMinUnits, vRateItemCharge, vRateMinCharge 
          FROM (SELECT UnitCharge, MinUnits, ItemCharge, MinCharge
                  FROM Rates
                 WHERE RateType = 'WHO'
                   AND SupplierFK = SupplierID
                   AND (TimeBandFK = vTimeBand OR TimeBandFK IS NULL)
                   AND (DDICat = vDDICat OR DDICat IS NULL)
                   AND (CLICat = vCLICat OR CLICat IS NULL)
                   AND (TLICat = vTLICat OR TLICat IS NULL)
                   AND (DDISubCat = vDDISubCat OR DDISubCat IS NULL)
                   AND (CLISubCat = vCLISubCat OR CLISubCat IS NULL)
                   AND (TLISubCat = vTLISubCat OR TLISubCat IS NULL)
                   AND StartDate <= TO_DATE(DateTimeStamp, 'DD/MM/YYYY')
                 ORDER BY StartDate DESC)
         WHERE ROWNUM = 1;
         
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          --ROLLBACK;
          RETURN 'NO RATE';
          
      END;
      
      
      -- IF SECOND SECTION OR LATER THEN OVERRIDE THE MINIMUM DURATION
      IF (vSection > 1) THEN
        vRateMinUnits := vMinimumDuration;
      END IF;
      
      
      -- CALCULATE COST
      vCost := GREATEST(GREATEST(vSectionDuration, vRateMinUnits) * vRateUnitCharge + vRateItemCharge, vRateMinCharge);
      
         
      -- FIND ROW TO UPDATE IF IT EXISTS
      BEGIN
        SELECT CallPaymentPK INTO vUpdateRow
          FROM CallPayments
         WHERE ProductFK = DDI
           AND SupplierFK = SupplierID
           AND DateStamp = TRUNC(DateTimeStamp)
           AND TimeBandFK = vTimeBand
           AND CLICat = vCLICat
           AND CLISubCat = vCLISubCat
           AND DDICat = vDDICat
           AND DDISubCat = vDDISubCat
           AND TLICat = vTLICat
           AND TLISubCat = vTLISubCat;
           
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          vUpdateRow := NULL;
          
        WHEN TOO_MANY_ROWS THEN
          --ROLLBACK;
          RETURN 'DUPLICATE CALL PAYMENT ENTRY';
          
      END;   
      
         
      -- DECIDE WHETHER TO CREATE A NEW ROW OR UPDATE EXISTING
      IF (vUpdateRow IS NULL) THEN
      
        INSERT INTO CallPayments (CallPaymentPK, ProductFK, SupplierFK, DateStamp, TimeBandFK, 
                                  CLICat, CLISubCat, DDICat, DDISubCat, TLICat, TLISubCat, 
                                  Units, Items, Cost)
                          VALUES (sCallPayments.NEXTVAL, DDI, SupplierID, 
                                  TRUNC(DateTimeStamp), vTimeBand, 
                                  vCLICat, vCLISubCat, vDDICat, vDDISubCat, vTLICat, vDDISubCat,
                                  vSectionDuration, vSectionPercentage, vCost);
    
      ELSE
        
        UPDATE CallPayments
           SET Units = Units + vSectionDuration,
               Items = Items + vSectionPercentage,
               Cost = Cost + vCost
         WHERE CallPaymentPK = vUpdateRow;
      
      END IF;
      
      
      -- ADD REFERENCE NUMBER TO CALL PAYMENT LOG
      BEGIN
        INSERT INTO CallPaymentLog (CallPaymentLogPK, DateStamp)
                            VALUES (CDRRef, SYSDATE);
      EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
          --ROLLBACK;
          RETURN 'DUPLICATE CDR';
          
      END;
      
      
      -- CALL RECURSIVLY IF CALL CROSSES A TIME BAND
      IF (vOverlapDuration > 0) THEN
      
        vRecursiveResult := AddCall(CLI, DDI, TLI, 
                                    SupplierID, vNextSectionDateTime, 
                                    vOverlapDuration, CDRRef, 
                                    vSection + 1,
                                    vPercentage - vSectionPercentage,
                                    GREATEST(vRateMinUnits - vSectionDuration, 0));
        
        IF (vRecursiveResult = 'SUCCESS') THEN
          RETURN 'SUCCESS';
        ELSE
          --ROLLBACK;
          RETURN vRecursiveResult;
        END IF;
          
      ELSE
        RETURN 'SUCCESS';
        
      END IF;
        
      
    
    EXCEPTION
      WHEN OTHERS THEN
        --ROLLBACK;
        RETURN 'FAILED: ' || SQLERRM;
      
    END;

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can put a SAVEPOINT at the start of the procedure, and then use ROLLBACK TO <savepoint> when you get an error.

    Alternatively: if your procedure raised an error (e.g. RAISE_APPLICATION_ERROR(-20001,'NO CLI CATEGORY')) instead of returning "successfully" with a message, then it would automatically roll back its own work (only), and the trigger could trap the exception and parse the error message to get the Result value. At the moment you are not leveraging the power of PL/SQL's way of working.

  5. #5
    Join Date
    Sep 2004
    Posts
    10
    Thanks for your help. I've just tried to put a save point at the top of the AddCall function but I get this runtime error:

    ORA-02074: cannot SET SAVEPOINT in a distributed transaction


    I'll try the other method with using the raise_application_error and see if that works.

  6. #6
    Join Date
    Sep 2004
    Posts
    10
    OK, I've now modified my AddCall procedure to raise errors instead of returning a varchar containing the error. And I've changed my trigger to the following:

    Code:
    CREATE OR REPLACE TRIGGER AddNewCallData
      BEFORE INSERT ON NewCallData
      FOR EACH ROW
    DECLARE
      
      PRAGMA AUTONOMOUS_TRANSACTION;
      vRef VARCHAR2(100);
      vResult VARCHAR2(100);
    
    BEGIN
      
      vRef := :new.CDRRef;
     
      BEGIN
        AddCall(:new.CLI, 
                :new.DDI, 
                :new.TLI, 
                :new.SupplierID, 
                :new.DateTimeStamp,
                :new.Duration,
                vRef);
        vResult := 'SUCCESS';
        COMMIT;
    
      EXCEPTION
        WHEN OTHERS THEN
          vResult := SUBSTR(SQLERRM, 12);  
          ROLLBACK;      
      END;
    
      :new.CDRRef := vRef;
      :new.Result := vResult;
    
    END;
    This works fine when there are no problems within the AddCall proc but if there is an error and it hits the ROLLBACK then I get this message:

    ORA-02074: cannot ROLLBACK in a distributed transaction


    If I remove the ROLLBACK I get this:

    ORA-06519: active autonomous transaction detected and rolled back


    I've trawlled though many examples of autonomous transactions in triggers but not one shows an example with a ROLLBACK in it. I can't understand why the COMMIT would work but the ROLLBACK would fail.

    Does anyone have any ideas about this?

    Thanks,
    Ben

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You don't want an autonomous transaction, and you don't need to explicitly rollback or commit:
    Code:
    CREATE OR REPLACE TRIGGER AddNewCallData
      BEFORE INSERT ON NewCallData
      FOR EACH ROW
    DECLARE
      
      vRef VARCHAR2(100);
      vResult VARCHAR2(100);
    
    BEGIN
      
      vRef := :new.CDRRef;
     
      BEGIN
        AddCall(:new.CLI, 
                :new.DDI, 
                :new.TLI, 
                :new.SupplierID, 
                :new.DateTimeStamp,
                :new.Duration,
                vRef);
        vResult := 'SUCCESS';
    
      EXCEPTION
        WHEN OTHERS THEN
          vResult := SUBSTR(SQLERRM, 12);  
      END;
    
      :new.CDRRef := vRef;
      :new.Result := vResult;
    
    END;
    If the AddCall procedure raises an exception, then any work it did will have become undone anyway - that's how exception handling works in PL/SQL - see this trivial example:
    Code:
    SQL> create table t1 (id integer primary key);
    
    Table created.
    
    SQL> begin
      2    insert into t1 values (1); -- This will succeed
      3    insert into t1 values (1); -- This will fail unique constraint
      4  end;
      5  /
    begin
    *
    ERROR at line 1:
    ORA-00001: unique constraint (TANDREWS.SYS_C0063567) violated
    ORA-06512: at line 3
    
    
    SQL> select * from t1;
    
    no rows selected
    See - the first, successful insert was undone when the exception was raised.

    I haven't much experience with distributed transactions, so I can't help with that part of your problem. But hopefull it doesn't arise if you do this right.

  8. #8
    Join Date
    Sep 2004
    Posts
    10
    Do all the INSERTS/UPDATES have to be in the same BEGIN...END block?

    Below is my modified AddCall proc but when an error is raised the previous inserts/updates don't seem to rollback even though they are in the same proc (although they are in there own separate sub blocks):

    Code:
    CREATE OR REPLACE PROCEDURE AddCall (CLI IN VARCHAR2, 
                                        DDI IN VARCHAR2, 
                                        TLI IN VARCHAR2,
                                        SupplierID IN INTEGER,
                                        DateTimeStamp IN DATE,
                                        Duration IN NUMBER,
                                        CDRRef IN OUT VARCHAR2,
                                        vSection IN INTEGER DEFAULT 1,
                                        vPercentage IN NUMBER DEFAULT 1,
                                        vMinimumDuration IN NUMBER DEFAULT 0) IS
      --PRAGMA AUTONOMOUS_TRANSACTION;
    
      vTimeBand INTEGER;
      vTimeBandFrom INTEGER;
      vTimeBandTo INTEGER;
      vCLICat VARCHAR2(15);
      vCLISubCat VARCHAR2(15);
      vDDICat VARCHAR2(15);
      vDDISubCat VARCHAR2(15);
      vTLICat VARCHAR2(15);
      vTLISubCat VARCHAR2(15);
      vSectionDuration INTEGER;
      vOverlapDuration INTEGER;
      vNextSectionDateTime DATE;
      vSectionPercentage NUMBER(9, 4);
      vRateUnitCharge NUMBER(9, 4);
      vRateMinUnits NUMBER(9, 4);
      vRateItemCharge NUMBER(9, 4);
      vRateMinCharge NUMBER(9, 4);
      vCost NUMBER(9, 4);
      vUpdateRow INTEGER;
    
      
    BEGIN
    
      --SAVEPOINT PROC_START;
    
      -- IGNORE ZERO DURATION CDRS
      IF (Duration = 0) THEN
        RETURN;
      END IF;
    
    
      -- IF A CDR REF IS GIVEN THEN USE THAT, OTHERWISE GENERATE ONE FROM THE DATA SUPPLIED
      IF (CDRRef IS NULL) THEN
        CDRRef := CLI || '-' || 
                   DDI || '-' || 
                   TLI || '-' || 
                   SupplierID || '-' || 
                   TO_CHAR(DateTimeStamp, 'YYYYMMDDHH24MISS') || '-' ||
                   TO_CHAR(Duration);
      END IF;
      
      
      -- APPEND SECTION ID TO REF IF GREATER THAN 1
      IF (vSection > 1) THEN
        IF (INSTR(CDRRef, '~', -1, 1) > 0) THEN
          CDRRef := SUBSTR(CDRRef, 1, INSTR(CDRRef, '~', -1, 1) - 1) || '~' || TO_CHAR(vSection);
        ELSE
          CDRRef := CDRRef || '~' || TO_CHAR(vSection);
        END IF;
      END IF;
        
      
      -- DELETE CALL LOGS OLDER THAN 3 MONTHS
      IF (MOD(TO_NUMBER(TO_CHAR(SYSDATE, 'HH24MISS')), 100) = 0) THEN
        DELETE FROM CallPaymentLog WHERE DateStamp < SYSDATE - 90;
      END IF;
    
    
      -- GET TIME BAND FOR CALL
      BEGIN
        SELECT TB.TimeBandPK, TBI.FromTime, TBI.ToTime 
          INTO vTimeBand, vTimeBandFrom, vTimeBandTo
          FROM TimeBands TB, TimeBandItems TBI
         WHERE TB.TimeBandPK = TBI.TimeBandFK
           AND TB.SupplierFK = SupplierID
           AND TBI.DayOfWeek = SUBSTR(TO_CHAR(DateTimeStamp, 'DAY'), 1, 3)
           AND TO_NUMBER(TO_CHAR(DateTimeStamp, 'HH24MISS')) BETWEEN FromTime AND ToTime;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          RAISE_APPLICATION_ERROR(-20001, 'NO TIME BAND');
        
        WHEN TOO_MANY_ROWS THEN
          RAISE_APPLICATION_ERROR(-20001, 'DUPLICATE TIME BAND MATCH');
          
        END;
      
         
         
      -- CALCULATE DURATION OVERLAP
      IF (TO_NUMBER(TO_CHAR(DateTimeStamp, 'HH24MISS')) + Duration > vTimeBandTo) THEN
        vOverlapDuration := (TO_NUMBER(TO_CHAR(DateTimeStamp, 'HH24MISS')) + Duration) - vTimeBandTo;
        vSectionDuration := Duration - vOverlapDuration;
        vNextSectionDateTime := DateTimeStamp + ((vSectionDuration + 1) / 86400);
      ELSE
        vOverlapDuration := 0;
        vSectionDuration := Duration;
        vNextSectionDateTime := DateTimeStamp;
      END IF;  
      
      
      -- CALCULATE CALL PERCENTAGE
      vSectionPercentage := vSectionDuration / (vSectionDuration + vOverlapDuration) * vPercentage;
         
         
      -- GET CLI CATEGORIES
      BEGIN
        SELECT CategoryFK, SubCategoryFK
          INTO vCLICat, vCLISubCat
          FROM (SELECT CategoryFK, SubCategoryFK
                  FROM SubCategoryPrefixes
                 WHERE CategoryType = 'CLI'
                   AND CLI LIKE CLIPrefix || '%'
                   AND DDI LIKE DDIPrefix || '%'
                   AND TLI LIKE TLIPrefix || '%'
                 ORDER BY LENGTH(CLIPrefix) ASC, LENGTH(DDIPrefix) ASC, LENGTH(TLIPrefix) ASC)
         WHERE ROWNUM = 1;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          RAISE_APPLICATION_ERROR(-20001, 'NO CLI CATEGORY');
    
      END;
       
    
      -- GET DDI CATEGORIES
      BEGIN
        SELECT CategoryFK, SubCategoryFK
          INTO vDDICat, vDDISubCat
          FROM (SELECT CategoryFK, SubCategoryFK
                  FROM SubCategoryPrefixes
                 WHERE CategoryType = 'DDI'
                   AND CLI LIKE CLIPrefix || '%'
                   AND DDI LIKE DDIPrefix || '%'
                   AND TLI LIKE TLIPrefix || '%'
                 ORDER BY LENGTH(DDIPrefix) ASC, LENGTH(TLIPrefix) ASC, LENGTH(CLIPrefix) ASC)
         WHERE ROWNUM = 1;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          RAISE_APPLICATION_ERROR(-20001, 'NO DDI CATEGORY');
          
      END;     
    
    
      -- GET TLI CATEGORIES
      BEGIN
        SELECT CategoryFK, SubCategoryFK
          INTO vTLICat, vTLISubCat
          FROM (SELECT CategoryFK, SubCategoryFK
                  FROM SubCategoryPrefixes
                 WHERE CategoryType = 'TLI'
                   AND CLI LIKE CLIPrefix || '%'
                   AND DDI LIKE DDIPrefix || '%'
                   AND TLI LIKE TLIPrefix || '%'
                 ORDER BY LENGTH(TLIPrefix) ASC, LENGTH(CLIPrefix) ASC, LENGTH(DDIPrefix) ASC)
         WHERE ROWNUM = 1;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          RAISE_APPLICATION_ERROR(-20001, 'NO TLI CATEGORY');
          
      END;     
    
    
      -- GET WHOLESALE RATE THAT MATCHES THIS CALL TYPE
      BEGIN
        SELECT UnitCharge, MinUnits, ItemCharge, MinCharge
          INTO vRateUnitCharge, vRateMinUnits, vRateItemCharge, vRateMinCharge 
          FROM (SELECT UnitCharge, MinUnits, ItemCharge, MinCharge
                  FROM Rates
                 WHERE RateType = 'WHO'
                   AND SupplierFK = SupplierID
                   AND (TimeBandFK = vTimeBand OR TimeBandFK IS NULL)
                   AND (DDICat = vDDICat OR DDICat IS NULL)
                   AND (CLICat = vCLICat OR CLICat IS NULL)
                   AND (TLICat = vTLICat OR TLICat IS NULL)
                   AND (DDISubCat = vDDISubCat OR DDISubCat IS NULL)
                   AND (CLISubCat = vCLISubCat OR CLISubCat IS NULL)
                   AND (TLISubCat = vTLISubCat OR TLISubCat IS NULL)
                   AND StartDate <= TRUNC(DateTimeStamp)
                 ORDER BY StartDate DESC)
         WHERE ROWNUM = 1;
         
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          RAISE_APPLICATION_ERROR(-20001, 'NO RATE');
          
      END;
      
      
      -- IF SECOND SECTION OR LATER THEN OVERRIDE THE MINIMUM DURATION
      IF (vSection > 1) THEN
        vRateMinUnits := vMinimumDuration;
      END IF;
      
      
      -- CALCULATE COST
      vCost := (GREATEST(vSectionDuration, vRateMinUnits) / 60) * vRateUnitCharge + vRateItemCharge;
      IF (vRateMinCharge < 0) THEN
        vCost := LEAST(vCost, vRateItemCharge);
      ELSIF (vRateMinCharge > 0) THEN
        vCost := GREATEST(vCost, vRateItemCharge);
      END IF;
    
         
      -- FIND ROW TO UPDATE IF IT EXISTS
      BEGIN
        SELECT CallPaymentPK INTO vUpdateRow
          FROM CallPayments
         WHERE ProductFK = DDI
           AND SupplierFK = SupplierID
           AND DateStamp = TRUNC(DateTimeStamp)
           AND TimeBandFK = vTimeBand
           AND CLICat = vCLICat
           AND CLISubCat = vCLISubCat
           AND DDICat = vDDICat
           AND DDISubCat = vDDISubCat
           AND TLICat = vTLICat
           AND TLISubCat = vTLISubCat;
           
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          vUpdateRow := NULL;
          
        WHEN TOO_MANY_ROWS THEN
          RAISE_APPLICATION_ERROR(-20001, 'DUPLICATE CALL PAYMENT ENTRY');
          
      END;   
      
         
      -- DECIDE WHETHER TO CREATE A NEW ROW OR UPDATE EXISTING
      IF (vUpdateRow IS NULL) THEN
      
        INSERT INTO CallPayments (CallPaymentPK, ProductFK, SupplierFK, DateStamp, TimeBandFK, 
                                  CLICat, CLISubCat, DDICat, DDISubCat, TLICat, TLISubCat, 
                                  Units, Items, Cost)
                          VALUES (sCallPayments.NEXTVAL, DDI, SupplierID, 
                                  TRUNC(DateTimeStamp), vTimeBand, 
                                  vCLICat, vCLISubCat, vDDICat, vDDISubCat, vTLICat, vTLISubCat,
                                  vSectionDuration, vSectionPercentage, vCost);
    
      ELSE
        
        UPDATE CallPayments
           SET Units = Units + vSectionDuration,
               Items = Items + vSectionPercentage,
               Cost = Cost + vCost
         WHERE CallPaymentPK = vUpdateRow;
      
      END IF;
      
      
      -- ADD REFERENCE NUMBER TO CALL PAYMENT LOG
      BEGIN
        INSERT INTO CallPaymentLog (CallPaymentLogPK, DateStamp)
                            VALUES (CDRRef, SYSDATE);
      EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
          RAISE_APPLICATION_ERROR(-20001, 'DUPLICATE CDR');
          
      END;
      
      
      -- CALL RECURSIVLY IF CALL CROSSES A TIME BAND
      IF (vOverlapDuration > 0) THEN
      
        BEGIN
          AddCall(CLI, DDI, TLI, 
                  SupplierID, vNextSectionDateTime, 
                  vOverlapDuration, CDRRef, 
                  vSection + 1,
                  vPercentage - vSectionPercentage,
                  GREATEST(vRateMinUnits - vSectionDuration, 0));
        EXCEPTION
          WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20001, SUBSTR(SQLERRM, 12));
        END;
        
      END IF;
        
      
    
    EXCEPTION
      WHEN OTHERS THEN
        --ROLLBACK TO PROC_START;
        RAISE_APPLICATION_ERROR(-20001, SUBSTR(SQLERRM, 12));
      
    END;

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, it makes no difference: when an exception is raised from a block, any uncommited changes made within it (including within sub-blocks within it) are undone:
    Code:
    SQL> begin
      2    begin
      3      insert into t1 values (1);
      4    end;
      5    begin
      6      insert into t1 values (1);
      7    end;
      8  end;
      9  /
    begin
    *
    ERROR at line 1:
    ORA-00001: unique constraint (TANDREWS.SYS_C0063567) violated
    ORA-06512: at line 6
    
    
    SQL> select * from t1;
    
    no rows selected
    Now here's the same thing with your exception handling:
    Code:
    SQL> begin
      2    begin
      3      insert into t1 values (1);
      4    exception
      5      when others then raise_application_error(-20001,'Insert 1 failed');
      6    end;
      7    begin
      8      insert into t1 values (1);
      9    exception
     10      when others then raise_application_error(-20001,'Insert 2 failed');
     11    end;
     12  EXCEPTION
     13   WHEN OTHERS THEN
     14     RAISE_APPLICATION_ERROR(-20001, SUBSTR(SQLERRM, 12));
     15  end;
     16  /
    begin
    *
    ERROR at line 1:
    ORA-20001: Insert 2 failed
    ORA-06512: at line 14
    
    
    SQL> select * from t1;
    
    no rows selected
    BTW, your final exception handler there is redundant - you have already raised an exception earlier: if you don't handle it, it will be passed back to the caller anyway:
    Code:
    SQL> begin
      2    begin
      3      insert into t1 values (1);
      4    exception
      5      when others then raise_application_error(-20001,'Insert 1 failed');
      6    end;
      7    begin
      8      insert into t1 values (1);
      9    exception
     10      when others then raise_application_error(-20001,'Insert 2 failed');
     11    end;
     12  end;
     13  /
    begin
    *
    ERROR at line 1:
    ORA-20001: Insert 2 failed
    ORA-06512: at line 10
    
    
    tandrews@SX3TEST
    SQL> select * from t1;
    
    no rows selected
    So I don't see any reason why you should get different behaviour. If you can simplify your example and post the full code, maybe I can try it and see what you mean.

  10. #10
    Join Date
    Sep 2004
    Posts
    10
    Tony,

    Thanks for you help with this one. I've simplified my code but I still get the same problem. If you try the code below hopefully you'll get the same results as I did. To test it do this:

    INSERT INTO TestNew VALUES (1, NULL);

    What should happen is TestData and TestLog should get filled with 1, 2, 3, 4, 5. But TestLog has a primary key constraint so if you do the same insert again then '1' gets inserted into TestData, then '1' tries to be inserted into TestLog but fails and raises an exception, however, this exception does not seem to rollback the '1' that was inserted into TestData.

    Thanks again for your help.


    Code:
    CREATE TABLE TestNew (f1 INTEGER, Result VARCHAR2(100));
    CREATE TABLE TestData (f1 INTEGER);
    CREATE TABLE TestLog (f1 INTEGER PRIMARY KEY);
    
    
    -------------------------------------------------------------
    
    CREATE OR REPLACE PROCEDURE TestProc (p1 INTEGER) IS
      
    BEGIN
    
      
      BEGIN
        INSERT INTO TestData (f1) VALUES (p1);
      EXCEPTION
        WHEN OTHERS THEN
          RAISE_APPLICATION_ERROR(-20001, 'TEST DATA ERROR: ' || SUBSTR(SQLERRM, 12));
          
      END;
      
      BEGIN
        INSERT INTO TestLog (f1) VALUES (p1);
      EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
          RAISE_APPLICATION_ERROR(-20001, 'DUPLICATE');
          
      END;
      
      
      -- CALL RECURSIVLY
      IF (p1 < 5) THEN
      
        BEGIN
          TestProc(p1 + 1);
    
        EXCEPTION
          WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20001, SUBSTR(SQLERRM, 12));
        END;
      END IF;
          
    END;
    
    
    
    ---------------------------------------------------------------------------
    CREATE OR REPLACE TRIGGER AddNew
      BEFORE INSERT ON TestNew
      FOR EACH ROW
    DECLARE
      
      vResult VARCHAR2(100);
    
    BEGIN
      
     
      BEGIN
        TestProc(:new.f1);
    
        vResult := 'SUCCESS';
    
      EXCEPTION
        WHEN OTHERS THEN
          vResult := SUBSTR(SQLERRM, 12);  
    
      END;
    
      :new.Result := vResult;
    
    END;

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    OK, I see what is happening. It is because the trigger traps the error but does not re-raise it - in effect, it decides that no error has occured and so no automatic rollback takes place. With your procedure you can see the difference like this:

    1) If we don't catch the exception at the outer level (the trigger):
    Code:
    SQL> select * from testdata;
    
    no rows selected
    
    SQL> select * from testlog;
    
    no rows selected
    
    SQL> begin
      2    testproc(1);
      3    testproc(1);
      4  end;
      5  /
    begin
    *
    ERROR at line 1:
    ORA-20001: DUPLICATE
    ORA-06512: at "TANDREWS.TESTPROC", line 17
    ORA-06512: at line 3
    
    
    SQL> select * from testdata;
    
    no rows selected
    
    SQL> select * from testlog;
    
    no rows selected
    As you can see, everything got rolled back. Now let's trap and re-raise the exception:
    Code:
    SQL> begin
      2    testproc(1);
      3    testproc(1);
      4  exception
      5    when others then
      6      raise_application_error(-20001,'Re-raised');
      7  end;
      8  /
    begin
    *
    ERROR at line 1:
    ORA-20001: Re-raised
    ORA-06512: at line 6
    
    
    SQL> select * from testdata;
    
    no rows selected
    
    SQL> select * from testlog;
    
    no rows selected
    Again, everything has been rolled back. Now let's catch but not re-raise the exception:
    Code:
    SQL> begin
      2    testproc(1);
      3    testproc(1);
      4  exception
      5    when others then
      6      dbms_output.put_line('Exception caught but not re-raised');
      7  end;
      8  /
    Exception caught but not re-raised
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from testdata;
    
            F1
    ----------
             1
             1
    
    SQL> select * from testlog;
    
            F1
    ----------
             1
    Now we get the behaviour your trigger exhibits. Now the way to get around this is to use a SAVEPOINT:
    Code:
    SQL> rollback;
    
    Rollback complete.
    
    SQL> begin
      2    savepoint a;
      3    begin
      4      testproc(1);
      5      dbms_output.put_line('call 1 succeeded');
      6    exception
      7      when others then
      8        dbms_output.put_line('call 1 failed');
      9        rollback to a;
     10    end;
     11    savepoint b;
     12    begin
     13      testproc(1);
     14      dbms_output.put_line('call 2 succeeded');
     15    exception
     16      when others then
     17        dbms_output.put_line('call 2 failed');
     18        rollback to b;
     19    end;
     20  end;
     21  /
    call 1 succeeded
    call 2 failed
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from testdata;
    
            F1
    ----------
             1
    
    SQL> select * from testlog;
    
            F1
    ----------
             1
    That is the result you need. With your procedure, it would look like this:
    Code:
    CREATE OR REPLACE PROCEDURE TestProc (p1 INTEGER) IS
      
    BEGIN
      SAVEPOINT a;
      BEGIN
        INSERT INTO TestData (f1) VALUES (p1);
      EXCEPTION
        WHEN OTHERS THEN
          RAISE_APPLICATION_ERROR(-20001, 'TEST DATA ERROR: ' || SUBSTR(SQLERRM, 12));
          
      END;
      
      BEGIN
        INSERT INTO TestLog (f1) VALUES (p1);
      EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
          RAISE_APPLICATION_ERROR(-20001, 'DUPLICATE');
          
      END;
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK TO a;
        RAISE;
    END;
    /
    
    SQL> begin
      2    testproc(1);
      3    testproc(1);
      4  exception
      5    when others then
      6      dbms_output.put_line('Exception caught but not re-raised');
      7  end;
      8  /
    Exception caught but not re-raised
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from testdata;
    
            F1
    ----------
             1
    
    tandrews@SX3TEST
    SQL> select * from testlog;
    
            F1
    ----------
             1
    However, unfortunately you can't do that in a trigger - SAVEPOINT and ROLLBACK are not allowed from within a trigger:
    Code:
    SQL> CREATE OR REPLACE TRIGGER AddNew
      2    BEFORE INSERT ON TestNew
      3    FOR EACH ROW
      4  BEGIN
      5      TestProc(:new.f1);
      6  
      7      :new.Result := 'SUCCESS';
      8  
      9  EXCEPTION
     10      WHEN OTHERS THEN
     11        :new.Result := SUBSTR(SQLERRM, 12, 100);  
     12  
     13  END;
     14  /
    
    Trigger created.
    SQL> insert into testnew (f1) values (1);
    
    1 row created.
    
    SQL> select * from testnew;
    
            F1
    ----------
    RESULT
    ------------------------------------------------------------------------------
             1
    cannot ROLLBACK in a trigger
    ORA-06512: at "TANDREWS.TESTPROC", line 22
    ORA-04092: cannot SET SAVEPO
    
    
    SQL> select * from testdata;
    
    no rows selected
    
    SQL> select * from testlog;
    
    no rows selected
    So, you need to "simulate" rollback to savpoint processing in your code. This is the best I managed to come up with:
    Code:
    SQL> CREATE OR REPLACE PROCEDURE TestProc (p1 INTEGER) IS
      2    v_error VARCHAR2(100);  
      3    v_rowid ROWID;
      4  BEGIN
      5    BEGIN
      6      INSERT INTO TestData (f1) VALUES (p1) RETURNING ROWID INTO v_rowid;
      7    EXCEPTION
      8      WHEN OTHERS THEN
      9        v_error := 'TEST DATA ERROR: ' || SUBSTR(SQLERRM, 12);
     10        GOTO done;
     11    END;
     12    
     13    BEGIN
     14      INSERT INTO TestLog (f1) VALUES (p1);
     15    EXCEPTION
     16      WHEN DUP_VAL_ON_INDEX THEN
     17        DELETE FROM TestData WHERE ROWID = v_rowid;
     18        v_error := 'DUPLICATE';
     19    END;
     20  
     21    <<DONE>>  
     22    IF v_error IS NOT NULL THEN
     23      RAISE_APPLICATION_ERROR(-20001, v_error);
     24    END IF;
     25  END;
     26  /
    
    Procedure created.
    
    SQL> insert into testnew(f1) values (1);
    
    1 row created.
    
    SQL> insert into testnew(f1) values (1);
    
    1 row created.
    
    SQL> select * from testnew;
    
            F1
    ----------
    RESULT
    --------------------------------------------------------------------------------
             1
    SUCCESS
    
             1
    DUPLICATE
    
    
    tandrews@SX3TEST
    SQL> select * from testdata;
    
            F1
    ----------
             1
    
    tandrews@SX3TEST
    SQL> select * from testlog;
    
            F1
    ----------
             1
    This is a very unusual requirement: for an insert to have triggered outcomes, which are allowed to fail without aborting the triggering statement. Some might suggest autonomous transactions as a work around, but that would not be correct. With an autonomous transaction in the procedure, the inserts to TestData and testLog will remain in place even if the original insert into TestNew gets rolled back - i.e. you will have data corruption.

    Perhaps you could consider un-linking the triggered actions from the main insert like this:
    1) In the trigger, set Result to 'Not yet processed' (or similar), and submit a background job to run TestProc using DBMS_JOB:
    Code:
    SQL> CREATE OR REPLACE TRIGGER AddNew
      2    BEFORE INSERT ON TestNew
      3    FOR EACH ROW
      4  DECLARE
      5      v_job INTEGER;
      6  BEGIN
      7      :new.Result := 'NOT YET PROCESSED';
      8      DBMS_JOB.SUBMIT (v_job, 'TestProc('||:new.f1||');',SYSDATE);
      9  END;
     10  /
    
    Trigger created.
    2) In the procedure, update TestNew with the result:
    Code:
    CREATE OR REPLACE PROCEDURE TestProc (p1 INTEGER) IS
      v_result VARCHAR2(100);  
    BEGIN
      SAVEPOINT a;
      BEGIN
        BEGIN
          INSERT INTO TestData (f1) VALUES (p1);
        EXCEPTION
          WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20001,'TEST DATA ERROR: ' || SUBSTR(SQLERRM, 12));
        END;
      
        BEGIN
          INSERT INTO TestLog (f1) VALUES (p1);
        EXCEPTION
          WHEN DUP_VAL_ON_INDEX THEN
            RAISE_APPLICATION_ERROR(-20001,'DUPLICATE');
        END;
        v_result := 'SUCCESS';
      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK TO a;
          v_result := SQLERRM;
      END;
    
      UPDATE TestNew SET Result = v_result WHERE f1 = p1;
    END;
    /
    That will work, except that you need to resolve the issue that TestNew curently has no primary key (every table should have one, even if it is just a meaningless sequence value). So currently, the UPDATE statement in TestProc updates both rows. It would work if you passed the primary key to the procedure from the trigger.

  12. #12
    Join Date
    Sep 2004
    Posts
    10

    Smile

    Thank you very much for your help.

    You idea of simulating a rollback is ok but I think it will get very complicated if I was to implement that with my original code because of the recurrsive call. Also, what if the delete fails?

    I tried making TestProc an autonomous_transaction and this kind of worked but for some strange reason I kept getting an intermittant deadlock error which happend on about 50% of the inserts... not sure why!

    I like your idea of submitting jobs. This seems by far the best solution. I'll have ago this afternoon!

    Thank you!

    Ben

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I agree, my "roll your own rollback" solution with the DELETEs isn't very elegant - and as you say, what happens if the delete fails? It is a mess!

    I don't know why you got deadlocks with the autonomous transaction, but it isn't a proper solution anyway: you can end up with data in TestData and TestLog that don't correspond to anything in TestNew at all.

    I hope the job solution works out: you will need to ensure that your server is set up to allow jobs to run, at the appropriate frequency, etc. For testing you can use DBMS_JOB.RUN to kick off jobs that are waiting in the queue (see USER_JOBS).

  14. #14
    Join Date
    Sep 2004
    Posts
    10
    The job queuing works perfectly!

    Is it ok to have 4 million or more one-off jobs in the queue at any one time (one for each row in a table) or would it be a better solution to have a single job that processes all rows in a table with a flag that is updated when that row has been processed? Or doesn't it made any difference?

  15. #15
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by bengolden
    Is it ok to have 4 million or more one-off jobs in the queue at any one time (one for each row in a table) or would it be a better solution to have a single job that processes all rows in a table with a flag that is updated when that row has been processed? Or doesn't it made any difference?
    4 millions jobs in the queue sounds most undesirable! Even if you had 100 queues processing 40,000 jobs each that would no doubt take a long time, and be very inefficient (4 million sessions being started and stopped).

    Yes, for that sort of volume I would go for an independent job (i.e. not submitted by the trigger) that runs e.g. every 5 minutes, every hour, or every day (whatever is reasonable) and processes all the 'Not Yet Processed' rows. (You don't need a new flag do you? The Result column does that).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •