1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

cursor with exception issue reg

Discussion in 'SQL PL/SQL' started by laxman, Mar 4, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear all,
    I have created one procedure and it is working perfectly ,but i want two point to be included in this code

    point 1: i want to trap any error through Exception which i need to include in this code
    so in which section i need to include the exception section and what type of useful exception i should include.

    point 2: i want status of no of row being updated while processing

    i need ur suggestion kindly

    regards
    Laxman
    Code (SQL):
    CREATE OR REPLACE PROCEDURE freereq24hr
    AS
    BEGIN
    FOR mtid_cur_rec IN (SELECT a.miniteamid FROM auto_miniteam a,miniteam m
    WHERE job_type=5 AND
    a.miniteamid=m.miniteamid) LOOP
    FOR age_cur_rec IN (SELECT requestID
    FROM Request
    WHERE   miniteamid IN   (
    SELECT childMiniTeamID
    FROM MiniTeamTree
    WHERE childMiniteamID = mtid_cur_rec.miniteamid AND
    parentMiniteamID IN(mtid_cur_rec.miniteamid)
    ) AND
    requestTypeCode = 1 AND
    statusCode IN (2,3) AND
    assigned_PersonID IS NULL AND
    lastmoddate < sysdate - 1) LOOP
    IntNotesHistoryInsert(age_cur_rec.requestid,'FREE REQUEST 24 HR');
    UPDATE Request
    SET statusCode = 1, assigned_PersonID = NULL
    WHERE requestID = age_cur_rec.requestid;
    commit;
    END LOOP;
    END LOOP;

    END;
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Not sure about the requirement.

    Point 1: Anywhere you want to trap the exception and any exception those are most likely to come across.

    In you case preferrably between First and Second Loop. (and commit should be after Inner Loop but before Main Loop)

    Point 2. %rowcount.
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear Sir this is the modified code of the previous one ,i just need ur attention in Exception part only which i have defined in two section one for inner loop and another for outer loop.my doubt!! whether this approach will create any problem? and whether we need nested begin end section in this code in order to allow Exception to trap any error effectively.
    need ur suggestion kindly

    Thanks n regards
    Laxman

    Code (SQL):
    CREATE OR REPLACE PACKAGE BODY freereq24
    AS
    app_error EXCEPTION;
    PRAGMA EXCEPTION_INIT(app_error,-20001);
    l_error varchar2(600);

    PROCEDURE freereq24hr(IN_FilePath   varchar2,
                          IN_FileName   varchar2,
                          IN_GenReqID   BOOLEAN )
    AS
        l_fderr  utl_file.file_type;
        l_filename varchar2(300);
        l_errormsg varchar2(600);
        l_predata varchar2(999);
        l_effectreqcount NUMBER(5);
        l_postdata varchar2(999);
       
    BEGIN
        l_filename:='freereq24hr.err';
        l_fderr:=utl_file.fopen(IN_FilePath,l_filename,'w');
        l_effectreqcount:= 0;
        l_postdata:='';
               BEGIN
             
                     FOR mtid_cur_rec IN (SELECT a.miniteamid FROM auto_miniteam a,miniteam m
                                         WHERE job_type=5 AND
                                         a.miniteamid=m.miniteamid) LOOP
                                BEGIN    
                                  FOR age_cur_rec IN (SELECT requestID
                                                    FROM Request
                                                    WHERE   miniteamid IN   (  
                                                    SELECT  childMiniTeamID
                                                    FROM MiniTeamTree
                                                    WHERE   childMiniteamID = mtid_cur_rec.miniteamid AND
                                                    parentMiniteamID IN(mtid_cur_rec.miniteamid)
                                                    ) AND
                                                    requestTypeCode = 1 AND
                                                    statusCode IN (2,3) AND
                                                    assigned_PersonID IS  NULL AND
                                                    lastmoddate < sysdate - 1) LOOP
                                                        BEGIN
                                                           
                                                             IntNotesHistoryInsert(age_cur_rec.requestid,'FREE REQUEST 24 HR');
                                                             
                                                             UPDATE Request
                                                             SET statusCode = 1, assigned_PersonID = NULL
                                                             WHERE requestID = age_cur_rec.requestid;
                                                           
                                                             COMMIT;
                                                             
                                                             l_effectreqcount := l_effectreqcount + 1 ;
                                                             
                                                             
                                                             IF(IN_GenReqID) THEN
                                                                 IF(l_effectreqcount = 1) THEN
                                                                    l_postdata:= l_postdata || age_cur_rec.requestid ;
                                                                 ELSE
                                                                    l_postdata:= l_postdata||','|| age_cur_rec.requestid ;
                                                                 END IF;
                                                             END IF;
                                                               
                                                             Exception
                                                             WHEN others THEN
                                                             l_errormsg := 'Error occur while processing request id ='||age_cur_rec.requestid||' INNER loop errMsg='|| sqlerrm;
                                                             Raise App_Error;
                                                       END;  
                                                 
                                    END LOOP;
                                                             
                                           
                                Exception
                                                 WHEN others THEN
                                                 l_errormsg := 'Error occur while processing miniteamid = '||mtid_cur_rec.miniteamid|| ' OUTER loop errMsg='|| sqlerrm;
                                                 Raise App_Error;  
                                                 
                                END;
                             
                        END LOOP;
                         
                             
                         /* REPORT GENERATION SECTION */
                    GenReport24HR(IN_FilePath,IN_FileName,0,l_effectreqcount,l_postdata);
             
                END;    
            EXCEPTION
            WHEN app_error THEN
            utl_file.put_line(l_fderr,l_errormsg);
            utl_file.new_line(l_fderr);
            utl_file.put_line(l_fderr,l_error);
            utl_file.fflush(l_fderr);
            utl_file.fclose(l_fderr);

            /* REPORT GENERATION SECTION */
            GenReport24HR(IN_FilePath, IN_FileName,1,l_effectreqcount,l_postdata);
       
       
    END freereq24hr;
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The structure of your PL/SQL is fine, however I wonder why you feel the need to attempt reinventing the wheel with your 'app_error' exception; you really should be using raise_application_error as it can return the error stack where your limited exception cannot. See here:

    http://oratips-ddf.blogspot.com/2008/03/what-was-that-masked-message.html

    I will note that you have declared app_error in lower case; you cannot successfully call it using upper or mixed case as you've done in the posted code. Obviously you have not yet tried to create this procedure as you'd receive errors since App_Error is undeclared.
     
  5. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    fine sir,let me modify this code ,hopefully it should solve the purpose.

    Thanks a lot

    Regards
    Laxman
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Currently the code looks as follows .

    Code (SQL):
    CREATE OR REPLACE PROCEDURE xxxxx
    AS
    BEGIN                                                      ------ >1
             FOR x IN ()
             LOOP                                                                  
                       BEGIN                                   ------ >2

                                        FOR y IN ()
                                        LOOP
                                                        BEGIN  ------ >3

                                                               Commit ;

                                                        EXCEPTION
                                                             WHEN others THEN
                                                              l_errormsg := 'Errm 01';
                                                              Raise App_Error;
                                                        END;

                                        END LOOP;             ---------------> for y loop

                        EXCEPTION
                           WHEN others THEN
                            l_errormsg := "Errm 02";
                            Raise App_Error;
                       END;

              END LOOP;                                      ---------------> for x loop

    EXCEPTION
    WHEN app_error THEN
            utl_file.put_line(l_fderr,l_errormsg);
            utl_file.new_line(l_fderr);
            utl_file.put_line(l_fderr,l_error);
            utl_file.fflush(l_fderr);
            utl_file.fclose(l_fderr);
    END;

    Some malpractices noticed.

    1. Commit is done inide 2nd Begin...End; . it could have placed After 'Y End Loop' and before 'Exception of 2nd BEgin...End;'.
    2. Exception raised in the 3rd Begin...End (Raise App_Error) will be captured by the 2nd Begin...End exception section before capturing it again in Main Begin...End. By the time error message will be changed in the exception section in the 2nd Begin...End section.

    Solution is to
    1. Remove Any one of exception section (2nd of 3rd Begin... End)
    2. provide appropriate exception instead of WHEN OTHERS Exception.
     
  7. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Thanks a lot sir for ur kind cooperation , let me modify it with the way u explained.

    Regards
    Laxman