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!

Oracle SQL Nested Cursor Issue

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

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear Sir,
    since this code is in development stage,i am trying to analyze and implementing the logic as per the requirement then i will implement the exception part also based on this logic .So i have your attention once again to the cursor section part in this code,actually i have modified this code and compiled it successfully without exception section in this code which i should include.but i have concern on the cursor loop issue.
    if i can able to implement with a different approach,sir ur suggestion would be highly appreciable.
    Thanks n regards
    Laxman






    Code (SQL):
    CREATE OR REPLACE PROCEDURE closeduplicates(IN_miniteamid IN 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_duplicatereqtext RequestRequestText.requestText%TYPE;
       
       
    cursor OREQID_CUR IS SELECT MIN(requestid) originalreq_id,COUNT(customeremailaddressid)
                         FROM request
                         WHERE EXISTS( SELECT childminiteamid
                         FROM   miniteamtree
                         WHERE childminiteamid = IN_miniteamid
                         AND parentMiniteamid IN (IN_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 MIN(requestid);
                         
                         
         
    BEGIN
         
     
      FOR origdata IN OREQID_CUR LOOP
               
                   
               SELECT customeremailaddressid
               INTO L_custmailaddid
               FROM request
               WHERE requestid = origdata.originalreq_id ;
                                     
               SELECT related_RequestID INTO L_originalrelated_reqid
               FROM RelatedRequest
               WHERE owning_Requestid = origdata.originalreq_id;
                 
               SELECT requestText INTO L_originalreqtext
               FROM RequestRequestText
               WHERE requestID = L_originalrelated_reqid;
               
              ---NESTED CURSOR LOOP
             
    FOR dupdata IN (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)   ----MODIFIED SECTION
               LOOP
         
                         
               SELECT related_RequestID INTO L_duplicaterelated_reqid
               FROM RelatedRequest
               WHERE owning_Requestid = dupdata.duplicatereq_id;
               
                   
               SELECT requestText INTO L_duplicatereqtext
               FROM RequestRequestText
               WHERE requestID = L_duplicaterelated_reqid;
               
                     
               IF (LOWER(L_originalreqtext)=LOWER(L_duplicatereqtext)) THEN
               
                   UPDATE SREQUEST
                   SET statusCode = 4
                   WHERE REQUESTID = dupdata.duplicatereq_id ;
                   
               END IF;
               
        END LOOP;
         
    END LOOP;
     
    END;
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Re: nested cursor issue(kind attn Zargon sir )

    Dear Laxman, I have been reminding you time and again to use the SQL Highlight Tags, I would be forced to take your posting rights for a few days if you continue to ignore them.
     
  3. salmankhalid

    salmankhalid Forum Advisor

    Messages:
    116
    Likes Received:
    6
    Trophy Points:
    260
    Location:
    Lahore, Pakistan
    Dear Laxman since you are Adviser to this forum, that means that you are our valuable user and knows much more than the others users that are guest or our registered users so pls try to full fill all the posting aspects and pls don't mind that just because together we all are in a single family to this forum that is knows as Club_Oracle

    and for Sadik pls aap ke profile picture smiling hai aur aap ka gusa itna.... ;)

    what i think that he forgot just because he is in problem with the above mentioned code so we all have to help him in this matter i am sure that we don't need to remind him again about this thing......
     
  4. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Haha Salman... :) Nopes I am not gussa... aise hi kabhi daatna padta hai... ;)

    I was just pointing out because, it's understandable for new users, but for someone who is using the forums for so long, should use the SQL button.