techxplore blog
13Mar

Oracle PL/SQL Developer ORA-01795 maximum number of expressions in a list is 1000 Error

Database query and PL/SQL programming are a daily grind for PL/SQL developers takes care of total software development process. There are times that database information needs to be extracted from a table for a report or a particular application software module. This is the scenario common to all who’s familiar on working with Oracle database PL/SQL programming.

There was this instance when doing a query in PL/SQL Developer have resulted in an “ORA-01795: maximum number of expressions in a list is 1000” error. The query was a simple one with more than a thousand values in ‘IN’ clause of a select statement.

PL/SQL Developer select statement IN clause expression limit error

PL/SQL Developer select statement IN clause expression limit error

The select statement looks like the following:

select * from transaction_table
where trx_number in (
'410000',
'410001',
'410002',
'410003',
'410004',
'410005',
'410006',
'410007',
.
.
.
'412994',
'412995',
'412996',
'412997',
'412998',
'412999')

The values in the ‘IN’ clause was more than a thousand and it caused the error ORA-01795. This limit was set in some setting in the database which only the database administrator have access. Thus, to execute the query without DBA’s help needs a work around solution.

One solution which worked fine is to limit the number of value items on the ‘IN’ clause not more than 1000.

Revised error free SQL statement is as follows:

select * from transaction_table
where trx_number in (
'410000',
'410001',
'410002',
'410003',
'410004',
'410005',
'410006',
'410007',
.
.
.
'411998',
'411999')
or trx_number in (
'412000'
'412001',
'412002',
'412003',
'412004',
'412005',
'412006'
.
.
.
'412998',
'412999')

Splitting the values into two groups of 1000 and using an ‘OR’ clause in the select statement have solved the ORA-01795 maximum number of expressions in a list is 1000 Error. There might be some other solution, but this database tip worked just okay for Oracle database with PL/SQL Developer integrated development environment software.

Leave a Reply