Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    1

    Unanswered: JDBC select with vector bind var?

    I need to do a select that looks like this via JDBC:

    select <colList> from my_table
    where id in ( v1, v2, v3, ..., vN );

    The values for the in clause are arbitrary and could be so many that I worry the length of the resultant SQL string could exceed Oracle's limit of 1000. I can't therefore naively create the appropriate SQL stament and send it to Oracle.

    How can I run such a query using JDBC so that I can provide an array of values for the in clause? I've seen batching for inserts, but nothing yet that would help me run this query in JDBC.

    Any help is greatly appreciated.
    thanks...
    H

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: JDBC select with vector bind var?

    Originally posted by hsilberman
    I need to do a select that looks like this via JDBC:

    select <colList> from my_table
    where id in ( v1, v2, v3, ..., vN );

    The values for the in clause are arbitrary and could be so many that I worry the length of the resultant SQL string could exceed Oracle's limit of 1000. I can't therefore naively create the appropriate SQL stament and send it to Oracle.

    How can I run such a query using JDBC so that I can provide an array of values for the in clause? I've seen batching for inserts, but nothing yet that would help me run this query in JDBC.

    Any help is greatly appreciated.
    thanks...
    H
    Two solutions that come to mind:

    1) create a temp table; insert the condition values into that tabel; modify your statement to read "SELECT ... WHERE ID IN (SELECT ID FROM TEMP)"

    2) create a stored procedure that accepts a collection of values; within it you could use the solution above or just run the original select as many times as you need and return the combined results...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •