#include <oci.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#define MAXL 4000

typedef struct sql_oracle_sock {
      OCIEnv		*env;
      OCIError		*errHandle;
      OCISvcCtx		*conn;
      OCIStmt		*queryHandle;
} sql_oracle_sock;

char *sqlerror(sql_oracle_sock *sqlsock) {                                                                                                       

   static char msgbuf[512];
   sb4	 errcode = 0;
   
   if (!sqlsock) return "No connection to database";

   memset((void *) msgbuf, (int)'\0', sizeof(msgbuf));

   OCIErrorGet((dvoid *) sqlsock->errHandle, (ub4) 1, (text *) NULL, &errcode, msgbuf, (ub4) sizeof(msgbuf), (ub4) OCI_HTYPE_ERROR);
   if (errcode) {
      return msgbuf;
   } else {
      return NULL;
   }
}     				       

main()
{

OCIBind  *bnd1p = (OCIBind *) 0;
OCIBind  *bnd2p = (OCIBind *) 0;

char *ConnString = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX.XXX.XXX.XXX)(PORT=1521))(CONNECT_DATA=(SID=XXXX)))";
char *login = "xxxxxxx";
char *passwd = "xxxxxxx";
char *query = "BEGIN TEST(:param1, :param2); END;";

text *param1 = (text *)malloc(sizeof(char)*MAXL);
text *param2 = (text *)malloc(sizeof(char)*MAXL);

memset ((void *)param1, '\0', MAXL);
memset ((void *)param2, '\0', MAXL);

strcpy((char *)param1, "This is a test");

sql_oracle_sock *oracle_sock;

if (OCIEnvCreate(&oracle_sock->env, OCI_DEFAULT|OCI_THREADED, (dvoid *)0, (dvoid * (*)(dvoid *, size_t)) 0, (dvoid * (*)(dvoid *, dvoid *, size_t))0, (void (*)(dvoid *, dvoid *)) 0, 0, (dvoid **)0))
{
   printf("%s", "EnvCreate failed");
	printf("%s\n", sqlerror(oracle_sock));
   return -1;
}

if (OCIHandleAlloc((dvoid *) oracle_sock->env, (dvoid **) &oracle_sock->errHandle, (ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0))
{
    printf("%s", "ErrHandleAlloc failed");
	printf("%s\n", sqlerror(oracle_sock));
    return -1;
}

if (OCIHandleAlloc((dvoid *) oracle_sock->env, (dvoid **) &oracle_sock->queryHandle, (ub4)OCI_HTYPE_STMT, (CONST size_t) 0, (dvoid **) 0))
{
    printf("%s", "QueryHandleAlloc failed");
	printf("%s\n", sqlerror(oracle_sock));
    return -1;
}
																											    
if (OCILogon(oracle_sock->env, oracle_sock->errHandle, &oracle_sock->conn, (unsigned char *) login, strlen(login), (unsigned char *) passwd,  strlen(passwd), (unsigned char *) ConnString, strlen(ConnString)))
{
    printf("%s", "Logon failed");
	printf("%s\n", sqlerror(oracle_sock));
    return -1;
}

if (OCIStmtPrepare(oracle_sock->queryHandle, oracle_sock->errHandle, (text *) query, (ub4) strlen(query), OCI_NTV_SYNTAX, OCI_DEFAULT))
{
    printf("%s\n", "StmtPrepare failed");
	printf("%s\n", sqlerror(oracle_sock));
    return -1;
}

if (OCIBindByName(oracle_sock->queryHandle, &bnd1p, oracle_sock->errHandle, (text *) ":param1", -1, (dvoid *) &param1, (sb4) MAXL, SQLT_STR, (dvoid *) 0, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))
{
    printf("%s\n", "BindNyName 1 failed");
	printf("%s\n", sqlerror(oracle_sock));
    return -1;
}

if (OCIBindByName(oracle_sock->queryHandle, &bnd2p, oracle_sock->errHandle, (text *) ":param2", -1, (dvoid *) &param2, (sb4) MAXL, SQLT_STR, (dvoid *) 0, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))
{
    printf("%s\n", "BindNyName 2 failed");
	printf("%s\n", sqlerror(oracle_sock));
    return -1;
}

if (OCIStmtExecute(oracle_sock->conn, oracle_sock->queryHandle, oracle_sock->errHandle, (ub4) 1, (ub4) 0, (OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT))
{
    printf("%s\n", "StmtExecute failed");
	printf("%s\n", sqlerror(oracle_sock));
    return -1;
}

printf("The param2 is %s\n", param2);

return 0;
}