rana/ora 连接Oracle数据库

1 环境配置

1.1 编译环境要求

使用下述命令查询系统是否具备依赖包:

rpm -qa | grep oracle-instantclient

若具备以下依赖则满足要求:

oracle-instantclient12.1-basic-11.2.0.4.0-1.x86_64.rpm 
oracle-instantclient12.1-devel-11.2.0.4.0-1.x86_64.rpm

若不存在依赖, 点击传送门下载. (注: 文中的Oracle版本号为12.1, 若有更新则数字相应变动即可.) 下载传送门, 安装依赖

rpm -ivh oracle-instantclient12.1-basic-11.2.0.4.0-1.x86_64.rpm oracle-instantclient12.1-devel-11.2.0.4.0-1.x86_64.rpm

1.2 拷贝oci8.pc

在$GOPATH/pkg目录下建立文件oci8.pc (注: 此处参考Go的部署)

vim $GOPATH/pkg/oci8.pc

文件内容为

prefix=/usr
includedir=${prefix}/include/oracle/12.1/client64
libdir=${prefix}/lib/oracle/12.1/client64/lib

Name: oci8
Description: Oracle Instant Client
Version: 12.1
Cflags: -I${includedir}
Libs: -L${libdir} -lclntsh

1.3 添加配置文件

vim /etc/ld.so.conf.d/oracle-client-12.1.conf
ldconfig

oracle-client-12.1.conf的内容为:

/usr/lib/oracle/12.1/client64/lib

1.4 配置环境变量

export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib
export DYLD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/
export ORACLE_HOME=/usr/lib/oracle/12.1/client64/
export TNS_ADMIN=/usr/lib/oracle/12.1/client64

2 代码应用

2.1 基于service name连接

func executeSQL() {
    var (
        gmt_create  sql.NullString
        name        sql.NullString
        raw_value   sql.NullString
        time_waited sql.NullString
    )
    db, err := sql.Open("ora", "jinge/jinge@10.10.90.11:1521/rac1")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    // Set timeout (Go 1.8)
    ctx, _ := context.WithTimeout(context.Background(), 50*time.Second)
    // Set prefetch count (Go 1.8)
    ctx = ora.WithStmtCfg(ctx, ora.Cfg().StmtCfg.SetPrefetchRowCount(1))
    rows, err := db.QueryContext(ctx, s)
    fmt.Println(rows)
    defer rows.Close()
    for rows.Next() {
        err := rows.Scan(&gmt_create, &name, &raw_value, &time_waited)
        if err != nil {
            log.Fatal(err)
        }
        log.Println(gmt_create.String, name.String, raw_value.String, time_waited.String)
    }
    err = rows.Err()
    if err != nil {
        log.Fatal(err)
    }
}