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

succinct version:

i'm looking c++ oci adaptation of following java technique, code able bind array of numbers (the array size can vary) non-pl/sql select statement , use resulting array in where id in (...) style check.

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

original question:

we have c++ app talks oracle via oci. we're trying fix old code generates sql queries concatenating text; instead want use bind variables as possible. 1 particular case has come don't have solution for.

select * mytable id in (1, 4, 10, 30, 93) 

where (1, 4, 10, 30, 93) part comes vector<int> or other flexibly-sized container of data. if knew 5 values, do:

select * mytable id in (:1, :2, :3, :4, :5) 

but might 1 entry, or ten, or maybe zero. obviously, if building query string, can append many numbers need, goal avoid if possible , stick bind variables.

is there way accomplish this? instance, in oci, can bind array , sub-select out of it?

select * mytable id in (select * :1) 

where :1 oci array? (probably syntax differ.) have experience this? sample code godsend tend struggle writing raw oci. :)

edit: i'd better binding in string parsed pl/sql procedure, if @ possible. confident blow out 4000 character limit in many cases, , feel that's trading 1 kind of string manipulation i'm comfortable with, kind i'm not (and can't debug easily). if possible i'd bind array of values (or form of dataset) 1 standard sql statement.

edit 2: investigation turned following link seems doing want, in java: http://rafudb.blogspot.com/2011/10/variable-inlist.html know how adapt approach c++ oci?

this example demonstrates approach using collection type, defined in database pass list of parameters.
sys.odcinumberlist standard collection type available users. query, used in sample select first 100 integers ( test ) , filter integers list in in(...) clause.

#include "stdafx.h" #include <iostream> #include <occi.h>  using namespace oracle::occi; using namespace std;  // vector type pass parameter list typedef vector<number> valuelist;  int _tmain(int argc, _tchar* argv[]) {   environment *env;   connection *con;    // note environment must initialized in object mode    // 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 * "                  " (select level col dual connect level <= 100)"                  "where "                  "  col in (select column_value table(:key_list))"                );      cout << endl << endl << "executing block :" << endl           << stmt->getsql() << endl << endl;      // create instance of vector trype defined above      // , populate 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 parameter #1 in query , treat 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 pass list of numbers, dates or strings oracle via oci or define own type in db.

example compiled visual studio 10 express , this version of oci libraries. tested against oracle 11.2.0.3.0 .

update

below example application same thing 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 shorten main code, returns true if critical error detected // , 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;       /* environment handle */   ociserver *mysrvhp;    /* server handle */   ocierror *myerrhp;     /* error handle */   ocisession *myusrhp;   /* user session handle */   ocisvcctx *mysvchp;    /*  service handle */    /* initialize mode threaded , object environment */   /* note: oci_object must present work 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 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 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 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 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 server attribute in 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 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 user session attribute in 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 ------------------------------------------------------    // section can invoked once per session 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 = 1; <= 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 , execute statement ------------------------------    ocistmt   *mystmthp   = null;   ocidefine *col1defp   = null;   double    col1value;     ocibind   *bndp       = null;    char      *query_text = "select * "                           " (select level col dual connect level < 100)"                           "where "                           "  col in (select column_value 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 , 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 shorten main code, returns true if critical error detected // , 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. can info oracle documentation , this example code.