ORA-01795 although there is just 1 parameter used!

From: Johannes Lebek <reply-to-newsgroup_at_domain.com>
Date: Tue, 06 Jul 2004 18:08:07 +0200
Message-ID: <cceip6$1k3e$1@sp15en20.hursley.ibm.com>

Hi @ll,

I got the following error on my Oracle 9.2 machine:

java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000

However, the statement in question is:

SELECT col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20, col21, col22 FROM table START WITH col1 IN (?) CONNECT BY (PRIOR col1 = col8)

The original statement definitely does not have too many parameters. Is the problem caused by the RECURSION?

I've heard that a subselect like SELECT * FROM table WHERE col1 IN (SELECT col2 FROM table) is not affected by the 1000-expressions-limitation. Is this true? If yes, why does this apply to subselects but not to recursion?

Any help is greatly appreciated!
Thanks in advance,

Johannes Received on Tue Jul 06 2004 - 11:08:07 CDT