• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1900
  • 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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