6 Replies Latest reply on Dec 6, 2019 8:23 AM by Quanwen Zhao

    ORA-06502: PL/SQL: numeric or value error: character to number conversion error

    Quanwen Zhao

      Hello my ODC friends ,

       

      Today I'm writng several Oracle SQL and PL/SQL code snippets to observe time spending by query count(*), count(1), count(id) and count(flag) of my TEST table.

       

      The following is my total 3 number of code.

       

      CREATE TABLE cnt_way (
         mark VARCHAR2(5) PRIMARY KEY
      );
      
      
      INSERT INTO cnt_way (mark) VALUES ('*');
      INSERT INTO cnt_way (mark) VALUES ('1');
      INSERT INTO cnt_way (mark) VALUES ('id');
      INSERT INTO cnt_way (mark) VALUES ('flag');
      
      
      COMMIT;
      

       

      CREATE OR REPLACE PROCEDURE crt_tab_test (l_num IN NUMBER)
      AS
         v_sql_1    VARCHAR2(2000);
         v_sql_2    VARCHAR2(2000);
      BEGIN
         v_sql_1 := 'DROP TABLE test PURGE';
         v_sql_2 := 'CREATE TABLE test '
                   || 'NOLOGGING '
                   || 'AS SELECT ROWNUM id '
                   || '          , CASE WHEN ROWNUM BETWEEN 1                      AND 1/5*' || l_num || ' THEN ''low'' '
                   || '                 WHEN ROWNUM BETWEEN 2/5*' || l_num || '    AND 3/5*' || l_num || ' THEN ''mid'' '
                   || '                 WHEN ROWNUM BETWEEN 4/5*' || l_num || '    AND     ' || l_num || ' THEN ''high'' '
                   || '                 ELSE ''unknown'' '
                   || '            END flag '
                   || '          , DBMS_RANDOM.string (''p'', 20) pwd '
                   || '   FROM dual '
                   || '   CONNECT BY LEVEL <= ' || l_num;
         EXECUTE IMMEDIATE v_sql_1;
         EXECUTE IMMEDIATE v_sql_2;
         DBMS_STATS.gather_table_stats(
                 OWNNAME            => user,
                 TABNAME            => 'TEST'
         );
      EXCEPTION
         WHEN OTHERS THEN
            EXECUTE IMMEDIATE v_sql_2;
            DBMS_STATS.gather_table_stats(
                    OWNNAME            => user,
                    TABNAME            => 'TEST'
            );
      END crt_tab_test;
      /
      

       

      CREATE OR REPLACE PROCEDURE qry_tab_cnt_no_pk_and_bitmap
      AS
         start_time DATE;
         end_time   DATE;
         TYPE type_mark IS TABLE OF cnt_way.mark%TYPE;
         var_mark type_mark := type_mark();
      BEGIN
         EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS''';
         SELECT mark BULK COLLECT INTO var_mark FROM cnt_way;
         FOR i IN var_mark.first .. var_mark.last LOOP
            start_time := SYSDATE;
            -- DBMS_OUTPUT.put_line (var_mark(i));
            EXECUTE IMMEDIATE 'SELECT COUNT(' || var_mark(i) || ') FROM test';
            end_time := SYSDATE;
            DBMS_OUTPUT.put_line ('Elapsed Time: ' || end_time - start_time);
         END LOOP;
      END qry_tab_cnt_no_pk_and_bitmap;
      /
      

       

      Now let me execute them respectively.

       

      SQL> CREATE TABLE cnt_way (
        2     mark VARCHAR2(5) PRIMARY KEY
        3  );
      
      
      INSERT INTO cnt_way (mark) VALUES ('*');
      
      
      Table created.
      
      
      SQL> SQL> INSERT INTO cnt_way (mark) VALUES ('1');
      
      
      1 row created.
      
      
      SQL> 
      1 row created.
      
      
      SQL> INSERT INTO cnt_way (mark) VALUES ('id');
      
      
      1 row created.
      
      
      SQL> INSERT INTO cnt_way (mark) VALUES ('flag');
      
      
      1 row created.
      
      
      SQL> 
      SQL> COMMIT;
      
      
      Commit complete.
      

       

      SQL> CREATE OR REPLACE PROCEDURE crt_tab_test (l_num IN NUMBER)
        2  AS
        3     v_sql_1    VARCHAR2(2000);
        4     v_sql_2    VARCHAR2(2000);
        5  BEGIN
        6     v_sql_1 := 'DROP TABLE test PURGE';
        7     v_sql_2 := 'CREATE TABLE test '
        8               || 'NOLOGGING '
        9               || 'AS SELECT ROWNUM id '
       10               || '          , CASE WHEN ROWNUM BETWEEN 1                      AND 1/5*' || l_num || ' THEN ''low'' '
       11               || '                 WHEN ROWNUM BETWEEN 2/5*' || l_num || '    AND 3/5*' || l_num || ' THEN ''mid'' '
       12               || '                 WHEN ROWNUM BETWEEN 4/5*' || l_num || '    AND     ' || l_num || ' THEN ''high'' '
       13               || '                 ELSE ''unknown'' '
       14               || '            END flag '
       15               || '          , DBMS_RANDOM.string (''p'', 20) pwd '
       16               || '   FROM dual '
       17               || '   CONNECT BY LEVEL <= ' || l_num;
       18     EXECUTE IMMEDIATE v_sql_1;
       19     EXECUTE IMMEDIATE v_sql_2;
       20     DBMS_STATS.gather_table_stats(
       21             OWNNAME            => user,
       22             TABNAME            => 'TEST'
       23     );
       24  EXCEPTION
       25     WHEN OTHERS THEN
       26        EXECUTE IMMEDIATE v_sql_2;
       27        DBMS_STATS.gather_table_stats(
       28                OWNNAME            => user,
       29                TABNAME            => 'TEST'
       30        );
       31  END crt_tab_test;
       32  /
      
      
      Procedure created.
      
      
      SQL> execute crt_tab_test(1000);
      
      
      PL/SQL procedure successfully completed.
      

       

      By the way running the previous 1st and 2nd step my code are both all Okay, but  I create 3rd PL/SQL code and execute it has shown an error of "ORA-06502" below.

       

      SQL> CREATE OR REPLACE PROCEDURE qry_tab_cnt_no_pk_and_bitmap
        2  AS
        3     start_time DATE;
        4     end_time   DATE;
        5     TYPE type_mark IS TABLE OF cnt_way.mark%TYPE;
        6     var_mark type_mark := type_mark();
        7  BEGIN
        8     EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS''';
        9     SELECT mark BULK COLLECT INTO var_mark FROM cnt_way;
       10     FOR i IN var_mark.first .. var_mark.last LOOP
       11        start_time := SYSDATE;
       12        -- DBMS_OUTPUT.put_line (var_mark(i));
       13        EXECUTE IMMEDIATE 'SELECT COUNT(' || var_mark(i) || ') FROM test';
       14        end_time := SYSDATE;
       15        DBMS_OUTPUT.put_line ('Elapsed Time: ' || end_time - start_time);
       16     END LOOP;
       17  END qry_tab_cnt_no_pk_and_bitmap;
       18  /
      
      
      Procedure created.
      
      
      SQL> exec qry_tab_cnt_no_pk_and_bitmap;
      BEGIN qry_tab_cnt_no_pk_and_bitmap; END;
      
      
      *
      ERROR at line 1:
      ORA-06502: PL/SQL: numeric or value error: character to number conversion error
      ORA-06512: at "SYS.QRY_TAB_CNT_NO_PK_AND_BITMAP", line 15
      ORA-06512: at line 1
      

       

      Could you help me troubleshooting it? Thanks beforehand!

       

      Best Regards

      Quanwen Zhao

        • 1. Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
          Cookiemonster76

          Change this:

          DBMS_OUTPUT.put_line ('Elapsed Time: ' || end_time - start_time);

          to:

          DBMS_OUTPUT.put_line ('Elapsed Time: ' || (end_time - start_time));

           

          ETA: presumably due to precedence of operations when doing implicit conversions.

          Really it should be:

          DBMS_OUTPUT.put_line ('Elapsed Time: ' || to_char(end_time - start_time));

          1 person found this helpful
          • 2. Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
            Cookiemonster76

            With reagards to crt_tab_test

            Having a procedure that re-excutes code from the main section in the exception handler doesn't strike me as a good idea.

            If you expect the drop to error (because the table doesn't exist) then wrap that in an exception handler.

            1 person found this helpful
            • 3. Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
              Cookiemonster76

              Having thought about it a bit more the problem is more precisely due to the fact that without the brackets || is evaluated first. Implicit conversion isn't particularly relevant.

              So that line is equivalent to:

              DBMS_OUTPUT.put_line (('Elapsed Time: ' || to_char(end_time)

                                    )

                                    - start_time

                                   );

              Oracle states that || and - have the same precedence, here. (admittedly that's SQL rather than PL/SQL but I assume it's the same)

              Consequently it does them left to right.

              This works:

              DBMS_OUTPUT.put_line (end_time - start_time || ' is the elapsed time'); 

              1 person found this helpful
              • 4. Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
                Mike Kutz

                Technical Notes:

                count(*) will always be faster than count(1) because the CBO must change count(1) into count(*) before it can process the SQL statement.

                 

                This translation takes a few hundred Jiffies.  (1 Jiffie = 1 CPU clock cycle).

                With CPUs running at >1GHZ, that  means you need to be able to measure 0.0000000001 * 500 in order to tell the difference.

                note TIMESTAMP has a resolution to 6 decimal places (0.000001)

                 

                The resolution of TIMESTAMP is too coarse to detect any actual differences

                 

                As such, any difference you see has to do with something else.

                • In some cases, the time difference  has to do with caching (especially, SQL statement caching)
                • In other cases, the time difference is caused by "background noise" (that is : other things running on the computer).

                 

                 

                 

                MK

                1 person found this helpful
                • 5. Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
                  Quanwen Zhao

                  Hello Cookiemonster76 ,

                   

                  A big thank you to help me analyze my PL/SQL code problem.

                   

                  Best Regards

                  Quanwen Zhao

                  • 6. Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
                    Quanwen Zhao

                    Hi Mike Kutz ,

                     

                    Thanks for reminding me!

                     

                    Now I've modified initial DATE to TIMESTAMP based on my previous PL/SQL code.

                     

                    CREATE OR REPLACE PROCEDURE qry_tab_cnt_no_pk_and_bitmap
                    AS
                       start_time TIMESTAMP;
                       end_time   TIMESTAMP;
                       interval_time NUMBER;
                       TYPE type_mark IS TABLE OF cnt_way.mark%TYPE;
                       var_mark type_mark := type_mark();
                    BEGIN
                       EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TIMESTAMP_FORMAT = ''HH24:MI:SS:FF''';
                       SELECT mark BULK COLLECT INTO var_mark FROM cnt_way;
                       FOR i IN var_mark.first .. var_mark.last LOOP
                          start_time := SYSTIMESTAMP;
                          EXECUTE IMMEDIATE 'SELECT COUNT(' || var_mark(i) || ') FROM test';
                          end_time := SYSTIMESTAMP;
                          interval_time := abs((trunc(end_time - 0, 'mi') - trunc((start_time - 0), 'mi')) * 24 * 60 * 60 + extract(second from end_time - start_time));
                          DBMS_OUTPUT.put_line ('Elapsed Time: ' || interval_time || ' ms.');
                       END LOOP;
                    END qry_tab_cnt_no_pk_and_bitmap;
                    /
                    

                     

                    And my running result is as follows.

                     

                    SQL> set serveroutput on
                    SQL> 
                    SQL> CREATE OR REPLACE PROCEDURE qry_tab_cnt_no_pk_and_bitmap
                      2  AS
                      3     start_time TIMESTAMP;
                      4     end_time   TIMESTAMP;
                      5     interval_time NUMBER;
                      6     TYPE type_mark IS TABLE OF cnt_way.mark%TYPE;
                      7     var_mark type_mark := type_mark();
                      8  BEGIN
                      9     EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TIMESTAMP_FORMAT = ''HH24:MI:SS:FF''';
                     10     SELECT mark BULK COLLECT INTO var_mark FROM cnt_way;
                     11     FOR i IN var_mark.first .. var_mark.last LOOP
                     12        start_time := SYSTIMESTAMP;
                     13        EXECUTE IMMEDIATE 'SELECT COUNT(' || var_mark(i) || ') FROM test';
                     14        end_time := SYSTIMESTAMP;
                     15        interval_time := abs((trunc(end_time - 0, 'mi') - trunc((start_time - 0), 'mi')) * 24 * 60 * 60 + extract(second from end_time - start_time));
                     16        DBMS_OUTPUT.put_line ('Elapsed Time: ' || interval_time || ' ms.');
                     17     END LOOP;
                     18  END qry_tab_cnt_no_pk_and_bitmap;
                     19  /
                    
                    
                    Procedure created.
                    
                    
                    SQL> execute qry_tab_cnt_no_pk_and_bitmap;
                    Elapsed Time: .000246 ms.
                    Elapsed Time: .000148 ms.
                    Elapsed Time: .000169 ms.
                    Elapsed Time: .000154 ms.
                    
                    
                    PL/SQL procedure successfully completed.
                    

                     

                    Best Regards

                    Quanwen Zhao