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!

nested cursor loop issue reg.

Discussion in 'SQL PL/SQL' started by laxman, Feb 23, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear all,
    Here is the purpose of procedure which should:
    • Identify all open requests (requests which are in Open state, not assigned to anyone and from same customers).
    • Identify all the requests from unique customers.
    • If there are request from the same person, Validate if the text is the same.
    • If the text is also the same close all but one request .

    i need ur attention basically in cursor section in the declaration part and logic applied in nested cursor loop section where based on each requestid in outer loop,inner cursor loop should execute.

    what i am worrying about the cursor declaration section is that i have assigned one cursor's value to another cursor,is it correct approach to define in such fashion if not how i can implement in another way.i need ur valuable suggestion kindly

    Thanks n regards
    Laxman

    Code (SQL):
    CREATE OR REPLACE PROCEDURE closeduplicates(IN_MINITEAMNAME varchar2)
    AS
    L_miniteamid miniteam.miniteamid%TYPE;
    L_custmailaddid request.customeremailaddressid%TYPE;
    L_originalrelated_reqid RelatedRequest.related_RequestID%TYPE;
    L_originalreqtext RequestRequestText.requestText%TYPE;
    L_duplicaterelated_reqid RelatedRequest.related_RequestID%TYPE;
    L_duplicaterelated_reqid RequestRequestText.requestText%TYPE;
       
       
    cursor OREQID_CUR IS SELECT MIN(requestid) originalreq_id,COUNT(customeremailaddressid)
                         FROM request
                         WHERE EXISTS( SELECT childminiteamid
                         FROM   miniteamtree
                        WHERE childminiteamid = L_miniteamid
                         AND parentMiniteamid IN (L_miniteamid))
                         AND requesttypecode = 1
                         AND statuscode = 1
                         AND assigned_personid IS NULL
                         AND customeremailaddressid NOT IN (1669595,6559354)
                         GROUP BY customeremailaddressid
                         HAVING (COUNT(customeremailaddressid) BETWEEN 2 AND 100)
                         ORDER BY originalreq_id;
                         
    cursor DREQID_CUR IS SELECT requestid duplicatereq_id
                         FROM request
                       WHERE customeremailaddressid = L_custmailaddid AND
                         requestid <> origdata.originalreq_id AND
                         requesttypecode = 1 AND
                         statuscode = 1 AND
                         assigned_personid IS NULL;
         
    BEGIN
         BEGIN
         
         ---find miniteamid from miniteam table
         SELECT miniteamid
         INTO L_miniteamid
         FROM miniteam
         WHERE miniteamname=IN_MINITEAMNAME; ---?????
         
          EXCEPTION
          WHEN TOO_MANY_ROWS THEN
          l_error:='Returning more that one value for Miniteam table!';
          raise app_error;
          WHEN NO_DATA_FOUND THEN
          l_error:='Data not found for miniteam table!';
          raise app_error;
         
        END;
         
      BEGIN  
         
      FOR origdata IN OREQID_CUR LOOP
               
               BEGIN
       
               SELECT customeremailaddressid
               INTO L_custmailaddid
               FROM request
               WHERE requestid = origdata.originalreq_id ; --????
               
               EXCEPTION
               WHEN TOO_MANY_ROWS THEN
               l_error:='Returning more than one value for request table!';
               raise app_error;
               WHEN NO_DATA_FOUND THEN
               l_error:='Data not found for request table!';
               raise app_error;
               
               END;
         
               BEGIN
               
               SELECT related_RequestID INTO L_originalrelated_reqid
               FROM RelatedRequest
               WHERE owning_Requestid = origdata.originalreq_id;
               
               EXCEPTION
               WHEN TOO_MANY_ROWS THEN
               l_error:='Returning more than one value for Relatedrequest table!';
               raise app_error;
               WHEN NO_DATA_FOUND THEN
               l_error:='Data not found for Relatedrequest table!';
               raise app_error;
               
               END;
               
               BEGIN
               
               SELECT requestText INTO L_originalreqtext
               FROM RequestRequestText
               WHERE requestID = L_originalrelated_reqid;
               
               EXCEPTION
               WHEN TOO_MANY_ROWS THEN
               l_error:='Returning more than one value for RequestRequestText table!';
               raise app_error;
               WHEN NO_DATA_FOUND THEN
               l_error:='Data not found for RequestRequestText table!';
               raise app_error;
               
               END;
               
        FOR dupdata IN DREQID_CUR LOOP
         
               BEGIN
                         
               SELECT related_RequestID INTO L_duplicaterelated_reqid
               FROM RelatedRequest
               WHERE owning_Requestid = dupdata.duplicatereq_id;
               
               EXCEPTION
               WHEN TOO_MANY_ROWS THEN
               l_error:='Returning more than one value in cursor DREQID_CUR for RelatedRequest table!';
               raise app_error;
               WHEN NO_DATA_FOUND THEN
               l_error:='Data not found in cursor DREQID_CUR for RelatedRequest table!';
               raise app_error;
               
               END;
               
               BEGIN
               
               SELECT requestText INTO L_duplicatereqtext
               FROM RequestRequestText
               WHERE requestID = L_duplicaterelated_reqid;
               
               EXCEPTION
               WHEN TOO_MANY_ROWS THEN
               l_error:='Returning more than one value in cursor DREQID_CUR for RequestRequestText table!';
               raise app_error;
               WHEN NO_DATA_FOUND THEN
               l_error:='Data not found in cursor DREQID_CUR for RequestRequestText table!';
               raise app_error;
               
               END;
               
               IF (LOWER(L_originalreqtext)=LOWER(L_duplicatereqtext)) THEN
               
                   UPDATE SREQUEST
                   SET statusCode = 4 //SET the request TO close STATUS
                   WHERE REQUESTID = duplicatereq_id ; -
                   
               END IF;
               
        END LOOP;
         
    END LOOP;
     
     EXCEPTION
        WHEN others THEN
        l_errormsg:='Error encountered while performing close duplicates action'|| sqlerrm;
        raise app_error;

     END;
       
    END;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your code won't compile as there are too many syntax errors present:

    *) Where is the exception app_error defined? Possibly you actually intended to
    use the supplied RAISE_APPLICATION_ERROR handler.
    *) This:
    Code (SQL):
    cursor DREQID_CUR IS SELECT requestid duplicatereq_id
    FROM request
    WHERE customeremailaddressid = L_custmailaddid AND
    requestid <> origdata.originalreq_id AND
    requesttypecode = 1 AND
    statuscode = 1 AND
    assigned_personid IS NULL;
    won't compile because origdata isn't open and hasn't been defined prior to
    the cursor definition
    *) Comments are not coded with //, they are coded with either /* */ or --
    *) Your 'when others then' section only confuses the issue by throwing away the
    error stack, obscuring the errors which caused the code to enter that handler.
    Please read here to learn how to write such exception handlers:
    http://oratips-ddf.blogspot.com/2008/03/what-was-that-masked-message.html
    *) This won't compile, either:
    Code (SQL):
    cursor OREQID_CUR IS SELECT MIN(requestid) originalreq_id,COUNT(customeremailaddressid)
    FROM request
    WHERE EXISTS( SELECT childminiteamid
    FROM miniteamtree
    WHERE childminiteamid = L_miniteamid
    AND parentMiniteamid IN (L_miniteamid))
    AND requesttypecode = 1
    AND statuscode = 1
    AND assigned_personid IS NULL
    AND customeremailaddressid NOT IN (1669595,6559354)
    GROUP BY customeremailaddressid
    HAVING (COUNT(customeremailaddressid) BETWEEN 2 AND 100)
    ORDER BY originalreq_id;
    again because L_miniteamid has yet to be declared

    You should do some reading and learn that cursors can accept input parameters and code them to do so in such situations as this, then you can pass the outer cursor value to the inner cursor without error. Also, procedures accept IN, OUT and IN OUT parameters but the parameter must be declared as one of those options. Thus your first line should be:

    Code (SQL):
    CREATE OR REPLACE PROCEDURE closeduplicates(IN_MINITEAMNAME IN varchar2)
    Fix these errors, get the procedure to compile then return to discuss the 'logic' of your code.
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    ok sir let me verify and compile it successfully

    Thanks n regards
    Laxman