• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1894
  • Last Modified:

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;
0
cutie_smily
Asked:
cutie_smily
  • 4
  • 3
4 Solutions
 
konektorCommented:
i miss "begin" between declaration and opening cursor vNotificationParms

it doesn't matter whether you declare cursor in declaration part of pl/sql block, or inside it. using code below you do not need declare variables for fetch - the row corresponding to returned resultset is declared automaticaly

declare
  cursor c_a is select ...
  cursor c_b (p_x in number) is select ... from ... where x=p_x ...
begin
  for r_a in c_a loop
    for r_b in c_b (r_a.x) loop
      -- code...
    end loop;
  end loop;
end;
/
0
 
jwahlCommented:
there are lots of syntax errors in you code ... the simplest was will be a structure like this:

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;

    CURSOR pnotificationlistcursor IS
        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));
BEGIN
    FOR vnotificationparms_rec IN vnotificationparms
    LOOP
        IF (venabled = vtrue AND
            vdirection = vdirectioninbound) THEN
            FOR pnotificationlist_rec IN pnotificationlistcursor
            LOOP
                --
                -- your code here ...
                --
            END LOOP;
        END IF;
    END LOOP;
END;

but your code isn't complete, is it?
vEnabled and vDirectionInbound are constants ...
0
 
jwahlCommented:
sorry, didn't notice that they come from the first cursor:

BEGIN
    FOR vnotificationparms_rec IN vnotificationparms
    LOOP
        IF (vnotificationparms_rec.venabled = vtrue AND
            vnotificationparms_rec.vdirection = vdirectioninbound) THEN
            --
            FOR pnotificationlist_rec IN pnotificationlistcursor
            LOOP
                ...
                ...

0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
cutie_smilyAuthor Commented:
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.
0
 
jwahlCommented:
simply add the DBMS_OUTPUT.PUT_LINE() wherever you want inside your loop.
note: the output is shown after the whole procedure has finished!

0
 
cutie_smilyAuthor Commented:
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;
/
0
 
jwahlCommented:
remove the ;:

    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); <-- remove ;
     IS ...

what type is trackingtypes.gencurtype in your OUT parameter? i think you can't put a cursor as OUT parameter. you have to use record types or ref_cursors instead.


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

Thanks konektor.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now