Example 31-1 Setting a Specific GTRID

/* Get current transaction handle */
OCITrans *current_txnhp;
OCIAttrGet(svchp, OCI_HTYPE_SVCCTX, &current_txnhp, NULL,
           errhp);
 
/* set GTRID in XID struct */
XID         res_xid;
strcpy(res_xid.data, "client_user_gtrid_1");
res_xid.gtrid_length = strlen("client_user_gtrid_1");
 
/* set XID as txn handle's attribute */
OCIAttrSet(current_txnhp, OCI_HTYPE_TRANS, &res_xid, sizeof(XID), OCI_ATTR_XID, errhp);

Example 31-2 Setting a Generated GTRID

/* Get current transaction handle */
OCITrans *current_txnhp;
OCIAttrGet(svchp, OCI_HTYPE_SVCCTX, &current_txnhp, NULL,
           errhp);
 
/* set XID as txn handle's attribute */
OCIAttrSet(current_txnhp, OCI_HTYPE_TRANS, NULL, sizeof(XID), OCI_ATTR_XID, errhp);

Example 31-3 Starting a New Sessionless Transaction on the Server with a User-Specified GTRID

The following example starts a new Sessionless transaction on the server with a specific GTRID:

SET SERVEROUTPUT ON

DECLARE
  gtrid VARCHAR2(128);
BEGIN
  gtrid := DBMS_TRANSACTION.START_TRANSACTION
  ( UTL_RAW.CAST_TO_RAW('user_specified_gtrid')
  , DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS
  , 20
  , DBMS_TRANSACTION.TRANSACTION_NEW
  );
  DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(gtrid));
END;
/

The output is:

user_specified_gtrid
                                                                                        
PL/SQL procedure successfully completed.

The following retrieves the transaction ID of the newly created Sessionless transaction.

BEGIN
  IF DBMS_TRANSACTION.GET_TRANSACTION_TYPE() = DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS THEN
    DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(DBMS_TRANSACTION.GET_TRANSACTION_ID()));
  ELSE
    DBMS_OUTPUT.PUT_LINE('Not a Sessionless transaction');
  END IF;
 END;
 /

The output is:

user_specified_gtrid          
                                                           
PL/SQL procedure successfully completed.

Example 31-4 Starting a New Sessionless Transaction on the Server with a Generated GTRID

The following example starts a new Sessionless transaction on the server using a generated GTRID.

-- Start a new Sessionless transaction with a GTRID generated by the
-- START_TRANSACTION function.
SET SERVEROUTPUT ON

DECLARE
  gtrid VARCHAR2(128);
BEGIN
  gtrid := DBMS_TRANSACTION.START_TRANSACTION
  ( NULL
  , DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS
  , 20
  , DBMS_TRANSACTION.TRANSACTION_NEW
  );
  DBMS_OUTPUT.PUT_LINE(gtrid);
END;
/

The output is:

9AF035CCB2024FA6BF17C8CDBF7B6D6C

PL/SQL procedure successfully completed.

The following retrieves the transaction ID of the newly created Sessionless transaction.

BEGIN
  IF DBMS_TRANSACTION.GET_TRANSACTION_TYPE() = DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS THEN
    DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(DBMS_TRANSACTION.GET_TRANSACTION_ID()));
  ELSE
    DBMS_OUTPUT.PUT_LINE('Not a Sessionless transaction');
  END IF;
 END;
 /

The output is:

9AF035CCB2024FA6BF17C8CDBF7B6D6C

PL/SQL procedure successfully completed.

Example 31-5 Starting a New Sessionless Transaction on the OCI Client

OCISvcCtx *svchp;
OCIError *errhp;
...
ub4 timeout = 60;

/* start the transaction. This will be attached on the subsequent server round trip.
    */
OCITransStart(svchp, errhp, timeout, OCI_TRANS_SESSIONLESS | OCI_TRANS_NEW);

timeout: how long (in seconds) after the suspension of this Sessionless transaction should the server roll back this transaction. This avoids a transaction from holding on to database resources (such as row locks) indefinitely.

Example 31-6 Retrieving GTRID on the Database Server

The following code samples start a Sessionless transaction and use the GET_TRANSACTION_ID() function to retrieve the Sessionless transaction's GTRID.

SET SERVEROUTPUT ON

DECLARE
  gtrid VARCHAR2(128);
BEGIN
  gtrid := DBMS_TRANSACTION.START_TRANSACTION
  ( UTL_RAW.CAST_TO_RAW('user_specified_gtrid')
  , DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS
  , 20
  , DBMS_TRANSACTION.TRANSACTION_NEW
  );
  DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(gtrid));
END;
/

The output is:

user_specified_gtrid

PL/SQL procedure successfully completed.

The following retrieves the GTRID.

BEGIN
  IF DBMS_TRANSACTION.GET_TRANSACTION_TYPE() = DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS THEN
    DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(DBMS_TRANSACTION.GET_TRANSACTION_ID()));
  ELSE
    DBMS_OUTPUT.PUT_LINE('Not a Sessionless transaction');
  END IF;
END;
/

The output is:

user_specified_gtrid

PL/SQL procedure successfully completed.

Example 31-7 Retrieving GTRID on the OCI Client

OCITrans *current_txnhp;
XID      *current_xidp;
char      gtrid_buffer[65];
 
/* get transaction handle from service context handle */
OCIAttrGet(svchp, OCI_HTYPE_SVCCTX, &current_txnhp, NULL, OCI_ATTR_TRANS, errhp);

/* get XID from transaction handle */ 
OCIAttrGet(current_txnhp, OCI_HTYPE_TRANS, &current_xidp, NULL, OCI_ATTR_XID, errhp));

/* get GTRID from XID */
memcpy(gtrid_buffer, current_xidp->data, current_xidp->gtrid_length);

Example 31-8 Suspending an Active Sessionless Transaction on the Server

The following is an example of suspending an active Sessionless transaction on the server:

SET SERVEROUTPUT ON

DECLARE
  gtrid VARCHAR2(128);
BEGIN
  -- start Sessionless transaction
  gtrid := DBMS_TRANSACTION.START_TRANSACTION
  ( UTL_RAW.CAST_TO_RAW('user_specified_gtrid')
  , DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS
  , 20
  , DBMS_TRANSACTION.TRANSACTION_NEW
  );

  -- confirm the transaction is started
  IF DBMS_TRANSACTION.GET_TRANSACTION_TYPE() = DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS THEN
    DBMS_OUTPUT.PUT_LINE('After start: ' || UTL_RAW.CAST_TO_VARCHAR2(DBMS_TRANSACTION.GET_TRANSACTION_ID()));
  ELSE
    DBMS_OUTPUT.PUT_LINE('Not a Sessionless transaction');
  END IF;

  -- suspend the transaction
  DBMS_TRANSACTION.SUSPEND_TRANSACTION;
  -- confirm the transaction is suspended
  DBMS_OUTPUT.PUT_LINE('After suspend: ' || DBMS_TRANSACTION.GET_TRANSACTION_ID());
END;
/

The output is:

After start: user_specified_gtrid
After suspend:

PL/SQL procedure successfully completed.

Example 31-9 Immediate Suspend

To suspend the active Sessionless transaction immediately, call the OCITransDetach function with the flag (OCI_TRANS_SESSIONLESS | OCI_DEFAULT), as in the following example.

OCITransDetach(svchp, errhp, OCI_TRANS_SESSIONLESS | OCI_DEFAULT);

Example 31-10 Pre-call Suspend (Suspend before the Next Server Round Trip)

To issue a pre-call suspend (suspend on the next server round trip, before the main call), call the OCITransDetach function with the OCI_TRANS_SESSIONLESS | OCI_TRANS_PRE_CALL flag. Being a pre-call function, the suspend code is run before the main call.

In the following example, the transaction is actually suspended before the OCIPing() call.

OCITransDetach(svchp, errhp, OCI_TRANS_SESSIONLESS | OCI_TRANS_PRE_CALL);
OCIPing(svchp, errhp, OCI_DEFAULT);

The run sequence of immediate suspend and pre-call suspend are the same, but pre-call suspend is attached to the next server round trip, thus reducing network latency. For example, if you would make a query after suspending a Sessionless transaction, using pre-call suspend can save a server round trip and reduce latency.

Example 31-11 Post-call Suspend (Suspend after the Next Server Round Trip)

To issue a post-call suspend (suspend on the next server round trip, after the main call), call the OCITransDetach function with the OCI_TRANS_SESSIONLESS | OCI_TRANS_POST_CALL flag.

OCITransDetach(svchp, errhp, OCI_TRANS_SESSIONLESS | OCI_TRANS_POST_CALL);
OCIPing(svchp, errhp, OCI_DEFAULT);

Doing this is useful if you know about the last round trip to the server (for example, the last DML of this unit of work).

Example 31-12 Post-call Suspend

In the following example, the transaction identified by the "my_Sessionless_txn3" GTRID is suspended automatically after the successful running of OCIStmtExecute().

OCISessionGet (envhp, errhp, &svchp, authInfop, poolName, poolNameLen, NULL, 0,
               NULL, NULL, NULL, OCI_SESSGET_SPOOL | OCI_SESSGET_PURITY_NEW);
OCIAttrSet (svchp, OCI_HTYPE_SVCCTX, txnhp, 0, OCI_ATTR_TRANS, errhp);
 
memcpy(xidp->data, "my_Sessionless_txn3", strlen("my_Sessionless_txn3"));
xidp->gtrid_length = strlen("my_Sessionless_txn3");
OCIAttrSet(txnhp, OCI_HTYPE_TRANS, &xidp, sizeof(XID), OCI_ATTR_XID, errhp);
OCITransStart(svchp, errhp, 60, OCI_TRANS_SESSIONLESS | OCI_TRANS_NEW);
 
// Ask a post-call suspend
OCITransDetach(svchp, errhp, OCI_TRANS_SESSIONLESS | OCI_POST_CALL);
 
// Do the DML
OCIStmtPrepare(…); /* insert into mytab1(c1, c2) values (1, 1); */
OCIStmtBindByPos(…);
OCIStmtExecute(…);   /* Server suspends the transaction after this DML is executed. */

Example 31-14 Resuming a Sessionless Transaction on the Database Server

CREATE PROCEDURE PRINT_GTRID IS
BEGIN
    IF DBMS_TRANSACTION.GET_TRANSACTION_TYPE() = DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS THEN
      DBMS_OUTPUT.PUT_LINE('Sessionless transaction in session: ' || UTL_RAW.CAST_TO_VARCHAR2(DBMS_TRANSACTION.GET_TRANSACTION_ID()));
    ELSE
      DBMS_OUTPUT.PUT_LINE('Not a Sessionless transaction');
    END IF;
END PRINT_GTRID;
/

The output is:

Procedure created.

The following starts a Sessionless transaction and prints the GTRID.

DECLARE
    gtrid VARCHAR2(128);
BEGIN
    gtrid := DBMS_TRANSACTION.START_TRANSACTION
    ( UTL_RAW.CAST_TO_RAW('my_sessionless_timeout_ex')
    , DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS
    , 20
    , DBMS_TRANSACTION.TRANSACTION_NEW
    );
    PRINT_GTRID;
    DBMS_OUTPUT.PUT_LINE('gtrid: ' || gtrid);
    DBMS_OUTPUT.PUT_LINE('cast gtrid: ' || UTL_RAW.CAST_TO_VARCHAR2(gtrid));
END;
/

The output is:

Sessionless transaction in session: my_sessionless_timeout_ex
gtrid: 6D795F73657373696F6E6C6573735F74696D656F75745F6578
cast gtrid: my_sessionless_timeout_ex

PL/SQL procedure successfully completed.

The following inserts values into the mytab1 table and fetches the updates.

INSERT INTO mytab1(c1, c2) values (1, 1);
SELECT * FROM mytab1;

The output is:

1 row created.

        C1 C2
---------- ----------
         1 1         

The following suspends the transaction.

EXEC DBMS_TRANSACTION.SUSPEND_TRANSACTION;
SELECT * FROM mytab1;

The output is:

PL/SQL procedure successfully completed.

no rows selected

The following resumes the transaction.

DECLARE
    gtrid VARCHAR2(128);
BEGIN
    gtrid := DBMS_TRANSACTION.START_TRANSACTION
    ( UTL_RAW.CAST_TO_RAW('my_sessionless_timeout_ex')
    , DBMS_TRANSACTION.TRANSACTION_TYPE_SESSIONLESS
    , 20
    , DBMS_TRANSACTION.TRANSACTION_RESUME
    );

    PRINT_GTRID;
    DBMS_OUTPUT.PUT_LINE('gtrid: ' || gtrid);
    DBMS_OUTPUT.PUT_LINE('cast gtrid: ' || UTL_RAW.CAST_TO_VARCHAR2(gtrid));
END;
/

The output is:

Sessionless transaction in session: my_sessionless_timeout_ex
gtrid: 6D795F73657373696F6E6C6573735F74696D656F75745F6578
cast gtrid: my_sessionless_timeout_ex

PL/SQL procedure successfully completed.

The following fetches the updates made to the table.

SELECT * FROM mytab1;

The output is:

        C1 C2
---------- ----------
         1 1         

The following rolls back the transaction.

ROLLBACK;

The output is:

Rollback complete.

The following drops the PRINT_GTRID procedure.

DROP PROCEDURE PRINT_GTRID;

The output is:

Procedure dropped.

Example 31-15 Resuming a Sessionless Transaction on the OCI Client

ub4 timeout = 20;
OCITransStart(svchp, errhp, timeout, OCI_TRANS_SESSIONLESS | OCI_TRANS_RESUME);

timeout: The timeout here refers to the resume timeout, which is different from the transaction timeout. The resume time-out value can be a 0 or a positive number. Within an instance, a Sessionless transaction can only be associated with one session at any given time. Thus, a Sessionless transaction cannot be resumed if it is associated with another session on the same instance. When this situation arises, the resume timeout value specifies how long (in seconds) this session waits for the other session to suspend the transaction, so that it can resume the transaction. If the other session does not suspend the transaction before the wait times out, an ORA-25351 error is raised. If the resume timeout value is 0, the error is immediately raised.

#ifndef OCI_ORACLE
#include <oci.h>
#endif
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#define USERNAME_LEN 256
#define PASSWORD_LEN 1025
#define DBNAME_LEN   1025

OCIEnv      *envhp  = (OCIEnv *)0;
OCIError    *errhp  = (OCIError *)0;
OCIAuthInfo *authp  = (OCIAuthInfo *)0;
OCISPool    *poolhp = (OCISPool *)0;
OCISvcCtx   *svchp  = (OCISvcCtx *)0;
OCITrans    *txnhp  = (OCITrans *)0;
OCIStmt     *stmthp = (OCIStmt *)0;
OCIBind     *bndhp[3];
OCIDefine   *defhp[3];

char    *username;
char    *password;
char    *dbname;
OraText *pool_name;
ub4      pool_name_len;

void insert_dept(int deptno, const char *dname, const char *loc);
void query_dept(const char *description);
void query_transaction_type(const char *description);
void chk_err(sword ret, OCIError *errhp, sb2 linenum);
#define CHK_ERR(cmd) chk_err(cmd, errhp, __LINE__)

int main(int argc, char **argv)
{
  int nread;
  size_t usernameLen = USERNAME_LEN; 
  size_t passwordLen = PASSWORD_LEN; 
  size_t dbnameLen = DBNAME_LEN; 

  unsigned int   i;
  unsigned char *gtrid;
  unsigned int   gtridlen = 0;
  unsigned char  gtridbuf[64];
  
  /* get username, password, dbname (connect string) */
  username = malloc(USERNAME_LEN * sizeof(char)); 
  password = malloc(PASSWORD_LEN * sizeof(char));
  dbname = malloc(DBNAME_LEN * sizeof(char));
  memset(username, 0, USERNAME_LEN);
  memset(password, 0, PASSWORD_LEN);
  memset(dbname, 0, DBNAME_LEN);
  
  printf("enter username:");
  if ((nread = (int)getline((char **)&username, &usernameLen, stdin)) == -1)
  {
    printf("ERROR: Failed to get username\n");
    return -1;
  }
  if(strlen(username) >= 1)
    username[strlen(username)-1] = '\0';
  printf("enter password:");
  if ((nread = (int)getline((char **)&password, &passwordLen, stdin)) == -1)
  {
    printf("ERROR: Failed to get password\n");
    return -1;
  }
  if(strlen(password) >= 1)
    password[strlen(password)-1] = '\0';
  printf("enter dbname:");
  if ((nread = (int)getline((char **)&dbname, &dbnameLen, stdin)) == -1)
  {
    printf("ERROR: Failed to get dbname\n");
    return -1;
  }
  if(strlen(dbname) >= 1)
    dbname[strlen(dbname)-1] = '\0';
  printf("\n");

  /* set up */
  {
    int ret;
    /* Create Environment */
    if ((ret = OCIEnvCreate((OCIEnv **)&envhp, (ub4)OCI_DEFAULT | OCI_OBJECT,
                            (dvoid *)0, (dvoid * (*)(dvoid *, size_t))0,
                            (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                            (void (*)(dvoid *, dvoid *))0, (size_t)0,
                            (dvoid **)0)) != OCI_SUCCESS)
    {
      printf("failed to create OCIEnv. ret = %d\n", ret);
      return 1;
    }

    /* Allocate error handle */
    CHK_ERR(OCIHandleAlloc(envhp, (void **)&errhp, OCI_HTYPE_ERROR, 0, 0));

    /* create a session pool and get a session from it */
    CHK_ERR(OCIHandleAlloc(envhp, (void **)&poolhp, OCI_HTYPE_SPOOL, 0, 0));
    CHK_ERR(OCISessionPoolCreate(
      envhp, errhp, poolhp, &pool_name, &pool_name_len, (OraText *)dbname,
      strlen(dbname), 4, 10, 1, (OraText *)username, strlen(username),
      (OraText *)password, strlen((char *)password), OCI_DEFAULT));
    CHK_ERR(OCISessionGet(envhp, errhp, &svchp, NULL, pool_name,
                          pool_name_len, 0, 0, 0, 0, 0, OCI_SESSGET_SPOOL));

    /* allocate a transaction handle and set is as OCI_ATTR_TRANS */
    CHK_ERR(OCIHandleAlloc(envhp, (void **)&txnhp, OCI_HTYPE_TRANS, 0, 0));
    CHK_ERR(
      OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, txnhp, 0, OCI_ATTR_TRANS, errhp));
  }

  /* Query dept table before changes. */
  query_dept("before begin");

  /* Start a new Sessionless transaction with a generated GTRID. Insert a row
   * in dept table, then query the table. We should see the inserted row. */
  CHK_ERR(
    OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, txnhp, 0, OCI_ATTR_TRANS, errhp));
  CHK_ERR(
    OCITransStart(svchp, errhp, 60, OCI_TRANS_SESSIONLESS | OCI_TRANS_NEW));
  insert_dept(50, "DEVELOPMENT1", "SEATTLE");
  query_dept("in sessionless txn, after insert");

  /* Get the generated GTRID so we can suspend and resume transaction. */
  CHK_ERR(OCIAttrGet(txnhp, OCI_HTYPE_TRANS, &gtrid, &gtridlen,
                     OCI_ATTR_TRANS_NAME, errhp));
  memcpy(gtridbuf, gtrid, gtridlen);
  printf("Generated GTRID in hexadecimal is: ");
  for (i = 0; i < gtridlen; ++i)
    printf("%02X", (unsigned int)gtridbuf[i]);
  printf("\n");

  /* Suspend Sessionless transaction and query dept table. We should not see
   * the inserted row. */
  CHK_ERR(OCITransDetach(svchp, errhp, OCI_TRANS_SESSIONLESS | OCI_DEFAULT));
  query_dept("outside sessionless txn");

  /* Set OCI_ATTR_TRANS to NULL. */
  CHK_ERR(OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, NULL, 0, OCI_ATTR_TRANS, errhp));

  /* release the session here and get another session connected to the
   * same database. This sessions can be a different session and can connect
   * to a different instance in RAC configuration. */
  CHK_ERR(OCISessionRelease(svchp, errhp, 0, 0, OCI_DEFAULT));
  CHK_ERR(OCISessionGet(envhp, errhp, &svchp, NULL, pool_name,
                          pool_name_len, 0, 0, 0, 0, 0, OCI_SESSGET_SPOOL));

  /* resume the Sessionless transaction and query dept. We can see the previous
   * change. */
  CHK_ERR(OCIAttrSet(txnhp, OCI_HTYPE_TRANS, gtrid, gtridlen,
                     OCI_ATTR_TRANS_NAME, errhp));
  CHK_ERR(
    OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, txnhp, 0, OCI_ATTR_TRANS, errhp));
  CHK_ERR(
    OCITransStart(svchp, errhp, 60, OCI_TRANS_SESSIONLESS | OCI_TRANS_RESUME));
  query_dept("resumed sessionless txn");
  
  /* insert an additional row, and commit the Sessionless transaction. */
  insert_dept(51, "DEVELOPMENT2", "SAN FRANCISCO");
  CHK_ERR(OCITransCommit(svchp, errhp, OCI_DEFAULT));

  /* after commit, we can confirm no transaction is active in the session. when
   * query the table, we can see both rows got inserted. */
  query_transaction_type("after commit");
  query_dept("after commit");

  /* tear down */
  {
    if (txnhp)
      CHK_ERR(OCIHandleFree(txnhp, OCI_HTYPE_TRANS));
    if (svchp)
    {
      CHK_ERR(OCISessionRelease(svchp, errhp, 0, 0, OCI_DEFAULT));
      svchp = NULL;
    }
    CHK_ERR(OCISessionPoolDestroy(poolhp, errhp, OCI_DEFAULT));
    if (poolhp)
      OCIHandleFree((void *)poolhp, OCI_HTYPE_SPOOL);
  }
  
  free(dbname);
  free(password);
  free(username);
  return 0;
}

void insert_dept(int deptno, const char *dname, const char *loc)
{
  OraText dml[] = "INSERT INTO dept(deptno, dname, loc) values (:1, :2, :3)";
  CHK_ERR(OCIStmtPrepare2(svchp, &stmthp, errhp, dml, strlen((char *)dml), 0,
                          0, OCI_NTV_SYNTAX, OCI_DEFAULT));
  CHK_ERR(OCIBindByPos(stmthp, &bndhp[0], errhp, 1, &deptno, sizeof(deptno),
                       SQLT_INT, 0, 0, 0, 0, 0, OCI_DEFAULT));
  CHK_ERR(OCIBindByPos(stmthp, &bndhp[1], errhp, 2, (void *)dname,
                       strlen(dname) + 1, SQLT_STR, 0, 0, 0, 0, 0,
                       OCI_DEFAULT));
  CHK_ERR(OCIBindByPos(stmthp, &bndhp[1], errhp, 3, (void *)loc,
                       strlen(loc) + 1, SQLT_STR, 0, 0, 0, 0, 0, OCI_DEFAULT));
  CHK_ERR(OCIStmtExecute(svchp, stmthp, errhp, 1, 0, 0, 0, OCI_DEFAULT));
  CHK_ERR(OCIStmtRelease(stmthp, errhp, 0, 0, OCI_DEFAULT));
}

void query_dept(const char *description)
{
  OraText query[] = "SELECT deptno, dname, loc FROM dept ORDER BY deptno";
  int     deptno;
  OraText dname[20];
  OraText loc[20];
  int     i = 1;
  int     ret;
  printf("DEPT table [%s]\n", description);
  CHK_ERR(OCIStmtPrepare2(svchp, &stmthp, errhp, query, strlen((char *)query),
                          0, 0, OCI_NTV_SYNTAX, OCI_DEFAULT));
  CHK_ERR(OCIDefineByPos(stmthp, &defhp[0], errhp, 1, &deptno, sizeof(deptno),
                         SQLT_INT, 0, 0, 0, OCI_DEFAULT));
  CHK_ERR(OCIDefineByPos(stmthp, &defhp[1], errhp, 2, (void *)dname, 20,
                         SQLT_STR, 0, 0, 0, OCI_DEFAULT));
  CHK_ERR(OCIDefineByPos(stmthp, &defhp[2], errhp, 3, (void *)loc, 20,
                         SQLT_STR, 0, 0, 0, OCI_DEFAULT));
  CHK_ERR(OCIStmtExecute(svchp, stmthp, errhp, 0, 0, 0, 0, OCI_DEFAULT));
  while ((ret = OCIStmtFetch2((OCIStmt *)stmthp, (OCIError *)errhp, (ub4)1,
                       (ub4)OCI_FETCH_NEXT, (sb4)0, (ub4)OCI_DEFAULT)) == 0)
  {
    printf("Row %d: (%d, %s, %s)\n", i++, deptno, dname, loc);
  }
  if (ret && ret != OCI_NO_DATA)
    CHK_ERR(ret);
  printf("\n");
  CHK_ERR(OCIStmtRelease(stmthp, errhp, 0, 0, OCI_DEFAULT));
}

void query_transaction_type(const char *description)
{
  OraText query[] =
    "SELECT NVL(TO_CHAR(DBMS_TRANSACTION.GET_TRANSACTION_TYPE), 'NULL') from "
    "dual";
  char txn_type[10];
  int  ret;
  printf("Query transaction type [%s]: ", description);
  CHK_ERR(OCIStmtPrepare2(svchp, &stmthp, errhp, query, strlen((char *)query),
                          0, 0, OCI_NTV_SYNTAX, OCI_DEFAULT));
  CHK_ERR(OCIDefineByPos(stmthp, &defhp[0], errhp, 1, &txn_type,
                         sizeof(txn_type), SQLT_STR, 0, 0, 0, OCI_DEFAULT));
  CHK_ERR(OCIStmtExecute(svchp, stmthp, errhp, 0, 0, 0, 0, OCI_DEFAULT));
  while (
    (ret = OCIStmtFetch2((OCIStmt *)stmthp, (OCIError *)errhp, (ub4)1,
                         (ub4)OCI_FETCH_NEXT, (sb4)0, (ub4)OCI_DEFAULT)) == 0)
    printf("%s\n", txn_type);

  if (ret && ret != OCI_NO_DATA)
    CHK_ERR(ret);
  printf("\n");
  CHK_ERR(OCIStmtRelease(stmthp, errhp, 0, 0, OCI_DEFAULT));
}

void chk_err(sword ret, OCIError *errhp, sb2 linenum)
{
  OraText msgbuf[1024];
  sb4     errcode;
  memset((void *)msgbuf, '\0', 1024);
  if (ret == OCI_ERROR || ret == OCI_SUCCESS_WITH_INFO)
    OCIErrorGet((dvoid *)errhp, (ub4)1, (OraText *)NULL, &errcode, msgbuf,
                (ub4)sizeof(msgbuf), (ub4)OCI_HTYPE_ERROR);
  
  if (ret == OCI_SUCCESS)
    return;

  printf("------------------------------------------------------------\n");
  printf(":Line # %d: ", linenum);
  switch (ret)
  {
  case OCI_ERROR:
  case OCI_SUCCESS_WITH_INFO:
    printf(ret == OCI_SUCCESS_WITH_INFO ? "OCI_SUCCESS_WITH_INFO\n"
                                        : "OCI_ERROR\n");
    printf("%s\n", msgbuf);
    break;

  case OCI_NEED_DATA:
    printf("OCI_NEED_DATA\n");
    break;

  case OCI_NO_DATA:
    printf("OCI_NO_DATA\n");
    break;

  case OCI_INVALID_HANDLE:
    printf("OCI_INVALID_HANDLE\n");
    exit(1);

  case OCI_STILL_EXECUTING:
    printf("OCI_STILL_EXECUTING\n");
    break;

  case OCI_CONTINUE:
    printf("OCI_CONTINUE\n");
    break;

  default:
    printf("Error Code %d\n", ret);
    break;
  }
}

The output is as follows. Note that your output may have a different username, password, dbname (TNS name), and generated GTRID.

enter username:scott
enter password:tiger
enter dbname:cdb1_pdb1

DEPT table [before begin]
Row 1: (10, ACCOUNTING, NEW YORK)
Row 2: (20, RESEARCH, DALLAS)
Row 3: (30, SALES, CHICAGO)
Row 4: (40, OPERATIONS, BOSTON)

DEPT table [in sessionless txn, after insert]
Row 1: (10, ACCOUNTING, NEW YORK)
Row 2: (20, RESEARCH, DALLAS)
Row 3: (30, SALES, CHICAGO)
Row 4: (40, OPERATIONS, BOSTON)
Row 5: (50, DEVELOPMENT1, SEATTLE)

Generated GTRID in hexadecimal is: 688330D5C1764F96BFF8267F58E5709E
DEPT table [outside sessionless txn]
Row 1: (10, ACCOUNTING, NEW YORK)
Row 2: (20, RESEARCH, DALLAS)
Row 3: (30, SALES, CHICAGO)
Row 4: (40, OPERATIONS, BOSTON)

DEPT table [resumed sessionless txn]
Row 1: (10, ACCOUNTING, NEW YORK)
Row 2: (20, RESEARCH, DALLAS)
Row 3: (30, SALES, CHICAGO)
Row 4: (40, OPERATIONS, BOSTON)
Row 5: (50, DEVELOPMENT1, SEATTLE)

Query transaction type [after commit]: NULL

DEPT table [after commit]
Row 1: (10, ACCOUNTING, NEW YORK)
Row 2: (20, RESEARCH, DALLAS)
Row 3: (30, SALES, CHICAGO)
Row 4: (40, OPERATIONS, BOSTON)
Row 5: (50, DEVELOPMENT1, SEATTLE)
Row 6: (51, DEVELOPMENT2, SAN FRANCISCO)