1.oracle docker 环境构建
镜像pull:
docker pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
创建容器:
docker run -d -p 1521:1521 --name oracle11g registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
进入容器:
docker exec -it 4b71c92ecd92 /bin/bash
切换到root用户模式下
su root
输入密码helowin
编辑profile文件配置ORACLE环境变量
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2
export ORACLE_SID=helowin
export PATH=$ORACLE_HOME/bin:$PATH
建立软连接:
ln -s $ORACLE_HOME/bin/sqlplus /usr/bin
切换到oracle 用户
su oracle
登录sqlplus并修改sys、system用户密码
sqlplus /nolog
conn /as sysdba
alter user system identified by system;
alter user sys identified by sys;
create user test identified by test;
grant connect,resource,dba to test;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
2.lsnrctl status:
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-MAR-2020 10:26:02
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 22-MAR-2020 10:14:26
Uptime 0 days 0 hr. 11 min. 37 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
Listener Log File /home/oracle/app/oracle/diag/tnslsnr/42b46f712d0e/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=42b46f712d0e)(PORT=1521)))
Services Summary...
Service "helowin" has 1 instance(s).
Instance "helowin", status READY, has 1 handler(s) for this service...
Service "helowinXDB" has 1 instance(s).
Instance "helowin", status READY, has 1 handler(s) for this service...
The command completed successfully
3.oracle的sid是helowin
4.oracle创建表数据
create table user (
id number(6) primary key, ---主键
name varchar(50) not null, ---姓名 不为null
sex varchar2(6) default '男' check ( sex in ('男','女')) ---性别 默认'男'
);
create table userinfo (id number(6) primary key, name varchar(50));
insert into userinfo(id,name) values(1,'zhangsan');
5.紧接在docker环境中测试golang连接oracle数据库操作,也可以不再docker中,宿主机中要配置golang,instantclient的环境
环境要求:
1.yum -y install git
2.golang conn oracle
tar命令安装
yum install -y tar
go环境变量
export GOROOT=/usr/local/go
export GOPATH=/workspace_go
export PATH=$PATH:$GOROOT/bin:$GOPATH
source /etc/profile
3.gcc更新了下(可省略)
6.连接oracle需要的环境安装步骤
Oracle 11g 64bit(这里使用的是最新的oracle的instantclient库文件,在使用11_2库文件时出现错误/usr/bin/ld: cannot find -lclntsh)
instantclient下载:
http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
为了防止出现这种环境错误,所以使用最新包:
OCI -> instantclient-basic-linux.x64-19.6.0.0.0dbru.zip
OCI SDK -> instantclient-sdk-linux.x64-19.6.0.0.0dbru.zip
环境变量配置:
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/instantclient_19_6
所有环境的配置,都需要验证你的环境变量设置,否则会出很多问题:
echo $LD_LIBRARY_PATH
***安装pkg-config
(-)编辑oci8.pc,配置环境变量PKG_CONFIG_PATH:
创建oci8.pc文件,放在项目地址如/home/go/src/test/oracle/oci8.pc, 编辑oci8.pc内容如下,确保oracle库文件及头文件地址正确:
prefix=/usr
includedir=${prefix}/instantclient_19_6/sdk/include
libdir=${prefix}/instantclient_19_6
Name: oci8
Description: Oracle Instant Client
Version: 19.6
Cflags: -I${includedir}
Libs: -L${libdir} -lclntsh
(二)在/usr/lib 目录下创建pkgconfig 目录
在/usr/lib/pkgconfig 目录下创建文件oci8.pc,内容如下 11g:
prefix= /usr/lib/instantclient_19_6
libdir=${prefix}
includedir=${prefix}/sdk/include/
Name: oci8
Description: Oracle database engine
Version: 19.6
Libs: -L${libdir} -lclntsh
Libs.private:
Cflags: -I${includedir}
#环境变量一定要设置
oci8.pc文件所在路径
export PKG_CONFIG_PATH=/usr/lib/pkgconfig
因为使用到go-xorm,需要xorm.io/core库:
go get xorm.io/core
测试代码:
package main_test
import (
"database/sql"
"testing"
"github.com/go-xorm/xorm"
_ "github.com/mattn/go-oci8"
)
var driverName = "oci8" //Oracle 驱动
var dataSourceName = "test/test@127.0.0.1:1521/helowin" //数据库账号:test,密码:test,实例服务(SID):helowin---这里一定要写你oracle的network/admin中tns配置的参数,否则连接会报错误
var engine *xorm.Engine
func TestXormOracle(t *testing.T) {
var err error
engine, err = xorm.NewEngine(driverName, dataSourceName)
if err != nil {
t.Error(err)
}
tabs, err := engine.DBMetas()
if err != nil {
t.Error(err)
}
println(len(tabs))
}
func TestMattnOracle(t *testing.T) {
var db *sql.DB
var err error
if db, err = sql.Open(driverName, dataSourceName); err != nil {
t.Error(err)
return
}
var rows *sql.Rows
if rows, err = db.Query("select * from userinfo"); err != nil {
t.Error(err)
return
}
defer rows.Close()
for rows.Next() {
var id int
var name string
rows.Scan(&id, &name)
println(id, name) // 3.14 foo
}
}