数字或值错误：原始变量长度太长 ORA-06512："SYS.UTL_RAW"

Question

从表中选择 BLOB(存储的 JSON 字符串(归档值时，我面临原始变量长度太长的问题。

查询：

select utl_raw.cast_to_varchar2(dbms_lob.substr(TRANSACTION_DATA)) from PS_ISA_INB_PAYLOAD_LOG;

这是我用来将 JSON 对象插入 BLOB 字段的 SP：

create or replace PROCEDURE SDIX_TICK_LOG 
(
ORGANIZATIONNAME IN VARCHAR2 
, TRANSACTION_TYPE IN VARCHAR2 
, TRANSACTION_DATA IN BLOB 
, TRANSACTION_STATUS IN VARCHAR2 
) AS 
l_r   RAW(32767);
l_blob        blob;
l_clob        clob :='"ItemMasterTransfer":[{"ORACLE_UNIQUE_REC_ID":"123assd4434","CUSTOMER_ID":"PMC","ORGANIZATION_CODE":"BMftrdsM","ITEM":"696738","INVENTORY_ITEM_ID":"0000000000000000000000000000546","ORGANIZATION_ID":" ","SUBINVENTORY_CODE":"000000000000000000","LOCATOR_ID":" ","ISA_MATERIAL_GROUP":" ","TAX_GROUP":" ","CATEGORY_ID":"1956","NOUN":" ","MODIFIER":" ","MANUFACTURER_ID":" ","MFG_ITEM_ID":" ","UNIT_OF_MEASURE":"BOX","ITEM_TYPE":"P","STOCK_ENABLED_FLAG":"Y","INVENTORY_ITEM_STATUS_CODE":"A","LIST_PRICE_PER_UNIT":"0","FULL_LEAD_TIME":"0","MAX_MINMAX_QUANTITY":"10","MIN_MINMAX_QUANTITY":"10","SAFETY_LEVEL":"0","REPLENISH_TO_ORDER_FLAG":"N","UTILIZ_CD":"","CURRENCY_CD":"USD","DESCRIPTION":"","ATTRIBUTE1":" ","ATTRIBUTE2":" ","ATTRIBUTE3":" ","ATTRIBUTE4":" ","ATTRIBUTE5":" ","ATTRIBUTE6":" ","ATTRIBUTE7":" ","ATTRIBUTE8":" ","ATTRIBUTE9":" ","ATTRIBUTE10":" ","TRANSACTION_STATUS":" ","TRANS_STATUS_DESCRIPTION":" "},{"ORACLE_UNIQUE_REC_ID":"123assd4434","CUSTOMER_ID":"PMC","ORGANIZATION_CODE":"BMftrdsM","ITEM":"696738","INVENTORY_ITEM_ID":"0000000000000000000000000000546","ORGANIZATION_ID":" ","SUBINVENTORY_CODE":"000000000000000000","LOCATOR_ID":" ","ISA_MATERIAL_GROUP":" ","TAX_GROUP":" ","CATEGORY_ID":"1956","NOUN":" ","MODIFIER":" ","MANUFACTURER_ID":" ","MFG_ITEM_ID":" ","UNIT_OF_MEASURE":"BOX","ITEM_TYPE":"P","STOCK_ENABLED_FLAG":"Y","INVENTORY_ITEM_STATUS_CODE":"A","LIST_PRICE_PER_UNIT":"0","FULL_LEAD_TIME":"0","MAX_MINMAX_QUANTITY":"10","MIN_MINMAX_QUANTITY":"10","SAFETY_LEVEL":"0","REPLENISH_TO_ORDER_FLAG":"N","UTILIZ_CD":"","CURRENCY_CD":"USD","DESCRIPTION":"","ATTRIBUTE1":" ","ATTRIBUTE2":" ","ATTRIBUTE3":" ","ATTRIBUTE4":" ","ATTRIBUTE5":" ","ATTRIBUTE6":" ","ATTRIBUTE7":" ","ATTRIBUTE8":" ","ATTRIBUTE9":" ","ATTRIBUTE10":" ","TRANSACTION_STATUS":" ","TRANS_STATUS_DESCRIPTION":" "},{"ORACLE_UNIQUE_REC_ID":"123assd4434","CUSTOMER_ID":"PMC","ORGANIZATION_CODE":"BMftrdsM","ITEM":"696738","INVENTORY_ITEM_ID":"0000000000000000000000000000546","ORGANIZATION_ID":" ","SUBINVENTORY_CODE":"000000000000000000","LOCATOR_ID":" ","ISA_MATERIAL_GROUP":" ","TAX_GROUP":" ","CATEGORY_ID":"1956","NOUN":" ","MODIFIER":" ","MANUFACTURER_ID":" ","MFG_ITEM_ID":" ","UNIT_OF_MEASURE":"BOX","ITEM_TYPE":"P","STOCK_ENABLED_FLAG":"Y","INVENTORY_ITEM_STATUS_CODE":"A","LIST_PRICE_PER_UNIT":"0","FULL_LEAD_TIME":"0","MAX_MINMAX_QUANTITY":"10","MIN_MINMAX_QUANTITY":"10","SAFETY_LEVEL":"0","REPLENISH_TO_ORDER_FLAG":"N","UTILIZ_CD":"","CURRENCY_CD":"USD","DESCRIPTION":"","ATTRIBUTE1":" ","ATTRIBUTE2":" ","ATTRIBUTE3":" ","ATTRIBUTE4":" ","ATTRIBUTE5":" ","ATTRIBUTE6":" ","ATTRIBUTE7":" ","ATTRIBUTE8":" ","ATTRIBUTE9":" ","ATTRIBUTE10":" ","TRANSACTION_STATUS":" ","TRANS_STATUS_DESCRIPTION":" "}],"Organization":"PMC Biogenix","SharedSecret":"sTc1QowIu5Iy1Qt8iilnmQ==","TimeStamp":"09/28/2018 00:19:21","RowsSent":"1"}';
l_amt         integer := dbms_lob.lobmaxsize;
l_dest_offset integer := 1;
l_src_offset  integer := 1;
l_csid        integer := dbms_lob.default_csid;
l_ctx         integer := dbms_lob.default_lang_ctx;
l_warn        integer;
BEGIN
dbms_lob.createTemporary( l_blob, false );
dbms_lob.convertToBlob( l_blob,
l_clob,
l_amt,
l_dest_offset,
l_src_offset,
l_csid,
l_ctx,
l_warn );
INSERT INTO PS_ISA_INB_PAYLOAD_LOG Values(ORGANIZATIONNAME,TRANSACTION_TYPE,l_blob,SYSDATE,TRANSACTION_STATUS); 
END SDIX_TICK_LOG;

Answer 1

你的问题就在这里：DBMS_LOB.SUBSTR()

DBMS_LOB在内部使用VARCHAR2，VARCHAR2限制为2000个字符。Blob 的大小为2829个字符，因此太长，无法由DBMS_LOB.SUBSTR()一次处理。

您可以通过以下命令对此进行测试：

仅从 BLOB 中获取前2000个字符：

select utl_raw.cast_to_varchar2(dbms_lob.substr(TRANSACTION_DATA), 2000, 1) from PS_ISA_INB_PAYLOAD_LOG;

还行。

从 BLOB 中获取2001个字符：

select utl_raw.cast_to_varchar2(dbms_lob.substr(TRANSACTION_DATA, 2001, 1)) from PS_ISA_INB_PAYLOAD_LOG;

错误报告 - SQL 错误：

ORA-06502：PL/SQL：数字或值错误：原始变量长度太长

ORA-06512：在第 1 行

06502.00000 - "PL/SQL： 数字或值错误%s">

Answer 2

Is that possible to select entire BLOB Field value?

基本上没有。你需要一个类似于你已经描述的PL/SQL函数，除了这次，它将变成BLOB-to-CLOB。你可以从SQL调用它，如果它返回CLOB或VARCHAR2(4000(。

作为旁注，我质疑你为什么要采用JSON，即字符数据，并将其存储为BLOB，然后想要取回字符数据。为什么不首先将其存储为 CLOB？