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:
Thank You, This explanation was incredibly helpfull.
Glad you found it useful Rodrigo.
This error is a little hard to track down the first time.
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
Ah, you have obviously googled for a 6502 error.
Sorry, ORA-6502 has nothing to do with gmail.
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.
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!
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.
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.
Thanks for this post. I was getting the same error and your simple fix resolved the issue!
Glad it was helpful.
Thank you, I could solve my 06512 instantly with the help of your explanation.
Is there an advantage to using INDEX BY PLS_INTEGER, over INDEX BY BINARY_INTEGER? Or is it just a preference?
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.
great explanation - thanks
Thank you.Really helpful.I wish i could find similarly elaborated explaination every time i google for an ORA error.
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!
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.
Thanks Jesse, good to know.
Very useful. Thank you!
Jared, Thank you for the nice details for this error. I can able to resolve this error.
You're welcome Swamy.
I still get comments on this one, it was difficult to track down, which is why I blogged about it.
Thanks Jared. Most helpful.
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
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.
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.
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.
Never gets old ! Still useful note !!
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
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.
Fantastic!
Post a Comment