Oracle OCI, bind variables, and queries like ID IN (1, 2, 3)
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.
Oracle in C#, bind variables, and queries like ID IN (1, 2, 3)
Code:
oraParam.UdtTypeName = "SYS.ODCINUMBERLIST";
VArray newArray = new VArray();
newArray.Array = new Int32[] {12,24,42};
oraParam.OracleDbType = OracleDbType.Array;
oraParam.Value = newArray;
string query = @"Select * from TABLE(:1) ";
OracleCommand command = new OracleCommand(query, MyConnection);
command.Parameters.Add(oraParam);
OracleDataReader reader;
var m_connection = new OracleConnection("The CONNECTION STRING");
m_connection.Open();
var reader = command.ExecuteReader();
reader.Close();
m_connection.Close();
Which is followed by the following Helper Classes:
class VArray : IOracleCustomType, INullable
{
[OracleArrayMapping()]
public Int32[] Array;
private OracleUdtStatus[] m_statusArray;
public OracleUdtStatus[] StatusArray
{
get
{
return this.m_statusArray;
}
set
{
this.m_statusArray = value;
}
}
private bool m_bIsNull;
public bool IsNull
{
get
{
return m_bIsNull;
}
}
public static VArray Null
{
get
{
VArray obj = new VArray();
obj.m_bIsNull = true;
return obj;
}
}
public void ToCustomObject(OracleConnection con, IntPtr pUdt)
{
object objectStatusArray = null;
Array = (Int32[])OracleUdt.GetValue(con, pUdt, 0, out objectStatusArray);
m_statusArray = (OracleUdtStatus[])objectStatusArray;
}
public void FromCustomObject(OracleConnection con, IntPtr pUdt)
{
OracleUdt.SetValue(con, pUdt, 0, Array, m_statusArray);
}
public override string ToString()
{
if (m_bIsNull)
return "VArray.Null";
else
{
string rtnstr = String.Empty;
if (m_statusArray[0] == OracleUdtStatus.Null)
rtnstr = "NULL";
else
rtnstr = Array.GetValue(0).ToString();
for (int i = 1; i < m_statusArray.Length; i++)
{
if (m_statusArray[i] == OracleUdtStatus.Null)
rtnstr += "," + "NULL";
else
rtnstr += "," + Array.GetValue(i).ToString();
}
return "VArray(" + rtnstr + ")";
}
}
}
[OracleCustomTypeMapping("SYS.ODCINUMBERLIST")]
public class VArrayFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
{
// IOracleCustomTypeFactory
public IOracleCustomType CreateObject()
{
return new VArray();
}
// IOracleArrayTypeFactory Interface
public Array CreateArray(int numElems)
{
return new Int32[numElems];
}
public Array CreateStatusArray(int numElems)
{
// CreateStatusArray may return null if null status information
// is not required.
return new OracleUdtStatus[numElems];
}
}
DESCRIPTION:
The general idea is that similar to the OCI example, you have to cast the parameter as a SYS.ODCINUMBERLIST (Or other valid type). That type is not defined by default in the C# OracleDBType so you have to use the UdtTypeName and a custom Factory/Class to bind successfully.
This was inspired from the following post on defining custom types.
Limits:
This exact solution will only work with INT/NUMBER values because it is piggybacking off of the SYS.ODCINUMBERLIST table type. If it is needed for other types you may need to find/write additional custom table types.
Oracle SQL Developer: can bind variables begin with upper case letter?
Oracle SQL Developer: can bind variables begin with upper case letter?
Yes.
There is no issue with SQL Developer. I have tested it on version 3.2.20.10
Please see the screenshots:
Query:
Result:
No issues in SQL*Plus either:
SQL> variable BindVariable VARCHAR2(20)
SQL> EXEC :BindVariable := 'SMITH'
PL/SQL procedure successfully completed.
SQL> SELECT empno FROM emp WHERE ename LIKE :BindVariable;
EMPNO
----------
7369
SQL>
Issue with Oracle bind variables not using index properly
This is a bigger topic really, but this is the approach that I think is easiest to implement and works well. The trick is to use dynamic SQL, but implement it so that you always pass the same number of parameters (needed), AND you allow Oracle to short-circuit when you don't have a value for a parameter (what you are lacking in your current approach). For example:
set serveroutput on
create or replace procedure test_param(p1 in number default null, p2 in varchar2 default null) as
l_sql varchar2(4000);
l_cur sys_refcursor;
l_rec my_table%rowtype;
l_ctr number := 0;
begin
l_sql := 'select * from my_table where 1=1';
if (p1 is not null) then
l_sql := l_sql || ' and my_num_col = :p1';
else
-- short circuit for optimizer (1=1)
l_sql := l_sql || ' and (1=1 or :p1 is null)';
end if;
if (p2 is not null) then
l_sql := l_sql || ' and name like :p2';
else
-- short circuit for optimizer (1=1)
l_sql := l_sql || ' and (1=1 or :p2 is null)';
end if;
-- show what the SQL query will be
dbms_output.put_line(l_sql);
-- note always have same param list (using)
open l_cur for l_sql using p1,p2;
-- could return this cursor (function), or simply print out first 10 rows here for testing
loop
l_ctr := l_ctr + 1;
fetch l_cur
into l_rec;
exit when l_cur%notfound OR l_ctr > 10;
dbms_output.put_line('Name is: ' || l_rec.name || ', Address is: ' || l_rec.address1);
end loop;
close l_cur;
end;
To test, simply run it. For example:
set serveroutput on
-- using 0 param
exec test_param();
-- using 1 param
exec test_param(123456789);
-- using 2 params
exec test_param(123456789, 'ABC%');
On my system, the table used is over 100mm rows with an index on the number field and name field. Returns almost instantly. Also note that you may not want to do a select * if you don't need all columns, but I'm being a bit lazy and using %rowtype for this example.
Hope that helps
Using bind variables in SQL Plus with more than one row returned?
Similar to @Glenn's approach, but you can declare a bind variable in SQL*Plus and use it in a plain SQL query. First declare it with the var[iable] command:
variable comment_id number;
Then set it with the exec[ute] command, which is essentially an anonymous block:
execute :comment_id := 3052753;
Then run your original query with the :comment_id references, and no BEGIN or END:
select e.label as doc_name,
e.url,
i.item_id,
'multi' as form_type
from cr_items i, cr_extlinks e
where i.parent_id = :comment_id
and e.extlink_id = i.item_id
UNION
select null as doc_name,
utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(r.content, 2000, 1)) as url,
r.item_id,
'single' as form_type
from cr_revisions r
where r.revision_id = ( select content_item.get_latest_revision(:comment_id) from dual);
I don't think there's much functional difference between the two approaches beyond personal preference, and both also work in SQL Developer (when run as a script). I find this easier when running SQL copied from a Pro*C file which already uses the : bind form, purely because you don't have to modify the code at all.
Incidentally, you can write:
where r.revision_id = ( select content_item.get_latest_revision(:comment_id) from dual)
without the extra select, as:
where r.revision_id = content_item.get_latest_revision(:comment_id)
What is the impact/limitation of oracle select with large number of bind variables?
In Oracle 8 and later, you can create usage profiles that control and limit the resources that any one session can consume. You create a profile and associate it to a user or role and the database will make sure that thing like logical/physical IO, CPU, and other limited resources are shared more equitably.
The interesting bits of a profile include:
[CPU_PER_SESSION n|UNLIMITED|DEFAULT]
[CPU_PER_CALL n|UNLIMITED|DEFAULT]
[CONNECT_TIME n|UNLIMITED|DEFAULT]
[IDLE_TIME n|UNLIMITED|DEFAULT]
[LOGICAL_READS_PER_SESSION n|UNLIMITED|DEFAULT]
[LOGICAL_READS_PER_CALL n|UNLIMITED|DEFAULT]
[COMPOSITE_LIMIT n|UNLIMITED|DEFAULT]
[PRIVATE_SGA n [K|M]|UNLIMITED|DEFAULT]
As for bind variables, I'm not aware of any way to have the database or OCI client limit the use of these. In fact, bind variables are generally better for performance (and security) than embedded values in the SQL. Specifically, the reduce the number of hard parses that the database must perform when executing SQL that only varies in the values of parameters.
Pro*C returning Not a number in the IN clause
IN clause accept bind variables only as (:1,:2,:3) , so you have yo know the number of bind variables before hand. Which is not likely.
Simplest way is to form a dynamic query string with hard coded values in Pro*C.
There are alternative solutions from AsKTom and My SO answer
Correct use of bind variables with dates in Oracle?
Is the date format a constant? Or does it change at runtime?
Normally, you know what format the string is (at least expected) to be in so the date format would be a constant. If something is a constant, it is not necessary to make it a bind variable, it can just be hard-coded as part of the statement. In this case, it wouldn't matter either way but there are cases where you'd rather the value be hard-coded in the SQL statement because you want to give the optimizer more information (think of a column with highly skewed data where you're always looking for a particular hard-coded value).
On the other hand, if the date format changes at runtime because someone is passing both the string representation of the date and the format the string is in to your procedure, it would make sense for the date format to be a bind variable.
Related Topics
How to Run the Preprocessor on Local Headers Only
How to Program for Windows Phone 7 in Standard C++ Only
How to Convert "Pointer to Pointer Type" to Const
What Is Aggregate Initialization
Qt MACro Keywords Cause Name Collisions
In C++ 11, How to Invoke an Arbitrary Callable Object
C++:Handle Resources If Constructors May Throw Exceptions (Reference to Faq 17.4]
C++ Issue with Cin and Ctrl + Z
What Is the Purpose of the "Final" Keyword in C++11 for Functions
How to Handle a Transitive Dependency Conflict Using Git Submodules and Cmake
Oracle Oci, Bind Variables, and Queries Like Id in (1, 2, 3)
What's the Difference Between Type(Myvar) and (Type)Myvar
Difference Between Regex_Match and Regex_Search
Why Does Sqrt() Work Fine on an Int Variable If It Is Not Defined for an Int
Ordering of Using Namespace Std; and Includes
How to Remove Straight Lines or Non-Curvical Lines in a Canny Image