You are loading an associative array and you are getting the following error:
ORA-06502: PL/SQL: value or conversion error:
associative array key violates its type constraints
 |
| image created by Google Gemini |
When I asked Gemini for this error message it said it had to do with a
null value in the key but this is false, if the key value is null you'll get a different error message:
ORA-06502: PL/SQL: value or conversion error: NULL index table key value
And there is also another interesting fact, if you explicitly assign an array element indexed by varchar2(n), in this fashion:
g_arr('') := 'something';
no error whatsoever, the whole array is wiped out, as if you executed g_arr.delete, and it's not clear to me if this is a bug or expected behavior.
So let's see the real reason for "associative array key violates its type constraints":
Create table config (config_key varchar2(50), config_value varchar2(500));
insert into config values('some_key', 'some value');
insert into config values('some_key_with_a_long_name', 'some other value');
declare
type t_associative_array is table of varchar2(500) index by varchar2(10);
g_arr t_associative_array;
k varchar2(50);
begin
FOR rec in (select config_key, config_value from config)
LOOP
g_arr(rec.config_key) := rec.config_value;
END LOOP;
k := g_arr.first;
WHILE k IS NOT NULL LOOP
DBMS_Output.PUT_LINE ('''' || k || ''':''' || g_arr(k)||'''');
k := g_arr.NEXT(k);
END LOOP;
end;
/
And bum!
ORA-06502: PL/SQL: value or conversion error:
associative array key violates its type constraints
The problem is that you declared the associative array key to be INDEX BY VARCHAR2(10), but the value coming from table is longer.
This type of problems can easily happen when you add new values over time whose length can be longer than original ones and you were a bit lazy when you wrote the code, declaring some variables with explicit data types to save some typing.
To avoid surprises you can rewrite the script as follows:
declare
type t_associative_array is table of config.config_value%type
index by config.config_key%type;
g_arr t_associative_array;
k config.config_key%type;
begin
FOR rec in (select config_key, config_value from config)
LOOP
g_arr(rec.config_key) := rec.config_value;
END LOOP;
k := g_arr.first;
WHILE k IS NOT NULL LOOP
DBMS_Output.PUT_LINE ('''' || k || ''':''' || g_arr(k)||'''');
k := g_arr.NEXT(k);
END LOOP;
end;
/
This time it will output the values without errors.
'some_key':'some value'
'some_key_with_a_long_name':'some other value'
Statement processed.
Is this error specific for associative array key ?
Yes it is, if the problem is in the length of the value, you will get this error instead:
ORA-06502: PL/SQL: value or conversion error: character string buffer too small
This distinction is good because it pinpoints the root cause of the problem.
Last but not least: watch out for the column and variable length semantics!
If you store the keys containing some variable length characters and you defined the associative array using byte semantics using explicit data types, you could incur in the same errors and be confused as the length of the key or the value seem to be within the valid range.
The best solution, and best practice by the way, is to define the array and the variables as shown above, referring to the table columns definition.
