Thursday, March 13, 2008

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.

31 comments:

Anonymous said...

Thank You, This explanation was incredibly helpfull.

Jared said...

Glad you found it useful Rodrigo.

This error is a little hard to track down the first time.

Anonymous said...

I'm not a computer whiz, so your explantion was klajsdohgbnbdsf, to me.
However, I'm receiving the error 6502 when trying to send regular email from Google. Would your explantion have anything to do with my problem?
Any help would be appreciated.
Confused

Jared said...

Ah, you have obviously googled for a 6502 error.

Sorry, ORA-6502 has nothing to do with gmail.

Eric said...

Jared,

Indeed very useful and right on the spot. If you enlarge an attribute in OWB and have row based processing set on in the mapping, this is also what you get. OWB generates intermediate table types itself. And you must be right about the under-published status of this error. Your blog was the first to pop up on a google search (not counting the (artificially?) enhanced popularity of 'you know who'). Writing this entry was time well spent, thank you.

Mark said...

I wound up taking my cursor's select statement and using it with "CREATE TABLE AS SELECT" syntax to find out what the TYPE's characteristics should be. I suppose I could've created a view and examined that as well.

That kind of analysis is what I expected from Oracle when I originally tried to define it by the cursor's %ROWTYPE.

You saved me a lot of time with your explanation, thanks!

Anonymous said...

However, how do you know which column in staging table is too small than that in cursor? I have come across this problem, but check the table column data type and size, they are the same because staging table was created based on source table on a remote database. I select data through db_link. Thanks.

Jared said...

Re "How do you know which column is too small"?

The way I did it was to eliminate code until I found the likely set of culprits.

Most suspicious will be variables that are declared explicitly rather than relying on table types.

For intance, given a column LAST_NAME in a table of PERSON the following should not be subject to this error:

v_last_name person.last_name%type

If however the variable were declared like this:

v_last_name varchar2(20)

you would encounter a problem if the column in the table were greater than 20 characters (or bytes, depending on how the table was created) and the data size exceeded the size of the variable.

Anonymous said...

Thanks for this post. I was getting the same error and your simple fix resolved the issue!

Jared said...

Glad it was helpful.

Sebastian said...

Thank you, I could solve my 06512 instantly with the help of your explanation.

jimmyb said...

Is there an advantage to using INDEX BY PLS_INTEGER, over INDEX BY BINARY_INTEGER? Or is it just a preference?

Jared said...
This comment has been removed by the author.
Jared said...

It used to be that PLS_INTEGER was more efficient than BINARY_INTEGER.

PLS_INTEGER What's New

Now it's just habit I guess.

Anonymous said...

great explanation - thanks

Anonymous said...

Thank you.Really helpful.I wish i could find similarly elaborated explaination every time i google for an ORA error.

Unknown said...

Jared, I ran across this when researching this error. I can't tell you how much time and effort this saved me. Thanks so much!

Jesse Gonzalez said...

Jared, thanks. A few years later and I found this post. Thanks for the detailed info.

I wanted to add that when you use (as I do frequently) the dbms_sql.varchar2_table data type you will note that this error will be observed still in 10.2.0.4.0. I observed mine with BULK COLLECT logic. Reason being that there is a limit (2000, I believe) on what that data type stores so I had to create my own TABLE type and define it as "table of varchar2(4000) ..." to get past this error. Your post triggered this thought since I'd observed it quite a while back but had since flushed that tidbit.

Thanks again.

Jared said...

Thanks Jesse, good to know.

AHMSA said...

Very useful. Thank you!

Swamy said...

Jared, Thank you for the nice details for this error. I can able to resolve this error.

Jared said...

You're welcome Swamy.
I still get comments on this one, it was difficult to track down, which is why I blogged about it.

Jon said...

Thanks Jared. Most helpful.

Anonymous said...

Thanks, Jared!
Your article helped a lot!
The mysterious thing about this is, that it didn't work from one day to another...do you know what the reason could be? A security patch? A changed parameter?
Thanks anyway - could reproduce the error and correct it!
greetz from austria
martin

Jared said...

Martin, the PL/SQL can work well for a long time - the problem is that the data changes, and the code was not able to cope with it.

Anonymous said...

Just wanted to say thank you for explaining this so well. Pointed me at the error in someone else's code that I had to modify.

Anonymous said...

I've had a similar problem.
But there was no BULK COLLECT in my code. So i found the following reason for that.
You can find the discription in the comments:

BEGIN
-- create a temporary table for compilation purposes of the script
EXECUTE IMMEDIATE 'CREATE TABLE tbl( attrib VARCHAR2( 1 ))'; -- attribute attrib is declared as type VARCHAR2( 1 )
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/

DECLARE
val VARCHAR2( 4001 );

-- this cursor will be precompiled with the attribute "attrib" which is of type VARCHAR2( 1 )
CURSOR cur
IS
SELECT attrib
FROM tbl;

BEGIN

-- dynamicly create a temporay table (e.g. for cursor performance etc. )
EXECUTE IMMEDIATE 'DROP TABLE tbl';
EXECUTE IMMEDIATE 'CREATE TABLE tbl AS SELECT ''1234567890'' AS attrib FROM dual'; -- Attrib is of type CHAR/VARCHAR2( 10 ) NOT (VARCHAR2( 1 )

-- HERE is the place, where the error occures
-- In the folllowing loop, ORACLE executes an internal fetch with predefined bulksize of 50 (???)
-- and ties to fetch a selected field of 10 characters into predefined internal storage structure based on records of type VARCHAR2( 1 )
-- Bang!!!
-- Thats why the following error message occures:
-- ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
FOR rec IN cur LOOP
val := rec.attrib;
END LOOP;

-- cleanup
EXECUTE IMMEDIATE 'DROP TABLE tbl';
END;
/


All this maybe depends on the DB prameter CURSOR_SHARING.

If you find any errors in my text, you can keep it.

Anonymous said...

Never gets old ! Still useful note !!

Anonymous said...

Thanks Jared. Useful pointer. Though mine did not quite have a data type mismatch... it was a weird error which started happening on existing code

Anonymous said...

Thanks Jared. I Got Same error and looking for suggestions in Google. This query is running for quite some data and Suddenly from nowhere got this error. Hope ur suggestion should be really helpful.

Anonymous said...

Fantastic!