Hi All,
I am trying to create a procedure, where we need to update a table based on the columns from other table.
Since
I have created a main cursor and sub cursor which will return records based on main cursor. here is the code sample:
CREATE OR REPLACE PROCEDURE LOAD
IS
l_level NUMBER := 0;
l_src_val ERROR_LOG.SOURCE_VALUE%TYPE;
l_list_date_inp VARCHAR2 (15) := '201401';
l_last_record_date_cor DATE;
l_last_record_date_cov DATE;
l_target_data_load VARCHAR2 (40) := 'DCX_CORECTION_FACTOR_CAL';
l_List_Date VARCHAR2 (15);
l_monthly_pcf VARCHAR2 (15);
l_monthly_wcf VARCHAR2 (15);
l_monthly_lcf VARCHAR2 (15);
l_monthly_pcf_1 VARCHAR2 (15);
CURSOR c_war_clm_hdr
IS
SELECT DISTINCT Category, List_Date, Sub_Category
FROM LOAD_HDR
WHERE List_Date BETWEEN l_list_date_inp
AND TO_NUMBER (TO_CHAR (SYSDATE, 'YYYYMM'));
CURSOR c_cov_cont_mtrx (
Category VARCHAR2,
Sub_Category VARCHAR2)
IS
SELECT Category,
COVERAGE_CODE,
MODEL_YR,
OLD_Category_FLAG,
Sub_Category,
FIXED_PCF,
FIXED_LCF,
FIXED_RES
FROM COV_HDR
WHERE Category = i.Category
AND NVL (Sub_Category, '~') = NVL (i.Sub_Category, '~');
BEGIN
l_level := 1;
BEGIN
OPEN c_war_clm_hdr;
LOOP
FETCH c_war_clm_hdr
BULK COLLECT INTO l_process_ctgry_hdr,
l_list_date_mnth_hdr,
l_sub_prcs_ctgry_hdr
LIMIT 10000;
EXIT WHEN c_war_clm_hdr%NOTFOUND;
OPEN c_cov_cont_mtrx;
LOOP
FETCH c_cov_cont_mtrx
BULK COLLECT INTO l_process_ctgry_cov,
l_coverage_code_cov,
l_model_yr_cov,
l_old_prcs_cat_flg_cov,
l_sub_prcs_ctgry_cov,
l_fixed_pcf_cov,
l_fixed_lcf_cov,
l_fixed_res_cov
LIMIT 10000;
EXIT WHEN c_cov_cont_mtrx%NOTFOUND;
END LOOP;
END LOOP;
l_level := 2;
BEGIN
FOR i IN c_war_clm_hdr
LOOP
IF l_process_ctgry_hdr.COUNT > 0
THEN
BEGIN
FOR j IN c_cov_cont_mtrx
LOOP
IF l_process_ctgry_cov.COUNT > 0
THEN
BEGIN
SELECT MONTHLY_PCF,
MONTHLY_WCF,
MONTHLY_LCF,
MONTHLY_PCF_1
INTO l_monthly_pcf,
l_monthly_wcf,
l_monthly_lcf,
l_monthly_pcf_1
FROM COR_HDR
WHERE List_Date = l_list_date_mnth_hdr (i);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_monthly_pcf := NULL;
l_monthly_wcf := NULL;
l_monthly_lcf := NULL;
l_monthly_pcf_1 := NULL;
WHEN OTHERS
THEN
EXIT;
END;
BEGIN
/* Some calculation */
END;
/* Update statement for LOAD_HDR*/
END IF;
END LOOP;
COMMIT;
CLOSE c_cov_cont_mtrx;
END;
END IF;
END LOOP;
CLOSE c_war_clm_hdr;
END;
END LOAD;
I am getting few errors in this code:
| PLS-00225: subprogram or cursor 'C_WAR_CLM_HDR' reference is out of scope |
|
| PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list |
| PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list |
| PLS-00302: component 'L_PROCESS_CTGRY_HDR' must be declared |
Kindly help, how to pass values as parameter to cursor. and even after I have declared all the variables I am getting error that it must be declared.
Your help is appreciated...