ORA-6502 "Bulk Bind: Truncated Bind" error

ORA-6502 is an error that is apparently not well documented when it occurs in conjunction with the use of PL/SQL tables, and possibly bulk binds.

I ran into this problem recently when some custom code that had worked well for several years suddenly started failing.

As it turns out, and you will see just a little later here, the error is rather easy to fix. What makes it diffucult is if you've never encountered an ORA-6502 under these circumstances. There is precious little about it via MetaLink or Google. Writing about it here may be of help to the next innocent victim of ORA-6502 with bulk binds.

My suspicion was that new data loaded into the system from another database had something to do with the problem, the problem was determining where it was occurring and why.

The problem went unnoticed for some time due to a quirk in error handling built into the package. (Tom Kyte would likely agree)
Why Do People Do This?"
Built into the package is detailed profiling information. This is a good thing. Also built into the package is an error handling routine. This might be a good thing to have if the package is only run from the command line as it provides a call stack and the error message.

There is an attribute of this type of error handling that does not lend itself well to use in a job that is run via DBMS_JOB. The error message is printed, but the error is not raised.

The consequence of this type of error handling is that regardless of any errors encountered during the execution of the job, they are hidden from DBMS_JOB. No error is ever raised. After a user reports that production data is not propagating to the system, the DBA (me) checks the DBA_JOBS view and find that it is executing successfully.

Wondering what is going on, the erstwhile DBA (me again) runs the job manually and discovers that it does not actually work at all, but no error was raised.

The printed error message was "PL/SQL: numeric or value error" - bulk bind truncated.

The PL/SQL package that generated this is 3000 lines long and has some level of abstration. Translation: hard to debug when you don't know what the code is doing.

Before showing a more relevant example, I will show a simple one. If you found this page via google, this is enough to help you understand what is going on.

The problem is simply a mismatch of the data type scale. Obvious once you see it. This is a 9i example (10g would take less code) because the database it was written on was 9.2 at the time.

If you are accustomed to working with the data dictionary the problem will likely be obvious to you.

declare
  type owner_type is table of varchar2(30) index by pls_integer;
  type object_name_type is table of varchar2(20) index by pls_integer;
  type object_type_type is table of varchar2(18) index by pls_integer;

  t_owner owner_type;
  t_object_name object_name_type;
  t_object_type object_type_type;

  cursor c_obj
  is
  select owner, object_name, object_type
  from dba_objects;

begin
  open c_obj;
  while c_obj%isopen
  loop
  fetch c_obj
    bulk collect into t_owner, t_object_name, t_object_type
    limit 100;

  if c_obj%notfound then
    close c_obj;
  end if;

  end loop;
end;
/

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at line 19

The fix is simple: change length of object_name_type from 20 to 30. The data dictionary (DBA_OBJECTS.OBJECT_NAME) actually specifies this as 128, but this particular database has no objects with names longer than the standard length of 30 characters.

Where the mystery of what caused this error becomes difficult to unravel is when there are a few levels of indirection.

There is a staging table that is the temporary home for data from another system. The PL/SQL code gets its specifications for data types from the staging table, not the source table. You probably already know what the problem is.

This example is similar to what was happening.

First create the staging table.

create table tbind_test (
 owner varchar2(30) not null,
 object_name varchar2(20) not null,
 object_type varchar2(18) not null
)
/

Now run a similar, but somewhat different PL/SQL block.

declare
  type owner_type is table of tbind_test.owner%TYPE index by pls_integer;
  type object_name_type is table of tbind_test.object_name%TYPE index by pls_integer;
  type object_type_type is table of tbind_test.object_type%TYPE index by pls_integer;

  t_owner owner_type;
  t_object_name object_name_type;
  t_object_type object_type_type;

  cursor c_obj
  is
  select owner, object_name, object_type
  from dba_objects;

begin
  open c_obj;
  while c_obj%isopen
  loop
    fetch c_obj
      bulk collect into t_owner, t_object_name, t_object_type
      limit 100;
    if c_obj%notfound then
      close c_obj;
    end if;

  end loop;
end;
/

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at line 19

Here's the offending bit of code:

19    fetch c_obj
20       bulk collect into t_owner, t_object_name, t_object_type
21       limit 100;

Hmmmm. The specification for the PL/SQL tables came straight from the
table via %TYPE, what could be the problem? The problem is that you
need to look at the cursor, and compare the datatypes in the table
referenced by the cursor with those of the TBIND_TEST table.

Doing that you will find that TBIND_TEST.OBJECT_NAME should really be 128

 1* alter table tbind_test modify (object_name varchar2(128) )
  SQL> /

Table altered.

Now the bulk binds will run without error.