ORA-22813:操作数值超出系统限制 [英] ORA-22813: operand value exceeds system limits
问题描述
我创建的oracle对象有问题.存储过程的目标是退回飞机上包含的所有电线，或者将电线清单与另一根电线清单进行比较(过去的飞机电线配置).您将了解到，这可能会很大...我对对象表进行了第一次拍摄.我做出一条选择语句，并通过游标循环以在我的对象中插入值.问题出在我尝试此操作时:
I'm having an issue with an oracle object I created. The goal of the stored procedure is to either retreive all wires contained on an airplane or compare the list of wires with another one (past airplane wire configuration). You will understand that this can be quite big... I did a first shot with a table of objects. I make a select statement and loop through a cursor to insert values in my objects. The problem comes from when I try this:
OPEN P_CURSOR FOR
SELECT * FROM TABLE(CAST(GET_WIRES(P_ACTUAL_HAR_LIST, P_END_IDENT_TYPES) AS TBL_TP_WIRE_LIST));
我得到了ORA-22813:操作数值超出系统限制.因此，我开始在互联网上浏览，发现应该使用PIPELINED函数，并对其进行了尝试.但是，在PL/SQL上下文中不允许使用PIPELINED函数，因此这不适合我的问题，因为之后需要重新处理该对象.因此，我尝试了一个全局临时表，现在我遇到了性能问题.我还尝试与DBA联系，如果他可以增加PGA的大小(他现在做到了150mb)，但是我也遇到了同样的问题.也许它还不够大，但是如果它不起作用，我不希望他再增加它……只是您有一个主意，我有1 487 761:
I get the ORA-22813: operand value exceeds system limits. So, I started looking on the internet and I found that I should have used a PIPELINED function, which I tryied. But, the PIPELINED function is not allowed in a PL/SQL context, so that doesn't fit my problem, since I need to rework the object afterward. So, I tryied a Global Temporary Table and I have now performance issue. I also tryied to contact the DBA if he could increase the PGA size, which he did (it's now 150mb) but I have the same problem. Maybe it's still not big enough, but I don't want him to reincrease it if it's not going to work... Just so you have an idea, I have 1 487 761 of those:
CREATE OR REPLACE
TYPE TP_WIRE_LIST_ENTRY AS OBJECT (
ATA_NUM VARCHAR2(8 BYTE),
EFFECTIVITIES VARCHAR2(4000 BYTE),
WIRE_ID VARCHAR2(50 BYTE),
DRAWING_NO VARCHAR2(25 BYTE),
END_IDENT_1 VARCHAR2(25 BYTE),
END_IDENT_2 VARCHAR2(25 BYTE),
PIN_1 VARCHAR2(25 BYTE),
PIN_2 VARCHAR2(25 BYTE),
TERM_1 VARCHAR2(25 BYTE),
TERM_2 VARCHAR2(25 BYTE),
DES_LENGTH NUMBER(6,2),
TECH_PUBS_REMARKS VARCHAR2(500 BYTE),
WIRE_CODE VARCHAR2(25 BYTE),
W_CODE VARCHAR2(3 BYTE),
AWG VARCHAR2(3 BYTE),
COLOR VARCHAR2(4 BYTE),
GA VARCHAR2(6 BYTE),
END_IDENT_DESC_1 VARCHAR2(50 BYTE),
END_IDENT_DESC_2 VARCHAR2(50 BYTE),
TECH_PUBS_REMARKS_1 VARCHAR2(500 BYTE),
TECH_PUBS_REMARKS_2 VARCHAR2(500 BYTE),
IDENT_TYPE_ID_1 VARCHAR2(10 BYTE),
IDENT_TYPE_ID_2 VARCHAR2(10 BYTE),
BUS_NAME_1 VARCHAR2(20 BYTE),
PROPERTY_COLLECTION_ID_1 NUMBER(10,0),
BUS_NAME_2 VARCHAR2(20 BYTE),
PROPERTY_COLLECTION_ID_2 NUMBER(10,0),
X_1 NUMBER(8,3),
Y_1 NUMBER(8,3),
Z_1 NUMBER(8,3),
UOM_1 VARCHAR2(10 BYTE),
X_2 NUMBER(8,3),
Y_2 NUMBER(8,3),
Z_2 NUMBER(8,3),
UOM_2 VARCHAR2(10 BYTE),
COORD_CODE_1 VARCHAR2(10 BYTE),
COORD_CODE_2 VARCHAR2(10 BYTE),
UOM_DESC_1 VARCHAR2(50 BYTE),
UOM_DESC_2 VARCHAR2(50 BYTE),
PART_NO VARCHAR2(25 BYTE),
PART_DESC VARCHAR2(50 BYTE),
WIRE_GAUGE VARCHAR2(4 BYTE),
SPEC_REFERENCE VARCHAR2(60 BYTE),
MPS_NUM VARCHAR2(25 BYTE),
PPS_NUM VARCHAR2(25 BYTE),
INSERTION_TOOL VARCHAR2(25 BYTE),
EXTRACTION_TOOL VARCHAR2(25 BYTE)
);
/
我们使用Oracle9i企业版9.2.0.6.0版
We work with Oracle9i Enterprise Edition Release 9.2.0.6.0
最诚挚的问候，
推荐答案
只是想添加此花絮，以防其他人使用.当DBA将RESULT_CACHE_MODE设置为AUTO作为另一个过程的测试时，我们突然开始在PL/SQL中收到此错误.直到很久以后，我们才知道他这样做了.无论如何，将其重新设置为MANUAL即可解决此问题.
Just wanted to add this tidbit in case it helps someone else. We suddenly started getting this error in our PL/SQL when the DBA set RESULT_CACHE_MODE to AUTO as a test for another process. We didnt know he did this until much later. Anyway, setting it back to MANUAL fixed this problem.
这篇关于ORA-22813:操作数值超出系统限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
