ORA - 06502：PL/SQL：数字或值错误：批量绑定：绑定被截断
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22327222/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA - 06502:PL/SQL : Numeric or Value Error:Bulk Bind : truncated Bind
提问by 7783
Here is my stored procedure:
这是我的存储过程：
CREATE OR REPLACE PACKAGE BS_SAMPLES AS
TYPE type_memo_raw IS TABLE OF LONG RAW;
PROCEDURE MIGRATE_MEMO_TO_MEMO_CLOB(RMEMO OUT type_memo_raw);
END BS_SAMPLES;
CREATE OR REPLACE PACKAGE BODY BS_SAMPLES AS
PROCEDURE MIGRATE_MEMO_TO_MEMO_CLOB (RMEMO OUT type_memo_raw)
AS
ls_memo_raw type_memo_raw;
BEGIN
SELECT MR.MEMO_DATA BULK COLLECT
INTO ls_memo_raw
FROM V3_TO_V4_MEMO A, MEMO_RTF MR
WHERE A.MEMO_ID = MR.MEMO_ID;
RMEMO :=ls_memo_raw;
End MIGRATE_MEMO_TO_MEMO_CLOB;
END BS_SAMPLES;
When I try to execute the Procedure, I am getting the following error:
当我尝试执行程序时，出现以下错误：
ORA - 06502:PL/SQL : Numeric or Value Error:Bulk Bind : truncated Bind
ORA - 06502:PL/SQL : Numeric or Value Error:Bulk Bind : truncated Bind
Using Oracle version: Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
使用 Oracle 版本：Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
Table Memo_rtf
表格备忘录_rtf
Name Null Type
MEMO_ID NOT NULL NUMBER(10)
MEMO_DATA LONG RAW()
Table V3_TO_V4_MEMO
表 V3_TO_V4_MEMO
Name Null Type
MEMO_ID NUMBER(10)
回答by Dmitry Nikiforov
There is a difference between LONG ROW in PL/SQL and SQL.
PL/SQL 和 SQL 中的 LONG ROW 之间存在差异。
In SQL:
在 SQL 中：
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm
"Raw binary data of variable length up to 2 gigabytes."
“可变长度的原始二进制数据，最大可达 2 GB。”
In PL/SQL:
在 PL/SQL 中：
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/datatypes.htm#i10924
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/datatypes.htm#i10924
" You use the LONG RAW datatype to store binary data or byte strings. LONG RAW data is like LONG data, except that LONG RAW data is not interpreted by PL/SQL. The maximum size of a LONG RAW value is 32760bytes. "
“您使用 LONG RAW 数据类型来存储二进制数据或字节字符串。LONG RAW 数据与 LONG 数据类似，只是 LONG RAW 数据不被 PL/SQL 解释。LONGRAW 值的最大大小为32760字节。”
See example below:
请参阅下面的示例：
SQL> desc t
Имя Пусто? Тип
----------------------------------------- -------- ----------------------------
X LONG RAW
CREATE OR REPLACE PACKAGE BS_SAMPLES AS
TYPE type_memo_raw IS TABLE OF LONG RAW;
PROCEDURE MIGRATE_MEMO_TO_MEMO_CLOB(RMEMO OUT type_memo_raw);
END BS_SAMPLES;
CREATE OR REPLACE PACKAGE BODY BS_SAMPLES AS
PROCEDURE MIGRATE_MEMO_TO_MEMO_CLOB (RMEMO OUT type_memo_raw)
AS
ls_memo_raw type_memo_raw;
BEGIN
SELECT t.x BULK COLLECT
INTO ls_memo_raw
FROM t;
RMEMO := ls_memo_raw;
End MIGRATE_MEMO_TO_MEMO_CLOB;
END BS_SAMPLES;
OK, now I added 1 row to T table and put into X column the image (using PL/SQL Developer tool) what has about 90K size.
好的，现在我向 T 表添加了 1 行，并将大约 90K 大小的图像（使用 PL/SQL Developer 工具）放入 X 列。
SQL> declare
2 a BS_SAMPLES.type_memo_raw;
3 begin
4 BS_SAMPLES.MIGRATE_MEMO_TO_MEMO_CLOB(a);
5 end;
6 /
declare
*
error in line 1:
ORA-06502: PL/SQL: : Bulk Bind: Truncated Bind
ORA-06512: at "SCOTT.BS_SAMPLES", line 7
ORA-06512: at line 4
SQL> alter table t modify (x blob);
SQL> select dbms_lob.getlength(x) a from t;
a
------------------------------
90025
Ok, let's now re-create table T and add the image with about 29K size - all will work:
好的，现在让我们重新创建表 T 并添加大约 29K 大小的图像 - 一切都会起作用：
SQL> declare
2 a BS_SAMPLES.type_memo_raw;
3 begin
4 BS_SAMPLES.MIGRATE_MEMO_TO_MEMO_CLOB(a);
5 end;
6 /
PL/SQL procedure completed.
SQL> alter table t modify (x blob);
SQL> select dbms_lob.getlength(x) a from t;
a
------------------------------
25554
So LONG ROW should be converted to BLOB and this is the best way to handle it.
所以 LONG ROW 应该转换为 BLOB，这是处理它的最佳方法。
