Click here to Skip to main content
Click here to Skip to main content

OraLib - a lightweight C++ wrapper over Oracle's OCI library

, 3 Jul 2002
Rate this:
Please Sign up or sign in to vote.
6 classes that provide a lightweight C++ wrapper over Oracle's OCI library

On this page

  1. Introduction
  2. Connection handling
  3. Executing commands
    1. Data Definition Language (DDL)
    2. Storing data
    3. Transactions
    4. Retrieving data
    5. Executing PL/SQL blocks
  4. Use-case examples
    1. Insert table row and retrieve sequence value
    2. Retrieving particular table row
    3. Calling a stored procedure
    4. Calling a function in a package
  5. Alternatives
  6. Some final words
  7. Disclaimer

Introduction

This page will present some basic use-cases for OraLib - a lightweight C++ wrapper over Oracle's OCI library. My intention was to code an easy-to-use yet feature-full C++ library.

A note about resource ownership: almost all classes have release method and it is expected method to be called when object instance is no longer needed by the user code. There are cases, where release body is empty, but this could change in the future. release method in not called on all of the code examples below.

Pre-requisites: basic C++, SQL, PL/SQL knowedge.

In order to run samples you will also need Microsoft OS (Windows 9x, NT or 2000), Microsoft Visual C++ compiler 6.0, STLport 4.0.x or 4.5.x, Oracle 8.1.6 and up. I suppose it is possible to run the library on Linux/Unix, but I do not have the knowedge (nor patience and time) to do it yet.

What is OraLib? OraLib is a C++ library, wrapper over Oracle's OCI library. OraLib saves you low-level function (API) calls and low-level knowedge you should otherwise posess. With OraLib you can easily do the following:

  • connect/disconnect an Oracle server;
  • execute DDL commands;
  • execute SQL select/insert/update/delete commands;
  • work with transactions;
  • call packages/stored procedures or execute anonymous PL/SQL blocks;
  • work with (named) bound variables;
  • use build-in error handling by using C++ exceptions.

Following topics will present you various use-cases for OraLib.

Connection handling

There are two ways to connect to an Oracle server: specify server name, login and password as a parameters to connection object's constructor or by creating a blank object instance and calling open method later. In either case an error will be thrown if connect fails.

oralib::connection cn ("MyOracleServer", "MyLogin", "MyPassword");
...
cn.close ();

// or

oralib::connection cn ();
cn.open ("MyOracleServer", "MyLogin", "MyPassword");
...
cn.close ();

Connection could be explicitly closed by calling close method or implicitly, when object is deleted or goes out of scope. In first case object instance could be reused to connect to the same or another Oracle instance at later time.

Executing commands

Data Definition Language (DDL)

Execution of DDL commands is the simpliest case. connection object's execute method could be called explicitly with SQL string to be executed.

cn.execute ("create table a (id numeric, name varchar2 (10))");
...
cn.execute ("drop table a");

Storing data

The easiest way to store data in an Oracle database is to use SQL insert statement. A more complicated case is by calling a stored procedure, but generally there are two cases: (1) data to be stored could be inside SQL statement - as text - and, (2) data to be stored could be passed via bound variables (or parameters). Both approaches have advantages and disadvantages. In first case you should build a text string containing SQL insert statement (or stored procedure name) along with fully formatted data values - for example by calling printf. Second approach requires SQL insert statement to include bound variables names only (or it will always be a constant) and to manually bind named variables and set their values without worying about formatting. Here is an example for both:

// common for both
long id_column;
TCHAR *name_column;

// 1st approach
char sql [100];
sprintf (sql, "insert into a (id, name) values (%d, '%s')",
    id_column, name_column);
cn.execute (sql);

// 2nd approach
statement &st = *cn.prepare ("insert into a values (:n, :s)");
st.bind (":n") = id_column;
st.bind (":s") = name_column;
st.execute ();
st.release ();

Second approach is the better one, because: (1) Unicode text data could be used - OCI expects SQL statements to be in an ANSI string and Unicode text doesn't fit in ANSI character set;

wchar_t *name_in_unicode; // initialized somewhere below...
...
statement &st = cn.prepare ("insert into a (name) values (:s)");
st.bind (":s") = name_in_unicode;
st.execute ();

(2) multiple SQL insert statements (where only insert values are different) could be executed sequentially (this is also much faster compared to the 1st approach).

statement &st = *cn.prepare ("insert into a (id) values (:n)");
parameter &p = st.bind (":n");
for (long i=0; i<1000; i++)
{
    p = i;
    st.execute ();
}

Transactions

Normally in Oracle first data-related SQL statement creates an implicit transaction. For example "insert into a (id) values (1)" creates a transaction that should be explicitly closed (commited or rolled-back) ot it will be closed implicitly when the connection is closed. Until the transaction is closed the change made is visible only from within the very same connection and in some cases other connections could be blocked.

connection object provides two methods for transactions handling: commit and rollback. If you read the source code, you will find-out that both are nothing more than simple calls to connection.execute. Anyway, you should concider that transaction should be closed ASAP, because a contention could occur - either by calling one of connection.commit / connection.rollback or by including commit / rollback in your stored procedures.

Retrieving data

There are two options when data should be retrieved. The choice depends on how much data you wish to retrieve. When required data is a flag or count for example, named variables could be used. But if you need to fetch a rows of data you should use cursors (resultsets).

The usage of named variables for data retrieval is similar to their use for storing of data:

statement &st = *cn.prepare (
    "begin select count (id) into :n from a; end;");
st.bind (":n");
st.execute ();
num_rows = st [":n"];

Approach is suitable for cases where you wish to use a same named variable for both input and output.

To fetch data from an explicit SQL select statement call either connection.select or statement.select, depending whether you need to supply some input data (select condition for example).

// connection.select case

resultset &rs = *cn.select ("select name from a");
if (!rs.eod ())
    do
        cout << (Pstr) rs ["NAME"] << '\n';
    while (++rs);
rs.release ();

// statement.select case

statement &st = *cn.prepare ("select id, name from a where id = :n");
st.bind (":n") = id_required;
resultset &rs1 = *st.select ();
cout << '#' << (long) rs1 ["ID"] << ' ' << rs1 [2].as_string ();
rs1.release ();

When SQL select statement is executed and resultset object is returned, columns could be accessed in two ways: (1) by name (case sensitive) and (2) by index (whether index is 0- or 1-based is configured in oralib.h).

If you need to execute more then one SQL select statement then cursor bound variables should be used (described in the following section).

Executing PL/SQL blocks

One of powerful features of Oracle database is PL/SQL. By using OraLib you can execute PL/SQL blocks, pass input parameters and receive output parameters. Output parameters can even be resultsets (cursor in Oracle docs). Following example will execute two SQL select statements and will fetch rows by using cursor named variables:

statement &st = *cn.prepare (
    "begin\n"
    " open :c1 for select id, name from a;\n"
    " open :c2 for select * from a;\n"
    "end;");
st.bind (":c1");
st.bind (":c2");
st.execute ();

resultset &rs = st [":c1"]; // id and name columns
column &id_column = st [":c1"].as_resultset () ["ID"];
column &name_column = rs ["NAME"];
if (!rs.eod ())
    do
        cout << '#' << (long) id_column << ' '
            << (Pstr) name_column << '\n';
    while (++rs);
name_column.release ();
id_column.release ();
rs.release ();
...

resultset columns could be accessed by asking the resultset every time we need column's value or by caching it in a column object. Second approach is faster, of course, but since late binding is being used, statement should be executed first.

Use-case examples

Insert table row and retrieve sequence value

Oracle uses the concept of sequences to allow simultaneous inserts in a single table (Microsoft SQL Server uses autonumber columns). Because almost every modern system is used by more than one user at a time, "select max (id) from a_table"-way is definately wrong. But actually retrieving newly created row's id column is easy:

statement &st = *cn.prepare (
    "begin\n"
    " insert into a (id, name) values (a_seq.nextval, :s);\n"
    " :n := a_seq.currval;\n"
    " commit;\n"
    "end;");
st.bind (":s") = name_column;
st.bind (":n");
st.execute ();
cout << "newly created row's id = " << (long) st [":n"];
st.release ();

Of course this should be placed into a stored procedure.

Retrieving particular table row

statement &st = *cn.prepare (
    "select col1, col2, col3 from table_name where id = :n");
st.bind (":n") = id_we_re_looking_for;
resultset &rs = *st.select ();
...
rs.release ();
st.release ();

Calling a stored procedure

statement &st = *cn.prepare (
    "begin sp_name (:param1, :param2, :param3); end;");
st.bind (":param1", DT_TYPE) = param1_value;
st.bind (":param2", DT_TYPE) = param2_value;
st.bind (":param3", DT_TYPE) = param3_value;
st.execute ();
...
st.release ();

Calling a function in a package

statement &st = *cn.prepare (
    "begin :result := package_name.function_name ("
    ":param1, :param2, :param3); end;");
st.bind (":param1", DT_TYPE) = param1_value;
st.bind (":param2", DT_TYPE) = param2_value;
st.bind (":param3", DT_TYPE) = param3_value;
st.bind (":result", DT_TYPE);
st.execute ();
    // use st [":result"] here
...
st.release ();

Alternatives

OraLib includes only a few classes (6 to be exact), but supports lots of powerful features provided by OCI library: named variables, output cursors, PL/SQL blocks execution. Library is distributed with full source code.

OCI will took you 50 lines of formatted code to start with in order to connect an Oracle server. Not to mention "simple" things, like executing a select statement and fetching the result or binding a named variable. OO4O for C++ is simply a COM wrapper. OCCI comes with the newer Oracle versions (9 and up), but it looks like that source code is unavailable.

There are other similar projects available - go here for a bigger list.

Some final words

Hope you like it. Comments, feedback and requests are welcome.

Disclaimer

This software comes with no warranty. Use at your own risk.

Include library name and my e-mail in your projects. Notify me.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Bobi B.
Web Developer
Bulgaria Bulgaria
No Biography provided

Comments and Discussions

 
QuestionAlternative PinmemberkilroyFR10-Aug-12 2:48 
GeneralMy vote of 4 Pinmemberbikashdeshmukh3454-Jan-11 2:08 
GeneralFlexible datatype PinmemberAjay Kale New11-Nov-10 0:32 
GeneralOracle中有long型字段的问题 Pinmemberbinos16-Aug-09 16:54 
General一个小问题 Pinmemberbinos16-Aug-09 16:45 
GeneralRe: 一个小问题 Pinmemberslwgu20-Mar-12 5:57 
GeneralA small mistake Pinmemberliangar11-Oct-08 16:18 
Generaldoes oci support cursorLib just like odbc PinmemberMember 336410823-Jul-08 4:45 
GeneralTwo remarks PinmemberJoerg Walter30-Nov-07 22:11 
QuestionMany warnings compiling oralib, 3 errors and 4 warnings compiling test suite PinmemberScreaming In Digital25-Aug-07 2:09 
AnswerRe: Many warnings compiling oralib, 3 errors and 4 warnings compiling test suite PinmemberBobi B.29-Aug-07 7:09 
GeneralCompatibility with Oracle 10g PinmemberOL1720-Jun-07 6:07 
AnswerRe: Compatibility with Oracle 10g PinmemberBobi B.20-Jun-07 6:30 
GeneralGetRows PinmemberTamás Károly14-Jun-06 4:10 
GeneralRe: GetRows [modified] Pinmemberliangar1-Jul-06 4:21 
GeneralCompilet to DLL PinmemberThejimmyfo23-May-06 11:03 
Questionhelp me please. PinmemberM_Nuaimi25-Apr-06 13:14 
QuestionHow to bind float or double parameter to store procedure ? PinmemberChooi Ting24-Apr-06 23:41 
GeneralFuture support PinmemberEnrico Berto17-Mar-06 6:28 
GeneralRequest for oci library PinmemberKishore N. Kumar6-May-05 19:35 
GeneralRe: Request for oci library Pinmemberliangar16-Nov-05 16:54 
General&lt;OCI_ERROR&gt; ORA-06401 NETCMN Invalid driver program inspector PinmemberGF Hou17-Jan-05 16:18 
GeneralDLL for VB PinsussAnonymous11-Nov-04 0:28 
GeneralCLOB Datatype PinmemberLeong Hoe Wai18-Aug-04 19:14 
GeneralRe: CLOB Datatype PinmemberBobi B.18-Aug-04 22:39 
GeneralConfiguration Error PinmemberLeong Hoe Wai18-Aug-04 19:04 
GeneralRe: Configuration Error PinmemberBobi B.18-Aug-04 22:35 
Generalis there anything like this for oracle9i on linux Pinmembervamshipajjuri15-Apr-04 8:04 
GeneralRe: is there anything like this for oracle9i on linux PinmemberWim Jans13-May-04 6:27 
GeneralLink error Pinmemberngtrungthanh27-Feb-04 21:46 
GeneralRe: Link error PinmemberBobi B.1-Mar-04 0:15 
GeneralRe: Link error Pinmemberiamfjun26-May-08 14:13 
Generalnative connection PinmemberInstrumentalist12-Feb-04 23:45 
GeneralRe: native connection PinmemberBobi B.13-Feb-04 23:08 
Generalcompile error Pinmemberdaemonsys25-Nov-03 22:25 
GeneralRe: compile error Pinmemberdaemonsys25-Nov-03 23:42 
GeneralOracle9i via VC6.0 App. on W2k Pinmemberimisir24-Oct-03 0:59 
GeneralRe: Oracle9i via VC6.0 App. on W2k PinmemberBobi B.25-Oct-03 20:55 
Generalproblems with rs.eod() Pinmembergbernardes26-Sep-03 7:12 
GeneralRe: problems with rs.eod() Pinmembergbernardes26-Sep-03 7:20 
GeneralRe: problems with rs.eod() PinmemberBobi B.1-Oct-03 4:39 
GeneralNot retrieve data from resultset Pinmemberanhtrung18-Sep-03 1:26 
GeneralRe: Not retrieve data from resultset PinmemberBobi B.21-Sep-03 20:45 
GeneralDifficult to debug error PinmemberChris Meech17-Jun-03 10:24 
GeneralRe: Difficult to debug error PinmemberBobi B.18-Jun-03 21:05 
GeneralRe: Difficult to debug error PinmemberChris Meech20-Jun-03 2:02 
GeneralRe: Difficult to debug error PinmemberBobi B.20-Jul-03 3:02 
GeneralCompiler Error PinmemberRK_200012-Jun-03 3:58 
GeneralRe: Compiler Error PinmemberBobi B.12-Jun-03 5:25 
GeneralRe: Compiler Error Pinsussrkhalili@hotmail.com12-Jun-03 15:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 4 Jul 2002
Article Copyright 2002 by Bobi B.
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid