Re: extracting records using a cursor within a cursor

From: swoop <carl.barrett_at_newcastle.gov.uk>
Date: 25 May 2005 08:02:36 -0700
Message-ID: <1117033356.373057.28610@g44g2000cwa.googlegroups.com>

Hi,

I have tried with the help you have given above (putting it into 1 cursor as shown below), and I get an error: Once again, I appreciate the help.

Thanks very much.

*
ERROR at line 62:

ORA-06550: line 62, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the
following:

, ; return returning

DECLARE
  CURSOR c IS

SELECT	CMPY, SUPP, ENTRY_DATE, SYSREF, GROSS_AMT, TAX_AMT,
	TAX_CODE, STLMNT_GROSS, STLMNT_WHOLD_GROSS, TAKE_DSCNT_IND, DSCNT_AMT,
DSCNT_PERC,
	DSCNT_TERM_1, DSCNT_TERM_2, DSCNT_TERM_3, DSCNT_DATE_1, DSCNT_DATE_2,
DSCNT_DATE_3,
	STLMNT, CURR, BANK_CHRGS, USR, TERM, PER, YY, ELEM, BANK,
PRE_STTLD_REF,
	PRPYMNT_OCCUR, PRPYMNT_STRT_PER, PRPYMNT_STRT_YY, RECUR_INTRVL,
RECUR_OCCUR, ASSOC_TRAN_FLAG,
	BTCH_NUM, MIN_AUTH, AUTH_TOT_LINES, CIT_GROSS_AMT, TRAN_TYPE,
ACCT_TYPE, PAID_HIS_FLAG,
	PYMNT_ADDR, ENTRY_DATE_COMP, SYSREF_COMP, TRAN_STYPE, TRAN_DATE,
PYMNT_DUE_DATE,
	USR_DUE_DATE_FLAG, AWAIT_EXCH_RATE, RETAIN_AMT, RETAIN_DATE, TRF_SUPP,
SUSPEND_IND, AUTH_STAT,
	GNRT_PYMNT_FLAG, CIT_SYSREF, VAT_RPRTD_IND, LOCK_USR, LOCK_SESS,
LOCK_FLAG, LINES_AUTH,
	ACT_AUTH, RECON_PYMNT_FLAG, CLRNC_DATE, DAILY_ANAL_FLAG,
MNTHLY_ANAL_FLAG, EXMPT_VAT_FLAG,
	PYMNT_RPRT_FLAG, CIT_RPRT_FLAG, PA_RPRTD_IND, PYMNT_ANAL, SCHD_NUM,
SCHD_ENTRY_NUM,
	DATE_LAST_PAID, AMT_PAID, DSCNT_CLMD, AMT_OUTSTD, OLD_TRAN_STAT,
TRAN_REF, TRAN_STAT,
	MTCH_TOT, MTCH_RLSE_DATE, ORDR_RLTD_FLAG, TAX_DATE, SLFASSD_TAX_IND,
SLFASSD_TAX_AMT, HAND,
	VAT_DATE, SNGL_CHQ_FLAG, OVRD_FACTOR, SUPP_POSTCODE, LOG_USR,
DETL_USR, INV_LOCTN,
	TAX_POINT_DATE, USR_DATA

FROM tgbtran t
WHERE tran_type = 1

AND     ordr_rltd_flag = 'Y'
AND     yy = 2005

AND NOT EXISTS
  ( select null from TGBPAYM p

    where p.cmpy = t.cmpy
    and   p.supp = t.supp
    and   p.trans_entry_date = t.entry_date
    and   p.trans_sysref = t.sysref

);
BEGIN
FOR r IN c LOOP
INSERT INTO utgbtran (CMPY, SUPP, ENTRY_DATE, SYSREF, GROSS_AMT, TAX_AMT,
        TAX_CODE, STLMNT_GROSS, STLMNT_WHOLD_GROSS, TAKE_DSCNT_IND, DSCNT_AMT, DSCNT_PERC,
        DSCNT_TERM_1, DSCNT_TERM_2, DSCNT_TERM_3, DSCNT_DATE_1, DSCNT_DATE_2, DSCNT_DATE_3,
        STLMNT, CURR, BANK_CHRGS, USR, TERM, PER, YY, ELEM, BANK, PRE_STTLD_REF,
        PRPYMNT_OCCUR, PRPYMNT_STRT_PER, PRPYMNT_STRT_YY, RECUR_INTRVL, RECUR_OCCUR, ASSOC_TRAN_FLAG,
        BTCH_NUM, MIN_AUTH, AUTH_TOT_LINES, CIT_GROSS_AMT, TRAN_TYPE, ACCT_TYPE, PAID_HIS_FLAG,
        PYMNT_ADDR, ENTRY_DATE_COMP, SYSREF_COMP, TRAN_STYPE, TRAN_DATE, PYMNT_DUE_DATE,
        USR_DUE_DATE_FLAG, AWAIT_EXCH_RATE, RETAIN_AMT, RETAIN_DATE, TRF_SUPP, SUSPEND_IND, AUTH_STAT,
        GNRT_PYMNT_FLAG, CIT_SYSREF, VAT_RPRTD_IND, LOCK_USR, LOCK_SESS, LOCK_FLAG, LINES_AUTH,
        ACT_AUTH, RECON_PYMNT_FLAG, CLRNC_DATE, DAILY_ANAL_FLAG, MNTHLY_ANAL_FLAG, EXMPT_VAT_FLAG,
        PYMNT_RPRT_FLAG, CIT_RPRT_FLAG, PA_RPRTD_IND, PYMNT_ANAL, SCHD_NUM, SCHD_ENTRY_NUM,
        DATE_LAST_PAID, AMT_PAID, DSCNT_CLMD, AMT_OUTSTD, OLD_TRAN_STAT, TRAN_REF, TRAN_STAT,
        MTCH_TOT, MTCH_RLSE_DATE, ORDR_RLTD_FLAG, TAX_DATE, SLFASSD_TAX_IND, SLFASSD_TAX_AMT, HAND,

	VAT_DATE, SNGL_CHQ_FLAG, OVRD_FACTOR, SUPP_POSTCODE, LOG_USR,
DETL_USR, INV_LOCTN,
	TAX_POINT_DATE, USR_DATA)

VALUES (r.CMPY, r.SUPP, r.ENTRY_DATE, r.SYSREF, r.GROSS_AMT, r.TAX_AMT,
	r.TAX_CODE, r.STLMNT_GROSS, r.STLMNT_WHOLD_GROSS, r.TAKE_DSCNT_IND,
r.DSCNT_AMT, r.DSCNT_PERC,
	r.DSCNT_TERM_1, r.DSCNT_TERM_2, r.DSCNT_TERM_3, r.DSCNT_DATE_1,
r.DSCNT_DATE_2, r.DSCNT_DATE_3,
	r.STLMNT, r.CURR, r.BANK_CHRGS, r.USR, r.TERM, r.PER, r.YY, r.ELEM,
r.BANK, r.PRE_STTLD_REF,
	r.PRPYMNT_OCCUR, r.PRPYMNT_STRT_PER, r.PRPYMNT_STRT_YY,
r.RECUR_INTRVL, r.RECUR_OCCUR, r.ASSOC_TRAN_FLAG,
	r.BTCH_NUM, r.MIN_AUTH, r.AUTH_TOT_LINES, r.CIT_GROSS_AMT,
r.TRAN_TYPE, r.ACCT_TYPE, r.PAID_HIS_FLAG,
	r.PYMNT_ADDR, r.ENTRY_DATE_COMP, r.SYSREF_COMP, r.TRAN_STYPE,
r.TRAN_DATE, r.PYMNT_DUE_DATE,
	r.USR_DUE_DATE_FLAG, r.AWAIT_EXCH_RATE, r.RETAIN_AMT, r.RETAIN_DATE,
r.TRF_SUPP, r.SUSPEND_IND, r.AUTH_STAT,
	r.GNRT_PYMNT_FLAG, r.CIT_SYSREF, r.VAT_RPRTD_IND, r.LOCK_USR,
r.LOCK_SESS, r.LOCK_FLAG, r.LINES_AUTH,
	r.ACT_AUTH, r.RECON_PYMNT_FLAG, r.CLRNC_DATE, r.DAILY_ANAL_FLAG,
r.MNTHLY_ANAL_FLAG, r.EXMPT_VAT_FLAG,
	r.PYMNT_RPRT_FLAG, r.CIT_RPRT_FLAG, r.PA_RPRTD_IND, r.PYMNT_ANAL,
r.SCHD_NUM, r.SCHD_ENTRY_NUM,
	r.DATE_LAST_PAID, r.AMT_PAID, r.DSCNT_CLMD, r.AMT_OUTSTD,
r.OLD_TRAN_STAT, r.TRAN_REF, r.TRAN_STAT,
	r.MTCH_TOT, r.MTCH_RLSE_DATE, r.ORDR_RLTD_FLAG, r.TAX_DATE,
r.SLFASSD_TAX_IND, r.SLFASSD_TAX_AMT, r.HAND,
	r.VAT_DATE, r.SNGL_CHQ_FLAG, r.OVRD_FACTOR, r.SUPP_POSTCODE,
r.LOG_USR, r.DETL_USR, r.INV_LOCTN,
	r.TAX_POINT_DATE, r.USR_DATA) 

END LOOP
END;
/ Received on Wed May 25 2005 - 10:02:36 CDT