2 Replies Latest reply on Jan 19, 2013 9:20 AM by Billy~Verreynne

    Numeric or value error

    980493
      Hi Experts,

      I've wrote below code and its throwing error or numeric or value error at line no 29...

      CODE
       1  declare
       2  V_REPORT_DATE DATE:='&V_REPORT_DATE';
       3  V_FILTERCOL VARCHAR2(30):=UPPER('&V_FILTERCOL');
       4  V_COL_NAME VARCHAR2(30):=UPPER('&V_COL_NAME');
       5  V_TAB_NAME VARCHAR2(30):=UPPER('&V_TAB_NAME');
       6  V_VALUE_TYPE VARCHAR2(8):=UPPER('&V_VALUE_TYPE');
       7  V_GRP_COL VARCHAR2(100):=UPPER('&V_GRP_COL');
       8  V_START_DATE VARCHAR2(20);
       9  V_CURRMNTH_STRT VARCHAR2(11);
      10  V_CURRMNTH_END VARCHAR2(11);
      11  V_CURRMNTH VARCHAR2(1000);
      12  V_CURRMNTH_SUM NUMBER(14,3):=0;
      13  A_RET_VALUE NUMBER(14,3):=0;
      14  TYPE NUMTAB IS TABLE OF DWH_PRODUCTION.ITEM_CODE%TYPE;
      15  TYPE NUMSTAB IS TABLE OF DWH_PRODUCTION.ACTUAL_YIELD%TYPE;
      16  ENUMS NUMTAB;
      17  ENUMS1 NUMSTAB;
      18  BEGIN
      19  -------------------GETTING START_END DATE OF THE MONTH-----------------------------------------
      20  SELECT TRUNC(TO_DATE(V_REPORT_DATE), 'MM') INTO V_CURRMNTH_STRT FROM DUAL;
      21  SELECT LAST_DAY (TO_DATE (V_CURRMNTH_STRT)) INTO V_CURRMNTH_END FROM DUAL;
      22  DBMS_OUTPUT.PUT_LINE (V_CURRMNTH_STRT||'          '||V_CURRMNTH_END);
      23  -------------------CURRENT MONTH VALUE---------------------------------------------------------
      24  V_CURRMNTH:='SELECT '||V_GRP_COL||', SUM('||V_COL_NAME||')  FROM  '||V_TAB_NAME|| ' WHERE '||V_
      25  DBMS_OUTPUT.PUT_LINE(V_CURRMNTH);
      26  --EXECUTE IMMEDIATE V_CURRMNTH INTO V_CURRMNTH_SUM;
      27  --DBMS_OUTPUT.PUT_LINE(V_CURRMNTH_SUM);
      28  EXECUTE IMMEDIATE V_CURRMNTH BULK COLLECT INTO ENUMS,ENUMS1;
      29  FOR I IN ENUMS.FIRST .. ENUMS.LAST LOOP
      30  DBMS_OUTPUT.PUT_LINE (
      31  ' ITEM_CODE''              '       || ENUMS(I) || ': '    || ENUMS1(I));
      32  END LOOP;
      33  --------------------IF CODN. TO GET THE VALUES AS GIVEN DURATION-------------------------------
      34  IF(V_VALUE_TYPE = 'CURRMNTH') THEN
      35     A_RET_VALUE := V_CURRMNTH_SUM;
      36  --ELSIF(VALUE_OF = 'CURRQTD') THEN
      37  --   A_RET_VALUE := V_CURRQTD_SUM;
      38  --ELSIF(VALUE_OF = 'PRVQTD') THEN
      39  --   A_RET_VALUE := V_PRVQTD_SUM;
      40  --ELSIF (VALUE_OF = 'CURRYTD') THEN
      41  --   A_RET_VALUE := V_CURRYTD_SUM;
      42  --ELSIF (VALUE_OF = 'PRVYTD') THEN
      43  --   A_RET_VALUE := V_PRVYTD_SUM;
      44  --ELSIF (VALUE_OF = 'PRVYRMTH') THEN
      45  --   A_RET_VALUE := V_PRVYRMTH_SUM;
      46  END IF;
      47  -------------------END OF CURRENT MONTH VALUE--------------------------------------------------
      ERROR
      ERROR at line 1:
      ORA-06502: PL/SQL: numeric or value error
      ORA-06512: at line 29
      Thanks in advance for the help.
        • 1. Re: Numeric or value error
          user346369
          Your bulk collect found no rows, so ENUMS is empty.


          This will work:
          <pre>FOR I IN nvl(ENUMS.FIRST,1) .. nvl(ENUMS.LAST,0) LOOP</pre>


          [Rant]PL/SQL should be smart enough to just skip the loop if either value in a FOR loop is null.
          ...or at least the error should be more specific.[Rant]
          • 2. Re: Numeric or value error
            Billy~Verreynne
            Do not use First() and Last() - as that has a different usage then when one want simply step through the collection which a for i in 1..collection.Count does just fine and well. Even should the collection be empty.

            +<insert rant here about failure to understand PL/SQL>+