Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

Quanwen ZhaoDec 5 2019 — edited Dec 6 2019

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

This post has been answered by Cookiemonster76 on Dec 5 2019
Jump to Answer

Comments

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));

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.

Cookiemonster76
Answer

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'); 

Marked as Answer by Quanwen Zhao · Sep 27 2020
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

Quanwen Zhao

Hello Cookiemonster76 ,

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

Best Regards

Quanwen Zhao

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

1 - 6

Post Details

Added on Dec 5 2019
6 comments
6,400 views