Solved

PL/SQL Packages and passing info within

Posted on 2011-09-27
9
234 Views
Last Modified: 2012-05-12
Hello,

Using a package in Oracle.  A stored procedure: sp_Notice,  calls the sp_getnoticeofficerdetails:

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) IS


BEGIN
 
  sp_GetDossierKey(p_noticeID);
  sp_GetNoticeOfficerDetails(p_noticeOfficerUserId);
END sp_Notice;

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 v_rec;

            EXIT WHEN cur_noticeofficerdetails%NOTFOUND;

            CLOSE cur_noticeofficerdetails;
        END LOOP;
       
END sp_GetNoticeOfficerDetails;

If the sp_notice returns the cur_noticeofficerdetails to the calling app, then I have to modify the sp_GetNoticeOfficerDetails to return cur_noticeofficerdetails to sp_notice  - and how would I modify the two to do this properly?
Thank you very much!
0
Comment
Question by:Mosquitoe
  • 5
  • 4
9 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 36710638
don't fetch from the cursor, just open it and return the cursor variable


PROCEDURE sp_getnoticeofficerdetails(p_officerid IN INTEGER, p_cursor OUT cur_noticeofficerdetails)
IS
BEGIN
    OPEN p_cursor FOR
        SELECT wst_auth_phone_number, wst_auth_e_mail_address
          FROM waste_authority
         WHERE wst_auth_identifier = p_officerid;
END sp_getnoticeofficerdetails;
0
 

Author Comment

by:Mosquitoe
ID: 36711036
OK.  Thank you for all the helpful comments.  So, I decided to change the sp to a function and I removed it from the package declaration area because it should not be callable external of the package.

It is now:
FUNCTION fn_GetNoticeOfficerDetails(p_officerId IN INTEGER,
p_cursor OUT cur_noticeofficerdetails) RETURN cur_noticeofficerdetails IS

  BEGIN
 
    OPEN p_cursor FOR
   
        SELECT wst_auth_phone_number, wst_auth_e_mail_address
        FROM waste_authority
        WHERE wst_auth_identifier = p_officerId;
       
END fn_getnoticeofficerdetails;

The cursors are declared in the package declaration area using records like this:

-- NoticeOfficerDetails
TYPE rec_NoticeOfficerDetails IS RECORD
(PhoneNumberText VARCHAR2(30) NOT NULL := 'N/A',
EmailText VARCHAR2(255) NOT NULL := 'N/A');

TYPE cur_NoticeOfficerDetails IS REF CURSOR RETURN rec_NoticeOfficerDetails;

Am I using the record type declared above correctly if I have the fn_GetNoticeOfficerDetails function as is?

And in sp_notice how do I (yes I know, this is like dealing with an idiot)  how do I  assign the cursor the function returns?  I tried a couple of things and they all threw errors - this is what it looks like now (see below); this line throws an error, what am I not getting?
cur_NoticeOfficerDetails := fn_GetNoticeOfficerDetails(p_noticeOfficerUserId, p_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) IS


BEGIN
 
  sp_GetDossierKey(p_noticeID);
  cur_NoticeOfficerDetails := fn_GetNoticeOfficerDetails(p_noticeOfficerUserId, p_cursor);

END sp_Notice;
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36711106
your function would look something like this...  (no out parameter for a function)

FUNCTION fn_GetNoticeOfficerDetails(p_officerId IN INTEGER) RETURN cur_noticeofficerdetails IS

v_cursor  cur_noticeofficerdetails;
  BEGIN
 
    OPEN v_cursor FOR
        SELECT wst_auth_phone_number, wst_auth_e_mail_address
        FROM waste_authority
        WHERE wst_auth_identifier = p_officerId;

    return v_cursor;
       
END fn_getnoticeofficerdetails;
0
 

Author Comment

by:Mosquitoe
ID: 36711142

Thanks so much - I had worked it all out except to RETURN it.  


I am still stumbling over how to catch it from the sp_Notice...
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.

 
LVL 73

Expert Comment

by:sdstuber
ID: 36711150
in  sp_notice it would be something like this...

NoticeOfficerDetails  := fn_GetNoticeOfficerDetails(p_noticeOfficerUserId);
0
 

Author Comment

by:Mosquitoe
ID: 36711156

Sorry - it works:

 NoticeOfficerDetails := fn_GetNoticeOfficerDetails(p_noticeOfficerUserId);


Then do I just add RETURN NoticeOfficerDetails
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36711487
no , RETURN is for function.


sp_notice is a procedure   the assignment of the function value to the out parameter is sufficient

now, just do the same for the others
0
 

Author Comment

by:Mosquitoe
ID: 36711672
when I attempt to do this again:

FUNCTION fn_GetNoticeDetails
 RETURN cur_NoticeDetails IS
 p_cursor cur_NoticeDetails;
 
 BEGIN
 
    OPEN p_cursor FOR
   
      SELECT DISTINCT cs.COUNTRY_CODE AS ExportCountryCode,
      dw.notice_identifier AS ExportCountryNoticeID,
      d.dossier_expiry_date,
      wd.wst_decision_cd_assigned_date
      FROM dossier d, dossier_waste dw, dossier_company dc, waste_decision wd, company_site cs
      WHERE d.dos_hwmd_sys_enter_yr = v_dossierYear
      AND d.dossier_identifier = v_dossierId
      AND d.dossier_type_code = v_dossierType
      AND dc.dos_hwmd_sys_entered_yr = d.dos_hwmd_sys_enter_yr
      AND dc.dossier_identifier = d.dossier_identifier
      AND dc.dossier_type_code = d.dossier_type_code
      AND dw.dos_hwmd_sys_entered_yr = d.dos_hwmd_sys_enter_yr
      AND dw.dossier_identifier = d.dossier_identifier
      AND dw.dossier_type_code = d.dossier_type_code
      AND wd.dos_hwmd_sys_entered_yr = d.dos_hwmd_sys_enter_yr
      AND wd.dossier_identifier = d.dossier_identifier
      AND wd.dossier_type_code = d.dossier_type_code
      AND dc.company_number = cs.company_number
      AND dc.company_site_number = cs.company_site_number
      AND dc.dossier_co_role_code = 'G';
      
    RETURN p_cursor;
       
END fn_GetNoticeDetails;

I get errors.  It ignores the statement and also throws Error(46,7): PLS-00382: expression is of wrong type .  I took the select statement and can run it successfully against the db.  It underlines in red, these two lines:


    OPEN p_cursor FOR
   
      SELECT DISTINCT cs.COUNTRY_CODE AS ExportCountryCode,
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36711802
probably your cursor and your query don't synch.

open a new question with all of the needed details (cursor def, variable defs, the query above, etc.)
0

Featured Post

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.

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

746 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