02 April 2007

Solution of ORA-06502

When you work on associative arrays in Oracle you must consider key lengths. When you create an associative arrays with 4 chars length, it means that you canuse key names up to 4 letters.If you convert keys expilicitly with to_char built-in function, you must trim to eliminate spaces; otherwise you are a potential person that get ORA-06502 error as i demonstrated below:
 
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL> set serverout on 5000
SQL>
SQL> DECLARE
  2    i NUMBER;
  3    k VARCHAR2(3);
  4    TYPE t_StringHashTable IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(3);
  5    vt_List t_StringHashTable;
  6  BEGIN
  7    i := 1;
  8    k := to_char(i, '000');
  9    vt_List(k) := 'Test item...';
 10    dbms_output.put_line(vt_List(k));
 11  END;
 12  /

DECLARE
  i NUMBER;
  k VARCHAR2(3);
  TYPE t_StringHashTable IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(3);
  vt_List t_StringHashTable;
BEGIN
  i := 1;
  k := to_char(i, '000');
  vt_List(k) := 'Test item...';
  dbms_output.put_line(vt_List(k));
END;

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8

SQL> DECLARE
  2    i NUMBER;
  3    k VARCHAR2(1000);
  4    TYPE t_StringHashTable IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(3);
  5    vt_List t_StringHashTable;
  6  BEGIN
  7    i := 1;
  8    k := to_char(i, '000');
  9    dbms_output.put_line(length(k));
 10    dbms_output.put_line('k is "' || k || '"');
 11    vt_List(k) := 'Test item...';
 12    dbms_output.put_line(vt_List(k));
 13  END;
 14  /

4
k is " 001"

DECLARE
  i NUMBER;
  k VARCHAR2(1000);
  TYPE t_StringHashTable IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(3);
  vt_List t_StringHashTable;
BEGIN
  i := 1;
  k := to_char(i, '000');
  dbms_output.put_line(length(k));
  dbms_output.put_line('k is "' || k || '"');
  vt_List(k) := 'Test item...';
  dbms_output.put_line(vt_List(k));
END;

ORA-06502: PL/SQL: numeric or value error: associative array key violates its type constraints
ORA-06512: at line 11

SQL> DECLARE
  2    i NUMBER;
  3    k VARCHAR2(3);
  4    TYPE t_StringHashTable IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(3);
  5    vt_List t_StringHashTable;
  6  BEGIN
  7    i := 1;
  8    k := TRIM(to_char(i, '000'));
  9    vt_List(k) := 'Test item...';
 10    dbms_output.put_line(vt_List(k));
 11  END;
 12  /

Test item...

PL/SQL procedure successfully completed

SQL> DECLARE
  2    i NUMBER;
  3    TYPE t_StringHashTable IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(3);
  4    vt_List t_StringHashTable;
  5  BEGIN
  6    i := 1;
  7    vt_List(i) := 'Test item...';
  8    dbms_output.put_line(vt_List(i));
  9  END;
 10  /

Test item...

PL/SQL procedure successfully completed

SQL> 

No comments: