Re: Fetch out of sequence error

PROCEDURE GetRegStatus
    (iNewPersonID     IN INTEGER,
     c_PageStatusCur  IN OUT RegGetPageStatus_RefCurType,
     iStatus          OUT INTEGER) AS

- declare local variables
CURSOR Get_GenderBirthdayIncome_Cur(PID INTEGER) IS
SELECT Gender,DateOfBirth,YearOfBirth,MonthOfBirth,IncomeRange
  FROM PersonWhitePages
 WHERE PersonID = PID;

CURSOR Get_Preferences_Cur(PID INTEGER) IS
SELECT SponsorID,
       WhitePagesYes,
       PermissionViewFirstDegree,
       SpecialOffersPref
  FROM Person
 WHERE PersonID = PID;

CURSOR Check_Occupation_Cur(PID INTEGER) IS
SELECT ROWID
  FROM PersonOccupation
 WHERE PersonID = PID;

iError INTEGER := 0;
vErrMsg VARCHAR2(255);
iTotalRelsAdded INTEGER;
vGen VARCHAR2(1);
iGenOK INTEGER := 0;
iDateOfBirth INTEGER;
iMonthOfBirth INTEGER;
iYearOfBirth INTEGER;
iInc INTEGER;
iIncOK INTEGER := 0;
iWhitePagesYes INTEGER;
iWhitePYesOK INTEGER := 0;
iPermissionViewFirstDegree INTEGER;
iSpecialOffersPref INTEGER;
iSponID INTEGER;
iGeogOK INTEGER := 0;
iPermViewFirstDegreeOK INTEGER := 0;
iSpecOffersPrefOK INTEGER := 0;
iOccuOK INTEGER := 0;
iRelStatus INTEGER;
iRolStatus INTEGER;
nContactsAdded INTEGER;
vCountry VARCHAR2(50);
iRowID uRowID;
iRegionG_ID INTEGER;
iBirthOK INTEGER := 0;
iRelTypeID INTEGER;
iRelTypeName VARCHAR2(50);
iHasResolvSponsorRelOK INTEGER := 1;
iTotRelsAdded INTEGER;
iRetStatus INTEGER;
iSponsorID INTEGER := 0;

BEGIN
iStatus := 0;--If successful then iStatus = 0.

-- a. FirstName, LastName
-- nothing to do: if other values are present, name must have been submitted from form

-- b. Password
-- nothing to do. no way to verify whether password is assigned or chosen OPEN Get_GenderBirthdayIncome_Cur(iNewPersonID);

-- c. Gender, Birthday, Income

OPEN Get_GenderBirthdayIncome_Cur(iNewPersonID);
FETCH Get_GenderBirthdayIncome_Cur
 INTO vGen,
      iDateOfBirth,
      iYearOfBirth,
      iMonthOfBirth,
      iInc;

IF Get_GenderBirthdayIncome_Cur%NOTFOUND THEN
  CLOSE Get_GenderBirthdayIncome_Cur;
ELSE
  IF vGen IS NOT NULL THEN
    iGenOK := 1;
  END IF;

  IF iDateOfBirth IS NOT NULL
   OR iMonthOfBirth IS NOT NULL
   OR iYearOfBirth IS NOT NULL THEN
     iBirthOK := 1;
  END IF;

  IF iInc IS NOT NULL THEN
    iIncOK := 1;
  END IF;

  CLOSE Get_GenderBirthdayIncome_Cur;
END IF;

-- d.PermissionViewFirstDegree, WhitePagesPreference, SPecialOffers Preference

OPEN Get_Preferences_Cur(iNewPersonID);
FETCH Get_Preferences_Cur
 INTO iSponID,
      iWhitePagesYes,
      iPermissionViewFirstDegree,
      iSpecialOffersPref;

IF Get_Preferences_Cur%NOTFOUND THEN
  CLOSE Get_Preferences_Cur;
ELSE
  IF iWhitePagesYes IS NOT NULL THEN
    iWhitePYesOK := 1;
  END IF;

  IF iPermissionViewFirstDegree IS NOT NULL THEN
    iPermViewFirstDegreeOK := 1;
  END IF;

  IF iSpecialOffersPref IS NOT NULL THEN
    iSpecOffersPrefOK := 1;
  END IF;

--e. HasResolvedSponsorRelationShipStatus
--THIS PROCEDURE HAS GOT ANOTHER CURSOR THAT JUST SELECT THE INFO INTO THESE VARS
SDRelationship.GetInfo(iNewPersonID, iSponID, iRelStatus, iRolStatus, iRelTypeID, iRelTypeName, iRetStatus);

  iSponsorID := NVL(iSponID, 0);
  IF (iRelStatus <> SDRelationship.RelConfirmed AND iRetStatus = 0) OR iRetStatus <> 0 THEN
    iHasResolvSponsorRelOK := 0;
  END IF;

  CLOSE Get_Preferences_Cur;
END IF;

-- f. Geography
--THIS PROCEDURE HAS GOT ANOTHER CURSOR THAT JUST SELECT THE INFO INTO THESE VARS
SDPerson.PersonGetGeoStat(iNewPersonID,vCountry,iRegionG_ID,iRetStatus);

IF iRetStatus = 0 THEN
  iGeogOK := 1;
END IF;

-- g. No of ContactsAdded
--THIS PROCEDURE HAS GOT ANOTHER CURSOR THAT JUST SELECT THE INFO INTO THESE VARS
SDRelationship.PersonGetContactsAdded(iNewPersonID, nContactsAdded, iRetStatus);

IF iRetStatus = 0 THEN
  iTotRelsAdded := nContactsAdded;
ELSE
  iTotRelsAdded := 0;
END IF;

--h. Occupation
OPEN Check_Occupation_Cur (iNewPersonID);
FETCH Check_Occupation_Cur INTO iRowID;

IF Check_Occupation_Cur%FOUND THEN
  iOccuOK:= 1;
END IF;

CLOSE Check_Occupation_Cur;

--RefCursor to select all the description names and the status values for each field.

OPEN c_PageStatusCur FOR
SELECT 'FirstName', 1,
       'LastName', 1,
       'Password', 1,
       'Gender', iGenOK,
       'Birthday', iBirthOK,
       'Income', iIncOK,
       'Geography', iGeogOK,
       'Occupation', iOccuOK,
       'SpecialOffers', iSpecOffersPrefOK,
       'MemDirPreference', iPermviewFirstDegreeOK,
       'PeopleSurfingPreference', iWhitePYesOK,
       'HasResolvedSponsorRelationShip', iHasResolvSponsorRelOK,
       'TotalRelationsAdded', iTotRelsAdded,
       'SponsorID', iSponsorID
  FROM DUAL;

--Exception handling starts here

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    iStatus := -1;

END GetRegstatus;