Sorry to bother you with a ORA-06502 error.
But I'm not understanding this behavior.
As I saw, the length fits (see the dbms_output in result showing that the length is 16).
The only thing i can think is that in the procedure proc, pl/sql is changing (in the proc parameter) test_col.col%type with char(4000) or something like this.
If i use in pcol (at the select into) a function like trim, substr, rpad. It works.
Can anyone help me understanding this behavior?
How to reproduce:
create table test_ (
col char(16) default rpad('0', 16, '0')
)
/ declare
procedure proc(pcol in test_.col%type) is
r test_%rowtype;
begin
dbms_output.put_line('pcol length: ' || length(pcol));
select pcol
into r
from dual;
end;
begin
proc('XXXXXXXXXXXXXXXX');
end; pcol length: 16
1 1 ORA-06502: PL/SQL: erro: character string buffer too small numérico ou de valor
ORA-06512: em line 6
ORA-06512: em line 11
Notes about the code:
- It´s a simplified version of my real code to reproduce the error. The "dual" actually is a join of tables in the real code;
I know that:
- r.col := pcol works, but using the select is simpler in my real code.
- works with varchar2, but the ERP (that is not mine) uses char.
My workaround solution was to assign pcol to a local variable of the same type then use the local variable.
But i would like to understand the problem.
My workaround (again, a simplified version):
declare
procedure proc(pcol in test_.col%type) is
r test_%rowtype;
lcol test_.col%type;
begin
dbms_output.put_line('pcol length: ' || length(pcol));
lcol := pcol;
select lcol -- plus many others columns in the real code
into r
from dual;
end;
begin
proc('XXXXXXXXXXXXXXXX');
end;
When you pass parameters to PL/SQL programs, they don't inherit the size of the type. In this example, the parameter uses a varchar2(4). But you can pass and use strings much longer than this:
declare
subtype tp is varchar2(4);
procedure proc ( p tp ) is
begin
dbms_output.put_line(p);
end;
begin
proc('tooooo looooong');
end;
/
tooooo looooong http://docs.oracle.com/database/122/LNPLS/plsql-subprograms.htm#GUID-BA13F32D-4E04-4004-8CE5-5B628678A253 So when you pass the string to your procedure, the database doesn't really know it's a char(16) anymore.
So why the error?
Parsing is expensive. So the database takes steps to reduce the amount you might do. The parameter could be any length. If it bound it to a variable matching the exact length this could lead to a lot of extra parsing. So it assigns it a default size. In this case char(32).
You can see this in the following:
create or replace procedure p ( pcol in test_.col%type ) as
r test_%rowtype;
begin
select /*+ len */pcol
into r
from dual;
dbms_output.put_line(r.col);
end p;
/
exec p('1234567890123456');
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
select sql_id, sql_text from v$sql
where sql_text like '%/*+ len */%'
and sql_text not like '%not this%'
and sql_text not like 'declare%';
SQL_ID SQL_TEXT
btm7nury991gm SELECT /*+ len */:B1 FROM DUAL
select name, datatype_string, max_length
from v$sql_bind_capture
where sql_id = 'btm7nury991gm';
NAME DATATYPE_STRING MAX_LENGTH
:B1 CHAR(32) 32 And of course, a char 32 is padded up to 32 characters. So doesn't fit in the local variable.
The easiest workaround is to assign the values in PL/SQL and cut out the SQL step. If you insist on sticking with SQL, you can get around this by casting the variables to the correct length:
create or replace procedure p ( pcol in test_.col%type ) as
r test_%rowtype;
begin
select /*+ len */cast(pcol as char(16))
into r
from dual;
dbms_output.put_line(r.col);
end p;
/
exec p('1234567890123456');
1234567890123456Though this will silently trim the input if it's longer:
exec p('12345678901234567');
1234567890123456