I have create a below procedure where i am trying to create csv file from the output result of my procedure but getting error as
PLS-00302: component 'TEST_DATE' must be declared
PLS-00302: component 'TEST_DATE' must be declared
This error i am getting in the loop in the if else condition.
Create or replace PROCEDURE tes_reportt IS
test_dir VARCHAR2(4000);
lv_data_file VARCHAR2(4000) := 'test_report' || '_' ||
to_char(SYSDATE, 'dd-Mon-yyyy') ||
'.csv';
lv_err_file VARCHAR2(4000) := 'test_report' || '_' ||
to_char(SYSDATE, 'dd-Mon-yyyy') ||
'.csv';
lv_dfile utl_file.file_type;
lv_efile utl_file.file_type;
ln_err_cnt NUMBER;
ln_prc_cnt NUMBER;
BEGIN
test_dir := 'test_report';
lv_dfile := utl_file.fopen(test_dir, lv_data_file, 'W');
utl_file.put_line(lv_dfile,
'NAME' || ',' || 'ID' || ',' ||
'DT' ||','||'ID_TST');
lv_efile := utl_file.fopen(test_dir, lv_err_file, 'W');
utl_file.put_line(lv_efile,
'NAME' || ',' || 'ID' || ',' ||
'DT' || ',' ||'ID_TST'||','|| 'ERROR_DESC');
ln_err_cnt := 0;
ln_prc_cnt := 0;
FOR stg IN ( SELECT ID,TEST_DATE,SALES_ID from TEST_AGENDA)))
LOOP
IF stg.sys_db_name IS NOT NULL
THEN
ln_prc_cnt := ln_prc_cnt + 1;
utl_file.put_line(lv_dfile,
stg.sys_db_name
|| ',' || replace(stg.id,'.','|')
|| ',' ||
replace(stg.TEST_DATE,'.',' ')
|| ',' ||
stg.SALES_ID);
ELSE
ln_err_cnt := ln_err_cnt + 1;
utl_file.put_line(lv_efile,
stg.name
|| ',' ||
replace(stg.ID,'.','|')
|| ',' ||
replace(stg.TEST_DATE,'.',' ')
|| ',' ||
stg.prod_sap_leg_hier
|| ',' ||
stg.SALES_ID);
END IF;
END LOOP;
utl_file.fclose(lv_dfile);
utl_file.fclose(lv_efile);
END test_report;