CODEWITHSUNDEEP
oracle-database
user2948533
user2948533

Reputation: 1163

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);

I am executing the below query:

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

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

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.

Upvotes: 3

Views: 10284

Answers (1)

AER
AER

Reputation: 1531

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.

You may be able to use EXISTS to solve what you're looking for.

Upvotes: 0

Related Questions