SELECT with bind array of strings only uses the first value in the array

[meastp]

I am struggling with a query that I'm converting to use a bind array. No matter what I try, it looks like the query will only use the first string in the bind array.

The query is really "SELECT x FROM table_a WHERE table_a.x IN :1", but the example below has the same behaviour.

(Note that the queries I already converted that uses integers instead of strings in the bind array works perfectly. I also have INSERT INTO - statements with bind arrays which works as expected.)

ocilib version: latest release v 4.4.0 oracle client: 12.1

auto statement = std::unique_ptr<OCI_Statement, std::function<boolean(OCI_Statement*)>>(OCI_StatementCreate(connection.get()), OCI_StatementFree);
OCI_SetFetchSize(statement.get(), 1000);
OCI_SetPrefetchSize(statement.get(), 1000);

if (!OCI_Prepare(statement.get(), "SELECT :1 FROM dual"))
{
    // "Oracle Database Error: OCI_Prepare in readDataContainer failed"
}

std::unique_ptr<char[]> alloc_mem;

if (!OCI_BindArraySetSize(statement.get(), 2))
{
    // "Oracle Database Error: OCI_BindArraySetSize failed"
}

alloc_mem = std::unique_ptr<char[]>(new char[2 * 37]);

if (!OCI_BindArrayOfStrings(statement.get(), (std::string(":") + std::to_string(static_cast<long long>(1))).c_str(), alloc_mem.get(), 36, 0))
{
    // "Oracle Database Error: OCI_BindArrayOfStrings failed"
}

auto uuid1 = std::string("f4000000-0000-0010-0000-00000000000");
std::copy(begin(uuid1), end(uuid1), &alloc_mem[0]);
alloc_mem[35] = '\0';

auto uuid2 = std::string("d7000000-0000-0010-0000-00000000000");
std::copy(begin(uuid1), end(uuid1), &alloc_mem[36]);
alloc_mem[36*2-1] = '\0';

// The array alloc_mem now looks like this (interpreded as as characters), which I think is correct:
// "f4000000-0000-0010-0000-00000000000\0d7000000-0000-0010-0000-00000000000\0"

auto bnd = OCI_GetBind2(statement.get(), ":1"); 
auto cnt = OCI_BindGetDataCount(bnd); // Returns 2
auto result = OCI_BindSetDirection(bnd, OCI_BDM_IN);

auto b_nullat1 = OCI_BindIsNullAtPos(bnd, 1); // Returns 0
auto b_nullat2 = OCI_BindIsNullAtPos(bnd, 2); // Returns 0

auto b_size = OCI_BindGetDataSize(bnd); // Returns 36
auto b_sizeat1 = OCI_BindGetDataSizeAtPos(bnd, 1); // Returns 36
auto b_sizeat2 = OCI_BindGetDataSizeAtPos(bnd, 2); // Returns 36

if (!OCI_Execute(statement.get()))
{
    // "Oracle Database Error: OCI_Execute in readDataContainer failed"
}

auto cnt2 = OCI_BindGetDataCount(bnd); // Returns 2
auto a_nullat1 = OCI_BindIsNullAtPos(bnd, 1); // Returns 0
auto a_nullat2 = OCI_BindIsNullAtPos(bnd, 2); // Returns 0

auto a_size = OCI_BindGetDataSize(bnd); // Returns 36
auto a_sizeat1 = OCI_BindGetDataSizeAtPos(bnd, 1); // Returns 36
auto a_sizeat2 = OCI_BindGetDataSizeAtPos(bnd, 2); // Returns 36

{
    auto result = OCI_GetResultset(statement.get());
    while (OCI_FetchNext(result))
    {
        std::cout << "Found one uuid";
    }

    if (!OCI_ReleaseResultsets(statement.get()))
    {
        // "Oracle Database Error: OCI_ReleaseResultsets failed"
    }
}

This example always returns just a single "Found one uuid".

[FladoToo]

I think you misunderstand how bind arrays work in (Oracle) SQL. There is no way "select :whatever from dual" will return more than one row. And a bind array holds one element per row returned. The same goes for "select x from t where y in :whatever". That is a syntax error which is probably why you are trying to go the "select from dual" route. Here is one of the many discussions on that same problem: https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:210612357425

[meastp]

I'm not sure I understand. I will read the link you provided. The " ... from dual" was just (a misguided attempt) to provide an example that did not depend on a table.

What I have is two uuids: uuid1 and uuid2, and they are both strings.

I have a table, table_a with columns id (integer) and globalid (varchar2).

I want to select all rows from table_a where the globalid is equal to one of my uuid strings.

Are you saying that this is not possible?

EDIT: According to the link, this is possible, but I need to use the create type .. as table of varchar2. Correct?

[vrogier]

Hi,

you cannot bind a single host array as a single bind ":1" in statements like 'select ... from ... where ... in :1". This is not an OCILIB limitation but an Oracle client API (OCI) limitation.

There are workarounds available:

• generating yourself a string based on array values (e.g. "('value1', 'value2')") thus not using binds
• generating yourself a string containing binds (e.g. "(:1, :2)") and loop on the array and call bind function for each array element
• using various PL/SQL tricks you can find on google (search for "oracle select in array")

Best regards,

Vincent

[FladoToo]

Oh , sorry. I thought you wanted to try something like "select x from t where y in (select :whatever from dual)" which would bypass the syntax error but still not work as you want it to. Vincent's suggestions are good, but please, please don't generate literal strings of UUIDs ( ... in ('xxx1','xxx2')) if you plan to scale beyond 2 concurrent users and 10 queries per second.

[meastp]

Hi,

Thank you for the quick answers, they are very helpful. What I'm trying to do is to optimize my application from using global temporary tables to using bind arrays.

What I don't understand is why a host array as a single bind ":1" in statements like 'select ... from ... where ... = :1' works for integers, but the same pattern will not work for strings? I have tested both with IN and =...

Can you confirm that this is the case? This issue is just with bind array of strings?

[FladoToo]

Actually, the Global Temporary Table (GTT) approach is pretty sound. You can populate the GTT with an array insert, which is very efficient and can accommodate lists of huge lengths without blowing up the PGA and SGA on the server. It writes to disk, though, so if you anticipate only shorter lists (below, say, a thousand entries), you'll be better off with the other approaches. Can you give an example for "select x from t where x=:1" returning more than one row (provided there is a unique constraint on t(x))? That would be revolutionary, even if it only worked for integers.

[vrogier]

@meastp It should not work for integers. Best case it could take into account only first value but not the whole array

[meastp]

Oh. I was hoping that I could avoid GTT's, primarily to avoid a lot of undo/redo because I need to empty the GTT's I'm using between uses. I'm back to where I started, then (except for UPDATEs and DELETEs, I already use host arrays for INSERTs)

So, is this quote from Pro*C/C++ Precompiler Programmer's Guide Release 9.2 - 8 Host Arrays - Using Arrays in SQL Statements the definitive answer, then?

You can use host arrays as input variables in the INSERT, UPDATE, and DELETE statements and as output variables in the INTO clause of SELECT and FETCH statements.

I admit I did not read the entire chapter too carefully... :/

[meastp]

However, look at this https://docs.oracle.com/cd/E11882_01/appdev.112/e10825/pc_08arr.htm#i1770 from the latest Pro*C/C++ Precompiler Programmer's Guide - it looks like some form of SELECT ... FROM ... WHERE a = :1 is allowed for host arrays after all. Is it only allowed if the select contains INTO?

Or is it only supposed to illustrate a point with the UPDATE statement below?

[vrogier]

ProC is a different thing from OCI and may have different features / limitations compared to OCI. Anyway, given examples show that what you're trying to achieve cannot be done in ProC as well (1st example) while the 2nd example is a different use case which is also supported by OCI and thus OCILIB.

[FladoToo]

@meastp If the undo/redo associated with GTTs is your problem, you definitely need to continue using them. It's because having lots of undo means your GTTs are huge and if you try to put them in the PGA (using any of the PL/SQL approaches) or PGA and SGA (using the in-list of binds approach), you will open a whole new can of worms (unless you have huge SGA and PGA, of course). If using Oracle 12c is an option, you might want to look into Temporary Undo: https://oracle-base.com/articles/12c/temporary-undo-12cr1

[meastp]

Thanks!

I have gone back to using temp-tables for SELECTs. Host arrays are now used for INSERT, UPDATE and DELETE only.

I will look at the link for further optimization.

[thegeekyboy]

The above does not close the issue properly, at least for me. What if we rewrite the query as following?

select * from products where name in (:s)

and use the following code

#include "ocilib.h"

#define ARRAY_SIZE 1000
#define STRING_SIZE 20

void err_handler(OCI_Error *err)
{
    printf("%s\n", OCI_ErrorGetString(err));
}

int main(void)
{
    OCI_Connection *cn;
    OCI_Statement  *st;
    OCI_Error      *err;

    char tab_str[ARRAY_SIZE][STRING_SIZE+1];
    int i;

    if (!OCI_Initialize(err_handler, NULL, OCI_ENV_DEFAULT | OCI_ENV_CONTEXT))
    {
        return EXIT_FAILURE;
    }
    cn = OCI_ConnectionCreate("DB", "USER", "PASS", OCI_SESSION_DEFAULT);
    OCI_SetAutoCommit(cn, TRUE);

    st = OCI_StatementCreate(cn);
    OCI_Prepare(st, "select * from products where name in (:s)");
    OCI_BindArraySetSize(st, ARRAY_SIZE);
    OCI_BindArrayOfStrings(st, ":s", (char*)tab_str, STRING_SIZE, 0);

    for (i = 0; i < ARRAY_SIZE; i++)
        sprintf(tab_str[i], "Name %d", i + 1);

    if (!OCI_Execute(st))
    {
        printf("there was an error: %s\n", OCI_ErrorGetString(OCI_GetLastError()));
    }

    OCI_Resultset *rs = OCI_GetResultset(st);

    while (OCI_FetchNext(rs))
    {
        printf("code: %i, name %s\n", OCI_GetInt(rs, 1), OCI_GetString(rs, 2));
    }

    OCI_StatementFree(st);
    OCI_ConnectionFree(cn);
    OCI_Cleanup();
    return EXIT_SUCCESS;
}

output

[amir@jump ~]$ ./test3
code: 1, name Name 1

Still I only get the WHERE clause matched with the first item in array. Something to note, if I use C++ library and Bind(":s", ..., ..., ocilib::BindInfo::In); it works without any issue.

Any idea what I might be doing wrong?

[meastp]

Does this mean that using bind-arrays in SELECT queries is somehow supported? That would be very convenient for my use case.

[thegeekyboy]

no unfortunately, I was wrong with the C++ Statement::Bind working.. it does not.. so yeah.. stuck..

[meastp]

Ok, then the only workaround I've found is using a temp table, unfortunately. (As I understand, this is a limit in Oracle, not ocilib)

[bj8sk]

You forgot to add calls to OCI_GetNextResultset, this should get both rows:

  OCI_Resultset *rs = OCI_GetResultset(st);
  while ( rs != nullptr )  // Each row in it's own resultset when using array bind and returning into
  {
    while (OCI_FetchNext(rs))
    {
        printf("code: %i, name %s\n", OCI_GetInt(rs, 1), OCI_GetString(rs, 2));
    }
    rs = OCI_GetNextResultset(st); // This will get the next row
  }

[meastp]

@bj8sk Do you have an official link to where this is documented by Oracle? I'd be interested in the performance implications of this. :)

[bj8sk]

Maybe here ? https://docs.oracle.com/en/database/oracle/oracle-database/19/lnoci/binding-and-defining-in-oci.html#GUID-FBBE8A75-8EEC-4166-A574-6FEE4AC78FA3

Although for ocilib lib that's here: http://vrogier.github.io/ocilib/doc/html/group___ocilib_c_api_feature_returning_into.html#ga20d23ea50075c16a1f7c5ae3515bd025