Does Oracle have an API?

Oracle has an API. There are four main types of APIs for Oracle database: 1. OCI API, which is a C language programming interface provided by Oracle; 2. JDBC API, which is an API library for connecting Oracle database in Java language; 3. ODBC API, which is provided by Microsoft Unified API specification for connecting various databases; 4. .NET API, etc.

The operating environment of this tutorial: Windows 10 system, Oracle version 19c, Dell G3 computer.

In recent years, Oracle database has become more and more widely used and has become the first choice for many enterprise application systems. As a developer, it is very important to master the API development skills of Oracle database. This article will introduce the content of Oracle API development and provide some practical examples.

1. Introduction

The full name of API (Application Programming Interface) is the application programming interface. Simply put, API is the interface for software applications to interact with the outside world. Through API, external data interaction and calls can be realized, and data transmission and communication between various applications and systems can be realized.

There are four main categories of Oracle database APIs:

(1) OCI API: Oracle Call Interface, which is a C language programming interface provided by Oracle.

(2) JDBC API: Java Database Connectivity is an API library for connecting the Oracle database in the Java language.

(3) ODBC API: Open Database Connectivity is a unified API specification provided by Microsoft to connect various databases.

(4).NET API: API library provided by Microsoft .NET Framework to connect to Oracle database.

Here, we will mainly introduce the OCI API.

2. OCI API

OCI API is Oracle Call Interface, which is the C language programming interface of Oracle database. OCI API is an efficient and powerful API library that supports multiple languages.

OCI interacts with the Oracle database by providing a set of library functions to the application and allowing the application to call these functions at runtime. The programming model of OCI is similar to the standard I/O function library in C language, so programmers with C language can get started quickly.

The installation package of OCI can be found on the Oracle official website or Oracle installation CD. After installation, add the include, lib, oraclnt.dll in the directory where OCI is located to your project.

If you are using VS Code, you can use Visual Studio Code integrated Oracle Database operations to integrate the OCI API into the application.

Let’s take a look at some practical OCI API usage examples.

3. OCI API usage examples

3.1 Connecting to Oracle database

Connecting to Oracle database is one of the most basic operations of OCI API. Let's first look at a simple code to connect to the Oracle database.

#include 
#include 
#include 
#define USERNAME "scott"
#define PASSWORD "tiger"
#define DATABASE "orcl"
#define SQLSTR "select * from emp"
 
int mn(int argc, char *argv[])
{
    OCIEnv *envhp;          // 环境句柄
    OCIError *errhp;        // 错误句柄
    OCISvcCtx *svchp;       // 服务上下文句柄
    OCIServer *srvhp;       // 服务器句柄
    OCISession *authp;      // 验证句柄
    OCIStmt *stmthp;        // 语句句柄
    OCIDefine *defnp;       // 取值句柄
    sword errcode;
    text errbuf[512];
    ub4 errlen;
    ub2 rcount;
    ub4 empno;
 
    if (OCIEnvCreate(&envhp, OCI_DEFAULT, (dvoid *)0, (dvoid *(*)(dvoid *, size_t))0, (dvoid *(*)(dvoid *, dvoid *, size_t))0, (void (*)(dvoid *, dvoid *))0, (size_t)0, (dvoid **)0))
    {
        printf("OCIEnvCreate Error!\n");
        return 0;
    }
 
    OCIHandleAlloc(envhp, (dvoid **)&errhp, OCI_HTYPE_ERROR, 0, (dvoid **)0);
 
    OCIHandleAlloc(envhp, (dvoid **)&svchp, OCI_HTYPE_SVCCTX, 0, (dvoid **)0);
    OCIHandleAlloc(envhp, (dvoid **)&srvhp, OCI_HTYPE_SERVER, 0, (dvoid **)0);
    OCIHandleAlloc(envhp, (dvoid **)&authp, OCI_HTYPE_SESSION, 0, (dvoid **)0);
    OCIHandleAlloc(envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, 0, (dvoid **)0);
 
    if (OCIServerAttach(srvhp, errhp, (text *)DATABASE, strlen(DATABASE), OCI_DEFAULT) != OCI_SUCCESS)
    {
        OCIErrorGet(errhp, 1, (text *)0, &errcode, errbuf, sizeof(errbuf), OCI_HTYPE_ERROR);
        printf("OCIServerAttach Error: %s\n", errbuf);
        return 0;
    }
 
    OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp, (ub4)0, OCI_ATTR_SERVER, errhp);
 
    OCIAttrSet(authp, OCI_HTYPE_SESSION, (dvoid *)USERNAME, strlen(USERNAME), OCI_ATTR_USERNAME, errhp);
    OCIAttrSet(authp, OCI_HTYPE_SESSION, (dvoid *)PASSWORD, strlen(PASSWORD), OCI_ATTR_PASSWORD, errhp);
 
    if (OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS, OCI_DEFAULT))
    {
        OCIErrorGet(errhp, 1, (text *)0, &errcode, errbuf, sizeof(errbuf), OCI_HTYPE_ERROR);
        printf("OCISessionBegin Error:%s\n", errbuf);
        return 0;
    }
 
    OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, (dvoid *)authp, (ub4)0, OCI_ATTR_SESSION, errhp);
 
    if (OCIStmtPrepare(stmthp, errhp, (text *)SQLSTR, strlen(SQLSTR), OCI_NTV_SYNTAX, OCI_DEFAULT))
    {
        OCIErrorGet(errhp, 1, (text *)0, &errcode, errbuf, sizeof(errbuf), OCI_HTYPE_ERROR);
        printf("OCIStmtPrepare Error:%s\n", errbuf);
        return 0;
    }
 
    if (OCIStmtExecute(svchp, stmthp, errhp, 1, 0, NULL, NULL, OCI_STMT_SCROLLABLE_READ_ONLY))
    {
        OCIErrorGet(errhp, 1, (text *)0, &errcode, errbuf, sizeof(errbuf), OCI_HTYPE_ERROR);
        printf("OCIStmtExecute Error:%s\n", errbuf);
        return 0;
    }
 
    while ((errcode = OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT)) == OCI_SUCCESS)
    {
        OCIAttrGet(stmthp, OCI_HTYPE_STMT, (dvoid *)&empno, NULL, OCI_ATTR_ROWID, errhp);
        printf("%d\n", empno);
    }
 
    OCISessionEnd(svchp, errhp, authp, OCI_DEFAULT);
    OCIServerDetach(srvhp, errhp, OCI_DEFAULT);
 
    OCIHandleFree(errhp, OCI_HTYPE_ERROR);
    OCIHandleFree(srvhp, OCI_HTYPE_SERVER);
    OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
    OCIHandleFree(authp, OCI_HTYPE_SESSION);
    OCIHandleFree(stmthp, OCI_HTYPE_STMT);
 
    OCIEnvFree(envhp);
 
    return 0;
}

The above code uses OCI to connect to the Oracle database and implement data query operations through OCI statement handles.

3.2 Insert data

OCI API can use the OCIStmtPrepare and OCIStmtExecute functions to insert data into the database. The code is as follows:

char *insert_sql = "insert into emp values(:empno, :ename, :job, :mgr, :hiredate, :sal, :comm, :deptno)";
OCIBind *bind[8];
struct tm *p_tm;
time_t tm = time(NULL);
p_tm = localtime(&tm);
OCIDate o_hire;
OCIDateTime *p_hire;
OCIDescriptor *p_dt = NULL;
OCIStmtPrepare(stmthp, errhp, (text *)insert_sql, strlen(insert_sql), OCI_NTV_SYNTAX, OCI_DEFAULT);
OCIDescriptorAlloc(envhp, &p_dt, OCI_DTYPE_TIMESTAMP, 0, NULL);
OCIDateTimeConstruct(envhp, errhp, p_dt, p_tm->tm_year + 1900, p_tm->tm_mon + 1, p_tm->tm_mday, p_tm->tm_hour, p_tm->tm_min, p_tm->tm_sec, 0, NULL, 0);
OCIDateTimeToOracle(envhp, p_dt, &o_hire);
// 绑定 empno 参数
OCIBindByName(stmthp, &bind[0], errhp, (text *)":empno", -1, &empno, sizeof(empno), SQLT_INT, NULL, NULL, NULL, 0, NULL, OCI_DEFAULT);
// 绑定 ename 参数
OCIBindByName(stmthp, &bind[1],

The above is the detailed content of Does Oracle have an API?. For more information, please follow other related articles on the PHP Chinese website!