Oracle OCI, bind variables, and queries like ID IN (1, 2, 3)

Question

Succinct Version:

I'm looking for a C++ OCI adaptation of the following Java technique, where code is able to bind an array of numbers (the array size can vary) into a non-PL/SQL SELECT statement and then use the resulting array in a WHERE ID IN (...) style check.

http://rafudb.blogspot.com/2011/10/variable-inlist.html

Original Question:

We have a C++ app which talks to Oracle via OCI. We're trying to fix old code which generates SQL queries by concatenating text; instead we want to use bind variables as much as possible. One particular case has come up that we don't have a good solution for.

SELECT * FROM MyTable WHERE ID IN (1, 4, 10, 30, 93)

Where the (1, 4, 10, 30, 93) part comes from a vector<int> or some other flexibly-sized container of data. If we knew it would always be five values, we could do:

SELECT * FROM MyTable WHERE ID IN (:1, :2, :3, :4, :5)

But it might be one entry, or ten, or maybe even zero. Obviously, if we are building up the query as a string, we can just append as many numbers as we need, but the goal is to avoid that if possible and stick to just bind variables.

Is there a good way to accomplish this? For instance, in OCI, can I bind an array and then sub-select out of it?

SELECT * FROM MyTable WHERE ID IN (SELECT * FROM :1)

Where :1 is an OCI array? (Probably the syntax would differ.) Does anyone have experience with this? Sample code would be a godsend as I tend to struggle with writing raw OCI. Thanks :)

EDIT: I'd like to do better than binding in a string which is parsed by a PL/SQL procedure, if at all possible. I am confident that we would blow out the 4000 character limit in many cases, and I also feel like that's just trading one kind of string manipulation that I'm comfortable with, for another kind that I'm not (and I can't debug as easily). If possible I'd like to bind an array of values (or some form of dataset) into one standard SQL statement.

EDIT 2: Some investigation turned up the following link which seems to be doing just what I want, but in Java: http://rafudb.blogspot.com/2011/10/variable-inlist.html Does anyone know how to adapt this approach to C++ OCI?

Mendelsohn

Go read this: asktom.oracle.com/pls/apex/… If you are not familiar with asktom, you are missing a good resource. – Kandace

It's doable in OCI, although it is not easy. These collection level operations are not well documented in OCI reference. You have install the whole Oracle server (including Demos) and read OCI demo sources. But anyway you have to 1st cast OCI Collection onto TABLE. Oracle query can not directly read from collection. – Seligmann

Ivan, sounds promising--can you point me towards a a specific demo or provide a code snippet? Thanks :) – Mendelsohn

Arrays must work (at least this approach works for PHP which uses OCI to interact with Oracle databse). Two key points is to use PL/SQL block which returns cursor in output parameter and use collection type which defined on database level and visible for SQL (e.g. sys.ODCINumberList) – Merta

I am not sure about OCI. But you can use DBMS_UTILITY.COMMA_TO_TABLE() procedure to convert comma seperated values into a temporary table. – Viviyan

I do not want to pack my array into a string. If I'm doing that, it's frankly no better than what I'm doing today, especially considering limitations like max 4000 characters in a string. – Mendelsohn

I have a related question here: stackoverflow.com/questions/22843035. ThinkJet, any idea on that one? – Figurative

Answer 1

This example demonstrates approach with using collection type, defined in database to pass list of parameters.
SYS.ODCINumberList is standard collection type available for all users. Query, used in sample just select first 100 integers ( test ) and then filter this integers with list in IN(...) clause.

#include "stdafx.h"
#include <iostream>
#include <occi.h>

using namespace oracle::occi;
using namespace std;

// Vector type to pass as parameter list
typedef vector<Number> ValueList;

int _tmain(int argc, _TCHAR* argv[])
{
  Environment *env;
  Connection *con;

  // Note that Environment must be initialized in OBJECT mode 
  // to use collection mapping features.
  env = Environment::createEnvironment(Environment::OBJECT);

  con = env->createConnection ("test_user", "test_password", "ORACLE_TNS_NAME");

  try {

    Statement *stmt = con->createStatement(
                 "select * from "
                 " (select level as col from dual connect by level <= 100)"
                 "where "
                 "  col in (select column_value from table(:key_list))"
               );

    cout << endl << endl << "Executing the block :" << endl 
         << stmt->getSQL() << endl << endl;

    // Create instance of vector trype defined above 
    // and populate it with numbers.
    ValueList value_list;
    value_list.push_back(Number(10));
    value_list.push_back(Number(20));
    value_list.push_back(Number(30));
    value_list.push_back(Number(40));

    // Bind vector to parameter #1 in query and treat it as SYS.ODCINumberList type. 
    setVector(stmt, 1, value_list, "SYS", "ODCINUMBERLIST");

    ResultSet *rs = stmt->executeQuery();

    while(rs->next())
      std::cout << "value: " << rs->getInt(1) << std::endl;

    stmt->closeResultSet(rs); 
    con->terminateStatement (stmt);

  } catch(SQLException ex) {
    cout << ex.what();
  }


  env->terminateConnection (con);
  Environment::terminateEnvironment (env);

    return 0;
}

You can use various ODCIxxxList types to pass list of numbers, dates or strings to Oracle via OCI or even define your own type in DB.

Example compiled with Visual Studio 10 Express and this version of OCI libraries. Tested against Oracle 11.2.0.3.0 .

Update

Below is example application which does same thing but with plain C OCIxxx functions.

//
// OCI collection parameters binding - example application
//

#include "stdafx.h"
#include <iostream>
#include <oci.h>
#include <oro.h>

using namespace std;

// connection parameters
const char *db_alias         = "ORACLE_DB_ALIAS";
const char *db_user_name     = "test_user";
const char *db_user_password = "test_password";

// helper error checking procedure to shorten main code, returns true if critical error detected
// and prints out error information
bool check_oci_error(char *error_point, OCIError *errhp, sword status, OCIEnv *envhp);

int _tmain(int argc, _TCHAR* argv[]) {

  //----- CONNECTION INITIALIZATION PART ------------------------------------------------------

  sword rc;
  OCIEnv *myenvhp;       /* the environment handle */
  OCIServer *mysrvhp;    /* the server handle */
  OCIError *myerrhp;     /* the error handle */
  OCISession *myusrhp;   /* user session handle */
  OCISvcCtx *mysvchp;    /* the  service handle */

  /* initialize the mode to be the threaded and object environment */
  /* NOTE: OCI_OBJECT must be present to work with object/collection types */
  rc = OCIEnvCreate(&myenvhp, OCI_THREADED|OCI_OBJECT, (dvoid *)0, 0, 0, 0, (size_t) 0, (dvoid **)0);

  if( check_oci_error("OCIEnvCreate", NULL, rc, NULL) ) {
    return -1; 
  }

  /* allocate a server handle */
  rc = OCIHandleAlloc ((dvoid *)myenvhp, (dvoid **)&mysrvhp, OCI_HTYPE_SERVER, 0, (dvoid **) 0);
  if( check_oci_error("OCIHandleAlloc(OCI_HTYPE_SERVER)", NULL, rc, myenvhp) ) return -1;

  /* allocate an error handle */
  rc = OCIHandleAlloc ((dvoid *)myenvhp, (dvoid **)&myerrhp, OCI_HTYPE_ERROR, 0, (dvoid **) 0);
  if( check_oci_error("OCIHandleAlloc(OCI_HTYPE_ERROR)", NULL, rc, myenvhp) ) return -1;

  /* create a server context */
  rc = OCIServerAttach(mysrvhp, myerrhp, (text *)db_alias, strlen (db_alias), OCI_DEFAULT);
  if( check_oci_error("OCIServerAttach()", myerrhp, rc, myenvhp) ) return -1;

  /* allocate a service handle */
  rc = OCIHandleAlloc ((dvoid *)myenvhp, (dvoid **)&mysvchp, OCI_HTYPE_SVCCTX, 0, (dvoid **) 0);
  if( check_oci_error("OCIHandleAlloc(OCI_HTYPE_SVCCTX)", myerrhp, rc, myenvhp) ) return -1;

  /* set the server attribute in the service context handle*/
  rc = OCIAttrSet((dvoid *)mysvchp, OCI_HTYPE_SVCCTX, (dvoid *)mysrvhp, (ub4) 0, OCI_ATTR_SERVER, myerrhp);
  if( check_oci_error("OCIAttrSet(OCI_HTYPE_SVCCTX,OCI_ATTR_SERVER)", myerrhp, rc, myenvhp) ) return -1;

  /* allocate a user session handle */
  rc = OCIHandleAlloc((dvoid *)myenvhp, (dvoid **)&myusrhp,  OCI_HTYPE_SESSION, 0, (dvoid **) 0);
  if( check_oci_error("OCIHandleAlloc(OCI_HTYPE_SESSION)", myerrhp, rc, myenvhp) ) return -1;

  /* set user name attribute in user session handle */
  rc = OCIAttrSet((dvoid *)myusrhp, OCI_HTYPE_SESSION, (dvoid *)db_user_name, strlen(db_user_name), OCI_ATTR_USERNAME, myerrhp);
  if( check_oci_error("OCIAttrSet(OCI_HTYPE_SESSION,OCI_ATTR_USERNAME)", myerrhp, rc, myenvhp) ) return -1;

  /* set password attribute in user session handle */
  rc = OCIAttrSet((dvoid *)myusrhp, OCI_HTYPE_SESSION, (dvoid *)db_user_password, strlen(db_user_password), OCI_ATTR_PASSWORD, myerrhp);
  if( check_oci_error("OCIAttrSet(OCI_HTYPE_SESSION,OCI_ATTR_PASSWORD)", myerrhp, rc, myenvhp) ) return -1;

  rc = OCISessionBegin(mysvchp, myerrhp, myusrhp, OCI_CRED_RDBMS, OCI_DEFAULT);
  if( check_oci_error("OCISessionBegin()", myerrhp, rc, myenvhp) ) return -1;

  /* set the user session attribute in the service context handle*/
  rc = OCIAttrSet( (dvoid *)mysvchp, OCI_HTYPE_SVCCTX, (dvoid *)myusrhp, (ub4) 0, OCI_ATTR_SESSION, myerrhp);
  if( check_oci_error("OCIAttrSet(OCI_HTYPE_SVCCTX,OCI_ATTR_SESSION)", myerrhp, rc, myenvhp) ) return -1;

  cout << endl << "Initialization done." << endl;

  //----- REGISTER TYPE INFORMATION ------------------------------------------------------

  // This section can be invoked once per session to minimize server roundtrips.

  char    *type_owner_name = "SYS";               
  char    *type_name       = "ODCINUMBERLIST";
  OCIType *type_tdo        = NULL;

  rc= OCITypeByName(
        myenvhp, myerrhp, mysvchp, 
        (CONST text *)type_owner_name, strlen(type_owner_name),
        (CONST text *) type_name, strlen(type_name),
        NULL, 0,
        OCI_DURATION_SESSION, OCI_TYPEGET_HEADER, 
        &type_tdo
      );
  if( check_oci_error("OCITypeByName()", myerrhp, rc, myenvhp) ) return -1;

  //----- PREPARE PARAMETER INSTANCE ---------------------------------------------

  OCIArray *array_param = NULL;

  rc = OCIObjectNew(
         myenvhp, myerrhp, mysvchp, 
         OCI_TYPECODE_VARRAY, 
         type_tdo, NULL, OCI_DURATION_SESSION, TRUE,
         (void**) &array_param
       );
  if( check_oci_error("OCITypeByName()", myerrhp, rc, myenvhp) ) return -1;

  //----- FILL PARAMETER ---------------------------------------------------------

  OCINumber num_val;
  int       int_val;

  for(int i = 1; i <= 3; i++) {
    int_val = i*10;

    rc = OCINumberFromInt(myerrhp, &int_val, sizeof(int_val), OCI_NUMBER_SIGNED, &num_val);
    if( check_oci_error("OCINumberFromInt()", myerrhp, rc, myenvhp) ) return -1;

    rc = OCICollAppend(myenvhp, myerrhp, &num_val, NULL, array_param);
    if( check_oci_error("OCICollAppend()", myerrhp, rc, myenvhp) ) return -1;
  }


  //----- BIND PARAMETER VALUE AND EXECUTE STATEMENT ------------------------------

  OCIStmt   *mystmthp   = NULL;
  OCIDefine *col1defp   = NULL;
  double    col1value;  
  OCIBind   *bndp       = NULL;

  char      *query_text = "select * from "
                          " (select level as col from dual connect by level < 100)"
                          "where "
                          "  col in (select column_value from table(:key_list))";

  rc = OCIHandleAlloc(myenvhp, (void **)&mystmthp, OCI_HTYPE_STMT, 0, NULL); 
  if( check_oci_error("OCIHandleAlloc(OCI_HTYPE_STMT)", myerrhp, rc, myenvhp) ) return -1;

  rc = OCIStmtPrepare( 
         mystmthp, myerrhp, 
         (const OraText *)query_text, strlen(query_text), 
         OCI_NTV_SYNTAX, OCI_DEFAULT
       );
  if( check_oci_error("OCIStmtPrepare()", myerrhp, rc, myenvhp) ) return -1;

  // result column
  rc =  OCIDefineByPos(mystmthp, &col1defp, myerrhp, 1, &col1value, sizeof(col1value), SQLT_BDOUBLE, NULL, NULL, NULL, OCI_DEFAULT);
  if( check_oci_error("OCIDefineByPos()", myerrhp, rc, myenvhp) ) return -1;

  // parameter collection
  rc = OCIBindByName(
         mystmthp, &bndp, myerrhp,
         (text *)":key_list", strlen(":key_list"), 
         NULL, 0,
         SQLT_NTY, NULL, 0, 0, 0, 0,
         OCI_DEFAULT
       );
  if( check_oci_error("OCIBindByName()", myerrhp, rc, myenvhp) ) return -1;

  rc = OCIBindObject(
         bndp, myerrhp, 
         type_tdo, (dvoid **) &array_param, 
         NULL, NULL, NULL
       );
  if( check_oci_error("OCIBindByName()", myerrhp, rc, myenvhp) ) return -1;

  // execute and fetch
  rc = OCIStmtExecute(mysvchp, mystmthp, myerrhp, 0, 0, NULL, NULL, OCI_DEFAULT);
  if( check_oci_error("OCIBindByName()", myerrhp, rc, myenvhp) ) return -1;

  rc = OCIStmtFetch2(mystmthp, myerrhp, 1, OCI_FETCH_NEXT, 0, OCI_DEFAULT);

  while(rc != OCI_NO_DATA) {
    if( check_oci_error("OCIStmtFetch2()", myerrhp, rc, myenvhp) ) return -1;
    cout << "value: " << col1value << endl;
    rc = OCIStmtFetch2(mystmthp, myerrhp, 1, OCI_FETCH_NEXT, 0, OCI_DEFAULT);
  }

  // free collection object parameter
  rc = OCIObjectFree(myenvhp, myerrhp, array_param, OCI_OBJECTFREE_FORCE);
  if( check_oci_error("OCIObjectFree()", myerrhp, rc, myenvhp) ) return -1;

  cout << endl << "Main test done." << endl;

  //------- FINALIZATION -----------------------------------------------------------
  rc= OCISessionEnd(mysvchp, myerrhp, myusrhp, OCI_DEFAULT);
  if( check_oci_error("OCISessionEnd()", myerrhp, rc, myenvhp) ) return -1;

  rc = OCIServerDetach(mysrvhp, myerrhp, OCI_DEFAULT);
  if( check_oci_error("OCIServerDetach()", myerrhp, rc, myenvhp) ) return -1;

  OCIHandleFree(myenvhp, OCI_HTYPE_ENV);

  cout << endl << "Finalization done." << endl;

  return 0;
}

// helper error checking procedure to shorten main code, returns true if critical error detected
// and prints out error information
bool check_oci_error(char *error_point, OCIError *errhp, sword status, OCIEnv *envhp) { 

  text errbuf[1024];
  sb4  errcode;
  bool ret_code = true;

  switch (status) { 
    case OCI_SUCCESS:
        ret_code = false;
      break;
    case OCI_SUCCESS_WITH_INFO:
        OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
        cout << error_point << " Error: OCI_SUCCESS_WITH_INFO; Info: " << errbuf << endl;
        ret_code = (errcode == 436 || errcode == 437 || errcode == 438 || errcode == 439);
      break;
    case OCI_NEED_DATA:
        cout << error_point << " Error: OCI_NEED_DATA"<< endl;
      break;
    case OCI_NO_DATA:
        cout << error_point << " Error: OCI_NO_DATA"<< endl;
      break;
    case OCI_ERROR:
        OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
        cout << error_point << " Error: " << errbuf << endl;
      break;
    case OCI_INVALID_HANDLE:
        cout << error_point << " Error: OCI_INVALID_HANDLE" << endl;
      break;
    case OCI_STILL_EXECUTING:
        cout << error_point << " Error: OCI_STILL_EXECUTE"<< endl;
      break;
    case OCI_CONTINUE:
        cout << error_point << " Error: OCI_CONTINUE" << endl;
      break;
    default:
        cout << error_point << " Error: UNKNOWN(" << status << ")" << endl;
      break;
  }

  if( ret_code && (envhp != NULL) ) OCIHandleFree(envhp, OCI_HTYPE_ENV);

  return ret_code;

}

P.S. You can get info from Oracle documentation and this example code.

Merta

This looks super promising and I will experiment with it when I get back to work on Monday. For now have an upvote. One important question: our codebase is C++, but our existing usage of OCI is using the vanilla C interface from <ocidfn.h>. What is the C equivalent call for setVector(stmt, 1, value_list, "SYS", "ODCINUMBERLIST")? – Mendelsohn

Old-plain-C interface involve much more coding so it's takes a time to make an example with equivalent code. I hope that I have enough free time to present such example till Thursday. – Merta

Yeah, looks like it. I found another example that uses BFLOAT_ARRAY... Is there any chance to reuse that code or is it too different? – Mendelsohn

BTW the bounty expires in 3 days and I don't think there's a way to extend the deadline, so please try to come up with your answer before Thursday! :) I don't want you to work hard on an answer and not get the points. – Mendelsohn

@Mendelsohn Done. Answer updated, please check code and feel free to ask me or Oracle documentation if anything unclear ... :) – Merta

You are incredible! I will check this tomorrow when I get in. Thank you! – Mendelsohn

I have not had a chance to verify this yet but I think you have earned the credit. I will post back soon when I actually get it running. – Mendelsohn

I implemented this in our code. All the binding appears to work normally, with no status codes, but executing the statement unfortunately returns this fairly meaningless error: ORA-00728: Message 728 not found; product=RDBMS; facility=ORA Help! – Mendelsohn

@Mendelsohn Seems that you meet this error: "PLS-00728: the limit of a VARRAY can only be increased and to a maximum 2147483647". Without seeing source code it's fairly hard to suppose possible sources of the error. If you can't fix an error in reasonable time, please create new question with details. – Merta

If I use your code exactly as written, it works. I'm currently trying to figure out exactly where our DB layer goes off the rails. I'll let you know if I get stuck. – Mendelsohn

I figured out the error--I was letting OCIArray *array_param go out of scope before OCIStmtExecute was called (because we set up binds in a stand-alone function); I didn't realize that this would cause problems. OCI was attempting to access freed memory from the stack. – Mendelsohn

Once that was solved, everything fell into place! You are a gentleman and a scholar! Thank you again. This was well worth 400 points. – Mendelsohn

I tried this approach, and it seems that there is still 4kb limit for binding a variable. Is it possible to bind more than 4kb ? – Hammy

@saberduck Seems that you meet maximum size of varchar2 type, but you can use CLOB to pass more data or use collection variable and split a big amount of data to pieces organized in a collection. – Merta

well it looks like there is same limit to any variable which is bind by OCI, regardless the type. So the collection variable type is also limited to 4kb. The feature is not really well documented. – Hammy

This is really good. I have a little problem I cant figure out what to do, I want to bind string vector. I tried the following. ``` std::vector<std::string> value_list; value_list.push_back("0SA26Y88J58NE9OXRNUV8DZL"); value_list.push_back("HJQK9KRM8N7CJLMCI20J19QZ"); value_list.push_back("23LB82G4AGRC80LGHYRQ1W79"); setVector(stmt, 1, value_list, "SYS", "ODCIVARCHAR2LIST"); ResultSet *rs = stmt->executeQuery(); ``` getting an error like ORA-00932: inconsistent datatypes: expected CHAR got SYS.ODCIVARCHAR2LIST if I change ODCIVARCHAR2LIST with CHAR, it crashes – Wittenberg

Answer 2

This is certainly possible and there's no need to use PL/SQL. Assuming that you're passing numbers as you've suggested you'll first need to create an object within the database that you can use:

create or replace type t_num_array as table of number;

You can then query your table using the table as follows:

select *
  from my_table
 where id in (select * from table(t_num_array(1,2,3)) )

You're still left with the same problem; how do you bind an unknown number of variables to a statement? But you now have a bindable structure in which to put them.

Ivan's certainly right that the docs are a little confusing and my knowledge of C++ is execrable so I'm sorry but I'm short of example code. There are a few things that would be more than worth reading though. Chapter 12 of the OCI Programmers Guide on Object Relational Datatypes. It would probably be useful to know about the Object Type Translator Utility, which:

is used to map database object types, LOB types, and named collection types to C++ class declarations

Example 8-12 (the declaration of my_table) in the many_types class would imply that you can declare it as a vector<int>.

Lawford

Answer 3

Instead of dynamically building a SQL statement to use in your IN clause, try using a global temporary table to insert the values you want in your IN clause. For this to work, you'll need to make sure your table is declared as "on commit preserve rows" and truncate your table on entry into you code block.

start database transaction;

truncate temporary_table;

for each value in array
    insert into temporary_table;
end for each

open cursor 'select * from mytable where id in (select id from temporary_table)';

end database transaction;

Sc

I can't imagine this being an improvement on any axis. – Mendelsohn

It meets all your criteria, while using core parts of the language. It uses bind variables. It uses native SQL to return the answer. I've written this code before in production systems. It works. Its simple. – Sc

It round-trips into the DB multiple times to build up a temporary table. That's going to be a big performance hit. The goal is one round trip into the DB with one non-changing query, where I bind in an array of IDs. – Mendelsohn

Then change your question to indicate that it has to be a single call the database, which it does not. Besides, you can bulk bind an insert statement. – Sc

Look at my EDIT 2 for an example of what I'm looking for. – Mendelsohn

I've reorganized the question to put the technique I'm looking for at the top of the question. Thanks for your suggestion, I don't mean to dismiss it entirely, I am just looking for something which doesn't add complexity to the query. We already have a one-line solution for "build the ID IN (x,y,z) string" and I don't want to go around replacing it with a more complicated concept, just so we can bind. – Mendelsohn