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

One remark to add:

I just wrote about parameter markers in prepared statements. Perhaps I also should mention that if one needs more than one parameter value, one has to specify the corresponding amount of parameter markers (= question marks).

Example:
The prepared statement for
"SELECT * FROM table WHERE col1 IN (1, 2, 3, 4);" is
"SELECT * FROM table WHERE col1 IN (?, ?, ?, ?);"

The statement
"SELECT * FROM table WHERE col1 IN (?);" cannot be used!!!

This is why I cannot explain this Oracle error message to myself... if there were 1000+ '?'... but there is just ONE. Regards,

Johannes

Johannes Lebek wrote:

> 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