oracle ORA-22813: 操作数值超出系统限制
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7597053/
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-22813: operand value exceeds system limits
提问by Sylvain Cloutier
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:
我创建的 oracle 对象有问题。存储过程的目标是检索飞机上包含的所有电线或将电线列表与另一个电线列表（过去的飞机电线配置）进行比较。你会明白这可能会很大......我用一张物体表做了第一次拍摄。我做一个选择语句并循环遍历一个游标以在我的对象中插入值。问题出在我尝试这个时：
OPEN P_CURSOR FOR
SELECT * FROM TABLE(CAST(GET_WIRES(P_ACTUAL_HAR_LIST, P_END_IDENT_TYPES) AS TBL_TP_WIRE_LIST));
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:
我得到 ORA-22813: 操作数值超出系统限制。所以，我开始在互联网上寻找，我发现我应该使用 PIPELINED 函数，我尝试了它。但是，PL/SQL 上下文中不允许使用 PIPELINED 函数，因此这不适合我的问题，因为我需要在之后重新处理对象。所以，我尝试了一个全局临时表，现在我遇到了性能问题。如果他可以增加 PGA 大小，我也尝试联系 DBA，他确实这样做了（现在是 150mb），但我遇到了同样的问题。也许它仍然不够大，但如果它不起作用，我不希望他重新增加它......所以你有一个想法，我有 1 487 761 个：
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)
);
/
We work with Oracle9i Enterprise Edition Release 9.2.0.6.0
我们使用 Oracle9i Enterprise Edition Release 9.2.0.6.0
Best regards,
此致，
回答by Bart K
I ran into a similar situation in a data warehousing environment at a past job.
我在过去的工作中在数据仓库环境中遇到过类似的情况。
Did your DBA increate pga_aggregate_target or _pga_max_size or both? Keep in mind that when the DBA increases pga_aggregate_target to 150MB that doesn't mean that your session would be able to grab all of that 150MB. Oracle will only allow a given session to take a small fraction of that space. Increasing pga_aggregate_target from 150MB to 200MB will only give your session a few extra megs of memory to play with for in-memory operations IIRC.
您的 DBA 是否创建了 pga_aggregate_target 或 _pga_max_size 或两者？请记住，当 DBA 将 pga_aggregate_target 增加到 150MB 时，并不意味着您的会话将能够获取所有这 150MB。Oracle 将只允许给定会话占用该空间的一小部分。将 pga_aggregate_target 从 150MB 增加到 200MB 只会给你的会话额外的几兆内存用于内存操作 IIRC。
If your DB server has more available memory, you'll have to have your DBA increase both pga_aggregate_target and _pga_max_size, otherwise you'll have to look at using a GTT or creating a scratch table to dump your data into and then work on it from there.
如果你的数据库服务器有更多的可用内存，你必须让你的 DBA 增加 pga_aggregate_target 和 _pga_max_size，否则你将不得不考虑使用 GTT 或创建一个临时表来转储你的数据，然后从那里。
回答by U Castillo
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.
只是想添加这个花絮，以防它对其他人有帮助。当 DBA 将 RESULT_CACHE_MODE 设置为 AUTO 作为对另一个进程的测试时，我们突然开始在我们的 PL/SQL 中收到此错误。我们直到很久以后才知道他这样做了。无论如何，将其设置回 MANUAL 解决了这个问题。
