数值或值错误:原始变量长度太长 ORA-06512:在“SYS.UTL_RAW"处 [英] numeric or value error: raw variable length too long ORA-06512: at "SYS.UTL_RAW"
问题描述
从表中选择 BLOB(存储的 JSON 字符串)字段值时,我面临 原始变量长度太长 问题.
查询:
select utl_raw.cast_to_varchar2(dbms_lob.substr(TRANSACTION_DATA)) from PS_ISA_INB_PAYLOAD_LOG;
这是我用来将 JSON 对象插入到 BLOB 字段中的 SP:
创建或替换 PROCEDURE SDIX_TICK_LOG(VARCHAR2 中的组织名称, VARCHAR2 中的 TRANSACTION_TYPE, BLOB 中的 TRANSACTION_DATA, VARCHAR2 中的 TRANSACTION_STATUS) 作为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"000000000000000000000000,"ORGANIZATION_ID":" ","SUBINVENTORY_CODE":"000000000000000000","LOCATOR_ID":"","ISA_MATERIAL_GROUP":"","TAX_GROUP":"","CATEGORY_ID":"1950",","","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"00000"0"0"0000000000ISA_0"SUBINVENTORY"000000"","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 整数:= dbms_lob.lobmaxsize;l_dest_offset 整数:= 1;l_src_offset 整数:= 1;l_csid 整数 := dbms_lob.default_csid;l_ctx 整数:= dbms_lob.default_lang_ctx;l_warn 整数;开始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_警告);插入 PS_ISA_INB_PAYLOAD_LOG 值(ORGANIZATIONNAME、TRANSACTION_TYPE、l_blob、SYSDATE、TRANSACTION_STATUS);结束 SDIX_TICK_LOG;
你的问题出在这里: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"
I am facing raw variable length too long issue when select the BLOB(Stored JSON string) filed value from the table.
Query:
select utl_raw.cast_to_varchar2(dbms_lob.substr(TRANSACTION_DATA)) from PS_ISA_INB_PAYLOAD_LOG;
This is my SP i have used to insert the JSON object into BLOB Field:
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;
Your problem lies here: DBMS_LOB.SUBSTR()
DBMS_LOB
is using VARCHAR2
internally, and VARCHAR2
has limit of 2000 chars. Your blob has the size of 2829 chars, therefore it is too long to be processed by DBMS_LOB.SUBSTR()
at once.
You can test this by these commands:
Take only first 2000 chars from BLOB:
select utl_raw.cast_to_varchar2(dbms_lob.substr(TRANSACTION_DATA), 2000, 1) from PS_ISA_INB_PAYLOAD_LOG;
OK.
Take 2001 chars from BLOB:
select utl_raw.cast_to_varchar2(dbms_lob.substr(TRANSACTION_DATA, 2001, 1)) from PS_ISA_INB_PAYLOAD_LOG;
Error report - SQL Error:
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at line 1
06502.00000 - "PL/SQL: numeric or value error%s"
这篇关于数值或值错误:原始变量长度太长 ORA-06512:在“SYS.UTL_RAW"处的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!