The cursor is not printing any count even if there is record in table in Oracle Procedure

Question

I have created a SP where I am running a logic to bring the V_SPANID_COUNT count but it is always showing 0 count even if there is some record say 10-20 records

Below is my SP..

create or replace PROCEDURE PROC_NELENGTH_LESS_THAN_CMMAPPROVED AS 

V_ERRORS NVARCHAR2(3000);
V_SPANID_COUNT   NUMBER := 0;

BEGIN

        FOR CUR_NE_DATA IN
        (
            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
        )
LOOP

          dbms_output.put_line('COUNT OF DATA: ' || V_SPANID_COUNT);
          
END LOOP;  

END PROC_NELENGTH_LESS_THAN_CMMAPPROVED;

Answer 1

Don't mix INTO and cursor loops. Use one or the other but not both:

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;

or:

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;

fiddle