Edited at

MacでgolangからOracleDBへ接続

More than 1 year has passed since last update.


はじめに

macからOracleDBに接続する機会があったのでメモ的に書き残します。

mattn/go-oci8パッケージを使用しています。


前提条件


環境


go

% go version

go version go1.9.2 darwin/amd64


sqlplus

% sqlplus -v

SQL*Plus: Release 12.2.0.1.0 Production



Instant Client

以下のサイトからダウンロード

Instant Client Downloads for macOS (Intel x86)


  • instantclient-sdk-macos.x64-12.2.0.1.0.zip

※自分のSQL*Plusクライアントと同じバージョンのものをダウンロードすること

ダウンロードしたファイルを任意の場所へ解凍

私はSQL*Plusクライアントをインストールする際に展開していた/opt/oracle


instantclient

% sudo unzip instantclient-sdk-macos.x64-12.2.0.1.0.zip -d /opt/oracle

Password:
Archive: instantclient-sdk-macos.x64-12.2.0.1.0.zip
creating: /opt/oracle/instantclient_12_2/sdk/
creating: /opt/oracle/instantclient_12_2/sdk/admin/
inflating: /opt/oracle/instantclient_12_2/sdk/admin/oraaccess.xsd
creating: /opt/oracle/instantclient_12_2/sdk/demo/
inflating: /opt/oracle/instantclient_12_2/sdk/demo/cdemo81.c
inflating: /opt/oracle/instantclient_12_2/sdk/demo/demo.mk
inflating: /opt/oracle/instantclient_12_2/sdk/demo/occidemo.sql
inflating: /opt/oracle/instantclient_12_2/sdk/demo/occidemod.sql
inflating: /opt/oracle/instantclient_12_2/sdk/demo/occidml.cpp
inflating: /opt/oracle/instantclient_12_2/sdk/demo/occiobj.cpp
inflating: /opt/oracle/instantclient_12_2/sdk/demo/occiobj.typ
inflating: /opt/oracle/instantclient_12_2/sdk/demo/oraaccess.xml
inflating: /opt/oracle/instantclient_12_2/sdk/demo/setuporamysql.sh
creating: /opt/oracle/instantclient_12_2/sdk/include/
inflating: /opt/oracle/instantclient_12_2/sdk/include/ldap.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/nzerror.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/nzt.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/occi.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/occiAQ.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/occiCommon.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/occiControl.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/occiData.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/occiObjects.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/oci.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/oci1.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/oci8dp.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/ociap.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/ociapr.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/ocidef.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/ocidem.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/ocidfn.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/ociextp.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/ocikpr.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/ocixmldb.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/ocixstream.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/odci.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/oratypes.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/ori.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/orid.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/orl.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/oro.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/ort.h
inflating: /opt/oracle/instantclient_12_2/sdk/include/xa.h
inflating: /opt/oracle/instantclient_12_2/sdk/ott
extracting: /opt/oracle/instantclient_12_2/sdk/ottclasses.zip
inflating: /opt/oracle/instantclient_12_2/sdk/SDK_README


oci8.pcファイルの作成

oci8.pcファイルがすでに存在していないかの確認

% pkg-config --exists oci8 || echo no

no

※pkg-configがインストールされていない場合はhomebrewを使用してインストール

% brew install pkg-config

oci8.pcの作成

/usr/lib/pkgconfigにはrootlessのため書き込みできない

% sudo mkdir -p /opt/oracle/instantclient_12_2/pkgconfig

% sudo vi /opt/oracle/instantclient_12_2/pkgconfig/oci8.pc

以下を参考に新規作成

prefix=/opt/oracle/instantclient_12_2/

libdir=${prefix}
includedir=${prefix}/sdk/include/

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


環境変数の作成

.bashrc.zshrcなどに以下の環境変数を設定する記述を追記

export ORACLE_HOME=/opt/oracle/instant_12_2

export PKG_CONFIG_PATH=$ORACLE_HOME/pkgconfig/
export LD_LIBRARY_PATH=$ORACLE_HOME


go-oci8パッケージの取得

% go get github.com/mattn/go-oci8


golangから接続できるかの確認

package main

import (
"database/sql"
"fmt"

_ "github.com/mattn/go-oci8"
)

func getDSN() string {
return "system/oracle@127.0.0.1:1521" // user/name@host:port/sid
}

func testSelect(db *sql.DB) error {
rows, err := db.Query("SELECT * FROM table01")
if err != nil {
return err
}

defer rows.Close()

for rows.Next() {
var f1 int
var f2 string
rows.Scan(&f1, &f2)
println(f1, f2)
}

return nil
}

func main() {
db, err := sql.Open("oci8", getDSN())
if err != nil {
fmt.Println(err)
return
}

defer db.Close()

if err = testSelect(db); err != nil {
fmt.Println(err)
return
}
}


出力結果

% go run main.go

0 teste
...

データベースからデータの取得ができました

go-oci8のドキュメントを読んで開発しましょう


参考

https://qiita.com/naoki_koreeda/items/8cd2ce4e70a82c48ec07

https://qiita.com/qt-luigi/items/fbbe6792a77b493a58f9

https://github.com/mattn/go-oci8