asked on

Using Multiple Cursors in a Loop

Need some help in fixing the below procedure. I am trying to pass the values from the first cursor vNotificationParms to pNotificationListCursor only if the condition
IF( vEnabled = vTrue AND vDirection = vDirectionInbound ) is met

Is it a good idea to declare the second cursor at the beginning?

Thanks,

***********************************************************************************************
create or replace
PROCEDURE NOTIF_GETTXFAILURES (
pNotificationId IN NUMBER,
pNotificationListCursor OUT TrackingTypes.GenCurType
)
AS
vCurrentTimestamp TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
vBusinessId NUMBER(19,0);
vTimeIntervalUnits INTERVAL DAY TO SECOND;
vTimeIntervalUnitOfMeasure VARCHAR(10) := 'MINUTE';
vTimeIntervalUnitsDefault INTERVAL DAY TO SECOND := NUMTODSINTERVAL('60', vTimeIntervalUnitOfMeasure);
vDocumentList VARCHAR2(250); -- NOT USED
vEmailSubject VARCHAR2(250);
vEmailSender VARCHAR2(250);
vCategory VARCHAR2(100); -- NOT USED
vDirection NUMBER(1);
vDirectionInbound NUMBER(1) := 1;
vDirectionOutbound NUMBER(1) := 0;
vEnabled NUMBER(1);
vTrue NUMBER(1) := 1;
vFalse NUMBER(1) := 0;
vMessageId NUMBER(19,0);
vTxReportFile VARCHAR2(25) := 'translationreport.dat';

-- Status Codes
vRed NUMBER(1) := 2;
vRedReviewed NUMBER(1) := 5;

CURSOR vNotificationParms IS
SELECT BUSINESSID, NUMTODSINTERVAL(TIMEUNITS, TIMEUNITOFMEASURE), EMAILSUBJECT, EMAILSENDER, DOCTYPES, CATEGORY, ENABLED, DIRECTION, MESSAGEID
FROM BUSINESSNOTIFICATION
WHERE NOTIFICATIONID = pNotificationId;

OPEN vNotificationParms;
LOOP
                  EXIT WHEN vNotificationParms%NOTFOUND;
                  FETCH vNotificationParms INTO vBusinessId, vTimeIntervalUnits, vEmailSubject, vEmailSender, vDocumentList, vCategory, vEnabled, vDirection, vMessageId;

IF( vEnabled = vTrue AND vDirection = vDirectionInbound ) THEN
OPEN pNotificationListCursor FOR
SELECT DISTINCT
bid.BUSINESSID, vEmailSender SENDER, vEmailSubject EMAIL_SUBJECT, (SELECT DETAILMESSAGE FROM MESSAGE WHERE MESSAGEID = vMessageId) DETAILMESSAGE, f.FILEDATA
FROM BUSINESSES b
JOIN DOCUMENTS d ON d.BUSINESSID = b.BUSINESSID
JOIN EVENTS e ON e.EVENTID = d.EVENTID
JOIN FILES f ON f.FILEID = e.FILEID
JOIN BUSINESSIDENTIFIER bid ON bid.BUSINESSIDENTIFIER = d.SENDERID
WHERE ( e.STATUSID = vRed or e.STATUSID = vRedReviewed )
AND b.businessid = vBusinessId
AND lower(e.ARCHIVEPATH) = vTxReportFile
AND ( e.EVENTDATETIME > (vCurrentTimestamp - vTimeIntervalUnits)
AND (e.EVENTDATETIME < vCurrentTimestamp ));
LOOP
                   EXIT WHEN pNotificationListCursor%NOTFOUND;
FETCH pNotificationListCursor INTO

END LOOP;
       CLOSE vNotificationParms;

END IF;
END;

ASKER CERTIFIED SOLUTION

konektor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

jwahl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

jwahl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cutie_smily

ASKER

Thanks for all the time. How do I output the values for bid.businessid,vemailsender sender,vemailsubject email_subject from the second cursor. I just want to add some dbms ouput statements and see the values.

jwahl

simply add the DBMS_OUTPUT.PUT_LINE() wherever you want inside your loop.
note: the output is shown after the whole procedure has finished!

cutie_smily

ASKER

I opted to use the parameter cursors as mentioned. I am getting the below errors
Error(2,1): PLS-00410: duplicate fields in RECORD,TABLE or argument list are not permitted.
Please do let me know if you something is wrong.

/***************************************************************************************************************/
create or replace
PROCEDURE NOTIF_GETBYNONRECVDDOCS (
pNotificationId IN NUMBER,
pNotificationListCursor OUT TrackingTypes.GenCurType
) AS
vCurrentTimestamp TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
--- vBusinessId NUMBER(19,0);
-- vTimeIntervalUnits INTERVAL DAY TO SECOND;
vTimeIntervalUnitOfMeasure VARCHAR(10) := 'MINUTE';
vTimeIntervalUnitsDefault INTERVAL DAY TO SECOND := NUMTODSINTERVAL('60', vTimeIntervalUnitOfMeasure);
--- vDocumentList VARCHAR2(250);
--- vEmailSubject VARCHAR2(250);
--- vEmailSender VARCHAR2(250);
---- vCategory VARCHAR2(100);
--- vDirection NUMBER(1);
--- vDirectionInbound NUMBER(1) := 1;
-- vDirectionOutbound NUMBER(1) := 0;
-- vEnabled NUMBER(1);
vTrue NUMBER(1) := 1;
vFalse NUMBER(1) := 0;
vMessageId NUMBER(19,0);
vRelationshipType VARCHAR(25) := 'GENERIC';

-- Staus Codes
vStatusGreen NUMBER(1) := 0;
vStatusGreenReviewed NUMBER(1) := 4;

-- Tracking Points
vTranslated2 NUMBER(1) := 2;
vTranslated5 NUMBER(1) := 5;
vProcessed7 NUMBER(1) := 7;
vSent3 NUMBER(1) := 3;
vSent6 NUMBER(1) := 6;

/****************************************************************************************************************************/
CURSOR vNotificationParms IS
SELECT BUSINESSID, NUMTODSINTERVAL(TIMEUNITS, TIMEUNITOFMEASURE)TIME_INTERVAL, EMAILSUBJECT, EMAILSENDER, DOCTYPES, CATEGORY, ENABLED, DIRECTION,MESSAGEID
FROM BUSINESSNOTIFICATION
WHERE NOTIFICATIONID = pNotificationId;

/****************************************************************************************************************************/
CURSOR pNotificationListCursor (vBusinessId in NUMBER, vTimeIntervalUnits in TIMESTAMP, vEmailSubject in VARCHAR2, vEmailSender in VARCHAR2,vDocumentList in VARCHAR2, vCategory in VARCHAR2, vEnabled in NUMBER, vDirection in NUMBER, vMessageId in NUMBER);
IS
SELECT b.BUSINESSID, vEmailSender EMAILSENDER, vEmailSubject EMAILSUBJECT, (SELECT DETAILMESSAGE FROM MESSAGE WHERE MESSAGEID = vMessageId) DETAILMESSAGE
FROM BUSINESSES b
JOIN RELATIONSHIPS r ON (
r.INTERNALPARTNERID = vBusinessId AND
r.EXTERNALPARTNERID = b.BUSINESSID
AND r.RELATIONSHIPTYPE = vRelationshipType
)
JOIN BUSINESSCATEGORY c ON (
      c.BUSINESSID = b.BUSINESSID AND
      c.CATEGORYTYPES = vCategory
)
WHERE r.EXTERNALPARTNERID NOT IN (
       SELECT DISTINCT
              bid.BUSINESSID
       FROM DOCUMENTS d
       JOIN DOCUMENTTYPES dt ON dt.DOCUMENTTYPEID = d.DOCUMENTTYPEID
       JOIN BUSINESSIDENTIFIER bid ON bid.BUSINESSIDENTIFIER = d.SENDERID
       JOIN BUSINESSCATEGORY bc ON (
            bc.BUSINESSID = bid.BUSINESSID AND
            bc.CATEGORYTYPES = vCategory )
       JOIN RELATIONSHIPS r ON (
            r.INTERNALPARTNERID = vBusinessId AND
r.EXTERNALPARTNERID = bid.BUSINESSID AND
r.RELATIONSHIPTYPE = vRelationshipType )
       JOIN EVENTS e ON (
            e.EVENTID = d.EVENTID
            AND e.TRACKINGPOINTID IN (vSent3, vSent6)
            AND e.STATUSID IN (vStatusGreen, vStatusGreenReviewed))
WHERE d.BUSINESSID = vBusinessId
       AND dt.DOCUMENTTYPENAME IN ( vDocumentList )
       AND ( e.EVENTDATETIME > (vCurrentTimestamp - vTimeIntervalUnits)
       AND (e.EVENTDATETIME < vCurrentTimestamp )));

/***************************************************************************************************************/

BEGIN
FOR v_parms IN vnotificationparms
LOOP IF (v_parms.venabled = vtrue AND
v_parms.vdirection = vdirectioninbound) THEN
FOR v_list_cur in pNotificationListCursor (v_parms.BUSINESSID, v_parms.TIME_INTERVAL,v_parms.EMAILSUBJECT,v_parms.EMAILSENDER,v_parms.DOCTYPES,v_parms.CATEGORY,v_parms.ENABLED,v_parms.DIRECTION,v_parms.MESSAGEID)
       LOOP
            DBMS_OUTPUT.put_line ('BUSINESSID: ' || v_list_cur.BUSINESSID);
DBMS_OUTPUT.put_line ('EMAILSENDER: ' || v_list_cur.EMAILSENDER);
DBMS_OUTPUT.put_line ('EMAILSUBJECT: ' || v_list_cur.EMAILSUBJECT);

END LOOP;
END IF;
END LOOP;
END;
/

SOLUTION

jwahl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cutie_smily

ASKER

Jwahl, Sorry not sure how the accepted works here but most points were alloted to you.

Thanks konektor.