Re: ORA-01795 although there is just 1 parameter used!

From: Johannes Lebek <reply-to-newsgroup_at_domain.com>
Date: Wed, 07 Jul 2004 10:05:38 +0200
Message-ID: <ccgash$dc4$1@sp15en20.hursley.ibm.com>

Hi Sybrand,

thank you for your reply.

The CONNECT BY clause is the part of the statement that is responsible for recursion. It causes Oracle to memorize the value of column "col1" (PRIOR col1) and compares it to the current value of column "col8". Using this you can retrieve result sets that are result trees.

The question mark is used for prepared statements in JDBC as a placeholder for one specific value. The SQL statement cache can be used more efficient if the SQL statement is generic.

Example:
"SELECT col1 FROM table WHERE col2 = 5;" is a different statement as
"SELECT col1 FROM table WHERE col2 = 3;"

The SQL statement cache has to store two access paths, one for each statement. However, these paths are the same. Prepared statements in JDBC are also stored in the SQL statement cache of Oracle. But in this example, Oracle has to store one single access path only:

"SELECT col1 FROM table WHERE col2 = ?;"

In Java it looks like this:

----8<---------------  Java code snippet ------------------------------

PreparedStatement stmt = myConnection.prepareStatement("SELECT col1 FROM table WHERE col2 = ?");
int position = 1;
int value = 5;
stmt.setInt(position, value);
ResultSet result = stmt.executeQuery();

// do something with the result set

value = 3;
stmt.setInt(position, value);
result = stmt.executeQuery();

----8<-----------------------------------------------------------------

I cannot tell what is the actual value of the parameter. But I can say, it is one single value -- not 1000+.

Regards,

Johannes

Sybrand Bakker wrote:

> On Tue, 06 Jul 2004 18:08:07 +0200, Johannes Lebek
> <reply-to-newsgroup_at_domain.com> wrote:
> 
> 

>>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 don't see recursion anywhere. I also don't see the contents of the
> parameter referred to by the (?) construct. So: who can tell.
> 
> 

>>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?

> 
> Yes.
> 
> If yes, why does this apply to subselects but not to 
> 

>>recursion?
>>

> 
> explain what your definition of recursion is.
> 
> 
> 

>>Any help is greatly appreciated!
>>Thanks in advance,
>>
>>Johannes

> 
> 
> --
> Sybrand Bakker, Senior Oracle DBA

Received on Wed Jul 07 2004 - 03:05:38 CDT