Solved

PL/SQL Package and using cursors

Posted on 2011-09-27
8
294 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 500 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

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

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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.

726 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