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

OraLib, Examples 

SQL select statement example 

Following code should make a list with objects (that is tables, packages, sequences, etc.) for Scott Tiger (Oracle sample) database schema.

	...
	cn.open ("server_name", "scott", "tiger");
	oralib::resultset &rs = *cn.select (
		"select OBJECT_NAME, OBJECT_TYPE, TIMESTAMP, STATUS from OBJ");
	oralib::column &col = rs ["OBJECT_TYPE"];
	while (!rs.eod ())
	{
		_tprintf (_T ("%-18s %-18s %-20s %s\n"),
			(oralib::Pstr) rs ["OBJECT_NAME"],
			(oralib::Pstr) col,
			(oralib::Pstr) rs [3],
			(oralib::Pstr) rs [4]);
		rs.next ();
	}
	rs.release ();
	cn.close ();
	...

Sample output (but for different schema):

...
CART_PKG           PACKAGE            2001-12-09:19:31:05  VALID
CART_PKG           PACKAGE BODY       2001-12-09:19:31:06  VALID
CATEGORIES         TABLE              2001-12-09:19:30:58  VALID
CAT_CID            INDEX              2001-12-09:19:31:00  VALID
CAT_PK             INDEX              2001-12-09:19:30:59  VALID
CAT_SEQ            SEQUENCE           2001-12-09:19:31:00  VALID
...

PL/SQL block example 

	...
	oralib::statement &s = *c.prepare ("begin :n1 := :n2 * 2; end;");
	oralib::parameter &p = s.bind (":n1");
	s.bind (":n2") = (long) 40;
	s.execute ();
	_tprintf (_T ("%d\n"), (long) p);
	p.release ();
	s.release ();
	...

Named variables (or parameters) 

	oralib::statement &s = *c.prepare (
		"insert into TableName values (:n1)");
	oralib::parameter &p = s.bind (":n1");
	for (int i=0; i<1000; ++i, p = (long) i)
		s.execute ();
	s.release ();
	c.commit ();

Prepared statements executed multiple times are about two-times faster than statements, prepared before each execution (when Oracle server and the client are running on a same machine).

OraLib port of cdemo81.c 

Following SQL code will create database tables used (if you don't have demo database installed):

create table EMP
(
EMPNO numeric,
ENAME varchar2 (50),
JOB varchar2 (50),
SAL numeric,
DEPTNO numeric
);
alter table EMP add constraint EMP_PK primary key (EMPNO);

create table DEPT
(
DEPTNO numeric,
DNAME varchar2 (50)
);
alter table DEPT add constraint DEPT_PK primary key (DEPTNO);

insert into DEPT values (1, 'Management');
insert into DEPT values (2, 'Support');
insert into DEPT values (3, 'Sales');
insert into DEPT values (4, 'Production');
insert into DEPT values (5, 'QA');
commit;

OraLib port of cdemo81.c source (original cdemo81.c is more than 400 lines long; this one is about a 100):

void	cdemo81 (void)
{
	oralib::connection cn;

	try
	{
		// connect
		cn.open ("ora1", "thesis", "thesis");

		// retrieve the current maximum employee number
		oralib::resultset &rs = *cn.select ("SELECT NVL(MAX(empno), 0) FROM emp");
		sword empno = (sword) (long) rs [1];
		rs.release ();

		// prepare both statements for execution
		oralib::statement &st_insert = *cn.prepare (
			"INSERT INTO emp(empno, ename, job, sal, deptno) "
			"VALUES (:nEmpno, :sEname, :sJob, :nSal, :nDeptno)");
		
		oralib::statement &st_dep_name = *cn.prepare (
			"SELECT dname FROM dept WHERE deptno = :nDeptno");

		// bind named variables
		st_insert.bind (":nEmpno");
		st_insert.bind (":sEname");
		st_insert.bind (":sJob");
		st_insert.bind (":nSal");
		st_insert.bind (":nDeptno");
		st_dep_name.bind (":nDeptno");

		while (true)
		{
			std::string ename, ejob, dept;
			sword esal, deptno;

			// get information
			std::cout << "\nEnter employee name (or Ctrl+Z, Enter to EXIT): ";
			std::cin >> ename;
			if (ename.length () == 0) break;
			std::cout << "Enter employee job: ";
			std::cin >> ejob;
			std::cout << "Enter employee salary: ";
			std::cin >> esal;

			// ask for department and verify that exists
			bool found = false;
			do
			{
				std::cout << "Enter employee dept: ";
				std::cin >> deptno;
				st_dep_name [1] = (long) deptno;
				oralib::resultset &rs = *st_dep_name.select ();
				found = !rs.eod ();
				if (found)
					dept = rs [1].as_string ();
				else
					std::cout << "The dept you entered doesn't exist.\n";
				rs.release ();
			}
			while (!found);

			// insert an employee record
			st_insert [":sEname"] = ename.c_str ();
			st_insert [":sJob"] = ejob.c_str ();
			st_insert [":nSal"] = (long) esal;
			st_insert [":nDeptno"] = (long) deptno;
			bool succeeded = false;
			do
			{
				st_insert [":nEmpno"] = (long) empno;
				try
				{
					st_insert.execute ();
					cn.commit ();
					succeeded = true;
				}
				catch (oralib::error &e)
				{
					empno += 10;
				}
			}
			while (!succeeded);

			std::cout << ename << " added to the " << dept <<
				" department as employee number " << empno;
		}

		st_insert.release ();
		st_dep_name.release ();
		cn.close ();
	}
	catch (oralib::error &e)
	{
		e.display ();
	}
}