如何解决"ORA-00939:函数的参数过多"?错误? [英] How to resolve "ORA-00939: too many arguments for function " error?
问题描述
我有以下查询: 首先,我创建了一个类型:
I have the following query: Firstly I have created a type:
CREATE OR REPLACE TYPE array_id IS VARRAY(50000) OF VARCHAR2(50);
我正在执行以下查询:
SELECT B.ID,
LISTAGG (A.NAME,', ') WITHIN GROUP (ORDER BY A.NAME),
Assignednames,
B.IsManager
FROM LOCATION A
INNER JOIN EMPLOYEES B
ON A.LOCATIONID = B.LOCATIONID
WHERE B.ID IN (SELECT * FROM table(array_id('244410','PERF507' )) )
GROUP BY B.ID,
B.IsManager
现在,当我执行上述查询时,当array_id('244410','PERF507')内的元素数量较少时,它可以正常工作.但是在实际环境中,我有大约15,000条记录;并给出以下错误:
Now, when I am executing the abovequery, it's working fine when the number of elements inside array_id('244410','PERF507') is less.But in real environment I have around 15K records; and its giving the below error:
ORA-00939: too many arguments for function
现在,我尝试更改类型: 创建或替换类型array_id为VARCHAR2(50)的VARRAY(50000); 创建或替换类型array_id为VARCHAR2(20)的VARRAY(500000);和其他组合,但仍然出现相同的错误.
Now, I tried changing the type: CREATE OR REPLACE TYPE array_id IS VARRAY(50000) OF VARCHAR2(50); to CREATE OR REPLACE TYPE array_id IS VARRAY(500000) OF VARCHAR2(20); and other combinations but still getting the same error.
我确定是由于尺寸过大,但无法获得正确的计算结果! 任何帮助将不胜感激. 预先感谢.
I am sure its due to the sizing, but not able to get the correct calculations! Any help will be highly appreciated. Thanks in advance.
推荐答案
我相信在Oracle DB中,任何包含1000个以上条目的插入都会抛出ORA-00939.我是通过让脚本从Oracle Spatial DB上的大量生成多边形的方式中发现这一点的.
I believe in Oracle DB, any insertion with more than 1,000 entries will throw ORA-00939. I found this by having a script generate polygons from lots on Oracle Spatial DB.
您也许可以使用EXISTS
来解决所需的内容.
You may be able to use EXISTS
to solve what you're looking for.
这篇关于如何解决"ORA-00939:函数的参数过多"?错误?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!