Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PL/SQL Package and using cursors

Posted on 2011-09-27
8
Medium Priority
?
300 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: 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!

 
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 Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

718 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