OCI

Prerequisites

Before you begin, ensure that you have:

• A database account in the PolarDB cluster. See Create a database account

• The IP address of your client host added to the cluster whitelist. See Configure a whitelist for a cluster

• A 64-bit Linux or 64-bit Windows operating system

• The Oracle OCI driver development package installed

Download the OCI driver

polardb-oci.tar.gz

Install the OCI driver

Write the code

The polardb_demo sample program is located in the sample folder of the driver package. It demonstrates the full OCI workflow: initializing handles, connecting to the database, creating tables, inserting data, calling stored procedures, and cleaning up resources.

Before using the sample, update the following connection parameters in main() to match your cluster:

The complete sample code (polardb_demo) is shown below:

/* ============================================================================
 * Copyright (c) 2004-2019 POLARDB Corporation. All Rights Reserved.
 * ===========================================================================
 */
#include <stdio.h>
#include <stdlib.h>

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

#ifdef WIN32
#include <time.h>
#else
#include <sys/time.h>
#endif

/* Define a macro to handle errors. */
#define HANDLE_ERROR(x,y) check_oci_error(x,y)

#define DATE_FMT "DAY, MONTH DD, YYYY"
#define DATE_LANG "American"

sword ConvertStringToDATE( char *datep, char *formatp, dvoid *datepp );
/* A custom routine to handle errors. */

/* This demonstrates the error and exception handling in OCI. */
void check_oci_error (dvoid * errhp, sword status);

/*
 * <<<<<<<<<<<<<<<<<<< FUNCTION PROTOTYPES<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
 */

/* Initialize and allocate all handles. */
void
initHandles (OCISvcCtx **, OCIServer **, OCISession **, OCIError **,
         OCIEnv **);

/* Log on to the database and begin the user session. */
void
logon (OCISvcCtx **, OCIServer **, OCISession **, OCIError **,
       OCIEnv **, text *, text *, text *);

/* Create required tables. */
void create_table (OCISvcCtx *, OCIError *, OCIEnv *);

/* Prepare data for our examples. */
void prepare_data (OCISvcCtx *, OCIError *, OCIEnv *);

/* Create procedures and functions to demonstrate in the example. */
void create_stored_procs (OCISvcCtx *, OCIError *, OCIEnv *);

/* Select and print data by iterating through the result set. */
void select_print_data (OCISvcCtx *, OCIError *, OCIEnv *);

/* Demonstrate calling stored procedures and retrieving values. */

/* proc1 demonstrates IN OUT. */
void call_stored_proc1 (OCISvcCtx *, OCIError *, OCIEnv *);

/* proc2 demonstrates OUT. */
void call_stored_proc2 (OCISvcCtx *, OCIError *, OCIEnv *);

/* Drop required tables. */
void drop_table (OCISvcCtx *, OCIError *, OCIEnv *);

/* Drop stored procedures and functions. */
void drop_stored_procs (OCISvcCtx *, OCIError *, OCIEnv *);

/* Clean up main handles before exit. */
void
cleanup (OCISvcCtx **, OCIServer **, OCISession **, OCIError **, OCIEnv **);

/*
 * <<<<<<<<<<<<<<<<<<<<<<<<< END OF FUNCTION PROTOYPES<<<<<<<<<<<<<<<<<<<<<<<<<<
 */


/* <<<<<<<<<< Global Variables */
 ub4 init_mode = OCI_DEFAULT;
ub4 auth_mode = OCI_CRED_RDBMS;

/* <<<<<<<<<< End Global Variables */

int
main (void)
{

  /*
   * Declare handles. A typical OCI program would need at least the
   * following handles: Environment Handle, Error Handle, Service Context
   * Handle, Server Handle, and User Session (Authentication Handle).
   */

  /* Environment */
  OCIEnv *envhp;

  /* Error */
  OCIError *errhp;

  /* Service Context */
  OCISvcCtx *svchp;

  /* Server */
  OCIServer *srvhp;

  /* Session (authentication) */
  OCISession *authp;


  /*
   * End of handle declaration.
   */

  /*
   * Declare local variables.
   */
  text *username = (text *) "parallels";
  text *passwd = (text *) "";

  /*
   * Oracle Instant Client connection string.
   */
  text *server = (text *) "//localhost:5432/postgres";

  /*
   * Initialize and allocate handles.
   */
  initHandles (&svchp, &srvhp, &authp, &errhp, &envhp);

  /*
   * Log on to the database.
   */
  logon (&svchp, &srvhp, &authp, &errhp, &envhp, username, passwd, server);

  /*
   * Create tables required for this example.
   */
  create_table (svchp, errhp, envhp);

  /*
   * Insert data into the table.
   */
  prepare_data (svchp, errhp, envhp);

  /*
   * Create stored procedures and functions.
   */
  create_stored_procs (svchp, errhp, envhp);

  /*
   * Select and print data by iterating through a simple result set.
   */
  select_print_data (svchp, errhp, envhp);

  /*
   * Demonstrate calling stored procedures and retrieving values.
   */
  call_stored_proc1 (svchp, errhp, envhp);

  /*
   * Demonstrate OUT parameters.
   */
  call_stored_proc2 (svchp, errhp, envhp);

  /*
   * Drop tables used in this example.
   */
  drop_table (svchp, errhp, envhp);

  /*
   * Drop stored procedures and functions used in this example.
   */
  drop_stored_procs (svchp, errhp, envhp);

  /*
   * Clean up resources.
   */
  cleanup (&svchp, &srvhp, &authp, &errhp, &envhp);

  return 0;
}

/* A custom routine to handle errors. */

/* This demonstrates the error and exception handling in OCI. */

void
check_oci_error (dvoid * errhp, sword status)
{
  text errbuf[512];
  sb4 errcode;

  if (status == OCI_SUCCESS)
    {
      return;
    }
  switch (status)
    {
    case OCI_SUCCESS_WITH_INFO:
      printf ("OCI_SUCCESS_WITH_INFO:\n");
      OCIErrorGet (errhp, (ub4) 1, (text *) 0, &errcode,
           errbuf, (ub4) sizeof (errbuf), OCI_HTYPE_ERROR);
      printf ("%s", errbuf);
      break;
    case OCI_NEED_DATA:
      printf ("Error - OCI_NEED_DATA\n");
      break;
    case OCI_NO_DATA:
      printf ("Error - OCI_NO_DATA\n");
      break;
    case OCI_ERROR:
      printf ("Error - OCI_ERROR:\n");
      OCIErrorGet (errhp, (ub4) 1, (text *) 0, &errcode,
           errbuf, (ub4) sizeof (errbuf), OCI_HTYPE_ERROR);
      printf ("%s", errbuf);
      break;
    case OCI_INVALID_HANDLE:
      printf ("Error - OCI_INVALID_HANDLE\n");
      break;
    case OCI_STILL_EXECUTING:
      printf ("Error - OCI_STILL_EXECUTING\n");
      break;
    case OCI_CONTINUE:
      printf ("Error - OCI_CONTINUE\n");
      break;
    default:
      break;
    }

  /*
   * Exit the application.
   */
  exit((int)status);
}

/* Initialize and allocate required handles. */
void
initHandles (OCISvcCtx ** svchp, OCIServer ** srvhp, OCISession ** authp,
         OCIError ** errhp, OCIEnv ** envhp)
{

  /*
   * This section initializes and allocates basic handles. This is a
   * required setup or initialization step before you can log on to and
   * work with the database. This initialization and preparation includes
   * the following steps:
   *
   * 1. Initialize the OCI (OCIInitialize()).
   * 2. Initialize the environment (OCIEnvInit()).
   * 3. Initialize and allocate the error handle.
   * 4. Initialize and allocate the service context handle.
   * 5. Initialize and allocate the session handle.
   * 6. Initialize and allocate the server handle.
   *
   * In newer versions of OCI, instead of using OCIInitialize()
   * and OCIEnvInit(), you can use a single API call named
   * OCIEnvCreate().
   */

  /*
   * Initialize OCI.
   */
  if (OCIInitialize (init_mode, (dvoid *) 0,
             (dvoid * (*)(dvoid *, size_t)) 0,
             (dvoid * (*)(dvoid *, dvoid *, size_t)) 0,
             (void (*)(dvoid *, dvoid *)) 0) != OCI_SUCCESS)
    {
      printf ("ERROR: failed to initialize OCI\n");
      exit (1);
    }
  /*
   * Initialize the environment.
   */
  HANDLE_ERROR (*envhp,
        OCIEnvInit (&(*envhp), OCI_DEFAULT, (size_t) 0,
                (dvoid **) 0));

  /*
   * Initialize and allocate the error handle.
   */
  HANDLE_ERROR (*envhp,
        OCIHandleAlloc (*envhp, (dvoid **) & (*errhp),
                OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0));

  /*
   * Initialize and allocate the service context handle.
   */
  HANDLE_ERROR (*errhp,
        OCIHandleAlloc (*envhp, (dvoid **) & (*svchp),
                OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0));

  /*
   * Initialize and allocate the session handle.
   */
  HANDLE_ERROR (*errhp,
        OCIHandleAlloc (*envhp, (dvoid **) & (*authp),
                OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0));

  /*
   * Initialize and allocate the server handle.
   */
  HANDLE_ERROR (*errhp,
        OCIHandleAlloc (*envhp, (dvoid **) & (*srvhp),
                OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0));

}

void
logon (OCISvcCtx ** svchp, OCIServer ** srvhp, OCISession ** authp,
       OCIError ** errhp, OCIEnv ** envhp, text * username, text * passwd,
       text * server)
{

  /*
   * This section describes how to log on to the database server. It includes two
   * steps:
   *
   * 1. Attaching to the server.
   * 2. Starting or beginning the session.
   *
   * This is the complex logon method. The easy way to log on is to avoid
   * server attach and session begin. Simply use OCILogon() or
   * OCILogon2() and then log off using OCILogoff().
   */

  /*
   * Attach to the server.
   */

  HANDLE_ERROR (*errhp,
        OCIServerAttach (*srvhp, *errhp, server,
                 (ub4) strlen ((char *) server),
                 OCI_DEFAULT));

  /*
   * The following code starts a session. Before you start a
   * session, you must:
   * 1. Set the server handle, which is now attached,
   *    into the service context handle.
   * 2. Set the username and password into the
   *    session handle.
   */

  /*
   * Set the server handle into the service context handle.
   */

  HANDLE_ERROR (*errhp,
        OCIAttrSet (*svchp, OCI_HTYPE_SVCCTX,
                (dvoid *) (*srvhp), (ub4) 0, OCI_ATTR_SERVER,
                *errhp));

  /*
   * Set the username and password into the session handle.
   */

  HANDLE_ERROR (*errhp,
        OCIAttrSet (*authp, OCI_HTYPE_SESSION,
                (dvoid *) username,
                (ub4) strlen ((char *) username),
                OCI_ATTR_USERNAME, *errhp));
  HANDLE_ERROR (*errhp,
        OCIAttrSet (*authp, OCI_HTYPE_SESSION, (dvoid *) passwd,
                (ub4) strlen ((char *) passwd), OCI_ATTR_PASSWORD,
                *errhp));

  /*
   * Finally, begin our session.
   */

  HANDLE_ERROR ((*errhp),
        OCISessionBegin (*svchp, *errhp,
                 *authp, auth_mode, OCI_DEFAULT));

  printf ("********************************************\n");
  printf ("Milestone  : Logged on as --> '%s'\n", username);
  printf ("********************************************\n");

  /*
   * After we begin our session, we will have to set the session
   */

  /*
   * (authentication) handle into the service context handle.
   */

  HANDLE_ERROR (*errhp,
        OCIAttrSet (*svchp, OCI_HTYPE_SVCCTX,
                (dvoid *) (*authp), (ub4) 0,
                OCI_ATTR_SESSION, *errhp));
}

/* Create tables required for this example. */
void
create_table (OCISvcCtx * svchp, OCIError * errhp, OCIEnv * envhp)
{
  OCIStmt *stmhp;
  text *create_statement =
    (text *)"CREATE TABLE OCISPEC \n (ENAME VARCHAR2(20)\n, MGR NUMBER\n, HIREDATE DATE)";
  ub4 status = OCI_SUCCESS;

  /*
   * Initialize and allocate the statement handle.
   */
  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & stmhp,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));


  /*
   * Prepare the CREATE statement.
   */

  HANDLE_ERROR (errhp,
        OCIStmtPrepare (stmhp, errhp,
                create_statement,
                strlen ((const char *) create_statement),
                OCI_NTV_SYNTAX, OCI_DEFAULT));


  /*
   * Execute the CREATE statement.
   */
  if ((status = OCIStmtExecute (svchp, stmhp, errhp,
                   (ub4) 1, (ub4) 0, NULL, NULL, OCI_DEFAULT)) < OCI_SUCCESS)
    {
      printf ("FAILURE IN CREATING TABLE(S)\n");
      HANDLE_ERROR (errhp, status);
      return;
    }
  else
    {
      printf ("********************************************\n");
      printf ("MileStone : Table(s) Successfully created\n");
      printf ("********************************************\n");
    }
  HANDLE_ERROR (errhp, OCIHandleFree (stmhp, OCI_HTYPE_STMT));
}

/* Prepare data for our examples. */
void
prepare_data (OCISvcCtx * svchp, OCIError * errhp, OCIEnv * envhp)
{
  OCIStmt *stmhp;
  text *insstmt =
    (text *)
    "INSERT INTO OCISPEC (ename,mgr, hiredate) VALUES (:ENAME,:MGR, CAST(:HIREDATE AS timestamp))";
  OCIBind *bnd1p = (OCIBind *) 0;    /* The first bind handle.   */
  OCIBind *bnd2p = (OCIBind *) 0;    /* The second bind handle. */
  OCIBind *bnd3p = (OCIBind *) 0;    /* The third bind handle.   */
  ub4 status = OCI_SUCCESS;
  int i = 0;

  char *ename[3] = { "SMITH", "ALLEN", "KING" };

  sword mgr[] = { 7886, 7110, 7221 };

  char *date_buffer[3] = { "02-AUG-07", "02-APR-07", "02-MAR-07" };

  /*
   * Initialize and allocate the statement handle.
   */
  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & stmhp,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

  /*
   * Prepare the INSERT statement.
   */
  HANDLE_ERROR (errhp,
        OCIStmtPrepare (stmhp, errhp, insstmt,
                (ub4) strlen ((char *) insstmt),
                (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));

  /*
   * In this loop, we will bind data from the arrays to insert multiple
   * rows into the database. A more elegant and better way to do this is
   * to use array binding (batch inserts). The PolarDB OCI Replacement
   * Library will support array bindings even if it is not used here
   * right now.
   */
  for (i = 0; i < 3; i++)
    {
      /*
       * Bind the variable for ENAME.
       */
      HANDLE_ERROR (errhp,
            OCIBindByName (stmhp, &bnd1p, errhp, (text *) ":ENAME",
                   -1, (dvoid *) ename[i],
                   (sb4) strlen (ename[i]) + 1, SQLT_STR,
                   (dvoid *) 0, 0, (ub2 *) 0, (ub4) 0,
                   (ub4 *) 0, OCI_DEFAULT));

      /*
       * Bind the variable for MGR.
       */
      HANDLE_ERROR (errhp,
            OCIBindByName (stmhp, &bnd2p, errhp, (text *) ":MGR",
                   -1, (dvoid *) & mgr[i], sizeof (mgr[i]),
                   SQLT_INT, (dvoid *) 0, 0, (ub2 *) 0,
                   (ub4) 0, (ub4 *) 0, OCI_DEFAULT));

      /*
       * Bind the variable for HIREDATE.
       */
      HANDLE_ERROR (errhp,
            OCIBindByName (stmhp, &bnd3p, errhp, (text *) ":HIREDATE",
                   -1, (dvoid *)  date_buffer[i],
                   strlen(date_buffer[i])+1, SQLT_STR, (dvoid *) 0, 0,
                   (ub2 *) 0, (ub4) 0, (ub4 *) 0,
                   OCI_DEFAULT));

      /*
       * Execute the statement and insert data.
       */
      if ((status = OCIStmtExecute (svchp, stmhp, errhp,
                   (ub4) 1, (ub4) 0, NULL, NULL, OCI_DEFAULT)) < OCI_SUCCESS)
    {
      printf ("FAILURE IN INSERTING DATA\n");
      HANDLE_ERROR (errhp, status);
      return;
    }
    }

  OCITransCommit (svchp, errhp, (ub4) 0);
  printf ("********************************************\n");
  printf
    ("MileStone : Data Sucessfully inserted \n & Committed via Transaction\n");
  printf ("********************************************\n");
  HANDLE_ERROR (errhp, OCIHandleFree (stmhp, OCI_HTYPE_STMT));

}

/* Create stored procedures and functions to be used in this example. */
void
create_stored_procs (OCISvcCtx * svchp, OCIError * errhp, OCIEnv * envhp)
{
  /*
   * This function creates two stored procedures and one stored function:
   * 1. StoredProcedureSample1 - Exhibits executing a procedure and
   *    receiving values from an IN OUT parameter.
   * 2. StoredProcedureSample2 - Exhibits executing a procedure and
   *    receiving values from an OUT parameter.
   * 3. StoredProcedureSample3 - Exhibits executing a function and receiving
   *    the value returned by the function in a callable statement.
   */
  OCIStmt *stmhp;
  OCIStmt *stmhp2;
  OCIStmt *stmhp3;

  text *create_statement =
    (text *)"CREATE OR REPLACE PROCEDURE StoredProcedureSample1\n (mgr1 int, ename1 IN OUT varchar2)\n   is\nbegin\ninsert into ocispec (mgr, ename) values (7990,'STOR1');\nename1 := 'Successful';\n end;\n";

  text *create_statement2 =
    (text *)"CREATE OR REPLACE PROCEDURE StoredProcedureSample2\n(mgr1 int, ename1 varchar2,eout1 OUT varchar2)\nis\nbegin\ninsert into ocispec(mgr,ename) values (7991, 'STOR2');\neout1 := 'Successful';\n    end;";

  text *create_statement3 =
    (text *)"CREATE OR REPLACE FUNCTION f1\nRETURN VARCHAR2\nis\nv_Sysdate DATE;\nv_charSysdate VARCHAR2(20);\nbegin\nSELECT TO_CHAR(SYSDATE, 'dd-mon-yyyy') into v_charSysdate FROM DUAL;\n    return(v_charSysdate);\nend;";



  ub4 status = OCI_SUCCESS;

  /*
   * Initialize and allocate statement handles.
   */
  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & stmhp,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));
  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & stmhp2,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));
  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & stmhp3,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

  /*
   * Prepare the CREATE statements.
   */

  HANDLE_ERROR (errhp,
        OCIStmtPrepare (stmhp, errhp,
                create_statement,
                strlen ((const char *) create_statement),
                OCI_NTV_SYNTAX, OCI_DEFAULT));
  HANDLE_ERROR (errhp,
        OCIStmtPrepare (stmhp2, errhp, create_statement2,
                strlen ((const char *) create_statement2),
                OCI_NTV_SYNTAX, OCI_DEFAULT));
  HANDLE_ERROR (errhp,
        OCIStmtPrepare (stmhp3, errhp, create_statement3,
                strlen ((const char *) create_statement3),
                OCI_NTV_SYNTAX, OCI_DEFAULT));

  /*
   * Execute the CREATE statement for SampleProcedure1.
   */
  if ((status = OCIStmtExecute (svchp, stmhp, errhp,
                   (ub4) 1, (ub4) 0, NULL, NULL, OCI_DEFAULT)) < OCI_SUCCESS)
    {
      printf ("FAILURE IN CREATING PROCEDURE 1\n");
      HANDLE_ERROR (errhp, status);
      return;
    }
  else
    {
      printf ("********************************************\n");
      printf ("MileStone : Sample Procedure 1 Successfully created\n");
      printf ("********************************************\n");

    }

  /*
   * Execute the CREATE statement for SampleProcedure2.
   */
  if ((status = OCIStmtExecute (svchp, stmhp2, errhp,
                   (ub4) 1, (ub4) 0, NULL, NULL, OCI_DEFAULT)) < OCI_SUCCESS)
    {
      printf ("FAILURE IN CREATING PROCEDURE 2\n");
      HANDLE_ERROR (errhp, status);
      return;
    }
  else
    {
      printf ("********************************************\n");
      printf ("MileStone : Sample Procedure 2 Successfully created\n");
      printf ("********************************************\n");
    }

  /*
   * Execute the CREATE statement for SampleProcedure3.
   */
  if ((status = OCIStmtExecute (svchp, stmhp3, errhp,
                   (ub4) 1, (ub4) 0, NULL, NULL, OCI_DEFAULT)) < OCI_SUCCESS)
    {
      printf ("FAILURE IN CREATING PROCEDURE 3\n");
      HANDLE_ERROR (errhp, status);
      return;
    }
  else
    {
      printf ("********************************************\n");
      printf ("MileStone : Sample Procedure 3 Successfully created\n");
      printf ("********************************************\n");
    }


  HANDLE_ERROR (errhp, OCIHandleFree (stmhp, OCI_HTYPE_STMT));
  HANDLE_ERROR (errhp, OCIHandleFree (stmhp2, OCI_HTYPE_STMT));
  HANDLE_ERROR (errhp, OCIHandleFree (stmhp3, OCI_HTYPE_STMT));
}

/* Select and print data by iterating through the result set. */
void
select_print_data (OCISvcCtx * svchp, OCIError * errhp, OCIEnv * envhp)
{

  /* Statement */
  OCIStmt *stmhp;

  /* Define */
  OCIDefine *define;

  /* Buffer for employee name. */
  char ename_buffer[10] ;

  /* Buffer for mgr. */
  sword mgr_buffer;

  /* Buffer for hiredate. */
  char hire_date[20];

  /*
   * A simple SELECT statement.
   */
  text * sql_statement =
      (text *) "select ename,mgr,hiredate from ocispec";

  /*
   * Additional local variables.
   */

  ub4 rows = 1;
  ub4 fetched = 1;
  ub4 status = OCI_SUCCESS;

  sb2 null_ind_ename = 0;

  /* Null indicator for ename. */
  sb2 null_ind_mgr = 0;

  /* Null indicator for mgr. */
  sb2 null_ind_hiredate = 0;

  /* Null indicator for hiredate. */

  /*
   * This section describes a simple query of the
   * database and how to loop through the result set. This includes the
   * following steps:
   *
   * 1. Initialize and allocate the statement handle.
   * 2. Prepare the statement.
   * 3. Define output variables to receive the output of the
   *    SELECT statement.
   * 4. Execute the statement.
   * 5. Fetch the result set and print values.
   *
   */
memset( ename_buffer, 0, sizeof(ename_buffer) );
memset( hire_date, 0, sizeof(hire_date) );
  /*
   * Initialize and allocate the statement handle.
   */

  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & stmhp,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

  /*
   * Prepare the statement.
   */

  HANDLE_ERROR (errhp,
        OCIStmtPrepare (stmhp, errhp,
                sql_statement,
                strlen ((const char *) sql_statement),
                OCI_NTV_SYNTAX, OCI_DEFAULT));


  /*
   * Bind a string (OCIString) variable on position 1. The data type used
   * is SQLT_STR.
   */
  HANDLE_ERROR (errhp,
        OCIDefineByPos (stmhp, &define, errhp,
                (ub4) 1, ename_buffer, 10,
                (ub2) SQLT_STR, &null_ind_ename, 0, 0,
                OCI_DEFAULT));

  /*
   * Bind a number (OCINumber) variable on position 2. The data type used
   * is SQLT_INT.
   */
  HANDLE_ERROR (errhp,
        OCIDefineByPos (stmhp, &define, errhp,
                (ub4) 2, &mgr_buffer, sizeof (sword),
                (ub2) SQLT_INT, &null_ind_mgr, 0, 0,
                OCI_DEFAULT));

  /*
   * Bind a date (OCIDate) variable on position 3. The data type used
   * is SQLT_STR.
   */
  HANDLE_ERROR (errhp,
        OCIDefineByPos (stmhp, &define, errhp,
                (ub4) 3, hire_date, 20,
                (ub2) SQLT_STR, &null_ind_hiredate, 0, 0,
                OCI_DEFAULT));



  /*
   * Execute the simple SQL statement.
   */
  status = OCIStmtExecute (svchp, stmhp, errhp,
               rows, (ub4) 0, NULL, NULL, OCI_DEFAULT);


  /*
   * Print the result set.
   */
  if (status == OCI_NO_DATA)
    {
      /*
       * Indicates that nothing was fetched (because we are not array fetching).
       */
      fetched = 0;
    }
  else
    {
      HANDLE_ERROR (errhp, status);
    }

  if (fetched)
    {
      /*
       * Print string.
       */
      if (null_ind_ename == -1)
    printf ("name -> [NULL]\t");
      else
    printf ("name -> [%s]\t",  ename_buffer);


      /*
       * Print number by converting it to an integer.
       */
      if (null_ind_mgr == -1)
    printf ("mgr -> [NULL]\n");
      else
    {
      printf ("mgr -> [%d]\n", mgr_buffer);
    }

      if (null_ind_hiredate == -1)
    printf ("hiredate -> [NULL]\n");
      else
    {
      printf ("hiredate -> [%s]\n",hire_date );
    }

      /*
       * Loop through the result set one by one using
       * OCIStmtFetch().
       */

      /*
       * Until we find nothing.
       */
      while (1)
    {
      status = OCIStmtFetch (stmhp, errhp,
                 rows, OCI_FETCH_NEXT, OCI_DEFAULT);
      if (status == OCI_NO_DATA)
        {
          /*
           * Indicates that nothing could be fetched.
           */
          break;
        }
      else
        {
          HANDLE_ERROR (errhp, status);
        }

      /*
       * Print string.
       */
      if (null_ind_ename == -1)
        printf ("name -> [NULL]\t");
      else
        printf ("name -> [%s]\t", ename_buffer);

      /*
       * Print number by converting it to an integer.
       */
      if (null_ind_mgr == -1)
        printf ("mgr -> [NULL]\n");
      else
        {
        printf ("mgr -> [%d]\n", mgr_buffer);
        }

      /*
       * Print date after converting to text.
       */
      if (null_ind_hiredate == -1)
        printf ("hiredate -> [NULL]\n");
      else
        {

          printf ("hiredate -> [%s]\n", hire_date);
        }
    }
    }
  HANDLE_ERROR (errhp, OCIHandleFree (stmhp, OCI_HTYPE_STMT));

}

void
call_stored_proc1 (OCISvcCtx * svchp, OCIError * errhp, OCIEnv * envhp)
{
  OCIStmt *p_sql;
  OCIBind *p_Bind1 = (OCIBind *) 0;
  OCIBind *p_Bind2 = (OCIBind *) 0;

  char field2[20];


  /*
   * char field3[20];
   */
  sword field1 = 3;
  text *mySql = (text *) "Begin StoredProcedureSample1(:MGR, :ENAME); END";

  memset( field2, 0, sizeof(field2) );
  strcpy( field2, "Entry 3" );

  printf ("*************************************************\n");
  printf ("Example 1 - Using an IN OUT Parameter\n");
  printf ("*************************************************\n");


  /*
   * Initialize and allocate the statement handle.
   */

  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & p_sql,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

  HANDLE_ERROR (errhp,
        OCIStmtPrepare (p_sql, errhp, mySql,
                (ub4) strlen ((char *)mySql), OCI_NTV_SYNTAX,
                OCI_DEFAULT));

  HANDLE_ERROR (errhp,
        OCIBindByPos (p_sql, &p_Bind1, errhp, 1,
                  (dvoid *) & field1, sizeof (sword),
                  SQLT_INT, 0, 0, 0, 0, 0, OCI_DEFAULT));

  HANDLE_ERROR (errhp,
        OCIBindByPos (p_sql, &p_Bind2, errhp, 2,
                  field2, (sizeof (field2)),
                  SQLT_STR, 0, 0, 0, 0, 0, OCI_DEFAULT));

  printf (" Field2 Before:\n");
  printf (" size ---> %d\n", sizeof (field2));
  printf (" length ---> %d\n", strlen (field2));
  printf (" value ---> %s\n", field2);

  HANDLE_ERROR (errhp,
        OCIStmtExecute (svchp, p_sql, errhp, (ub4) 1, (ub4) 0,
                (OCISnapshot *) NULL, (OCISnapshot *) NULL,
                (ub4) OCI_COMMIT_ON_SUCCESS));

  printf (" Field2 After:\n");
  printf (" size ---> %d\n", sizeof (field2));
  printf (" length ---> %d\n", strlen (field2));
  printf (" value ---> %s\n", field2);

  HANDLE_ERROR (errhp, OCIHandleFree (p_sql, OCI_HTYPE_STMT));
}

void
call_stored_proc2 (OCISvcCtx * svchp, OCIError * errhp, OCIEnv * envhp)
{
  OCIStmt *p_sql;
  OCIBind *p_Bind1 = (OCIBind *) 0;
  OCIBind *p_Bind2 = (OCIBind *) 0;
  OCIBind *p_Bind3 = (OCIBind *) 0;

  char field2[20] = "Entry 3";
  char field3[20];
  sword field1 = 3;
  text *mySql =
    (text *) "Begin StoredProcedureSample2(:MGR, :ENAME, :EOUT); END";


  memset( field2, 0, sizeof(field2) );
  strcpy( field2, "Entry 3" );

  memset( field3, 0, sizeof(field3) );


  printf ("*************************************************\n");
  printf ("Example 2 - Using an OUT Parameter\n");
  printf ("*************************************************\n");

  /*
   * Initialize and allocate the statement handle.
   */

  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & p_sql,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

  HANDLE_ERROR (errhp,
        OCIStmtPrepare (p_sql, errhp, mySql,
                (ub4) strlen ((char *)mySql), OCI_NTV_SYNTAX,
                OCI_DEFAULT));

  HANDLE_ERROR (errhp,
        OCIBindByPos (p_sql, &p_Bind1, errhp, 1,
                  (dvoid *) & field1, sizeof (sword),
                  SQLT_INT, 0, 0, 0, 0, 0, OCI_DEFAULT));

  HANDLE_ERROR (errhp,
        OCIBindByPos (p_sql, &p_Bind2, errhp, 2,
                  field2, strlen (field2) + 1,
                  SQLT_STR, 0, 0, 0, 0, 0, OCI_DEFAULT));

  HANDLE_ERROR (errhp,
        OCIBindByPos (p_sql, &p_Bind3, errhp, 3,
                  field3, 20,
                  SQLT_STR, 0, 0, 0, 0, 0, OCI_DEFAULT));

  printf (" Field3 Before:\n");
  printf (" size ---> %d\n", sizeof (field3));
  printf (" length ---> %d\n", strlen (field3));
  printf (" value ---> %s\n", field3);

  HANDLE_ERROR (errhp,
        OCIStmtExecute (svchp, p_sql, errhp, (ub4) 1, (ub4) 0,
                (OCISnapshot *) NULL, (OCISnapshot *) NULL,
                (ub4) OCI_COMMIT_ON_SUCCESS));


  printf (" Field3 After:\n");
  printf (" size ---> %d\n", sizeof (field3));
  printf (" length ---> %d\n", strlen (field3));
  printf (" value ---> %s\n", field3);

  HANDLE_ERROR (errhp, OCIHandleFree (p_sql, OCI_HTYPE_STMT));
}

/* Drop tables required for this example. */
void
drop_table (OCISvcCtx * svchp, OCIError * errhp, OCIEnv * envhp)
{
  OCIStmt *stmhp;
  text *statement = (text *)"DROP TABLE OCISPEC";
  ub4 status = OCI_SUCCESS;

  /*
   * Initialize and allocate the statement handle.
   */
  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & stmhp,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

  /*
   * Prepare the DROP statement.
   */
  HANDLE_ERROR (errhp,
        OCIStmtPrepare (stmhp, errhp,
                statement, strlen ((const char *) statement),
                OCI_NTV_SYNTAX, OCI_DEFAULT));

  /*
   * Execute the DROP statement.
   */
  if ((status = OCIStmtExecute (svchp, stmhp, errhp,
                   (ub4) 1, (ub4) 0, NULL, NULL, OCI_DEFAULT)) < OCI_SUCCESS)
    {
      printf ("FAILURE IN DROPING TABLE(S)\n");
      HANDLE_ERROR (errhp, status);
      return;
    }
  else
    {
      printf ("********************************************\n");
      printf ("MileStone : Table(s) Successfully Dropped\n");
      printf ("********************************************\n");
    }
  HANDLE_ERROR (errhp, OCIHandleFree (stmhp, OCI_HTYPE_STMT));
}

void
drop_stored_procs (OCISvcCtx * svchp, OCIError * errhp, OCIEnv * envhp)
{
  OCIStmt *stmhp;
  OCIStmt *stmhp2;
  OCIStmt *stmhp3;

  text *create_statement = (text *)"DROP PROCEDURE StoredProcedureSample1";
  text *create_statement2 = (text *)"DROP PROCEDURE StoredProcedureSample2";
  text *create_statement3 = (text *)"DROP FUNCTION  f1";


  ub4 status = OCI_SUCCESS;
  OCITransCommit( svchp, errhp, OCI_DEFAULT );
  /*
   * Initialize and allocate statement handles.
   */
  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & stmhp,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));
  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & stmhp2,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));
  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & stmhp3,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

  /*
   * Prepare the CREATE statements.
   */

  HANDLE_ERROR (errhp,
        OCIStmtPrepare (stmhp, errhp,
                create_statement,
                strlen ((const char *) create_statement),
                OCI_NTV_SYNTAX, OCI_DEFAULT));
  HANDLE_ERROR (errhp,
        OCIStmtPrepare (stmhp2, errhp, create_statement2,
                strlen ((const char *) create_statement2),
                OCI_NTV_SYNTAX, OCI_DEFAULT));
  HANDLE_ERROR (errhp,
        OCIStmtPrepare (stmhp3, errhp, create_statement3,
                strlen ((const char *) create_statement3),
                OCI_NTV_SYNTAX, OCI_DEFAULT));

  /*
   * Execute the CREATE statement for SampleProcedure1.
   */
  if ((status = OCIStmtExecute (svchp, stmhp, errhp,
                   (ub4) 1, (ub4) 0, NULL, NULL, OCI_DEFAULT)) < OCI_SUCCESS)
    {
      printf ("FAILURE IN DROPPING PROCEDURE 1\n");
      HANDLE_ERROR (errhp, status);
      return;
    }
  else
    {
      printf ("********************************************\n");
      printf ("MileStone : Sample Procedure 1 Successfully dropped\n");
      printf ("********************************************\n");
    }

  /*
   * Execute the CREATE statement for SampleProcedure2.
   */
  if ((status = OCIStmtExecute (svchp, stmhp2, errhp,
                   (ub4) 1, (ub4) 0, NULL, NULL, OCI_DEFAULT)) < OCI_SUCCESS)
    {
      printf ("FAILURE IN DROPPING PROCEDURE 2\n");
      HANDLE_ERROR (errhp, status);
      return;
    }
  else
    {
      printf ("********************************************\n");
      printf ("MileStone : Sample Procedure 2 Successfully dropped\n");
      printf ("********************************************\n");
    }

  /*
   * Execute the CREATE statement for SampleProcedure3.
   */
  if ((status = OCIStmtExecute (svchp, stmhp3, errhp,
                   (ub4) 1, (ub4) 0, NULL, NULL, OCI_DEFAULT)) < OCI_SUCCESS)
    {
      printf ("FAILURE IN DROPPING PROCEDURE 3\n");
      HANDLE_ERROR (errhp, status);
      return;
    }
  else
    {
      printf ("********************************************\n");
      printf ("MileStone : Sample Procedure 3 Successfully dropped\n");
      printf ("********************************************\n");
    }


  HANDLE_ERROR (errhp, OCIHandleFree (stmhp, OCI_HTYPE_STMT));
  HANDLE_ERROR (errhp, OCIHandleFree (stmhp2, OCI_HTYPE_STMT));
  HANDLE_ERROR (errhp, OCIHandleFree (stmhp3, OCI_HTYPE_STMT));

}

/* Clean up your resources. */
void
cleanup (OCISvcCtx ** svchp, OCIServer ** srvhp, OCISession ** authp,
     OCIError ** errhp, OCIEnv ** envhp)
{
  /*
   * Log off.
   */
  HANDLE_ERROR (*errhp, OCISessionEnd (*svchp, *errhp, *authp, OCI_DEFAULT));
  printf ("logged off\n");

  /*
   * Detach from the server.
   */
  HANDLE_ERROR (*errhp, OCIServerDetach (*srvhp, *errhp, OCI_DEFAULT));
  printf ("detached form server\n");

  /*
   * Free up handles.
   */
  HANDLE_ERROR (*errhp, OCIHandleFree (*authp, OCI_HTYPE_SESSION));
  /* Free the session handle. */
  *authp = 0;
  HANDLE_ERROR (*errhp, OCIHandleFree (*srvhp, OCI_HTYPE_SERVER));
  /* Free the server handle. */
  *srvhp = 0;
  HANDLE_ERROR (*errhp, OCIHandleFree (*svchp, OCI_HTYPE_SVCCTX));
  /* Free the service context. */
  *svchp = 0;
  HANDLE_ERROR (*errhp, OCIHandleFree (*errhp, OCI_HTYPE_ERROR));
  /* Free the error handle. */
  *errhp = 0;
  OCIHandleFree (*envhp, OCI_HTYPE_ENV);
  /* Free the environment handle. */
  *envhp = 0;
  printf ("free'd all handles\n");
}

Compile the code

# ============================================================================
# Copyright (c) 2004-2012 PolarDB Corporation. All Rights Reserved.
# ===========================================================================

# Makefile to build C testcases for OCILib
CC=gcc
CFLAGS=-Wall -g -I$(ORACLE_HOME)/ -L $(POLARDBOCI_LIB) -lpolaroci -lpq -liconv

SAMPLES = polardb_demo

all: $(SAMPLES)

%:%.o
    $(CC) $(CFLAGS) -o $@
clean:
    rm -rf $(SAMPLES)

make
./polardb_demo

********************************************
Milestone  : Logged on as --> 'parallels'
********************************************
********************************************
MileStone : Table(s) Successfully created
********************************************
********************************************
MileStone : Data Sucessfully inserted
 & Committed via Transaction
********************************************
********************************************
MileStone : Sample Procedure 1 Successfully created
********************************************
********************************************
MileStone : Sample Procedure 2 Successfully created
********************************************
********************************************
MileStone : Sample Procedure 3 Successfully created
********************************************
name -> [SMITH]    mgr -> [7886]
hiredate -> [2007-08-02 00:00:00]
name -> [ALLEN]    mgr -> [7110]
hiredate -> [2007-04-02 00:00:00]
name -> [KING]    mgr -> [7221]
hiredate -> [2007-03-02 00:00:00]
*************************************************
Example 1 - Using an IN OUT Parameter
*************************************************
 Field2 Before:
 size ---> 20
 length ---> 7
 value ---> Entry 3
 Field2 After:
 size ---> 20
 length ---> 10
 value ---> Successful
*************************************************
Example 2 - Using an OUT Parameter
*************************************************
 Field3 Before:
 size ---> 20
 length ---> 0
 value --->
 Field3 After:
 size ---> 20
 length ---> 10
 value ---> Successful
********************************************
MileStone : Table(s) Successfully Dropped
********************************************
********************************************
MileStone : Sample Procedure 1 Successfully dropped
********************************************
********************************************
MileStone : Sample Procedure 2 Successfully dropped
********************************************
********************************************
MileStone : Sample Procedure 3 Successfully dropped
********************************************
logged off
detached form server
free'd all handles