Errors running an sp from package

Hello - I am trying to test a stored procedure that is within a package when I run it I keep getting the error:  ORA-06550: line 17, column 66: PLS-00302: component 'AORDetails' must be declared etc...  I realise that this is telling me it cannot see that cursor but I do not understand why...  If anyone can help me understand, I would greatly appreciate that.

Here is the call:
DECLARE
  P_NOTICEID VARCHAR2(200);
  P_NOTICEOFFICERUSERID NUMBER;
  AORDETAILS EIHW.PK_NOTICEDETERMINATION.cur_AORDetails;
  NOTICEOFFICERDETAILS EIHW.PK_NOTICEDETERMINATION.cur_NoticeOfficerDetails;
BEGIN
  P_NOTICEID := 524362;
  P_NOTICEOFFICERUSERID := 300;

  PK_NOTICEDETERMINATION.SP_AOR(
    P_NOTICEID => P_NOTICEID,
    P_NOTICEOFFICERUSERID => P_NOTICEOFFICERUSERID,
    AORDETAILS => AORDETAILS,
    NOTICEOFFICERDETAILS => NOTICEOFFICERDETAILS
  );
  -- Modify the code to output the variable
  DBMS_OUTPUT.PUT_LINE('AORDETAILS = ' || PK_NOTICEDETERMINATION.AORDETAILS);
  -- Modify the code to output the variable
  DBMS_OUTPUT.PUT_LINE('NOTICEOFFICERDETAILS = ' || PK_NOTICEDETERMINATION.NOTICEOFFICERDETAILS);
END;

Here is the package declaration:
create or replace
PACKAGE PK_NoticeDetermination
AS
-- 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;

TYPE rec_AORDetails IS RECORD
(AcknowledgingCountryCode VARCHAR2(30) NOT NULL := 'CA',
ExportCountryNoticeID VARCHAR2(30) NOT NULL := 'N/A',
AcknowledgingCountryNoticeID VARCHAR2(30),
NotificationReceivedDate DATE  NOT NULL := (sysdate - 1),
AcknowledgementSentDate DATE  NOT NULL := sysdate);

TYPE cur_AORDetails IS REF CURSOR RETURN rec_AORDetails;

PROCEDURE sp_AOR (p_noticeId in VARCHAR2, p_noticeOfficerUserId in NUMBER,
            AORDetails out cur_AORDetails,
            NoticeOfficerDetails out cur_NoticeOfficerDetails);

   
END PK_NoticeDetermination;

And here is the package body:

create or replace
PACKAGE BODY PK_NoticeDetermination

AS

--Global variables within package
    v_noticeID    CORRESPONDENCE.notice_identifier%TYPE;
  v_dossierID   CORRESPONDENCE.DOSSIER_IDENTIFIER%TYPE;
    v_dossierYear   CORRESPONDENCE.DOS_HWMD_SYS_ENTER_YR%TYPE;
    v_dossierType     CORRESPONDENCE.DOSSIER_TYPE_CODE%TYPE;
PROCEDURE sp_GetDossierKey

(p_noticeId  IN VARCHAR2) IS

BEGIN
      SELECT DISTINCT dos_hwmd_sys_entered_yr,dossier_identifier,dossier_type_code
      INTO  PK_NoticeDetermination.v_dossierYear, PK_NoticeDetermination.v_dossierId, PK_NoticeDetermination.v_dossierType
      FROM dossier_waste
  WHERE trim(notice_identifier) = p_noticeId;
END sp_GetDossierKey;

PROCEDURE sp_AOR (p_noticeId in VARCHAR2, p_noticeOfficerUserId in NUMBER,
            AORDetails out cur_AORDetails,
            NoticeOfficerDetails out cur_NoticeOfficerDetails) IS
BEGIN

 sp_GetDossierKey(p_noticeID);  
 
  NoticeOfficerDetails := fn_GetNoticeOfficerDetails(p_noticeOfficerUserId);
  AORDetails := fn_getAORDetails;
END sp_AOR;


FUNCTION fn_GetNoticeOfficerDetails(p_officerId IN NUMBER)
 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;


FUNCTION fn_getAORDetails
RETURN cur_AORDetails IS
v_AOR cur_AORDetails;

BEGIN

OPEN v_AOR FOR

  SELECT 'CA' AS AcknowledgingCountryCode,
        wd.WST_FOREIGN_AUTH_NOTICE_ID As ExportCountryNoticeID,
        wd.Notice_Identifier AS AcknowledgingCountryNoticeID,
        dc.DOS_CDN_ACK_FA_REV_REQ_DATE As NotificationReceivedDate,
        sysdate AS AckowledgementSentdate
  FROM DOSSIER_WASTE wd INNER JOIN dossier dc
        on wd.dos_hwmd_sys_entered_yr = dc.dos_hwmd_sys_enter_yr
        AND wd.dossier_identifier = dc.dossier_identifier
        AND wd.dossier_type_code = dc.dossier_type_code
  WHERE wd.dos_hwmd_sys_entered_yr = v_dossieryear
          AND wd.dossier_identifier = v_dossierid
          AND wd.dossier_type_code = v_dossiertype;

RETURN v_AOR;
END fn_getAORDetails;


MosquitoeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
There is no such variable/function


PK_NOTICEDETERMINATION.AORDETAILS


you have declared your own local AORDETAILS,  try using that

  DBMS_OUTPUT.PUT_LINE('AORDETAILS = ' || AORDETAILS);
0
sdstuberCommented:
on second look, that won't work because aordetails is a cursor,  you can't pass a cursor to dbms_output.

you could pass elements fetched from it though
0
MosquitoeAuthor Commented:
DECLARE
  P_NOTICEID VARCHAR2(200);
  P_NOTICEOFFICERUSERID NUMBER;
  AORDETAILS EIHW.PK_NOTICEDETERMINATION.cur_AORDetails;
  NOTICEOFFICERDETAILS EIHW.PK_NOTICEDETERMINATION.cur_NoticeOfficerDetails;
BEGIN
  P_NOTICEID := 524362;
  P_NOTICEOFFICERUSERID := 300;

  PK_NOTICEDETERMINATION.SP_AOR(
    P_NOTICEID => P_NOTICEID,
    P_NOTICEOFFICERUSERID => P_NOTICEOFFICERUSERID,
    AORDETAILS => AORDETAILS,
    NOTICEOFFICERDETAILS => NOTICEOFFICERDETAILS
  );
  -- Modify the code to output the variable
  DBMS_OUTPUT.PUT_LINE('AORDETAILS = ' || AORDETAILS.AcknowledgingCountryCode);
  -- Modify the code to output the variable
  DBMS_OUTPUT.PUT_LINE('NOTICEOFFICERDETAILS = ' || NOTICEOFFICERDETAILS.PhoneNumberText);
END;

I tried this and it tells me invalid refernce to variable 'AORDetails' - How would show the elements fetched from it?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
you must FETCH from it


FETCH aordetails into some_variable;

that variable can then be used in the dbms_output
0
MosquitoeAuthor Commented:
Bear wth me - this is the first time I have ever done this - This is what I changed it to and it still says ORA-06550: expression is of wrong type on the line I open the cursor

DECLARE
  P_NOTICEID VARCHAR2(200);
  P_NOTICEOFFICERUSERID NUMBER;
  AORDETAILS EIHW.PK_NOTICEDETERMINATION.cur_AORDetails;
  NOTICEOFFICERDETAILS EIHW.PK_NOTICEDETERMINATION.cur_NoticeOfficerDetails;
 
  vAcknoCountryCode varchar2(200);
  vExportCountryID varchar2(200);
  vAckCountryNoticeId varchar2(200);
  vNotiReceiveddate Date;
  vAckSentDate Date;
 
BEGIN
  P_NOTICEID := 524362;
  P_NOTICEOFFICERUSERID := 300;

  PK_NOTICEDETERMINATION.SP_AOR(
    P_NOTICEID => P_NOTICEID,
    P_NOTICEOFFICERUSERID => P_NOTICEOFFICERUSERID,
    AORDETAILS => AORDETAILS,
    NOTICEOFFICERDETAILS => NOTICEOFFICERDETAILS
  );
 
   OPEN AORDETAILS;
   LOOP
      FETCH AORDETAILS INTO vAcknoCountryCode, vExportCountryID, vAckCountryNoticeId ,vNotiReceiveddate, vAckSentDate;
      EXIT WHEN AORDETAILS%ROWCOUNT = 1 OR AORDETAILS%NOTFOUND;
   END LOOP;
   CLOSE AORDETAILS;



  -- Modify the code to output the variable
  DBMS_OUTPUT.PUT_LINE('AckCountryCode = ' || vAcknoCountryCode ||'ExportCountryID = ' || vExportCountryID ||'AckCountryNoticID = '|| vAckCountryNoticeId || 'NoticReceivedDate = '|| vNotiReceiveddate || 'AcknowledgmentSendtDate = '|| vAckSentDate);
  -- Modify the code to output the variable
  --DBMS_OUTPUT.PUT_LINE('NOTICEOFFICERDETAILS = ' || NOTICEOFFICERDETAILS.PhoneNumberText);
END;
0
sdstuberCommented:
you don't need to open it,  it should have already been opened in the sp_aor procedure when it passes the cursor variable back to you


what are you trying to do here?

EXIT WHEN AORDETAILS%ROWCOUNT = 1 OR AORDETAILS%NOTFOUND;

if the goal is to fetch only once,  don't use a loop,  just fetch once.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MosquitoeAuthor Commented:
I am sorry - now that I see this, I feel enormously stupid.  Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.