0

I am using following environment :

  1. Oracle 19.0.0.0
  2. Windows 10
  3. ojdbc8.jar

Using Oracle19c as database with DBeaverhttps://dbeaver.io/ SQL client (which internally uses odjbc8.jar driver ) where i am executing below SQL query

select * from Student
  WHERE RollNo=1 OR RollNo=2 OR RollNo=3 OR RollNo=4 OR .... OR RollNo=19720

Above query is failed with below exception:

SQL Error [17410] [08000]: No more data to read from socket
Caused by: java.sql.SQLRecoverableException: No more data to read from socket
at oracle.jdbc.driver.T4CMAREngineNIO.prepareForReading(T4CMAREngineNIO.java:119)
at oracle.jdbc.driver.T4CMAREngineNIO.unmarshalUB1(T4CMAREngineNIO.java:534)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:485)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:213)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:37)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:733)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1737)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1692)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:300)

Can you please help me here ?

  1. There is some limitation introduced on Logical operator in oracle19c ?
  2. Or i have to do some settings (database level settings) to do work this query (I know changing the query to IN clause solve my problem).

Thanks in advance.

MT0
  • 143,790
  • 11
  • 59
  • 117
Developer
  • 150
  • 2
  • 12
  • 1
    You could just pass a Java array to an Oracle collection and then check if the value is contained in the array (rather than dynamically generating statements with tens-of-thonuands of `OR` conditions). Some examples: [[1](https://stackoverflow.com/a/37161584/1509264)] [[2](https://stackoverflow.com/a/34699771/1509264)] [[3](https://stackoverflow.com/a/47711778/1509264)] – MT0 May 14 '21 at 13:27

2 Answers2

1

It looks like you can either use a BETWEEN clause:

select * from Student
  WHERE RollNo BETWEEN 1 AND 19720

or use the equivalent >= / <= pair:

select * from Student
  WHERE RollNo >= 1 AND
        RollNo <= 19720
Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
1

1. There is some limitation introduced on Logical operator in oracle19c ?

No, but "Logical Database Limits" states:

Note:

The limit on how long a SQL statement can be depends on many factors, including database configuration, disk space, and memory

2. Or i have to do some settings (database level settings) to do work this query (I know changing the query to IN clause solve my problem).

Even if some changes(like increasing memory, etc) can allow to increase a number of your ORs, it still will not be reliable solution. So it's much better to use collections (or global temporary tables in case of problems with JPPD (join predication push-down)).

Btw, simple IN condition (x in (1,2,3...1000)) has a limitation in 1000 expressions (ORA-01795: maximum number of expressions in a list is 1000), but you can use tuples (0,x) in ((0,1),(0,2),(0,3)...(0,1000),...(0,10000))

Sayan Malakshinov
  • 8,492
  • 1
  • 19
  • 27
  • @ Sayan Malakshinov : If i divide the query into multiple IN Clause e.g. select * from Student WHERE RollNo IN (1....1000) OR RollNo IN (1001....2000) OR RollNo IN (20001....21000) OR RollNo IN (21001....21000) it is failing with SQL Error [17410] [08000]: No more data to read from socket whereas if i reduce one IN clause it is working properly. I am suspecting some timeout issue or query length. Can you please help me here? – Developer May 20 '21 at 15:35