oracleplsql

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


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;

Solution

  • 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