如何解决"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屋！