Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL Code: Error: PLS-00487: Invalid reference to variable (Oracle 10g)
PLSQL Code: Error: PLS-00487: Invalid reference to variable [message #589566] Mon, 08 July 2013 13:03 Go to next message
rasi_85
Messages: 9
Registered: May 2008
Junior Member
Hi,

I am using oracle 10G version. when i compiled the code I got the error "PLS-00487: Invalid reference to variable 'PROGRAM.MWPRG_PROGRAM_ID%TYPE'

can you please help and correct the below PLSQL error code (marked Bold)?


PLSQL procedure:
===================

create or replace
PROCEDURE SPT_PROGRAM
IS

CURSOR CUR_UPDATE IS
SELECT
P.PROGRAMID,L.PaymentPlan
FROM STG_BIX_PL_PSUBG P, STG_BIX_List L
WHERE P.PRODUCTLINEGROUPPL_ID= L.PRODUCTLGROUPPL_ID
and p.PRODUCTSUBGROUPS_ID= L.PRODUCTGROUPS_ID
and p.PZINSKEY=L.PZINSKEY and exists( SELECT 1 FROM PROGRAM WHERE
MWPRG_PROGRAM_ID = P.PROGRAMID);


TYPE T_MWPRG_PROGRAM_ID IS TABLE OF PROGRAM.MWPRG_PROGRAM_ID%TYPE INDEX BY PLS_INTEGER;
L_T_MWPRG_PROGRAM_ID T_MWPRG_PROGRAM_ID;


TYPE T_GICDT_PAYMENT_PLAN_CD IS TABLE OF PROGRAM.GICDT_PAYMENT_PLAN_CD%TYPE INDEX BY PLS_INTEGER;
L_T_GICDT_PAYMENT_PLAN_CD T_GICDT_PAYMENT_PLAN_CD;


LV_START_DATE DATE := NULL;
LV_END_DATE DATE := NULL;
LV_ERROR_DESC VARCHAR2(2000) :=NULL;
LV_ERROR_CODE VARCHAR2(2000) :=NULL;
ln_error_count NUMBER;
ln_err_idx NUMBER;
lv_c3 VARCHAR2(2000);
lv_c4 VARCHAR2(2000);


BEGIN

LV_START_DATE := TO_DATE(TO_CHAR(SYSDATE,'DD-MON-RRRR HH24:MI:SS'),'DD-MON-RRRR HH24:MI:SS');

OPEN CUR_UPDATE;
LOOP

FETCH CUR_UPDATE BULK COLLECT INTO
L_T_MWPRG_PROGRAM_ID,L_T_GICDT_PAYMENT_PLAN_CD LIMIT 100;
exit when CUR_UPDATE%notfound;


forall i in L_T_MWPRG_PROGRAM_ID.first .. L_T_MWPRG_PROGRAM_ID.last SAVE EXCEPTIONS

update PROGRAM
set GICDT_PAYMENT_PLAN_CD = L_T_GICDT_PAYMENT_PLAN_CD(i)
where MWPRG_PROGRAM_ID = L_T_MWPRG_PROGRAM_ID(i);

end loop;
close CUR_UPDATE;
commit;

LV_END_DATE := TO_DATE(TO_CHAR(SYSDATE,'DD-MON-RRRR HH24:MI:SS'),'DD-MON-RRRR HH24:MI:SS');
SPT_LOG('PROGRAM ',LV_START_DATE,LV_END_DATE,'N\A', 'N\A','N\A','N\A' );

EXCEPTION WHEN OTHERS THEN

LV_ERROR_CODE := sqlcode;
LV_ERROR_DESC := substr(SQLERRM,1,500);

ln_error_count := sql%bulk_exceptions.count;

for i in 1 .. ln_error_count loop

ln_err_idx := sql%bulk_exceptions(i).error_index;

lv_c3 := substr(('Total Rec: '||ln_error_count||'</ErrIdx>'||ln_err_idx||sqlerrm(-sql%bulk_exceptions(i).error_code)),1,1998);

lv_c4 := substr(('Program ID='||L_T_MWPRG_PROGRAM_ID (ln_err_idx).MWPRG_PROGRAM_ID),1,1998);
insert into
T_ERROR_LOG_DATA_LOAD(PROCESS,START_DATETIME,END_DATETIME,ERROR_CODE,ERROR_DESC,RECORDSTATUS,RECID)values('PROCESS',sysdate,sysdate,L V_ERROR_CODE,LV_ERROR_DESC,lv_c3,lv_c4);

end loop;
END;



Thanks,
rasi

Re: PLSQL Code: Error: PLS-00487: Invalid reference to variable [message #589567 is a reply to message #589566] Mon, 08 July 2013 13:09 Go to previous messageGo to next message
BlackSwan
Messages: 21942
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: PLSQL Code: Error: PLS-00487: Invalid reference to variable [message #589568 is a reply to message #589566] Mon, 08 July 2013 13:58 Go to previous message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Use SQL*Plus and copy and paste your session, the WHOLE session including the error, procedure creation and execution.

Regards
Michel

Previous Topic: Return Receipt in Oracle Email
Next Topic: Find shortest path with visits
Goto Forum:
  


Current Time: Fri Apr 18 13:36:26 CDT 2014

Total time taken to generate the page: 0.09383 seconds