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,
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)
IF i_user IS NOT NULL THEN
BULK COLLECT INTO q_array
WHERE change_date >= g_some_date
AND user_id = i_user;
BULK COLLECT INTO q_array
WHERE change_date >= g_some_date;
FOR l_i IN 1 .. q_array.COUNT
'Error-causing statement: ' || q_array(l_i).name;
-- END IF;
g_some_date := NULL;
BULK COLLECT INTO q_userviews
WHERE view_name NOT IN(SELECT c_some_prefix || UPPER(name)
AND view_name LIKE c_some_view_name_prefix;
FOR l_i IN 1 .. q_userviews.COUNT
'Error-causing statement: ' || q_userviews(l_i).view_name;
WHEN OTHERS THEN
g_err_string := g_err_string || ' - ' || SQLERRM;
Unfortunately, never had this error before myself. But code looks 100%. I would look at the configuration differences between the two environments. I might suspect a memory related issue due to the fact that a bulk collect loads all the data to memory for processing. Also check the amount of data being returned in each env.
Another thought, try changing your code to use a cursor with a bulk fetch with limit clause and see if you still get the error. Something like:
open refcur for select * from table;
fetch refcur bulk collect into q_array limit 250;
exit when q_array.count = 0;
for i in 1..q_array.count loop
-- do whatever
If it starts working with this type of code, you will know the problem is with the amt. of data...