MacでgolangからOracleDBへ接続

はじめに

macからOracleDBに接続する機会があったのでメモ的に書き残します。
mattn/go-oci8パッケージを使用しています。

前提条件

• Goがインストールされていること
• SQL*Plusクライアントがインストールされていること

環境

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