The Oracle Call Interface (OCI) lets you get close and cuddly with Oracle. One of the chief benifits of the OCI is the ability to leverage the OCI Instant Client, a small set of shared libaries that allow your application to run without an $ORACLE_HOME defined... and thus without a full install of the Oracle client software! These libraries (libociei.so, libclnstsh.so.10.1, and libnnz10.so) can really be a life saver if your creating small portable applications in places you don't want to install a full client.
However, the OCI isn't for the timid. You can have a look at the headers commonly used: oratypes.h and oci.h.
The typical flow is to initialize the OCI enviroment (OCIEnvCreate()), connect to the database (OCILogon()), prepare (OCIStmtPrepare()) and then execute (OCIStmtExecute()) SQL statments, and finally disconnect (OCILogoff). Along with that, you've got a pile of diffrent OCI handles that you've got to initialize, destroy, and manage which can become tedious. However, because this is a low level interface you can work outside of just using SQL statements and interact with Oracle directly.
Check out a sample SQL select in OCI:
text *sqlstmt = (text *)"SELECT * FROM employees WHERE employee_id = 100";
checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (OraText *)sqlstmt,
(ub4)strlen((char *)sqlstmt),
(ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, 0, 0,
(OCISnapshot *) 0, (OCISnapshot *) 0, OCI_DESCRIBE_ONLY));
/* .... */
Notice that for safety sake everything is wrapped in checkerr() functions. Here a SQL statement is defined (text * sqlstmt) then it is prepared with OCIStmtPrepare(), and executed with OCIStmtExecute(). Your seeing alot of diffrent database handles in there as arguments.
Like I said, not for the timid. But if you need absolute power and you've got way too much time on your hands, OCI is definately the way to go. If fits a variety of common needs despite the extra work and after spending some time with the API should get easier and easier to work with.
For the full detail on the OCI flip through the Oracle Call Interface Programmer's Guide:
http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10779/toc.htm