Sorry, Sybrand, the problem I posted, has been turned out as looking on
a "edited" SQL statement.
Thanks for your time.
Johannes
Sybrand Bakker wrote:
> Comments embedded
>
> On Wed, 07 Jul 2004 10:05:38 +0200, Johannes Lebek
> <reply-to-newsgroup_at_domain.com> 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 = ?;"
>>
>
>
>
> You really don't have to explain this to me. I am quite aware of this
> issue, especially as all of the developers I have to deal with NEVER
> use prepared statements!!!!!!
>
>
>>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+.
>
>
> Doesn't sound logical. You are sure 'table' is a table, and not a
> view?
>
>
>
>
>
>>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
>
>
> --
> Sybrand Bakker, Senior Oracle DBA
Received on Wed Jul 07 2004 - 12:43:05 CDT
