Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

PL/SQL Package and using cursors

Good Morning,

I am using a package in Oracle 11i (still new to this so patience is required).  This is Declared in Package :
-- NoticeOfficerDetails
TYPE rec_NoticeOfficerDetails IS RECORD
(PhoneNumberText VARCHAR2(30) NOT NULL := ' ',
EmailText VARCHAR2(255) NOT NULL := ' ');

TYPE cur_NoticeOfficerDetails IS REF CURSOR RETURN rec_NoticeOfficerDetails;

In the package body, this is its own sp and I need to populate the cursor.  I have started with this:
PROCEDURE sp_GetNoticeOfficerDetails
(p_officerId in integer) IS

BEGIN

 CURSOR cur_NoticeOfficerDetails RETURN rec_NoticeOfficerDetails IS
  SELECT WST_AUTH_PHONE_NUMBER, WST_AUTH_E_MAIL_ADDRESS
  FROM WASTE_AUTHORITY
  WHERE WST_AUTH_IDENTIFIER = p_officerId;

BEGIN
  OPEN cur_NoticeOfficerDetails;
    LOOP
      FETCH cur_NoticeOfficerDetails into rec_NoticeofficerDetails;
    EXIT WHEN cur_NoticeOfficerDetails%NOTFOUND;
  CLOSE cur_NoticeOfficerDetails;

END sp_GetNoticeOfficerDetails;

What am I doing wrong - I think I have confused myself now and just need someone to stop me from being stupid :)
Thanks for your help..
0
Mosquitoe
Asked:
Mosquitoe
  • 5
  • 3
1 Solution
 
sdstuberCommented:
try something like this...


    PROCEDURE sp_getnoticeofficerdetails(p_officerid IN INTEGER)
    IS
        CURSOR cur_noticeofficerdetails
        IS
            SELECT wst_auth_phone_number, wst_auth_e_mail_address
              FROM waste_authority
             WHERE wst_auth_identifier = p_officerid;

        v_rec   rec_noticeofficerdetails;
    BEGIN
        OPEN cur_noticeofficerdetails;

        LOOP
            FETCH cur_noticeofficerdetails INTO rec_noticeofficerdetails;

            EXIT WHEN cur_noticeofficerdetails%NOTFOUND;

            CLOSE cur_noticeofficerdetails;
        END LOOP;
    END sp_getnoticeofficerdetails;
0
 
sdstuberCommented:
alternatively  you might want to use a cursor for loop


    PROCEDURE sp_getnoticeofficerdetails(p_officerid IN INTEGER)
    IS
    BEGIN
        FOR r IN (SELECT wst_auth_phone_number, wst_auth_e_mail_address
                    FROM waste_authority
                   WHERE wst_auth_identifier = p_officerid)
        LOOP
            DBMS_OUTPUT.put_line(r.wst_auth_phone_number || ' ' || r.wst_auth_e_mail_address);
        END LOOP;
    END sp_getnoticeofficerdetails;
0
 
MosquitoeAuthor Commented:

Hi,
In your first reponse, wouldn't this line:
FETCH cur_noticeofficerdetails INTO rec_noticeofficerdetails;
Be:
FETCH cur_noticeofficerdetails INTO v_rec;
My second question is, this is a sub routine within the package - if I populate this cursor here, the calling routine returns the cursor:

PROCEDURE sp_Notice (p_noticeId in INTEGER,
    p_noticeOfficerUserId in VARCHAR2,
            ExporterRegulateeInfo out cur_RegulateeInfo,
            ExporterRegulateeAddress out cur_RegulateeAddress,
            ExporterRegulateeContact out cur_RegulateeContact,
            ImporterRegulateeInfo out cur_RegulateeInfo,
            ImporterRegulateeAddress out cur_RegulateeAddress,
            ImporterRegulateeContact out cur_RegulateeOptionalContact,
            TransporterRegulateeInfo out cur_RegulateeInfo,
            TransporterRegulateeAddress out cur_RegulateeOptionalAddress,
            TransporterRegulateeContact out cur_RegulateeOptionalContact,
            WasteGeneratorRegulateeInfo out cur_RegulateeInfo,
            WasteGeneratorRegulateeAddress out cur_RegulateeAddress,
            WasteGeneratorRegulateeContact out cur_RegulateeContact,
            FacilityRegulateeInfo out cur_RegulateeInfo,
            FacilityRegulateeAddress out cur_SpecifiedRegulateeAddress,
            FacilityRegulateeContact out cur_RegulateeOptionalContact,
            FacilityDetails out cur_FacilityDetails,
            NoticeDetails out cur_NoticeDetails,
            WasteStreamDetails out cur_WasteStreamDetails,
            TransitCountry out cur_TransitCountry,
            NoticeSubmitter out cur_NoticeSubmitter,
            NoticeOfficerDetails out cur_NoticeOfficerDetails);
Does this cursor continue to hold the values populated within it, for the session created when the package was called?  I understand from my research that if the cursor and rec type is declared in the package declaration area, then throughout one call of that package, you can move through several different sp's knowing that any cursors you have populated in the first call, still remains populated...Is this correct?  I hope I was clear enough..


0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sdstuberCommented:
ooops,  yes  

that's the whole reason I introduced the variable  :)


I'm not sure how to answer your question about "populating" a cursor.

A cursor is never "populated"  it's merely a pointer.  I guess with an internal pre-fetch  you might consider that populating the cursor, but you don't have access to those values until you do a fetch.

the cursor shouldn't change though unless you close it or otherwise manipulate that variable.  So in that sense, yes
you can be safe in passing a cursor variable from procedure to procedure.
0
 
MosquitoeAuthor Commented:
OK...so if the sp_Notice calls the sp_getnoticeofficerdetails, then sp_getnoticeofficerdetails has to return the cursor to sp_notice for sp_notice to be able to pass the cursor back out to the calling app?
0
 
sdstuberCommented:
yes that will be allowed,  however,  if you fetch from a cursor anywhere along the way you'll be impacting what the calling app is able to fetch,  
0
 
MosquitoeAuthor Commented:
I am confused ...if you look at the sp_notice, it returns 21 cursors to the calling app.  If sp_getnoticeofficerdetails returns the cur_noticeofficerdetails to sp_notice - then when within sp_notice the codes moves on to its next routine which will call say the sp for ExporterRegulateeInfo, and this in turn will return a cursor to sp_notice called cur_RegulateeInfo ...
this will affect the calling apps ability to see what cur_noticeofficerdetails references?
0
 
sdstuberCommented:
I've think I've demonstrated how in your other question.

each procedure will simply OPEN a cursor variable and return that variable to your sp_Notice procedure.


note, each of your "open" procedures could be a function too.
functionally there is not benefit one way or the other, but it might make it more intuitive in sp_notice to
do something like this...


cur_somecursor := get_somecursor(some_variable);
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.

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