3

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.

user2948533
  • 1,143
  • 3
  • 13
  • 32
  • Where is `array_id` defined, and is this query really what is being run? – Tim Biegeleisen Nov 01 '16 at 08:36
  • Yes, it's the same query.array_id is defined for the same client and its working absolutely fine when the number of elements inside is less, as mentioned. – user2948533 Nov 01 '16 at 08:38
  • I don't think you can use `array_id`, your custom type, in this way in a regular query. You might have to do this from dynamic SQL. – Tim Biegeleisen Nov 01 '16 at 08:46
  • As I said it's working fine for less number! – user2948533 Nov 01 '16 at 08:48
  • That it doesn't work with 2 or more entries means that it's not behaving as expected. So you should rethink your approach. Or, stick with your current query which does not work. – Tim Biegeleisen Nov 01 '16 at 08:50
  • Not 2 or more..I think its working upto 1000..I have given 2 as example! – user2948533 Nov 01 '16 at 08:56
  • 1
    I imagine it's related to [the restriction on expression lists](http://stackoverflow.com/a/19003103/266304). The collection can be bigger but you can't create/populate it with a list of more than 1000 items. So you need to look at how you're creating that list - comma-separating 15K values clearly isn't the way to go. Where are the values coming from, and where is the real query called from (e.g. over JDBC)? – Alex Poole Nov 01 '16 at 09:09

1 Answers1

0

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.

AER
  • 1,549
  • 19
  • 37