PL / SQL和SQL中的LONG ROW有所不同。
在SQL中:
“可变长度高达2千兆字节的原始二进制数据。”
在PL / SQL中:
“您使用LONG RAW数据类型来存储二进制数据或字节字符串.LONG RAW数据类似于LONG数据,但PL / SQL不会解释LONG RAW数据.LONG RAW值的最大大小为32760字节。”
见下面的例子:
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;
好的,现在我在T表中添加了1行,并在X列中放入了大约90K大小的图像(使用PL / SQL Developer工具)。
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
好的,我们现在重新创建表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
所以LONG ROW应该转换为BLOB,这是处理它的最佳方式。