Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

PL/SQL Package and using cursors

Posted on 2011-09-27
8
Medium Priority
?
305 Views
Last Modified: 2012-05-12
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
Comment
Question by:Mosquitoe
  • 5
  • 3
8 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 36709897
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36709909
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
 

Author Comment

by:Mosquitoe
ID: 36710128

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 74

Expert Comment

by:sdstuber
ID: 36710169
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
 

Author Comment

by:Mosquitoe
ID: 36710221
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36710600
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
 

Author Comment

by:Mosquitoe
ID: 36710659
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36710687
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question