?
Solved

Errors running an sp from package

Posted on 2011-10-18
7
Medium Priority
?
270 Views
Last Modified: 2012-05-12
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;


0
Comment
Question by:Mosquitoe
  • 4
  • 3
7 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 36986798
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 36986829
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
 

Author Comment

by:Mosquitoe
ID: 36987156
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 74

Expert Comment

by:sdstuber
ID: 36987184
you must FETCH from it


FETCH aordetails into some_variable;

that variable can then be used in the dbms_output
0
 

Author Comment

by:Mosquitoe
ID: 36987355
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 36987393
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
 

Author Comment

by:Mosquitoe
ID: 36987529
I am sorry - now that I see this, I feel enormously stupid.  Thank you
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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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 shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

807 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