| Bulk Bind: Truncated Bind [message #489208] |
Thu, 13 January 2011 14:47  |
mape
Messages: 298
Registered: July 2006
Location: Slovakia
|
Senior Member |
|
|
Hi
I've written the code (see below) and after run I get an error:
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
I dont know how to find out the wrong value from table.
Can anybody know how to find it out?
DECLARE
TYPE rowids IS TABLE OF ROWID;
r1 rowids;
type t_varchar is table of varchar2(50);
n1 t_varchar ;
cursor c1 is select e.rowid rid,msisdn_displayed
from the_table
where contract_id is not null;
BEGIN
OPEN c1;
WHILE TRUE LOOP
FETCH c1 BULK COLLECT INTO r1, n1 LIMIT 20000;
EXIT WHEN r1.COUNT = 0;
FORALL indx IN n1.FIRST..n1.LAST
UPDATE the_table
SET msisdn_displayed = n1(indx)
WHERE ROWID = r1(indx);
COMMIT;
END LOOP;
CLOSE c1;
end;
Thanks
|
|
|
|
|
|
| Re: Bulk Bind: Truncated Bind [message #489216 is a reply to message #489208] |
Thu, 13 January 2011 16:31  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
When you get the pl/sql numeric or value error, it is usually because you are trying to use insert or update using a value that is a mis-match with what you are inserting into or updating, due to either being the wrong data type or too large size. That is why it is a good practice to use "table of table_name.column_name%type", so if the type or size is changed, then your code still runs. I have provided a reproduction and correction below. However, note that the whole pl/sql block does the same thing as the simple update statement that I have provided below the corrected code. Both the block and the update statement, however, accomplish nothing. All they are doing is updating the value of msisdn_displayed with the value that already exists for the rows where contract_id is null. So, what is your code supposed to be doing?
-- test environment:
SCOTT@orcl_11gR2> CREATE TABLE the_table
2 (msisdn_displayed VARCHAR2 (60),
3 contract_id NUMBER)
4 /
Table created.
SCOTT@orcl_11gR2> INSERT ALL
2 INTO the_table VALUES ('abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz123', 1)
3 INTO the_table VALUES ('123abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz', 2)
4 INTO the_table VALUES ('test', NULL)
5 SELECT * FROM DUAL
6 /
3 rows created.
SCOTT@orcl_11gR2> COMMIT
2 /
Commit complete.
SCOTT@orcl_11gR2> SELECT * FROM the_table
2 /
MSISDN_DISPLAYED CONTRACT_ID
------------------------------------------------------------ -----------
abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz123 1
123abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz 2
test
3 rows selected.
-- reproduction of problem:
SCOTT@orcl_11gR2> DECLARE
2 TYPE rowids IS TABLE OF ROWID;
3 r1 rowids;
4 --
5 type t_varchar is table of varchar2(50);
6 n1 t_varchar ;
7 --
8 cursor c1 is
9 select e.rowid rid, msisdn_displayed
10 from the_table e
11 where contract_id is not null;
12 BEGIN
13 OPEN c1;
14 WHILE TRUE LOOP
15 FETCH c1 BULK COLLECT INTO r1, n1 LIMIT 20000;
16 EXIT WHEN r1.COUNT = 0;
17 FORALL indx IN n1.FIRST .. n1.LAST
18 UPDATE the_table
19 SET msisdn_displayed = n1(indx)
20 WHERE ROWID = r1(indx);
21 COMMIT;
22 END LOOP;
23 CLOSE c1;
24 end;
25 /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at line 15
SCOTT@orcl_11gR2> SELECT * FROM the_table
2 /
MSISDN_DISPLAYED CONTRACT_ID
------------------------------------------------------------ -----------
abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz123 1
123abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz 2
test
3 rows selected.
-- corrected code (that runs without error, but accomplishes nothing):
SCOTT@orcl_11gR2> DECLARE
2 TYPE rowids IS TABLE OF ROWID;
3 r1 rowids;
4 --
5 type t_varchar is table of the_table.msisdn_displayed%TYPE;
6 n1 t_varchar ;
7 --
8 cursor c1 is
9 select e.rowid rid, msisdn_displayed
10 from the_table e
11 where contract_id is not null;
12 BEGIN
13 OPEN c1;
14 WHILE TRUE LOOP
15 FETCH c1 BULK COLLECT INTO r1, n1 LIMIT 20000;
16 EXIT WHEN r1.COUNT = 0;
17 FORALL indx IN n1.FIRST .. n1.LAST
18 UPDATE the_table
19 SET msisdn_displayed = n1(indx)
20 WHERE ROWID = r1(indx);
21 COMMIT;
22 END LOOP;
23 CLOSE c1;
24 end;
25 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> SELECT * FROM the_table
2 /
MSISDN_DISPLAYED CONTRACT_ID
------------------------------------------------------------ -----------
abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz123 1
123abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz 2
test
3 rows selected.
-- update that does the same thing (accomplishes nothing):
SCOTT@orcl_11gR2> UPDATE the_table
2 SET msisdn_displayed = msisdn_displayed
3 WHERE contract_id IS NOT NULL
4 /
2 rows updated.
SCOTT@orcl_11gR2> SELECT * FROM the_table
2 /
MSISDN_DISPLAYED CONTRACT_ID
------------------------------------------------------------ -----------
abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz123 1
123abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz 2
test
3 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
| Re: Bulk Bind: Truncated Bind [message #489326 is a reply to message #489216] |
Fri, 14 January 2011 08:27 |
mape
Messages: 298
Registered: July 2006
Location: Slovakia
|
Senior Member |
|
|
Using type t_varchar is table of the_table.msisdn_displayed%TYPE
instead of type t_varchar is table of varchar2(50)
really helped me.
Thanks for help
Regards
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
