Solved

PL/SQL Package and using cursors

Posted on 2011-09-27
8
291 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 73

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 73

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
 
LVL 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 73

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 73

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle TEXT search question 9 48
SQL Retrieve Values 4 57
database upgrade 8 43
PL SQL Search Across Columns 4 21
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…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now