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?

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.

Answer 2

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;

Answer 3

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>.