Package Body Error

[timmbo]

Hi All,

Attempting to create my first Package. I am getting the following error and not sure what needs to be done. Any comments and/or suggestions are most welcome. I'm using Oracle 8i.


PLS-00323: subprogram or cursor 'ARCHIVE_PS_RELATIONSHIP_UPDATE' is declared in a package specification and must be defined in the package body


Package Code...
CREATE OR REPLACE PACKAGE ARCHIVE_TBL_UPDATE_PROC AS

PROCEDURE ARCHIVE_PS_RELATIONSHIP_UPDATE(v_prevMonthDate IN VARCHAR2, v_nextMonthDate IN VARCHAR2);

END ARCHIVE_TBL_UPDATE_PROC;
/
End Package Code...

Package Body Code...
CREATE OR REPLACE PACKAGE BODY ARCHIVE_TBL_UPDATE_PROC
IS

PROCEDURE ARCHIVE_PS_RELATIONSHIP_UPDATE(v_prevMonth VARCHAR2, v_nextMonth VARCHAR2)
IS

/*
InitialiZe Our Variables...

*/

v_companyID PS_RELATIONSHIP.COMPANYID%TYPE;
v_acctNum PS_RELATIONSHIP.ACCTNUM%TYPE;
v_acctType PS_RELATIONSHIP.ACCTTYPE%TYPE;
v_abaNum PS_RELATIONSHIP.ABANUM%TYPE;
v_rollUpAcct PS_RELATIONSHIP.ROLLUPACCT%TYPE;
v_masterAcct PS_RELATIONSHIP.MASTERACCT%TYPE;
v_sweepCD PS_RELATIONSHIP.SWEEPCD%TYPE;
v_locAcctNum PS_RELATIONSHIP.LOCACCTNUM%TYPE;
v_begBalMod PS_RELATIONSHIP.BEGBALMOD%TYPE;
v_ledgerBalMod PS_RELATIONSHIP.LEDGERBALMOD%TYPE;
v_activeFlag PS_RELATIONSHIP.ACTIVEFLAG%TYPE;

v_dateRecAdd PS_RELATIONSHIP.DATERECADD%TYPE;
v_dateLedgBalMod PS_RELATIONSHIP.DATELEDGBALMOD%TYPE;
v_dateBegBalMod PS_RELATIONSHIP.DATEBEGBALMOD%TYPE;
v_dateRecMod PS_RELATIONSHIP.DATERECMOD%TYPE;
v_begRptDate PS_RELATIONSHIP.BEGRPTDATE%TYPE;

v_begAvailBal PS_RELATIONSHIP.BEGAVAILBAL%TYPE;
v_begLedgerBal PS_RELATIONSHIP.BEGLEDGERBAL%TYPE;
v_ytdContribution PS_RELATIONSHIP.YTDCONTRIBUTION%TYPE;
v_investTarget PS_RELATIONSHIP.INVESTTARGET%TYPE;
v_loanTarget PS_RELATIONSHIP.LOANTARGET%TYPE;

v_recordCnt NUMBER(9);

c_dateMask CONSTANT VARCHAR(13) := 'MM/DD/YYYY';

BEGIN

v_recordCnt := 0;

DECLARE CURSOR archive_ps_update_cur IS
SELECT companyID,
acctNum,
acctType,
dateRecAdd,
abaNum,
rollupAcct,
masterAcct,
sweepCD,
locAcctNum,
begAvailBal,
begLedgerBal,
begBalMod,
dateBegBalMod,
ledgerBalMod,
dateLedgBalMod,
ytdContribution,
activeFlag,
investTarget,
loanTarget,
dateRecMod,
begRptDate
FROM PS_RELATIONSHIP
WHERE TRUNC(dateRecAdd) >= to_date(v_prevMonth, c_dateMask)
AND TRUNC(dateRecAdd) < to_date(v_nextMonth, c_dateMask);

BEGIN
OPEN archive_ps_update_cur;
LOOP
FETCH archive_ps_update_cur INTO
v_companyID,
v_acctNum,
v_acctType,
v_dateRecAdd,
v_abaNum,
v_rollUpAcct,
v_masterAcct,
v_sweepCD,
v_locAcctNum,
v_begAvailBal,
v_begLedgerBal,
v_begBalMod,
v_dateBegBalMod,
v_ledgerBalMod,
v_dateLedgBalMod,
v_ytdContribution,
v_activeFlag,
v_investTarget,
v_loanTarget,
v_dateRecMod,
v_begRptDate;
EXIT WHEN archive_ps_update_cur%NOTFOUND;
INSERT INTO ARCHIVE_PS_RELATIONSHIP
(companyID,
acctNum,
acctType,
dateRecAdd,
abaNum,
rollUpAcct,
masterAcct,
sweepCD,
locAcctNum,
begAvailBal,
begLedgerBal,
begBalMod,
dateBegBalMod,
ledgerBalMod,
dateLedgBalMod,
ytdContribution,
activeFlag,
investTarget,
loanTarget,
dateRecMod,
begRptDate)
VALUES
(v_companyID,
v_acctNum,
v_acctType,
v_dateRecAdd,
v_abaNum,
v_rollUpAcct,
v_masterAcct,
v_sweepCD,
v_locAcctNum,
v_begAvailBal,
v_begLedgerBal,
v_begBalMod,
v_dateBegBalMod,
v_ledgerBalMod,
v_dateLedgBalMod,
v_ytdContribution,
v_activeFlag,
v_investTarget,
v_loanTarget,
v_dateRecMod,
v_begRptDate);

v_recordCnt := v_recordCnt + 1;

END LOOP;

COMMIT;

CLOSE archive_ps_update_cur;
END archive_ps_update_cur;

DBMS_OUTPUT.PUT_LINE('Procedure ARCHIVE_PS_RELATIONSHIP_UPDATE is complete. ' || v_recordCnt || ' rows where added to ARCHIVE_PS_RELATIONSHIP table.');

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Error occurred while processing archive_ps_relationship_update procedure... ' || SQLERRM);
NULL;

END ARCHIVE_PS_RELATIONSHIP_UPDATE;


END ARCHIVE_TBL_UPDATE_PROC;
/
End Package Body Code...

TIA,

Tim

 

[sem]

They have different signatures:

ARCHIVE_PS_RELATIONSHIP_UPDATE(v_prevMonthDate IN VARCHAR2, v_nextMonthDate IN VARCHAR2)

and

ARCHIVE_PS_RELATIONSHIP_UPDATE(v_prevMonth VARCHAR2, v_nextMonth VARCHAR2)

thus are treated as different program units.


Regards, Dima

http://seminihin.narod.ru

 

[timmbo]

Thanks much! You would think I'd have seen this one. Thanks again and have a good one.