By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE CMP_PANINDIA_VIEW_AGING_UPD (
SPAN_LINK_ID,
NE_LENGTH,
ROUTE_APPROVED_BY_CMM,
CMM_APPROVED_DATE,
MISSING_ASBUILT,
SPAN_TYPE,
JOB_PROGRESS_FLAG
) AS
SELECT 1, 2, 3, SYSDATE, 0, 'Not FTTX', 1 FROM DUAL;
1 rows affected
create or replace PROCEDURE PROC_NELENGTH_LESS_THAN_CMMAPPROVED AS
V_ERRORS NVARCHAR2(3000);
BEGIN
FOR CUR_NE_DATA IN (
SELECT COUNT(SPAN_LINK_ID) AS cnt
FROM CMP_PANINDIA_VIEW_AGING_UPD
WHERE NE_LENGTH < ROUTE_APPROVED_BY_CMM
AND CMM_APPROVED_DATE IS NOT NULL
AND MISSING_ASBUILT = 0
and SPAN_TYPE <> 'FTTX'
AND job_progress_flag = 1
)
LOOP
dbms_output.put_line('COUNT OF DATA: ' || CUR_NE_DATA.cnt);
END LOOP;
END PROC_NELENGTH_LESS_THAN_CMMAPPROVED;
/
BEGIN
DBMS_OUTPUT.ENABLE();
PROC_NELENGTH_LESS_THAN_CMMAPPROVED();
END;
/
1 rows affected
dbms_output:
COUNT OF DATA: 1
create or replace PROCEDURE PROC_NELENGTH_LESS_THAN_CMMAPPROVED AS
V_ERRORS NVARCHAR2(3000);
V_SPANID_COUNT NUMBER;
BEGIN
SELECT COUNT(SPAN_LINK_ID)
INTO V_SPANID_COUNT
FROM CMP_PANINDIA_VIEW_AGING_UPD
WHERE NE_LENGTH < ROUTE_APPROVED_BY_CMM
AND CMM_APPROVED_DATE IS NOT NULL
AND MISSING_ASBUILT = 0
and SPAN_TYPE <> 'FTTX'
AND job_progress_flag = 1;
dbms_output.put_line('COUNT OF DATA: ' || V_SPANID_COUNT);
END PROC_NELENGTH_LESS_THAN_CMMAPPROVED;
/
BEGIN
DBMS_OUTPUT.ENABLE();
PROC_NELENGTH_LESS_THAN_CMMAPPROVED();
END;
/
1 rows affected
dbms_output:
COUNT OF DATA: 1