Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502 - Bulk Bind: Truncated Bind
ORA-06502 - Bulk Bind: Truncated Bind [message #208642] Mon, 11 December 2006 11:41 Go to next message
sebastianR
Messages: 33
Registered: August 2005
Member
Hello!

I get the following error message, when executing the code at the bottom of this post:

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


The additional problem about this error is, that it is only appearing when running the code on our test environment, but not in developement environment. Both machines are running Oracle 10g.

The only information I was possible to get about this error-message via Google-Search was that one has to look out that the format of the table one is selecting from, has to be the same in which to bulk collect into.

For example, table_1 has to have the same columns (count, types) like q_array. But as you can see in the code, I declare q_array as TABLE OF table_1%ROWTYPE. So it _is_ the same, isn't it?

My questions now would be:
- Have you encountered this error before?
- Where is this error likely to be thrown? (at select time, or when processing the FOR-LOOP?)
- What conclusion can I make, knowing that this code runs well on my developement environment, but not in testing environment (I can't debug on testing environment yet);
- Do you know a way of how I can force this error to appear in my developement environment - for example a minimalistic code-snippet, which always raises this error (bulk bind: truncated bind)

If necessary, I can provide additional code (call_procedure_1, call_procedure_2).

Thank you very much for your help,

yours,

Sebastian

   TYPE t_q_array IS TABLE OF table_1%ROWTYPE;
   TYPE t_q_userviews IS TABLE OF user_views%ROWTYPE;   
   ...

 PROCEDURE do_something(i_user USER)
   AS
      q_array        t_q_array;
      q_userviews    t_q_userviews;
      l_i            PLS_INTEGER;
      l_name   table_1.name%TYPE;
   BEGIN
      IF i_user IS NOT NULL THEN
         SELECT *
         BULK COLLECT INTO q_array
           FROM table_1
          WHERE change_date >= g_some_date
            AND user_id = i_user;
      ELSE
         SELECT *
         BULK COLLECT INTO q_array
           FROM table_1
          WHERE change_date >= g_some_date;
      END IF;
      FOR l_i IN 1 .. q_array.COUNT
      LOOP
         g_err_string    :=
                             'Error-causing statement: ' || q_array(l_i).name;
         call_procedure_1(q_array(l_i).name, q_array(l_i).sql_stmt);
      END LOOP;
--	  END IF;
      g_some_date    := NULL;
      SELECT *
      BULK COLLECT INTO q_userviews
        FROM user_views
       WHERE view_name NOT IN(SELECT c_some_prefix || UPPER(name)
                                FROM table_1)
         AND view_name LIKE c_some_view_name_prefix;
      FOR l_i IN 1 .. q_userviews.COUNT
      LOOP
         g_err_string    :=
                          'Error-causing statement: ' || q_userviews(l_i).view_name;
         call_procedure_2(q_userviews(l_i).view_name);
      END LOOP;
   EXCEPTION
      WHEN OTHERS THEN
         g_err_string    := g_err_string || ' - ' || SQLERRM;
         RAISE;
   END;
Re: ORA-06502 - Bulk Bind: Truncated Bind [message #209049 is a reply to message #208642] Wed, 13 December 2006 01:31 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try following changes:

PROCEDURE do_something(i_user USER)
AS
q_array t_q_array;
q_userviews t_q_userviews;
l_i PLS_INTEGER;
l_name table_1.name%TYPE;
BEGIN
IF i_user IS NOT NULL THEN
g_err_string := 'SELECT WITH USER_ID ';
SELECT *
BULK COLLECT INTO q_array
FROM table_1
WHERE change_date >= g_some_date
AND user_id = i_user;
ELSE
g_err_string := 'SELECT WITHOUT USER_ID ';
SELECT *
BULK COLLECT INTO q_array
FROM table_1
WHERE change_date >= g_some_date;
END IF;
FOR l_i IN 1 .. q_array.COUNT
LOOP
g_err_string :=
'call_procedure_1: ' || q_array(l_i).name;
call_procedure_1(q_array(l_i).name, q_array(l_i).sql_stmt);
END LOOP;
-- END IF;
g_some_date := NULL;
g_err_string := 'BULK COLLECT user_views';
SELECT *
BULK COLLECT INTO q_userviews
FROM user_views
WHERE view_name NOT IN(SELECT c_some_prefix || UPPER(name)
FROM table_1)
AND view_name LIKE c_some_view_name_prefix;
FOR l_i IN 1 .. q_userviews.COUNT
LOOP
g_err_string :=
'call_procedure_2: ' || q_userviews(l_i).view_name;
call_procedure_2(q_userviews(l_i).view_name);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
g_err_string := g_err_string || ' - ' || SQLERRM;
/* RAISE; */
DBMS_OUTPUT.PUT_LINE ( SUBSTR(g_err_string,1,250) );

END;

Run it with SET SERVEROUTPUT ON SIZE 999999
It will help you to find the statement causing exception.

HTH.
Michael
Re: ORA-06502 - Bulk Bind: Truncated Bind [message #355907 is a reply to message #209049] Tue, 28 October 2008 07:56 Go to previous messageGo to next message
cmccormick8
Messages: 1
Registered: October 2008
Junior Member
I'm replying to this old thread because google ranks it high on a search for "truncated bind". Most of the other threads I skimmed gave no clear answer or too much detail.

This problem occurs because you are using a bind variable in your SQL and your collection has a variable that is too short to hold the passed value.

Rough example:
declare
cursor type_cur is select somedata, filtervar from SOMETABLE;
-- filtervar is varchar2(4)
type collection_t is table of type_cur%type;
bindvar varchar2(10) := 'somevalue'; -- this is probably set via a parameter
begin
execute immediate 'select somedata, filtervar from SOMETABLE where filtervar = :bindvar' using bindvar; --> varchar2(10) cannot fit into varchar2(4) so the bind variable is truncated
end;
Re: ORA-06502 - Bulk Bind: Truncated Bind [message #527423 is a reply to message #355907] Tue, 18 October 2011 02:08 Go to previous messageGo to next message
ric90
Messages: 42
Registered: May 2011
Location: Belfort
Member
One more reply on this all thread that save my life Very Happy

I had a cursor with UNION :
SELECT 519 IDTIME FROM DUAL
UNION
SELECT IDTIME FROM TABLE1;


With a cursor, bulk collect limit fetch, and FORALL, the inserts caused ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur: Bulk Bind: Truncated Bind

I've just put the SELECT on TABLE1 in first, and UNION after, and it runs smoothly Wink

OLD CODE :
DECLARE
  CURSOR C1 IS
    SELECT 519 AS IDTEMPS FROM DUAL
    UNION
    SELECT IDTEMPS FROM TABLE1;
  TYPE R1 IS TABLE OF C1%ROWTYPE;
  rec R1;
BEGIN
  OPEN C1;
  LOOP
    FETCH C1 BULK COLLECT INTO rec LIMIT 100;
    FORALL i IN 1..rec.COUNT
      INSERT INTO TABLE2(IDTEMPS)
      VALUES(rec(i).IDTEMPS);
    EXIT  WHEN C1%NOTFOUND;
  END LOOP;
  CLOSE C1;
END;
/
---------------------
ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur: Bulk Bind: Truncated Bind


NEW CODE :
DECLARE
  CURSOR C1 IS
    SELECT IDTEMPS FROM TABLE1
    UNION
    SELECT 519 AS IDTEMPS FROM DUAL;
  TYPE R1 IS TABLE OF C1%ROWTYPE;
  rec R1;
BEGIN
  OPEN C1;
  LOOP
    FETCH C1 BULK COLLECT INTO rec LIMIT 100;
    FORALL i IN 1..rec.COUNT
      INSERT INTO TABLE2(IDTEMPS)
      VALUES(rec(i).IDTEMPS);
    EXIT  WHEN C1%NOTFOUND;
  END LOOP;
  CLOSE C1;
END;
/
---------------------
bloc anonyme terminé


I'm looking for the bug since yesterday.

Thank you OraFAQ
Re: ORA-06502 - Bulk Bind: Truncated Bind [message #527795 is a reply to message #527423] Thu, 20 October 2011 04:08 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
ric90 wrote on Tue, 18 October 2011 09:08
I'm looking for the bug since yesterday.

I do not think it is a bug, but a result of implicit data type conversion.

Literal 519 has NUMBER data type.
If column TABLE1.IDTIME (first code) or TABLE1.IDTEMPS (second code) has different data type (e.g. DATE/VARCHAR2), it is converted to NUMBER. If it does not contain only numeric characters (for DATE, this is related to NLS_DATE_FORMAT), this conversion will fail.

You may try using literal with the same type as the involved column ('519' for VARCHAR2, no idea which DATE would be represented with this).
Re: ORA-06502 - Bulk Bind: Truncated Bind [message #527807 is a reply to message #527795] Thu, 20 October 2011 04:45 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
If that's the issue then using cast should fix it.
Re: ORA-06502 - Bulk Bind: Truncated Bind [message #527821 is a reply to message #527795] Thu, 20 October 2011 05:58 Go to previous message
ric90
Messages: 42
Registered: May 2011
Location: Belfort
Member
flyboy wrote on Thu, 20 October 2011 11:08
ric90 wrote on Tue, 18 October 2011 09:08
I'm looking for the bug since yesterday.

I do not think it is a bug, but a result of implicit data type conversion.

Literal 519 has NUMBER data type.
If column TABLE1.IDTIME (first code) or TABLE1.IDTEMPS (second code) has different data type (e.g. DATE/VARCHAR2), it is converted to NUMBER. If it does not contain only numeric characters (for DATE, this is related to NLS_DATE_FORMAT), this conversion will fail.

You may try using literal with the same type as the involved column ('519' for VARCHAR2, no idea which DATE would be represented with this).



I post IDTIME when writing, and ITEMPS when i copy/paste my code, but it is IDTEMPS (literal traduction...)
IDTEMPS is NUMBER, as 519.

I don't know if it's really a bug, but using the new freatur on 11g which permit to call directly the fielsd on BULK COLLECT CURSOR%ROWTYPE, and i think that Oracle is not able to put the right datatype where quering from dual.


@cookiemonster :
Maybe cast to TABLE1 the result fix the "bug"
Previous Topic: To add null in select based on certain condition
Next Topic: how to connect multiple databases while using procedure
Goto Forum:
  


Current Time: Sun Dec 04 18:53:43 CST 2016

Total time taken to generate the page: 0.09268 seconds