Lesson 20-C Language and Oracle Database Operations

Installing Oracle Client Library

  • Install the Oracle client library, including header files such as oci.h and related library files.
  • Ensure that the environment variable LD_LIBRARY_PATH (Linux) or PATH ( Cream) includes the path to the Oracle client library.

Configuring the Compiler

  • Include the header file path of the Oracle client library in the compiler.
  • Link the Oracle client library.

Writing Code

  • Include the necessary header files.
  • Initialize the Oracle environment.
  • Create a session (connection).
  • Execute SQL commands.
  • Process the result set.
  • Close the session.
  • Terminate the Oracle environment.
#include <stdio.h>
#include <stdlib.h>
#include <oci.h>
#include <oratypes.h>

#define OCI_SUCCESS 0

void print_error(OCIError *errhp, ub4 errcode, ub4 line);

int main() {
    // Oracle environment handle
    OCIEnv *envhp = NULL;
    // Oracle error handle
    OCIError *errhp = NULL;
    // Oracle service context handle
    OCISvcCtx *svchp = NULL;
    // Oracle statement handle
    OCIStmt *stmthp = NULL;
    // Oracle server connection handle
    OCISession *usrhp = NULL;
    // Oracle descriptor handle
    OCIParam *paramhp = NULL;
    // Oracle result set handle
    OCIResult *rsethp = NULL;
    // Oracle result set metadata handle
    OCIAttr *attrhp = NULL;

    // Initialize Oracle environment
    if (OCI_SUCCESS != OCIEnvCreate(&envhp, OCI_OBJECT, NULL, NULL, NULL, NULL, 0, NULL)) {
        printf("Failed to create environment handle.\n");
        return 1;
    }

    // Allocate error handle
    if (OCI_SUCCESS != OCIHandleAlloc((dvoid *)envhp, (dvoid **)&errhp, OCI_HTYPE_ERROR, 0, NULL)) {
        printf("Failed to allocate error handle.\n");
        OCIEnvFree(envhp);
        return 1;
    }

    // Allocate service context handle
    if (OCI_SUCCESS != OCIHandleAlloc((dvoid *)envhp, (dvoid **)&svchp, OCI_HTYPE_SVCCTX, 0, NULL)) {
        printf("Failed to allocate service context handle.\n");
        OCIHandleFree(errhp, OCI_HTYPE_ERROR);
        OCIEnvFree(envhp);
        return 1;
    }

    // Set error handle for service context handle
    if (OCI_SUCCESS != OCISvcCtxSetErrorHandle(svchp, errhp)) {
        printf("Failed to set error handle for service context.\n");
        OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
        OCIHandleFree(errhp, OCI_HTYPE_ERROR);
        OCIEnvFree(envhp);
        return 1;
    }

    // Allocate session handle
    if (OCI_SUCCESS != OCIHandleAlloc((dvoid *)envhp, (dvoid **)&usrhp, OCI_HTYPE_SESSION, 0, NULL)) {
        printf("Failed to allocate session handle.\n");
        OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
        OCIHandleFree(errhp, OCI_HTYPE_ERROR);
        OCIEnvFree(envhp);
        return 1;
    }

    // Connect to database
    text *connect_str = (text *)"USER/PASSWORD@HOST/SID";
    ub4 connect_mode = OCI_SYSDBA; // Can be changed according to needs
    ub4 session_type = OCI_DEFAULT;
    if (OCI_SUCCESS != OCISessionBegin(svchp, errhp, usrhp, connect_str, (ub4)strlen(connect_str), connect_mode, session_type)) {
        print_error(errhp, OCI_SUCCESS, __LINE__);
        OCIHandleFree(usrhp, OCI_HTYPE_SESSION);
        OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
        OCIHandleFree(errhp, OCI_HTYPE_ERROR);
        OCIEnvFree(envhp);
        return 1;
    }

    // Allocate statement handle
    if (OCI_SUCCESS != OCIHandleAlloc((dvoid *)envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, 0, NULL)) {
        printf("Failed to allocate statement handle.\n");
        OCISessionEnd(svchp, errhp, usrhp, OCI_DEFAULT);
        OCIHandleFree(usrhp, OCI_HTYPE_SESSION);
        OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
        OCIHandleFree(errhp, OCI_HTYPE_ERROR);
        OCIEnvFree(envhp);
        return 1;
    }

    // Prepare SQL statement
    text *sql = (text *)"SELECT * FROM mytable";
    if (OCI_SUCCESS != OCIStmtPrepare(stmthp, errhp, sql, (ub4)strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT)) {
        print_error(errhp, OCI_SUCCESS, __LINE__);
        OCIHandleFree(stmthp, OCI_HTYPE_STMT);
        OCISessionEnd(svchp, errhp, usrhp, OCI_DEFAULT);
        OCIHandleFree(usrhp, OCI_HTYPE_SESSION);
        OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
        OCIHandleFree(errhp, OCI_HTYPE_ERROR);
        OCIEnvFree(envhp);
        return 1;
    }

    // Execute SQL statement
    if (OCI_SUCCESS != OCIStmtExecute(svchp, stmthp, errhp, 1, 0, 0, 0, OCI_DEFAULT)) {
        print_error(errhp, OCI_SUCCESS, __LINE__);
        OCIHandleFree(stmthp, OCI_HTYPE_STMT);
        OCISessionEnd(svchp, errhp, usrhp, OCI_DEFAULT);
        OCIHandleFree(usrhp, OCI_HTYPE_SESSION);
        OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
        OCIHandleFree(errhp, OCI_HTYPE_ERROR);
        OCIEnvFree(envhp);
        return 1;
    }

    // Fetch result set
    if (OCI_SUCCESS != OCIStmtFetch(stmthp, errhp, 1, 0, OCI_FETCH_NEXT, OCI_DEFAULT)) {
        print_error(errhp, OCI_SUCCESS, __LINE__);
        OCIHandleFree(stmthp, OCI_HTYPE_STMT);
        OCISessionEnd(svchp, errhp, usrhp, OCI_DEFAULT);
        OCIHandleFree(usrhp, OCI_HTYPE_SESSION);
        OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
        OCIHandleFree(errhp, OCI_HTYPE_ERROR);
        OCIEnvFree(envhp);
        return 1;
    }

    // Process result set
    ub4 num_cols;
    if (OCI_SUCCESS != OCIAttrGet(stmthp, OCI_HTYPE_STMT, (dvoid *)&num_cols, NULL, OCI_ATTR_NUM_COLUMNS, errhp)) {
        print_error(errhp, OCI_SUCCESS, __LINE__);
        OCIHandleFree(stmthp, OCI_HTYPE_STMT);
        OCISessionEnd(svchp, errhp, usrhp, OCI_DEFAULT);
        OCIHandleFree(usrhp, OCI_HTYPE_SESSION);
        OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
        OCIHandleFree(errhp, OCI_HTYPE_ERROR);
        OCIEnvFree(envhp);
        return 1;
    }

    // Output results
    for (ub4 i = 0; i < num_cols; i++) {
        text *col_name;
        if (OCI_SUCCESS != OCIAttrGet(stmthp, OCI_HTYPE_STMT, (dvoid *)&col_name, NULL, OCI_ATTR_COLUMN_NAME + (i + 1), errhp)) {
            print_error(errhp, OCI_SUCCESS, __LINE__);
            OCIHandleFree(stmthp, OCI_HTYPE_STMT);
            OCISessionEnd(svchp, errhp, usrhp, OCI_DEFAULT);
            OCIHandleFree(usrhp, OCI_HTYPE_SESSION);
            OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
            OCIHandleFree(errhp, OCI_HTYPE_ERROR);
            OCIEnvFree(envhp);
            return 1;
        }
        printf("%s\t", col_name);
    }
    printf("\n");
    ub4 row_num = 0;
    do {
        row_num++;
        for (ub4 i = 0; i < num_cols; i++) {
            text *col_val;
            if (OCI_SUCCESS != OCIAttrGet(stmthp, OCI_HTYPE_STMT, (dvoid *)&col_val, NULL, OCI_ATTR_COLUMN_VALUE + (i + 1), errhp)) {
                print_error(errhp, OCI_SUCCESS, __LINE__);
                OCIHandleFree(stmthp, OCI_HTYPE_STMT);
                OCISessionEnd(svchp, errhp, usrhp, OCI_DEFAULT);
                OCIHandleFree(usrhp, OCI_HTYPE_SESSION);
                OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
                OCIHandleFree(errhp, OCI_HTYPE_ERROR);
                OCIEnvFree(envhp);
                return 1;
            }
            printf("%s\t", col_val);
        }
        printf("\n");
} while (OCI_SUCCESS == OCIStmtFetch(stmthp, errhp, 1, 0, OCI_FETCH_NEXT, OCI_DEFAULT));

Error Handling Function

void print_error(OCIError *errhp, ub4 errcode, ub4 line) {
    text msg[1024];
    sb4 msg_len;
    if (OCI_SUCCESS != OCIErrorGet(errhp, 1, (text *)NULL, &errcode, msg, 1024, OCI_HTYPE_ERROR)) {
        printf("Failed to get error message at line %d\n", line);
        return;
    }
    msg[msg_len] = '\0';
    printf("Error code: %d, Message: %s at line %d\n", errcode, msg, line);
}

Releasing Resources and Terminating Oracle Environment

    // Release resources
    OCIHandleFree(stmthp, OCI_HTYPE_STMT);
    OCISessionEnd(svchp, errhp, usrhp, OCI_DEFAULT);
    OCIHandleFree(usrhp, OCI_HTYPE_SESSION);
    OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
    OCIHandleFree(errhp, OCI_HTYPE_ERROR);
    OCIEnvFree(envhp);
}

int main() {
    // ... the above code ...

    // Output column names
    for (ub4 i = 0; i < num_cols; i++) {
        text *col_name;
        if (OCI_SUCCESS != OCIAttrGet(stmthp, OCI_HTYPE_STMT, (dvoid *)&col_name, NULL, OCI_ATTR_COLUMN_NAME + (i + 1), errhp)) {
            print_error(errhp, OCI_SUCCESS, __LINE__);
            OCIHandleFree(stmthp, OCI_HTYPE_STMT);
            OCISessionEnd(svchp, errhp, usrhp, OCI_DEFAULT);
            OCIHandleFree(usrhp, OCI_HTYPE_SESSION);
            OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
            OCIHandleFree(errhp, OCI_HTYPE_ERROR);
            OCIEnvFree(envhp);
            return 1;
        }
        printf("%s\t", col_name);
    }
    printf("\n");

    // Loop through each row in the result set
    ub4 row_num = 0;
    do {
        row_num++;
        for (ub4 i = 0; i < num_cols; i++) {
            text *col_val;
            if (OCI_SUCCESS != OCIAttrGet(stmthp, OCI_HTYPE_STMT, (dvoid *)&col_val, NULL, OCI_ATTR_COLUMN_VALUE + (i + 1), errhp)) {
                print_error(errhp, OCI_SUCCESS, __LINE__);
                OCIHandleFree(stmthp, OCI_HTYPE_STMT);
                OCISessionEnd(svchp, errhp, usrhp, OCI_DEFAULT);
                OCIHandleFree(usrhp, OCI_HTYPE_SESSION);
                OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
                OCIHandleFree(errhp, OCI_HTYPE_ERROR);
                OCIEnvFree(envhp);
                return 1;
            }
            printf("%s\t", col_val);
        }
        printf("\n");
    } while (OCI_SUCCESS == OCIStmtFetch(stmthp, errhp, 1, 0, OCI_FETCH_NEXT, OCI_DEFAULT));

    // Release resources
    OCIHandleFree(stmthp, OCI_HTYPE_STMT);
    OCISessionEnd(svchp, errhp, usrhp, OCI_DEFAULT);
    OCIHandleFree(usrhp, OCI_HTYPE_SESSION);
    OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
    OCIHandleFree(errhp, OCI_HTYPE_ERROR);
    OCIEnvFree(envhp);

    return 0;
}
Course: Mastering C Programming From Basics to Advanced Techniques
Share your love

Trending now

Exploring Web 3D Core Technologies Development
Assembly Language Programming From Low-Level Logic to System Kernel
WebRTC Advanced Guide：Media, Transport & Architecture
WebSocket Deep Guide：Building High-Performance Realtime Systems