A simple Hello World program using C and PRO*C

Just because many visitors ask me to write some C and PRO*C code to interface with an ORACLE database, today I will write a simple "Hello World!" program.

First of all you have to set at least your LD_LIBRARY_PATH and in general you should set as $ORACLE_HOME/lib.
In my .profile file I have:

export ORACLE_SID=primary
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin;/bin;/usr/bin;/usr/sbin;/usr/local/bin;/usr/X11R6/bin
umask 022

Then login as sysdba and create the user hello:

CREATE USER hello IDENTIFIED BY hello
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS;

Grant to the hello user the following privileges:

GRANT CREATE SESSION, CONNECT, RESOURCE TO hello;

Login as hello user and create the hello_world table:

CONNECT hello/hello;

CREATE TABLE hello_world (msg VARCHAR2(50));

and exit from sqlplus;

Then type

vi hellodb.pc

and write the following code:
#include <stdio.h>

#include <sqlca.h>
#include <sqlcpr.h>
#include <oraca.h>
#include <sqlda.h>

/* Declare error handling function. */
void sql_error();

int main(int argc, char** argv)
{
char user[]="hello";
char pwd[]="hello";
char msg_buf[51]="";

/* Register sql_error() as the error handler. */
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error\n");

EXEC SQL CONNECT :user IDENTIFIED BY :pwd;

EXEC SQL
INSERT INTO hello_world
VALUES ('Hello world!');

EXEC SQL COMMIT;

EXEC SQL
SELECT msg
INTO :msg_buf
FROM hello_world
WHERE rownum <= 1;

printf("%s\n", msg_buf);

return(0);
}

void sql_error(char *msg)
{
char err_msg[128];
int buf_len, msg_len;

EXEC SQL WHENEVER SQLERROR CONTINUE;

printf("%s\n", msg);
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);

if (msg_len > buf_len)
msg_len = buf_len;

printf("%.*s\n", msg_len, err_msg);

EXEC SQL ROLLBACK RELEASE;

exit(1);
}

Save this file and then let the precompiler to create from the hellodb.pc file the hellodb.c file, typing:

proc iname=hellodb.pc MODE=ORACLE

If you type

more hellodb.c

you will see something like the following code:

/* Result Sets Interface */
#ifndef SQL_CRSR
# define SQL_CRSR
struct sql_cursor
{
unsigned int curocn;
void *ptr1;
void *ptr2;
unsigned int magic;
};
typedef struct sql_cursor sql_cursor;
typedef struct sql_cursor SQL_CURSOR;
#endif /* SQL_CRSR */

/* Thread Safety */
typedef void * sql_context;
typedef void * SQL_CONTEXT;

/* Object support */
struct sqltvn
{
unsigned char *tvnvsn;
unsigned short tvnvsnl;
unsigned char *tvnnm;
unsigned short tvnnml;
unsigned char *tvnsnm;
unsigned short tvnsnml;
};
typedef struct sqltvn sqltvn;

struct sqladts
{
unsigned int adtvsn;
unsigned short adtmode;
unsigned short adtnum;
sqltvn adttvn[1];
};
typedef struct sqladts sqladts;

static struct sqladts sqladt = {
1,1,0,
};

/* Binding to PL/SQL Records */
struct sqltdss
{
unsigned int tdsvsn;
unsigned short tdsnum;
unsigned char *tdsval[1];
};
typedef struct sqltdss sqltdss;
static struct sqltdss sqltds =
{
1,
0,
};

/* File name & Package Name */
struct sqlcxp
{
unsigned short fillen;
char filnam[11];
};
static struct sqlcxp sqlfpn =
{
10,
"hellodb.pc"
};


static unsigned int sqlctx = 73675;


static struct sqlexd {
unsigned int sqlvsn;
unsigned int arrsiz;
unsigned int iters;
unsigned int offset;
unsigned short selerr;
unsigned short sqlety;
unsigned int occurs;
short *cud;
unsigned char *sqlest;
char *stmt;
sqladts *sqladtp;
sqltdss *sqltdsp;
void **sqphsv;
unsigned int *sqphsl;
int *sqphss;
void **sqpind;
int *sqpins;
unsigned int *sqparm;
unsigned int **sqparc;
unsigned short *sqpadto;
unsigned short *sqptdso;
unsigned int sqlcmax;
unsigned int sqlcmin;
unsigned int sqlcincr;
unsigned int sqlctimeout;
unsigned int sqlcnowait;
int sqfoff;
unsigned int sqcmod;
unsigned int sqfmod;
void *sqhstv[4];
unsigned int sqhstl[4];
int sqhsts[4];
void *sqindv[4];
int sqinds[4];
unsigned int sqharm[4];
unsigned int *sqharc[4];
unsigned short sqadto[4];
unsigned short sqtdso[4];
} sqlstm = {12,4};

/* SQLLIB Prototypes */
extern sqlcxt (/*_ void **, unsigned int *,
struct sqlexd *, struct sqlcxp * _*/);
extern sqlcx2t(/*_ void **, unsigned int *,
struct sqlexd *, struct sqlcxp * _*/);
extern sqlbuft(/*_ void **, char * _*/);
extern sqlgs2t(/*_ void **, char * _*/);
extern sqlorat(/*_ void **, unsigned int *, void * _*/);

/* Forms Interface */
static int IAPSUCC = 0;
static int IAPFAIL = 1403;
static int IAPFTL = 535;
extern void sqliem(/*_ char *, int * _*/);

typedef struct { unsigned short len; unsigned char arr[1]; } VARCHAR;
typedef struct { unsigned short len; unsigned char arr[1]; } varchar;

/* CUD (Compilation Unit Data) Array */
static short sqlcud0[] =
{12,4130,1,0,0,
5,0,0,1,0,0,27,20,0,0,4,4,0,1,0,1,97,0,0,1,97,0,0,1,10,0,0,1,10,0,0,
36,0,0,2,50,0,3,22,0,0,0,0,0,1,0,
51,0,0,3,0,0,29,26,0,0,0,0,0,1,0,
66,0,0,4,53,0,4,28,0,0,1,0,0,1,0,2,97,0,0,
85,0,0,5,0,0,32,50,0,0,0,0,0,1,0,
};


#include <stdio.h>

#include <sqlca.h>
#include <sqlcpr.h>
#include <oraca.h>
#include <sqlda.h>

/* Declare error handling function. */
void sql_error();

int main(int argc, char** argv)
{
char user[]="hello";
char pwd[]="hello";
char msg_buf[51]="";

/* Register sql_error() as the error handler. */
/* EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n"); */


/* EXEC SQL CONNECT :user IDENTIFIED BY :pwd; */

{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 4;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int )10;
sqlstm.offset = (unsigned int )5;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)256;
sqlstm.occurs = (unsigned int )0;
sqlstm.sqhstv[0] = ( void *)user;
sqlstm.sqhstl[0] = (unsigned int )0;
sqlstm.sqhsts[0] = ( int )0;
sqlstm.sqindv[0] = ( void *)0;
sqlstm.sqinds[0] = ( int )0;
sqlstm.sqharm[0] = (unsigned int )0;
sqlstm.sqadto[0] = (unsigned short )0;
sqlstm.sqtdso[0] = (unsigned short )0;
sqlstm.sqhstv[1] = ( void *)pwd;
sqlstm.sqhstl[1] = (unsigned int )0;
sqlstm.sqhsts[1] = ( int )0;
sqlstm.sqindv[1] = ( void *)0;
sqlstm.sqinds[1] = ( int )0;
sqlstm.sqharm[1] = (unsigned int )0;
sqlstm.sqadto[1] = (unsigned short )0;
sqlstm.sqtdso[1] = (unsigned short )0;
sqlstm.sqphsv = sqlstm.sqhstv;
sqlstm.sqphsl = sqlstm.sqhstl;
sqlstm.sqphss = sqlstm.sqhsts;
sqlstm.sqpind = sqlstm.sqindv;
sqlstm.sqpins = sqlstm.sqinds;
sqlstm.sqparm = sqlstm.sqharm;
sqlstm.sqparc = sqlstm.sqharc;
sqlstm.sqpadto = sqlstm.sqadto;
sqlstm.sqptdso = sqlstm.sqtdso;
sqlstm.sqlcmax = (unsigned int )100;
sqlstm.sqlcmin = (unsigned int )2;
sqlstm.sqlcincr = (unsigned int )1;
sqlstm.sqlctimeout = (unsigned int )0;
sqlstm.sqlcnowait = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
if (sqlca.sqlcode <>
}



/* EXEC SQL
INSERT INTO hello_world
VALUES (1, 'Hello world!'); */

{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 4;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.stmt = "insert into hello_world values (1,'Hello world!')";
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )36;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)256;
sqlstm.occurs = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
if (sqlca.sqlcode <>
}



/* EXEC SQL COMMIT; */

{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 4;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )51;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)256;
sqlstm.occurs = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
if (sqlca.sqlcode <>
}



/* EXEC SQL
SELECT msg
INTO :msg_buf
FROM hello_world
WHERE rownum <= 1; */

{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 4;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.stmt = "select msg into :b0 from hello_world where rownum<=1";
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )66;
sqlstm.selerr = (unsigned short)1;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)256;
sqlstm.occurs = (unsigned int )0;
sqlstm.sqhstv[0] = ( void *)msg_buf;
sqlstm.sqhstl[0] = (unsigned int )51;
sqlstm.sqhsts[0] = ( int )0;
sqlstm.sqindv[0] = ( void *)0;
sqlstm.sqinds[0] = ( int )0;
sqlstm.sqharm[0] = (unsigned int )0;
sqlstm.sqadto[0] = (unsigned short )0;
sqlstm.sqtdso[0] = (unsigned short )0;
sqlstm.sqphsv = sqlstm.sqhstv;
sqlstm.sqphsl = sqlstm.sqhstl;
sqlstm.sqphss = sqlstm.sqhsts;
sqlstm.sqpind = sqlstm.sqindv;
sqlstm.sqpins = sqlstm.sqinds;
sqlstm.sqparm = sqlstm.sqharm;
sqlstm.sqparc = sqlstm.sqharc;
sqlstm.sqpadto = sqlstm.sqadto;
sqlstm.sqptdso = sqlstm.sqtdso;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
if (sqlca.sqlcode <>
}



printf("%s\n", msg_buf);
return(0);
}

void sql_error(char *msg)
{
char err_msg[128];
int buf_len, msg_len;

/* EXEC SQL WHENEVER SQLERROR CONTINUE; */

printf("\n%s\n", msg);
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
if (msg_len > buf_len)
msg_len = buf_len;
printf("%.*s\n", msg_len, err_msg);
/* EXEC SQL ROLLBACK RELEASE; */

{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 4;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )85;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)256;
sqlstm.occurs = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}
exit(1);
}

Now you have just to use the gcc compiler to create your executable, simply typing:

gcc hellodb.c -I/u01/app/oracle/product/10.2.0/db_1/precomp/public/ /u01/app/oracle/product/10.2.0/db_1/lib/libclntsh.so -o hellodb

Run your executable in this way:
./hellodb

What does the hellodb executable do ?

It connects to your ORACLE_SID database using the user hello and the password hello.
Then it inserts a row with the message "Hello world!" into the table hello_world and commits your work.
After it asks for the FIRST row from the hello_world table and puts the result in the buffer msg_buf and, at last, it prints on the standard output that message.
That's all...