[vwnc] Oracle and IN-clause binding?

Hi,

I'm trying to bind a collection of values to an IN-clause for Oracle.

This is what I do:


| connection result |
connection := OracleConnection new.
connection username: 'user'.
connection environment: 'db'.
connection connect: 'pswd'.
[session := connection getSession.
[session prepare: 'select * from my_table where primarykey in :1'.
session bindInput: #((1 2 3 4 5)).
session execute.
result := session answer upToEnd] ensure: [session disconnect].
result
] ensure: [connection disconnect]

I have a table called 'my_table' and it contains 5 rows. The primary keys of the rows are 1,2,3,4,5.

When I inspect the expression above, it only returns one row even though there are five rows in the table with matching primary keys.

What is the way to bind an array of values to an IN-clause?

Thanks,
Mark

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Hello Mark,

you run into a speciallity of Oracle array binding.
Oracle performs the query with each of the items in the array.
Thus it will issue five queries

    select * from my_table where primarykey in (1)
    select * from my_table where primarykey in (2)
    select * from my_table where primarykey in (3)
    select * from my_table where primarykey in (4)
    select * from my_table where primarykey in (5)

and answers the result of the last query or the first query.
I am not exactly shure which result is passed back.
Most likely you got the row with the ID 1 or 5.
Array binding is quite helpfull in insert/update/delete or
PL/SQL statements but not in select statements.

You may generically create an in clause with enough binding values like

   select * from my_table where primarykey in (:1, :2, :3, :4, :5)

This should work in your example. This should then look like

| connection result session |
connection := OracleConnection new.
connection username: 'user'.
connection environment: 'db'.
connection connect: 'pswd'.
[session := connection getSession.
[session prepare: 'select * from my_table where primarykey in (:1, :2, :3,
:4, :5)'.
session bindInput: #(1 2 3 4 5).
session execute.
result := session answer upToEnd] ensure: [session disconnect].
result
] ensure: [connection disconnect]

I never digged Oracle to figure out how to pass arrays into in clauses.
Maybe somebody managed to do so and can enlight us.

hope this helps

     Clemens Hoffmann

Original Message:
-----------------
From: Mark Plas [hidden email]
Date: Mon, 15 Sep 2008 17:06:05 +0200
To: [hidden email]
Subject: [vwnc] Oracle and IN-clause binding?


Hi,

I'm trying to bind a collection of values to an IN-clause for Oracle.

This is what I do:


| connection result |
connection := OracleConnection new.
connection username: 'user'.
connection environment: 'db'.
connection connect: 'pswd'.
[session := connection getSession.
[session prepare: 'select * from my_table where primarykey in :1'.
session bindInput: #((1 2 3 4 5)).
session execute.
result := session answer upToEnd] ensure: [session disconnect].
result
] ensure: [connection disconnect]

I have a table called 'my_table' and it contains 5 rows. The primary keys
of the rows are 1,2,3,4,5.

When I inspect the expression above, it only returns one row even though
there are five rows in the table with matching primary keys.

What is the way to bind an array of values to an IN-clause?

Thanks,
Mark

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc


--------------------------------------------------------------------
mail2web - Check your email from the web at
http://link.mail2web.com/mail2web



_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Hi Clemens,

Thanks for your answer.

Currently we generate our SQL statements and in case of an IN-clause we put the values literally into the SQL query. In order to reuse a prepared query (and avoid unnecessary parsing) I was looking to see whether it was possible to use a statement like 'select * from table where id in :1', prepare it once, and then execute it repeatedly while supplying an array for the :1, but it didn't seem to work. I could do it the way you suggest, but just having a short sql statement like the one I mention looks more interesting.

If somebody would know how to do this (perhaps it's not even possible?), I'd be happy to hear from them.

Best regards,
Mark

-----Original Message-----
From: [hidden email] [mailto:[hidden email]]
Sent: dinsdag 16 september 2008 8:47
To: Mark Plas; [hidden email]
Subject: RE: [vwnc] Oracle and IN-clause binding?

Hello Mark,

you run into a speciallity of Oracle array binding.
Oracle performs the query with each of the items in the array.
Thus it will issue five queries

    select * from my_table where primarykey in (1)
    select * from my_table where primarykey in (2)
    select * from my_table where primarykey in (3)
    select * from my_table where primarykey in (4)
    select * from my_table where primarykey in (5)

and answers the result of the last query or the first query.
I am not exactly shure which result is passed back.
Most likely you got the row with the ID 1 or 5.
Array binding is quite helpfull in insert/update/delete or
PL/SQL statements but not in select statements.

You may generically create an in clause with enough binding values like

   select * from my_table where primarykey in (:1, :2, :3, :4, :5)

This should work in your example. This should then look like

| connection result session |
connection := OracleConnection new.
connection username: 'user'.
connection environment: 'db'.
connection connect: 'pswd'.
[session := connection getSession.
[session prepare: 'select * from my_table where primarykey in (:1, :2, :3,
:4, :5)'.
session bindInput: #(1 2 3 4 5).
session execute.
result := session answer upToEnd] ensure: [session disconnect].
result
] ensure: [connection disconnect]

I never digged Oracle to figure out how to pass arrays into in clauses.
Maybe somebody managed to do so and can enlight us.

hope this helps

     Clemens Hoffmann

Original Message:
-----------------
From: Mark Plas [hidden email]
Date: Mon, 15 Sep 2008 17:06:05 +0200
To: [hidden email]
Subject: [vwnc] Oracle and IN-clause binding?


Hi,

I'm trying to bind a collection of values to an IN-clause for Oracle.

This is what I do:


| connection result |
connection := OracleConnection new.
connection username: 'user'.
connection environment: 'db'.
connection connect: 'pswd'.
[session := connection getSession.
[session prepare: 'select * from my_table where primarykey in :1'.
session bindInput: #((1 2 3 4 5)).
session execute.
result := session answer upToEnd] ensure: [session disconnect].
result
] ensure: [connection disconnect]

I have a table called 'my_table' and it contains 5 rows. The primary keys
of the rows are 1,2,3,4,5.

When I inspect the expression above, it only returns one row even though
there are five rows in the table with matching primary keys.

What is the way to bind an array of values to an IN-clause?

Thanks,
Mark

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc


--------------------------------------------------------------------
mail2web - Check your email from the web at
http://link.mail2web.com/mail2web



_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Hi Mark,

AFAIK, it is not possible to use array binding in that way. As Clemens
wrote, this is a limitation of Oracle's OCI.

Enumerating the values as literals in an IN-clause is limited, you can't put
more than 999 values into such a clause. The same limitation holds if you
use an enumeration of binding variables instead. Also, the overhead for
parsing each statement may cause severe scaling problems for your Oracle
server if the number of concurrent sessions grows to more than just a few.
The threshold where it breaks down depends on the hardware, the number of
statements per second, and the complexity of the statements, but sooner or
later you will notice performance problems.

Depending on the number of values which you have to use in the IN-clause,
there are several possible solutions:

1. If the number of values is small and limited by some upper bound, you
might use a statement like the one Clemens described. Use as many bind
variables as needed for the maximum number of values, and fill the bind
variables with nils when you have less values than bind variables.

2. If it is always the same type of query against the same table or view,
you could encapsulate the query in a PL/SQL procedure which accepts an array
of values (a table type in PL/SQL) as input parameter and has a table as
output parameter. This has limitations, as the array for the output
parameter can't grow beyond a certain size (16 MB or something like that).
You have to generate a PL/SQL procedure call instead of the SELECT. A
variant would be to implement a pipelined table function, and select from
the result of a function call casted as a table (see #3 below).

3. The sophisticated way to do this is to write a PL/SQL package with:
- a procedure which stores an array of values in a package variable of type
"table of number" (or whatever the type of the primary keys is),
- a pipelined table function which returns the contents of the package
variable, with an interface similar to
    function get_values() return number_set_type pipelined;
where number_set_type has to be declared as a "table of number" type (if I
recall this correctly, there are some details which one has to get right).
Queries will then have two steps:
- Call the procedure to store the parameters in the package variable,
- Retrieve the result using a query with a statement like

    SELECT * FROM my_table WHERE primarykey IN
        (SELECT * FROM  TABLE (MyPackage.get_values ()))

where get_values is the pipelined table function returning the contents of
the package variable.
The details are left as an excercise for the reader. ;-)

Solution #3 works for an arbitrary number of values, it's fast even with
large numbers of values, and it scales well because the statements always
have the same form, such that Oracle doesn't have to parse them over and
over again.

HTH,
Joachim Geidel

Mark Plas schrieb:

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Hi Joachim,

This was a very interesting read. I was hoping there was some simple trick to do these kind of things, but it seems there isn't one. I'll have to check out some of this stuff and see in what way it can be useful for us.

Thanks a lot for your elaborate answer,

Best regards,
Mark

-----Original Message-----
From: Joachim Geidel [mailto:[hidden email]]
Sent: vrijdag 19 september 2008 10:54
To: Mark Plas
Cc: [hidden email]
Subject: Re: [vwnc] Oracle and IN-clause binding?

Hi Mark,

AFAIK, it is not possible to use array binding in that way. As Clemens
wrote, this is a limitation of Oracle's OCI.

Enumerating the values as literals in an IN-clause is limited, you can't put
more than 999 values into such a clause. The same limitation holds if you
use an enumeration of binding variables instead. Also, the overhead for
parsing each statement may cause severe scaling problems for your Oracle
server if the number of concurrent sessions grows to more than just a few.
The threshold where it breaks down depends on the hardware, the number of
statements per second, and the complexity of the statements, but sooner or
later you will notice performance problems.

Depending on the number of values which you have to use in the IN-clause,
there are several possible solutions:

1. If the number of values is small and limited by some upper bound, you
might use a statement like the one Clemens described. Use as many bind
variables as needed for the maximum number of values, and fill the bind
variables with nils when you have less values than bind variables.

2. If it is always the same type of query against the same table or view,
you could encapsulate the query in a PL/SQL procedure which accepts an array
of values (a table type in PL/SQL) as input parameter and has a table as
output parameter. This has limitations, as the array for the output
parameter can't grow beyond a certain size (16 MB or something like that).
You have to generate a PL/SQL procedure call instead of the SELECT. A
variant would be to implement a pipelined table function, and select from
the result of a function call casted as a table (see #3 below).

3. The sophisticated way to do this is to write a PL/SQL package with:
- a procedure which stores an array of values in a package variable of type
"table of number" (or whatever the type of the primary keys is),
- a pipelined table function which returns the contents of the package
variable, with an interface similar to
    function get_values() return number_set_type pipelined;
where number_set_type has to be declared as a "table of number" type (if I
recall this correctly, there are some details which one has to get right).
Queries will then have two steps:
- Call the procedure to store the parameters in the package variable,
- Retrieve the result using a query with a statement like

    SELECT * FROM my_table WHERE primarykey IN
        (SELECT * FROM  TABLE (MyPackage.get_values ()))

where get_values is the pipelined table function returning the contents of
the package variable.
The details are left as an excercise for the reader. ;-)

Solution #3 works for an arbitrary number of values, it's fast even with
large numbers of values, and it scales well because the statements always
have the same form, such that Oracle doesn't have to parse them over and
over again.

HTH,
Joachim Geidel

Mark Plas schrieb:


_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc