Home » SQL & PL/SQL » SQL & PL/SQL » HELP ME!! CURSOR error while trying to FETCH.
HELP ME!! CURSOR error while trying to FETCH. [message #18623] Thu, 07 February 2002 11:27 Go to next message
Sabrina
Messages: 76
Registered: February 2002
Member
1 create or replace PROCEDURE transfer_d0_to_d1
2 (V_ClmID d0.tbl_claims.claimid%TYPE,
3 V_CKey d0.tbl_Claims.CarrierKey%TYPE,
4 V_CISID d0.tbl_Claims.CISID%TYPE,
5 V_Mbox d0.tbl_Claims.Mailbox%TYPE,
6 V_XmitTyp d0.tbl_Claims.TransmitType%TYPE,
7 V_ClmTyp d0.tbl_Claims.ClaimType%TYPE,
8 V_FrmTyp d0.tbl_Claims.FormType%TYPE,
9 V_ProdCod d0.tbl_Claims.ProductCode%TYPE,
10 V_SrvcSt d0.tbl_Claims.ServiceState%TYPE,
11 V_BillCod d0.tbl_Claims.BillCode%TYPE,
12 V_RedirectFlg d0.tbl_Claims.RedirectionFlag%TYPE,
13 V_TotalAmt d0.tbl_Claims.TotalAmount%TYPE,
14 V_ProvTaxID d0.tbl_Claims.ProviderTaxID%TYPE,
15 V_StatThru d0.tbl_Claims.StatementThrough%TYPE,
16 V_ClientBdgDate d0.tbl_Claims.ClientBridgeDate%TYPE,
17 V_ClientXmitDate d0.tbl_Claims.ClientTransmitDate%TYPE,
18 V_DepTime d0.tbl_ClaimTrack.DepositTime%TYPE,
19 V_XmitTime d0.tbl_ClaimTrack.TransmitTime%TYPE,
20 V_TPID d0.tbl_Claims.ThirdPartyID%TYPE,
21 V_Pyrindic d0.tbl_Claims.PayerIndicator%TYPE,
22 V_SrvcLCnt d0.tbl_Claims.ServiceLineCount%TYPE,
23 V_BillTyp d0.tbl_Claims.BillType%TYPE,
24 V_DRGCod d0.tbl_Claims.DRGCode%TYPE,
25 V_EdtMastVer d0.tbl_Claims.EditMasterVersion%TYPE,
26 V_CorMastVer d0.tbl_Claims.CoreProductVersion%TYPE,
27 V_EdtMastProdVer d0.tbl_Claims.EditMasterProductVersion%TYPE)
IS
28 CURSOR cursor_tbl_claims IS
29 select C.ClaimID,C.CarrierKey,C.CISID,C.Mailbox,C.TransmitType,C.FormType,C.ProductCode,C.ServiceState,C.BillCode,C.RedirectionFlag,C.TotalAmount,C.ProviderTaxID,C.StatementThrough,C.ClientBridgeDate,C.ClientTransmitDate,
C.ThirdPartyID,C.PayerIndicator,C.ServiceLineCount,C.BillType,C.DRGCode,C.EditMasterVersion,C.CoreProductVersion,C.EditMasterProductVersion,T.DepositTime,T.TransmitTime from d0.tbl_claims C, d0.tbl_ClaimTrack T
where d0.C.ClaimID = d0.T.ClaimID;

BEGIN
OPEN cursor_tbl_claims;
LOOP
FETCH cursor_tbl_claims INTO V_ClmID,V_CKey,V_CISID,V_Mbox,
V_XmitTyp,V_ClmTyp,V_FrmTyp,V_ProdCod,V_SrvcSt,
V_BillCod,V_RedirectFlg,V_TotalAmt,V_ProvTaxID,V_StatThru,V_ClientBdgDate,V_ClientXmitDate,V_TPID,V_PyrIndic,V_SrvcLinCnt,V_BillTyp,V_DRGCod,V_EdtMastVer,V_CorMastVer,V_EdtMastProdVer,V_DepTime,V_XmitTime;

EXIT WHEN cursor_tbl_claims%NOTFOUND;

INSERT INTO d1.tbl_ClaimsHistory
(claimid,CarrierKey,CISID,Mailbox,TransmitType,ClaimType,FormType,ProductCode,ServiceState,BillCode,RedirectionFlag,TotalAmount,ProviderTaxID,StatementThrough,ClientBridgeDate,ClientTransmitDate,ThirdPartyID,PayerIndicator,ServiceLineCount,BillType,DRGCode,EditMasterVersion,CoreProductVersion,EditMasterProductVersion,DepositTime,TransmitTime,BillDate)

VALUES
(V_ClmID,V_CKey,V_CISID,V_Mbox,V_XmitTyp,V_ClmTyp,V_FrmTyp,V_ProdCod,V_SrvcSt,V_BillCod,V_RedirectFlg,V_TotalAmt,V_ProvTaxID,V_StatThru,V_ClientBdgDate, V_ClientXmitDate,V_TPID,V_PyrIndic,V_SrvcLinCnt,V_BillTyp,V_DRGCod,V_EdtMastVer,V_CorMastVer,V_EdtMastProdVer,V_DepTime,V_XmitTime,'');

END LOOP;
CLOSE cursor_tbl_claims;
COMMIT;
END;

Warning: Procedure created with compilation errors.

SQL> SHO ERROR
Errors for PROCEDURE CURSOR_CLAIM_TRANSFER_D0_TO_D1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
39/1 PL/SQL: SQL Statement ignored
39/30 PLS-00403: expression 'V_CLMID' cannot be used as an INTO-target
of a SELECT/FETCH statement

45/1 PL/SQL: SQL Statement ignored
54/36 PL/SQL: ORA-00984: column not allowed here
Re: HELP ME!! CURSOR error while trying to FETCH. [message #18624 is a reply to message #18623] Thu, 07 February 2002 12:58 Go to previous message
sverch
Messages: 582
Registered: December 2000
Senior Member
You declared your variables at an Iput parameters.
you have to
CREATE OR REPLACE PROCEDURE transfer_d0_to_d1
AS
V_ClmID d0.tbl_claims.claimid%TYPE,
.....
Previous Topic: moving column from one table to another...
Next Topic: global temporary tables
Goto Forum:
  


Current Time: Tue Sep 28 12:03:53 CDT 2021