Home » SQL & PL/SQL » SQL & PL/SQL » Unknown Issue with Oracle procedure (Oracle 8i)
Unknown Issue with Oracle procedure [message #293200] Fri, 11 January 2008 03:31 Go to next message
neosan
Messages: 1
Registered: January 2008
Junior Member
Hi guys,

I have this piece of code which is giving me some weird problem. I was checking it against the spool file. The value for cur_purse_rv is zero but there is no NO_DATA exception and when I did a manual SQL check, there is a value for cur_purse_rv. I do not understand why cur_purse_rv is zero when it skipped the processing for NO_DATA exception and there is a value for it in that cursor.

To add, even if zero is not the value I am expected for cur_purse_rv. It should have a decent value.


BEGIN
  vStartTime := SYSDATE;
  
  vSettle_Date := GetSettlementDate(p_SettleDate);
  
  FOR txn IN rv_txn LOOP
    BEGIN
      dbms_Output.Put_Line('txn details:'
                           ||txn.cscPhysicalId
                           ||' '
                           ||txn.cscLifeCycleCount);
      
      SELECT Card_Physical_Id,
             Card_LifeCycle_Count,
             txn.txn_val + txn.Miss_val,
             p2.RemainIng_Value
      INTO   cscId,
             clc,
             txn_rv,
             Cur_Purse_rv
      FROM   Purse_Account p2,
             eod_SettlementDate_Info eod
      WHERE  eod.NewsettleMentDate = vSettle_Date
             AND eod.NewsettleMentDate = p2.Card_Settlement_Date
             AND p2.Card_Physical_Id = txn.cscPhysicalId
             AND p2.Card_LifeCycle_Count = txn.cscLifeCycleCount
             AND p2.Purse_Number = 0
             AND p2.Purse_LifeCycle_Count = 0
             AND p2.Purse_Issuer_Id = 0
             AND p2.State <> 12;
      
      dbms_Output.Put_Line('txn_rv :'
                           ||txn_rv
                           ||' '
                           ||Cur_Purse_rv);
    EXCEPTION
      WHEN No_Data_Found THEN
        cscId := txn.cscPhysicalId;
        
        clc := txn.cscLifeCycleCount;
        
        txn_rv := txn.txn_val + txn.Miss_val;
        
        Cur_Purse_rv := 0;
        
        dbms_Output.Put_Line('No Data txn_rv :'
                             ||txn_rv
                             ||' '
                             ||Cur_Purse_rv);
      WHEN OTHERS THEN
        dbms_Output.Put_Line('CSCID: '
                             ||txn.cscPhysicalId
                             ||', '
                             ||'CLC: '
                             ||txn.cscLifeCycleCount);
        
        RAISE;
    END;
    
    BEGIN
      SELECT p1.RemainIng_Value
      INTO   pRev_Purse_rv
      FROM   Purse_Account p1,
             eod_SettlementDate_Info eod
      WHERE  eod.NewsettleMentDate = vSettle_Date
             AND eod.cLosingSettlementDate = p1.Card_Settlement_Date
             AND p1.Card_Physical_Id = txn.cscPhysicalId
             AND p1.Card_LifeCycle_Count = txn.cscLifeCycleCount
             AND p1.Purse_Number = 0
             AND p1.Purse_LifeCycle_Count = 0
             AND p1.Purse_Issuer_Id = 0
             AND p1.State <> 12;
      
      dbms_Output.Put_Line('Debug 1:'
                           ||txn_rv
                           ||' '
                           ||Cur_Purse_rv
                           ||' '
                           ||pRev_Purse_rv);
      
      dbms_Output.Put_Line('prev_purse_rv:'
                           ||pRev_Purse_rv);
    EXCEPTION
      WHEN No_Data_Found THEN
        pRev_Purse_rv := 0;
        
        dbms_Output.Put_Line('prev_purse_rv No Data:'
                             ||pRev_Purse_rv);
      WHEN OTHERS THEN
        dbms_Output.Put_Line('CSCID: '
                             ||txn.cscPhysicalId
                             ||', '
                             ||'CLC: '
                             ||txn.cscLifeCycleCount);
        
        RAISE;
    END;
    
    dbms_Output.Put_Line('Debug 2:'
                         ||txn_rv
                         ||' '
                         ||Cur_Purse_rv
                         ||' '
                         ||pRev_Purse_rv);
    
    IF txn_rv <> Cur_Purse_rv - pRev_Purse_rv THEN
      INSERT INTO Purse_rv_mvmt
      VALUES     (vSettle_Date,
                  cscId,
                  clc,
                  txn_rv,
                  Cur_Purse_rv - pRev_Purse_rv);
      
      COMMIT;
    END IF;
  END LOOP;
  -- COMMIT;
  
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK; --Rollback all operations that were done.
    
    dbms_Output.Put_Line('CSCID: '
                         ||cscId
                         ||', '
                         ||'CLC: '
                         ||clc);
    
    RAISE;
END SumMarIse_rv_mvmt_Test; -- End of Procedure

[Updated on: Fri, 11 January 2008 08:28] by Moderator

Report message to a moderator

Re: Unknown Issue with Oracle procedure [message #293220 is a reply to message #293200] Fri, 11 January 2008 04:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Copy and paste the session you executed and not just describe it.

Regards
Michel
Re: Unknown Issue with Oracle procedure [message #299386 is a reply to message #293200] Mon, 11 February 2008 15:47 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
Ok,

Put some logging in to monitor what happens to cur_purse_rv.

ie. output the value at the start of the procedure, a few times in the middle, and then again at the end.

I suspect session state is the problem here, and youl probably fnd the cursor having zero when the procedure starts, but logging it will definateley check that.
Previous Topic: question for "where x in" SQL
Next Topic: converting char type to date type?
Goto Forum:
  


Current Time: Mon Dec 05 12:44:19 CST 2016

Total time taken to generate the page: 0.06672 seconds