Mohamed Houri’s Oracle Notes

September 17, 2013

ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind

Filed under: PL/SQL — hourim @ 3:18 pm

Have you ever been faced to the above error?  I mean this bizarre Bulk Bind: Truncated Bind?  This is a strange and unclear error message. Let me put you in the context that gave me this error. I am trying to load data into an 11gR2 (11.2.0.3.0) database from data coming via a db link from a 10gR2 (10.2.0.5.0). This is not more as what I have simplified to the maximum here below

11gR2> begin
2   for x in (
3    select v2_col
4    from  distant_table
5    )
6   loop
7     null;
8  end loop;
9  end;
10  /

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

And if instead I issue a straightforward select from a SQL windows

11gR2>
select v2_col
from  distant_table;

I have no errors.

I was struggling with this issue until one of my smart colleagues puts me into the right direction: there is a difference in the character set between the 11g (multi-byte) and the 10g database (single byte) as shown below

11gR2> select * from nls_database_parameters;
PARAMETER                      VALUE
------------------------------ ----------------------------
NLS_CHARACTERSET               AL32UTF8    --> here multibyte
NLS_LENGTH_SEMANTICS           CHAR        --> here
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.3.0

10gR2> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ------------------------------------
NLS_CHARACTERSET               WE8ISO8859P1  --> here single byte
NLS_LENGTH_SEMANTICS           BYTE          --> here
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              10.2.0.5.0

I was thinking that the 11gR2 PL/SQL engine (because still I am not fetching anything explicitly, I am just selecting) could recognize this character set difference between the local and the distant database and implicitly declare its local variable so that it could have enough space to contain incoming column when this one will have special characters .

Having smart colleagues is always a lucky situation, because not only my colleague pointed me to the right direction but gave me a workaround that I am pleased to reproduce here below :

11gR2> begin
2   for x in (
3    select v2_col ||''
4    from  distant_table
5    )
6   loop
7     null;
8  end loop;
9  end;
10  /

PL/SQL procedure successfully completed.

The work around consists of concatenating the distant column v2_col with a null string. This magically overcome the Bulk Bind: Truncated Bind error (don’t tell me why the PL/SQL engine can overcome this error when I have used such a concatenation).

If you have already been faced to this error then I will be pleased to know how you managed to solved it

3 Comments »

  1. Hello Mohamed,

    we faced a similar problem last year too.
    Two applications were running on Oracle 10 g. One was reading some info from the second one through a DBLink.
    We migrated the first one ( the one reading the data) in 11gR2 , running on a different characterset ( on 11gr2 AL32UTF8 (char) , previously on 10g WE8ISO8859P1 ((byte))
    After the migration of the “reading application” we have noticed same kinf of issue.
    The fetched record was composed of several fields. To simplify let say it was a first field VARCHAR2(30) followed by a NUMBER(10)
    We have seen that if the field VARCHAR2(30) was full (on the source 10g database, all 30 chars filled ) AND contains some special characters ( some french “é” in our case ) the fetch gave some strange error: The number value following the varchar2 field was reported as corrupted ( i don’t remember the exact error message ).
    In fact it was like there was some bytes shifting in the result set and so result was not readable. ( and gave oracle error)

    Regards,
    Frank

    Comment by Frank Polet — September 17, 2013 @ 6:37 pm | Reply

  2. Thanks Frank for your comment. It would be nice if you could let me know how you solved your issue.

    Best regards

    Mohamed

    Comment by hourim — September 17, 2013 @ 6:43 pm | Reply

  3. […] ORA-06502: PL/SQL: numeric or value error: Bulk Bind … – This magically overcome the Bulk Bind: Truncated Bind error … So Many Oracle Manuals, So Little Time “Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! […]

    Pingback by How To Fix Oracle Error Bulk Bind Truncated Bind in Windows — January 20, 2015 @ 10:22 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: