Oracle C Interface

This document will describe how to compile and link a C program with Oracle 10g database software. It will include instructions on how to set up the development environment, what to include in a Makefile to compile and link code, and a sample program.

Overview

Oracle software includes the ability to create natively linked applications that access the database rapidly during run-time. ECN has maintained Oracle databases over many revisions, and with the all revision including the latest, Oracle 10g, the code to compile and link applications changes slightly.

Two of the biggest changes with the latest version of the database is that the libraries are setup for calling routines in Oracle using 64-bit mode instead 32-bit mode, the location of the Oracle include files moved to a new location, and that linking applications with Oracle use shared libraries instead of static libraries.

Development Environment

Create a development and testing enviornment with the following steps:

Oracle Environment

The first set to compiling an application with Oracle is to setup the Oracle environment. This is done with one of two scripts:

• /usr/new/bin/oracle_setup.sh (when running sh or ksh),
• /usr/new/bin/oracle_setup.csh (when running csh or tcsh).

Either source the script into your environment, or add the script to your login script (.profile for sh or ksh users, or .login for csh or tcsh users).

Compiling and Linking Flags

Next, update the Makefile to include a 64-bit compile and link with Oracle libraries.

64-bit compiles are enabled in two ways, depending on whether you're using gcc or cc. Add the following switch to the compile and link lines:

• Add -m64 (when running gcc compiler),
• Add -xtarget=ultra -xarch=v9 (when running cc compiler).

Next, update the Makefile to include the location of C include files:

• Set variable INCLUDE=$(ORACLE_HOME)/rdbms/public,
• Add -I$(INCLUDE) to the C compiler line.

Last, add Oracle libraries to the link lines:

• Set variable LIB=$(ORACLE_HOME)/lib,
• Add -L$(LIB) -R$(LIB) -lclntsh to the linker line.

Sample

In order to show the development environment in full, here is an example application. This application will log on to Oracle, extract data from a table, and return it to standard output as a comma-seperated value listing, suitable for import to another database.

The example program will need four things from the users:

• The SID of the Oracle database,
• The logon name of the Oracle user,
• The logon password of the Oracle user, and
• The name of the table to display.

The source code for the C program is (download a copy here):

/* Include files */
#include <stdio.h>
#include <stdlib.h>

/* Oracle include files */
#define __64BIT__
#include <ociapr.h>
#include <ocidem.h>

/* Maximum number of columns */
#define MAX_COLUMNS	256

/* Column descriptior */
struct column {
	ub1 * value;
	ub2 length;
	sb2 indicator;		
	ub2 return_code;
};

/* Name of program */
char * arg0;

/* Database connection and cursor */
Lda_Def lda;
ub1 hda[HDA_SIZE];
Cda_Def cda;

/* Make sure database is closed before exiting program */
static void do_exit(int status)
{
	/* Close cursor and log off */
	oclose(&cda);
	ologof(&lda);

	/* Exit program */
	exit(status);
}

/* Display error from last failed Oracle command - then exit */
static void err_report(Cda_Def * cursor)
{
	OraText msg[512];
	sword n;

	/* Get error message */
	n = oerhms(&lda, cursor->rc, msg, sizeof(msg));
	fprintf(stderr, "%s: Oracle error: %s", arg0, msg);

	/* Show additional function call information */
	if (cursor->fc > 0)
		fprintf(stderr, " (function %s)", oci_func_tab[cursor->fc]);
	fputc('\n', stderr);

	/* Exit with error */
	do_exit(1);
}

/* Main program */
int main(int argc, char ** argv)
{
	char * database;
	char * login;
	char * password;
	char * table;
	char command[128+1];
	sb1 name[32+1];
	sb2 cc;
	sb4 display;
	sb4 name_len;
	struct column col[MAX_COLUMNS];
	sword c;
	ub2 len;

	/* Save program name */
	arg0 = argv[0];

	/* Process all of the arguments */
	if (argc != 4) {
		fprintf(stderr, "Usage: %s database login table\n", arg0);
		return 1;
	}

	/* Save arguments */
	database = argv[1];
	login = argv[2];
	table = argv[3];

	/* Prompt for password */
	password = getpassphrase("Oracle password: ");

	/* Create select statement */
	snprintf(command, sizeof(command), "select * from %s", table);

	/* Log on to Oracle */
	if (olog(&lda, hda,
		(OraText *) login, -1,
		(OraText *) password, -1,
		(OraText *) database, -1,
		OCI_LM_DEF)
	)
		err_report(&lda);

	/* Open a cursor */
	if (oopen(&cda, &lda, NULL, -1, -1, NULL, -1))
		err_report(&cda);

	/* Parse the SQL command line */
	if (oparse(&cda, (OraText *) command, -1, 1, 2))
		err_report(&cda);

	/* Process all columns */
	for (c = 0; c < MAX_COLUMNS; c++) {

		/* Get name and size of this column */
		name_len = sizeof(name)-1;
		if (odescr(&cda, c+1,
			NULL,
			NULL,
			name, &name_len,
			&display,
			NULL,
			NULL,
			NULL)
		) {

			/* Stop if last column, otherwise error */
			if (cda.rc == VAR_NOT_IN_LIST)
				break;
			err_report(&cda);
		}

		/* Terminate name */
		name[name_len] = '\0';

		/* Show column name to output */
		if (c)
			putchar(',');
		putchar('"');
		fputs((char *) name, stdout);
		putchar('"');

		/* Allocate storage for result */
		col[c].value = malloc(display+1);
		if (col[c].value == NULL) {
			perror("malloc");
			do_exit(1);
		}

		/* Bind storage to column when fetched */
		if (odefin(&cda, c+1,
			col[c].value, display+1,
			SQLT_STR,
			-1,
			&col[c].indicator,
			NULL, -1, -1,
			&col[c].length,
			&col[c].return_code)
		)
			err_report(&cda);
	}
	putchar('\n');
	cc = c;

	/* Execute SQL command and get read to read results */
	if (oexec(&cda))
		err_report(&cda);
	do {

		/* Fetch next column */
		ofetch(&cda);

		/* Process result of next column */
		switch (cda.rc) {

		/* End of table */
		case NO_DATA_FOUND:
			break;

		/* Row returned */
		case NULL_VALUE_RETURNED:
		case 0:

			/* Display all columns. All columns will be strings
			so process string and check for quotes that need
			quoting */
			for (c = 0; c < cc; c++) {
				if (c)
					putchar(',');
				if (col[c].indicator)
					col[c].length = 0;
				putchar('"');
				for (len = 0; len < col[c].length; len++) {
					if (col[c].value[len] == '"')
						putchar('"');
					putchar(col[c].value[len]);
				}
				putchar('"');
			}
			putchar('\n');
			break;

		/* Some weird error happened */
		default:
			err_report(&cda);
		}
	} while (cda.rc != NO_DATA_FOUND);

	/* Success */
	do_exit(0);
	return 0; /* Not reached */
}

The Makefile for the application is (download a copy here):

#
#  Note: You must source the Oracle setup files first before attempting
#  to make or execute this program.  This is done with either:
#
#	. /usr/opt/bin/oracle_setup.sh
#  or
#
#	source /usr/opt/bin/oracle_setup.csh
#
#  Depending on your shell type
#

INCLUDE=$(ORACLE_HOME)/rdbms/public
LIB=$(ORACLE_HOME)/lib

CC=cc -xtarget=ultra -xarch=v9 
CFLAGS=-v -O -I$(INCLUDE)
LDFLAGS=-O
LIBS=-L$(LIB) -R$(LIB) -lclntsh

sql:		sql.o
		$(CC) $(LDFLAGS) -o sql sql.o $(LIBS)

clean:
		rm -f core *.core *.o sqlnet.log sql

(Also, a Makefile using the gcc compiler is available by download here.)