bELOW IS MY SCRIPT...USING A CURSOR FOR A SELECT STATEMENT AND INSERTING EACH SELECTED RECORD INTO ATABLE CALLED TESTCUR.......THE LOOP CONTINUES UNTIL THE ALL RECORDS HAVE BEEN FETCHED...but i receive the error as below
line xx, column yy PL/SQL: SQL Statement ignored
what i need to do
DECLARE
CON_NUM TESTCUR.contract_numbr%TYPE;
CON_TIT TESTCUR.CONTRACT_TITLE%TYPE;
CON_DESC TESTCUR.contract_description%TYPE;
CON_STAT_CD TESTCUR.c0ntract_stat_cd%TYPE;
CON_SRT_DATE TESTCUR.start_dt%TYPE;
CON_END_DATE TESTCUR.end_dt%TYPE;
CON_CNCL_DT TESTCUR.cncl_dt%TYPE;
CON_EXTN_DT TESTCUR.ext_dt%TYPE;
CON_TYPE_SRVC_CD TESTCUR.type_srvc_cd%TYPE;
CON_OWNR_CD TESTCUR.ownr_cd%TYPE;
CON_PBLC_SCTR_FLG TESTCUR.pblc_sctr_flg%TYPE;
CON_CUST_TYPE_CD TESTCUR.cust_type_cd%TYPE;
CON_MVB_CUST_TYPE_CD TESTCUR.mvb_cust_type_cd%TYPE;
CON_BID_MTHD_CD TESTCUR.bid_mthd_cd%TYPE;
CON_BID_MTHD_DESC TESTCUR.bid_mthd_desc%TYPE;
CON_PGM_CD TESTCUR.pgm_cd%TYPE;
CON_PGM_DESC TESTCUR.pgm_desc%TYPE;
CON_TERM_SALE_ID TESTCUR.term_sale_id%TYPE;
CON_TERM_SALE_DESC TESTCUR.term_sale_desc%TYPE;
CON_CTGRY_CD TESTCUR.ctgry_cd%TYPE;
CON_CTGRY_DESC TESTCUR.ctgry_desc%TYPE;
CON_LAST_UPDT_DT_TM TESTCUR.last_updt_dt_Tm%TYPE;
CON_LAST_UPDT_USER_ID TESTCUR.last_updt_user_id%TYPE;
CON_SRC_LOCTN_ID TESTCUR.src_loctn_id%TYPE;
CON_VACCINE_CONTRACT_TYPE TESTCUR.vaccine_contract_type%TYPE;
CON_SHORT_ZIP TESTCUR.short_zip%TYPE;
CON_ZIP TESTCUR.zip%TYPE;
CON_CONTRACT_SECTOR_FLAG TESTCUR.contract_sector_flag%TYPE;
CON_NDC TESTCUR.ndc%TYPE;
CON_PRODUCT_DESCRIPTION TESTCUR.prod_description%TYPE;
CON_DOSE_PER_PKG TESTCUR.dose_per_pkg%TYPE;
CON_PROD_PRICE TESTCUR.prod_price%TYPE;
CON_CONTRACT_TYPE TESTCUR.contract_type%TYPE;
CON_PR_DS TESTCUR.pr_ds%TYPE;
CON_PACKAGE_SIZE TESTCUR.package_size%TYPE;
CURSOR CONTRACTCUR IS
SELECT
D.CNTRCT_NMBR AS CONTRACT_NUMBR,
D.CNTRCT_TITLE AS CONTRACT_TITLE,
D.CNTRCT_DESC AS CONTRACT_DESCRIPTION,
D.CNTRCT_STAT_CD AS c0ntract_stat_cd,
D.START_DT AS START_DT,
D.END_DT AS END_DT,
D.CNCL_DT AS CNCL_DT,
D.EXTNDD_DT AS EXT_DT,
D.TYPE_SRVC_CD AS TYPE_SRVC_CD,
D.OWNER_CD AS OWNR_CD,
D.PBLC_SCTR_FLG AS PBLC_SCTR_FLG,
D.CUST_TYPE_CD AS CUST_TYPE_CD,
D.MVB_CUST_TYPE_CD AS MVB_CUST_TYPE_CD,
D.BID_MTHD_CD AS BID_MTHD_CD,
D.BID_MTHD_DESC AS BID_MTHD_DESC,
D.PGM_CD AS PGM_CD,
D.PGM_DESC AS PGM_DESC,
D.TERM_SALE_ID AS TERM_SALE_ID,
D.TERM_SALE_DESC AS TERM_SALE_DESC,
D.CTGRY_CD AS CTGRY_CD,
D.CTGRY_DESC AS CTGRY_DESC,
D.LAST_UPDT_DT_TM AS LAST_UPDT_DT_TM,
D.LAST_UPDT_USER_ID AS LAST_UPDT_USER_ID,
D.SRC_LOCTN_ID AS SRC_LOCTN_ID,
D.VACCINE_CONTRACT_TYPE AS VACCINE_CONTRACT_TYPE,
SRC.ZIP_CD AS SHORT_ZIP,
SRC.ZIP_CD ||' '||SRC.ZIP_CD_EXTN AS ZIP,
(CASE CUST.SECTOR_TYPE_CD
WHEN 'PUBLIC' THEN 'Y'
ELSE 'N' END)CONTRACT_SECTOR_FLAG,
DV.NDC_PROD_NMBR ||''||DV.NDC_SIZE_NMBR AS NDC,
DV.BATS_PROD_GROUP_NM AS PROD_DESCRIPTION,
CC.PROD_PRICE AS PROD_PRICE,
DV.DOSE_PER_PKG AS DOSE_PER_PKG,
(CASE (CASE CUST.SECTOR_TYPE_CD
WHEN 'PUBLIC' THEN 'Y'
ELSE 'N' END)
WHEN 'Y' THEN 'PUBLIC'
ELSE 'PRIVATE' END) AS CONTRACT_TYPE,
(CC.PROD_PRICE/DV.DOSE_PER_PKG) AS PR_DS,
--'$'||(CC.PROD_PRICE/DV.DOSE_PER_PKG) AS "CAL_PR/DS",
--'$'||CC.PROD_PRICE AS CONTRACT_PACKAGE_PRICE,
DV.DOSE_PER_PKG AS PACKAGE_SIZE
--Null AS Notes
FROM
DSS.CNTRCT D,
CUSTMKTG.CNTRCT_PP_CLASS CUST,
CCAS.CNTRCT_PROD_PRC_HSTRY CC,
DSS.VPROD_NON_DLNT DV,
DSS.SRC_LOCTN SRC,
CCAS.MVB_CNTRCT_ELIG_CUST ELIG
WHERE
D.CNTRCT_NMBR = CUST.CNTRCT_NMBR
AND D.CNTRCT_NMBR = CC.CNTRCT_NMBR
AND DV.NDC_CD = CC.NDC_CD
AND SRC.SRC_LOCTN_ID = ELIG.SRC_LOCTN_ID
AND (((D.CNTRCT_NMBR)IN(SELECT CONTRACT_EXCLUSION.CONTRACT_NMBR
FROM CONTRACT_EXCLUSION))) OR (((D.MVB_CUST_TYPE_CD)='PSH' Or (D.MVB_CUST_TYPE_CD)='HMO'));
--AND (((CC.START_DT)<=[Forms]![Main]![txtRunDate])AND((CC.END_DT)>=[Forms]![Main]![txtRunDate]))
--GROUP BY D.CNTRCT_NMBR,D.CNTRCT_TITLE,D.CNTRCT_DESC,D.CNTRC T_STAT_CD,D.START_DT,
-- D.END_DT,D.CNCL_DT,D.EXTNDD_DT,D.TYPE_SRVC_CD,D.OW NER_CD,D.PBLC_SCTR_FLG,D.CUST_TYPE_CD,
-- D.MVB_CUST_TYPE_CD,D.BID_MTHD_CD,D.BID_MTHD_DESC,D .PGM_CD,D.PGM_DESC,D.TERM_SALE_ID,
-- D.TERM_SALE_DESC,D.CTGRY_CD,D.CTGRY_DESC,D.LAST_UP DT_DT_TM,D.LAST_UPDT_USER_ID,D.SRC_LOCTN_ID,
-- D.VACCINE_CONTRACT_TYPE,CUST.SECTOR_TYPE_CD,DV.BAT S_PROD_GROUP_NM,CC.PROD_PRICE,DV.DOSE_PER_PKG,
-- SRC.ZIP_CD,SRC.ZIP_CD ||' '||SRC.ZIP_CD_EXTN,DV.NDC_PROD_NMBR ||''||DV.NDC_SIZE_NMBR,DV.BATS_PROD_GROUP_NM,
-- CC.PROD_PRICE,DV.DOSE_PER_PKG,CONTRACT_TYPE,CONTRA CT_PACKAGE_PRICE,PACKAGE_SIZE,Price/Dose,Calculated Price/Dose
--(Rounded),Notes
--HAVING ((ELIG.END_DT)>=[Forms]![Main]![txtRunDate]) AND ((D.CNTRCT_STAT_CD)="A"))
--ORDER BY CONTRACT_TITLE,PRODUCT_DESCRIPTION,NDC
BEGIN
OPEN CONTRACTCUR;
LOOP
FETCH CONTRACTCUR INTO
CON_NUM,CON_TIT,
CON_DESC,CON_STAT_CD,
CON_SRT_DATE,
CON_END_DATE,
CON_CNCL_DT,
CON_EXT_DT,
CON_TYPE_SRVC_CD,
CON_OWNR_CD,
CON_PBLC_SCTR_FLG,
CON_CUST_TYPE_CD,
CON_MVB_CUST_TYPE_CD,
CON_BID_MTHD_CD,
CON_BID_MTHD_DESC,
CON_PGM_CD,
CON_PGM_DESC,
CON_TERM_SALE_ID,
CON_TERM_SALE_DESC,
CON_CTGRY_CD,
CON_CTGRY_DESC,
CON_LAST_UPDT_DT_TM,
CON_LAST_UPDT_USER_ID,
CON_SRC_LOCTN_ID,
CON_VACCINE_CONTRACT_TYPE,
CON_SHORT_ZIP,
CON_ZIP,
CON_CONTRACT_SECTOR_FLAG,
CON_NDC,
CON_PRODUCT_DESCRIPTION,
CON_DOSE_PER_PKG,
CON_PROD_PRICE,
CON_CONTRACT_TYPE,
CON_PR_DS,
CON_PACKAGE_SIZE;
EXIT WHEN CONTRACTCUR%NOTFOUND;
INSERT INTO TESTCUR VALUES(CON_NUM,CON_TIT,CON_DESC,CON_STAT_CD,CON_SR T_DATE,
CON_END_DATE,CON_CNCL_DT,CON_EXT_DT,CON_TYPE_SRVC_ CD,CON_OWNR_CD,CON_PBLC_SCTR_FLG,
CON_CUST_TYPE_CD,CON_MVB_CUST_TYPE_CD,CON_BID_MTHD _CD,CON_BID_MTHD_DESC,CON_PGM_CD,
CON_PGM_DESC,CON_TERM_SALE_ID,CON_TERM_SALE_DESC,C ON_CTGRY_CD,CON_CTGRY_DESC,
CON_LAST_UPDT_DT_TM,CON_LAST_UPDT_USER_ID,CON_SRC_ LOCTN_ID,CON_VACCINE_CONTRACT_TYPE,
CON_SHORT_ZIP,CON_ZIP,CON_CONTRACT_SECTOR_FLAG,CON _NDC,CON_PRODUCT_DESCRIPTION,
CON_DOSE_PER_PKG,CON_PROD_PRICE,CON_CONTRACT_TYPE, CON_PR_DS,CON_PACKAGE_SIZE);
END LOOP;
CLOSE CONTRACTCUR;
END;