Oracle Caller interface using C

Using the C interface OCI for connectivity with Oracle Database
Steps :
1.      Check if OCI (Oracle Call Interface) exists at the Oracle home directory, for example : E:\oracle\ora92\product\9.2.0.1.0\db_1\oci

2.      Create a database ada1, with a table adatab (id integer, PartNumber varchar(20), Nomenclature varchar(20)); and insert some values into the table.

3.      Create a test file for testing the connectivity to Oracle database and for Fetching data from the database, called Test.c at say, D:\Temp\Test.c.

4.      Information about Oracle Database Connection :

- For each connection to Oracle DB you need to create one pair of LDA and HDA data structures.

- LDA stands for Logon Data Area.
- HDA stands for Host Data Area.
- Function olog() used to established connection to Oracle DB.
- Function oerhms() returns the text of an Oracle error message, given the error code.



5.      Following functions are used to fetch data from the database: oopen(), oparse(), odefin() and oexfet().
-          oopen() is a function that is used to create CDA – Cursor data Area. Cursor contains information about the processing of a SQL statement like error codes, number of records returned and as such.
-          oparse() is a function is used to parse SQL statement or a PL/SQL block and associates it with a cursor.
-          odefin() is a function is used to define an output variable for a specified SELECT-list item of a SQL SELECT query.
-          oexfet() is a function is used to execute SQL statement associated with a cursor, then fetches one or more rows.

6.      The contents of the Test.c file would be as follows :

#include <ocidem.h>
#include <stdlib.h>
#define  DEFER_PARSE        1
#define  VERSION_7          2

Lda_Def lda;
char    hda[256];

//Function for handling Error
void showError(Lda_Def* dataArea)
{
char msg[512];
oerhms(&lda, dataArea->rc, msg, sizeof(msg));
printf("Error code   : %i \n", dataArea->rc);
printf("Error message: %s \n", msg );   
exit  (EXIT_FAILURE);
}

void fetchTuples( Cda_Def *cda, char name[20][20], int id[20], char *sql)
{
/* CONNECT TO DB */
          if (olog (&lda, hda, "system", -1, "system", -1, "ada1", -1, OCI_LM_DEF))
{ showError(&lda); }
          printf("Connected to ORACLE. \n");


          /* RUN THE SELECT QUERY  */
// Attach the Handle
          if( oopen(cda, &lda, 0, -1,-1, 0, -1))
          showError(cda);
         
          //Parsing the SQL statement
          if( oparse( cda, sql, -1, DEFER_PARSE, VERSION_7) )
                      showError(cda);
         
          //Assigning the variables
          if( odefin( cda, 1, id, sizeof( int), INT_TYPE,  -1,0 ,0, -1,-1,0, 0) )
                      showError(cda);                     
          if(  odefin(cda, 2, name, sizeof( name[0]), STRING_TYPE, -1,0 ,0, -1,-1,0, 0))
                      showError(cda);

          //Fetching results
          if( oexfet(cda, 20, FALSE, FALSE) )
                      {
                                  if( cda->rc!=NO_DATA_FOUND)
                                              showError(cda);
                      }                     
}

/* CLOSE DB CONNECTION */
void closeDbConnection( Cda_Def *cda )
{
          if( oclose(cda) )
                      showError( &lda);
          if (ologof(&lda))
                      showError(&lda);
}

/*MAIN FUNCTION*/
main()
{     
          Cda_Def cda;
          char name[20][20];
          int id[20];
          int i;
         
          fetchTuples(&cda, name, id, "select id,PartNumber from adatab" );

          printf("\n\aName \t Id\n");
          for( i=0; i<cda.rpc; i++)
{
                      printf("%s\t ", name[i]);
                      printf("%i\t \n", id[i]);
          }
          closeDbConnection(&cda);

}


7.      To compile and link the file Test.c, do the following steps :
- cl Test.c /link oci.lib msvcrt.lib /nod:libcmt
- Test.exe

* If you don't want to rely on Environment Variables INCLUDE and LIB you can specify directories manually.
NOTE : Successful execution of the above mentioned steps indicates successful linking with the Oracle database for fetching information from the database.




Refrerence: 
function reference:
http://www.helsinki.fi/~atkk_klp/oradoc/DOC/api/doc/OCI73/ch4a.htm#odefin
nmake:  http://www.bogotobogo.com/cplusplus/make.php