ORA-06502 - Bulk Bind: Truncated Bind [message #208642] |
Mon, 11 December 2006 11:41  |
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   |
michael_bialik
Messages: 621 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   |
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 #527795 is a reply to message #527423] |
Thu, 20 October 2011 04:08   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
ric90 wrote on Tue, 18 October 2011 09:08I'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 #527821 is a reply to message #527795] |
Thu, 20 October 2011 05:58  |
 |
ric90
Messages: 42 Registered: May 2011 Location: Belfort
|
Member |
|
|
flyboy wrote on Thu, 20 October 2011 11:08ric90 wrote on Tue, 18 October 2011 09:08I'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"
|
|
|