This discussion is archived
9 Replies Latest reply: Feb 8, 2012 1:24 AM by ddevienne RSS

Binding collection to right-hand-side of WHERE num_col in (:1) clause?

ddevienne Newbie
Currently Being Moderated
I'm trying to get all child rows of a set of parent rows. I have in memory the set of parent PKs, and want to select in the child table all rows which have their parent FK among the set of parents. Given Oracle's support for collections like VARRAY, I thought easy, lets just bind a VARRAY of parent PKs as the right-hand-side in a WHERE parent_fk in (:1) clause. However, I somehow cannot make that work :(

I've simplified it to a very simple test table and varray of number:

create table entity_tab (id number primary key, data number)
create type guid_varray as VARRAY(1000000) of NUMBER

And I've tried all the following combinations of select, and got the following Oracle errors:
select data from entity_tab where id in :1 
ORA-00932: inconsistent datatypes: expected NUMBER got MARSU.GUID_VARRAY

select data from entity_tab where id in (:1)
ORA-00932: inconsistent datatypes: expected NUMBER got MARSU.GUID_VARRAY

select data from entity_tab where id in GUID_VARRAY(:1)
ORA-00932: inconsistent datatypes: expected NUMBER got MARSU.GUID_VARRAY

select data from entity_tab where id in table(:1)
ORA-00936: missing expression

select data from entity_tab where id in table(cast(:1 as GUID_VARRAY))
ORA-00936: missing expression

select data from entity_tab where id in table(GUID_VARRAY(:1))
ORA-00936: missing expression
I believe my OCI code to instantiate, fill, and bind the VARRAY is correct, and it sounds more like my SQL select statement is simply not valid when the bind placeholder holds a VARRAY of number.

Can someone please point out what I am doing wrong? Is it possible to achieve my stated goal to get all child rows given a set of parent PKs, perhaps some other way, using a single statement and server round-trip?

As a last resort, I guess I can still create a temporary table and join on that, but seems so un-Oracle to me, there's got to be a better solution, no? All the examples I see binding varray send it to a PL/SQL procedure that explicitly takes one, and I see none in a WHERE IN clause. Perhaps a PL/SQL solution is the only way to achieve my goal?

Thanks in advance for any help on this. --DD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  • 1. Re: Binding collection to right-hand-side of WHERE num_col in (:1) clause?
    ddevienne Newbie
    Currently Being Moderated
    After some more looking around (see [1] and [2]), I finally figured out I needed to use a sub-query.

    Given that TABLE() is supposed to turn a collection into a table, and the "select * from TABLE(:1)" is also kinda like a table, I don't quite undertand why one works and not the other, but I'm happy as long as it works.

    Note that all 3 selects below as equivalent, and work as expected. Thanks, --DD
    select data from entity_tab where id in (select * from TABLE(:1))
    select data from entity_tab where id in (select COLUMN_VALUE from TABLE(:1))
    select data from entity_tab where id in (select VALUE(in_varray) from TABLE(:1) in_varray)
    [1] http://docstore.mik.ua/orelly/oracle/prog2/ch19_05.htm
    [2] Re: Binding an array of integers using OCI for WHERE IN clause
  • 2. Re: Binding collection to right-hand-side of WHERE num_col in (:1) clause?
    894085 Explorer
    Currently Being Moderated
    You can also use MEMBER OF rather than IN for a predicate involving a collection.
    SQL> select * from test where id member of ( number_collection(1,2,3,4,5) );
    
            ID
    ----------
             1
    You'd be able to bind the number_collection() as a variable in your application.
    select data from entity_tab where id member of (:1);
  • 3. Re: Binding collection to right-hand-side of WHERE num_col in (:1) clause?
    ddevienne Newbie
    Currently Being Moderated
    I tried that Adrian, commenting out my where id in subquery, but I get ORA-00932 then. Haven't looked at member of yet, so maybe it doesn't like my varray of numbers? AFAIK, GUID_VARRAY is a UDT, so the error message is a bit confusing to me. Any ideas? Thanks, --DD
        stmt.prepare(
            "select data from entity_tab "
            //"where id in (select * from TABLE(:1)) "
            "where id member of (:1) "
            "order by id" // for the unit test
        );
    
    ORA-00932: inconsistent datatypes: expected UDT got MARSU.GUID_VARRAY
  • 4. Re: Binding collection to right-hand-side of WHERE num_col in (:1) clause?
    894085 Explorer
    Currently Being Moderated
    Sorry didn't see the VARRAY. It would have to be a TABLE OF NUMBER, rather than a VARRAY OF NUMBER. One is treated as a named type and one as a named collection. Apologies.

    Any specific reason you need a VARRAY. 1000000 seems kinda random? Example only, I know. But?
  • 5. Re: Binding collection to right-hand-side of WHERE num_col in (:1) clause?
    ddevienne Newbie
    Currently Being Moderated
    The upper bound is arbitrary indeed, except it has to be large enough.

    Following up on your latest comment, I decided to give "member of :1" a try, binding a OCITable instead.

    Both give me the same result set, but using a VARRAY is tremendously faster than using the NTABLE. Selecting 10% of 100,000 rows (i.e. 10,000 elements in the collection, be it varray or table) takes 63 ms with varray (WHERE id IN (select * from TABLE(:1))), while it takes 24,570 ms with nested table (WHERE id MEMBER OF :1). I use prefetching of 128 rows and/or 8 KiB on the select statement. For reference, inserting the 100,000 rows takes 6,115 ms, and only because I did a scalar-insert loop instead of using array binding. 4x the scalar-insert time for the select using ntable is pretty awful. I even randomized the ids I put in the varray I'm binding, fearing having them nicely ordered was giving an unfair advantage to varray, but it made no difference whatsoever.

    Any idea what could be wrong with the MEMBER OF :OCITable case? Thanks, --DD

    PS: Both VARRAY and NTABLE have to be bound using SQLT_NTY (named type), rather than SQLT_NCO (named collection), to get the correct result. Using SQLT_NCO raises no errors and returns no rows... When if ever does one use SQLT_NCO???
  • 6. Re: Binding collection to right-hand-side of WHERE num_col in (:1) clause?
    Vincent Rogier Explorer
    Currently Being Moderated
    Hello,

    From my experience with OCILIB, SQLT_NCO is only used ( I mean returned) by OCI Client when getting OCI_ATTR_TYPECODE attribute of an handle of type OCI_HTYPE_DESCRIBE (like when describing a type using OCIDescribeAny() for example).

    For binding, all object based types (object, varrays, nested tables) must be binded using SQLT_NTY.
    For these types, a call to OCIBindObject() is required.
    One parameter is the type TDO pointer that has the SQLT_NCO value for the attribute OCI_ATTR_TYPECODE in case of collections.
    So, Oracle knows that you're binding a collection even if SQLT_NTY is used for the primarly OCIBindxxx() call
    It may have been this way in order to not break code compatibility with early 8.0.x versions


    Regards,

    Vincent

    Edited by: Vincent Rogier on 2 févr. 2012 13:34
  • 7. Re: Binding collection to right-hand-side of WHERE num_col in (:1) clause?
    894085 Explorer
    Currently Being Moderated
    ddevienne wrote:
    The upper bound is arbitrary indeed, except it has to be large enough.

    Following up on your latest comment, I decided to give "member of :1" a try, binding a OCITable instead.

    Both give me the same result set, but using a VARRAY is tremendously faster than using the NTABLE. Selecting 10% of 100,000 rows (i.e. 10,000 elements in the collection, be it varray or table) takes 63 ms with varray (WHERE id IN (select * from TABLE(:1))), while it takes 24,570 ms with nested table (WHERE id MEMBER OF :1). I use prefetching of 128 rows and/or 8 KiB on the select statement. For reference, inserting the 100,000 rows takes 6,115 ms, and only because I did a scalar-insert loop instead of using array binding. 4x the scalar-insert time for the select using ntable is pretty awful. I even randomized the ids I put in the varray I'm binding, fearing having them nicely ordered was giving an unfair advantage to varray, but it made no difference whatsoever.

    Any idea what could be wrong with the MEMBER OF :OCITable case? Thanks, --DD

    PS: Both VARRAY and NTABLE have to be bound using SQLT_NTY (named type), rather than SQLT_NCO (named collection), to get the correct result. Using SQLT_NCO raises no errors and returns no rows... When if ever does one use SQLT_NCO???
    Was that a one-off test? or was it repeatable?
  • 8. Re: Binding collection to right-hand-side of WHERE num_col in (:1) clause?
    ddevienne Newbie
    Currently Being Moderated
    Adrian wrote:
    ddevienne wrote:
    Both give me the same result set, but using a VARRAY is tremendously faster than using the NTABLE.
    Was that a one-off test? or was it repeatable?
    It's fully repeatable for me. I'm on Win7-64, with "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production" server and instantclient-11.2.0.2.0. --DD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  • 9. Re: Binding collection to right-hand-side of WHERE num_col in (:1) clause?
    ddevienne Newbie
    Currently Being Moderated
    Vincent Rogier wrote:
    From my experience with OCILIB...
    Thank you for your comments and the details Vincent. A+, --DD                                                                                                                                                                                                                                                                                                   

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points