Exporting Schema DDL

Toad for Oracle & DevOps Toolkit
1

Message from: yrichard_062

Hi ! I am trying to export a schema DDL from Toad and I get ORA-01795 error:
maximum number of expressions in a list is 1000 Cause: More than 254 columns or
expressions were specified in a list. Action: Remove some of the expressions
from the list. Any idea ? I am using Toad for Oracle Base, version 10.6.0.42.
The database is Oracle 10g Express. Thanks ! Yannick

Historical Messages

Author: yrichard_062
Date: Mon Oct 18 11:01:04 PDT 2010
Hi ! I am trying to export a schema DDL from Toad and I get ORA-01795 error:
maximum number of expressions in a list is 1000 Cause: More than 254 columns or
expressions were specified in a list. Action: Remove some of the expressions
from the list. Any idea ? I am using Toad for Oracle Base, version 10.6.0.42.
The database is Oracle 10g Express. Thanks ! Yannick
__

2

You have too many items in the in clause.

Where x in (1,2,3,4,5,6,…) you can only give a list of 254 items if you specify
them.

The way around this is a subquery

Where x in (select column_1 from table where column_2 = value)

That list size is not capped.

Chris

3

Evening Yannick,

Hi ! I am trying to export a schema DDL from Toad and I get ORA-01795
error: maximum number of expressions in a list is 1000 Cause: More than
254 columns or expressions were specified in a list. Action: Remove some
of the expressions from the list. Any idea ? I am using Toad for Oracle
Base, version 10.6.0.42. The database is Oracle 10g Express. Thanks !
What is the output if yo spool the SQL to screen? (Database-Spool
Output->To Screen). That will show the command that is trying to be
executed and which is failing.

I'd be pretty certain that the problem is most likely an IN statement
which has somehow managed to get over 1000 members in the in list.

Oracle allows up to 1000 literals in an in-list, however, if the in-list
can be replaced by a SQL Statement that provides the in-list, then the
limit is removed, so:

Select
From

Where IN (1,2,3, .... 999,1000,1001...);

Will fail, whereas:

Select
From

Where IN (Select more_than_1000_values from another table);

Will work.

Cheers,
Norm. [TeamT]