go_ora

package module
v1.2.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: May 29, 2021 License: MIT Imports: 25 Imported by: 43

README

go-ora

what's new:

note:
to use version 2 you will need to import github.com/sijms/go-ora/v2
version 2.1.19
  • support more charsets (0x33D, 0x33E, 0x33F, 0x340, 0x352, 0x353, 0x354)
version 2.0-beta
  • update client version to 317
  • update ttc version to: 9
  • use 4 byte packet length instead of 2 bytes
  • use advanced negotiation
  • use big clear chunks
  • use more verifier type in authentication object

Pure go oracle client

important notes:

  • the client is tested against oracle 10G, 11G and 12G and working properly
  • supported parameter types is integer, double, string, time.Time and []byte
  • BLOB is now supported in SELECT and DML statement
  • named parameter not supported to define parameter just put ':' + parameter_name in sql statement
  • integration with sql/database is done using simple form

Usage:

there are 2 way to use the client

A. Using sql/database interface
1- importing:
import (
  "database/sql"
  _ "github.com/sijms/go-ora/v2"
)
2- create the connection
conn, err := sql.Open("oracle", "oracle://user:pass@server/service_name")
// check for error
defer conn.Close()
3- create statment
stmt, err := conn.Prepare("SELECT col_1, col_2, col_3 FROM table WHERE col_1 = :1 or col_2 = :2")
// check for error
defer stmt.CLose()
4- query
// suppose we have 2 params one time.Time and other is double
rows, err := stmt.Query(time.Date(2020, 9, 1, 0, 0, 0, 0, time.UTC), 9.2)
// check for error
defer rows.Close()
5- extract data using next
 for rows.Next() {
    // define vars
    err = rows.Scan(/*vars here */)
    // check for error
 }
6- use exec instead of query for update and insert stmt
// i make change in parameter no 4 to explain that you can use string in parameter name instead of numbers
stmt, err := conn.Prepare("UPDATE table SET col_1=:1, col_2=:2 WHERE col_3 = :3 or col_4 = :col_4_par")
// check for error
defer stmt.Close()
result, err := stmt.Exec(/*pars value*/)
// check for error
fmt.Println(result.RowsAffected())
7- using transaction:
// after step 2 "Create Connection"
tx, err := conn.Begin()
// check for error
stmt, err := tx.Prepare("sql text")
// check for error
// continue as above
tx.Commit()
// or
tx.Rollback()
// note: any stmt created from conn will not be committed or rolled back
B. direct use of the package

the benefit here is that you can use pl/sql and output parameters

1- import go_ora "go-ora"
2- create connection
conn, err := go_ora.NewConnection("oracle://user:pass@server/service_name")
// check for error
err = conn.Open()
// check for error
defer conn.Close()
2- create stmt
stmt := go_ora.NewStmt("sql or pl/sql text", conn)
defer stmt.Close()
3- add parameters
stmt.AddParam("name", value, size, go_ora.Input /* or go_ora.Output*/)
// note that size is need when you define string output parameters
4- exec or query as above and pass nil for parameters
5- after that you can read the output parameters using Pars variable of stmt structure

Server's URL options

The complete syntax of connection url is:

oracle://user:pass@server/service_name[?OPTION1=VALUE1[&OPTIONn=VALUEn]...]

Check possible options in connection_string.go

TRACE FILE

This option enables logging driver activity and packet content into a file.

oracle://user:pass@server/service_name?TRACE FILE=trace.log

The log file is created into the current directory.

This produce this kind of log:

2020-11-22T07:51:42.8137: Open :(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.10)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)(CID=(PROGRAM=C:\Users\Me\bin\hello_ora.exe)(HOST=workstation)(USER=Me))))
2020-11-22T07:51:42.8147: Connect
2020-11-22T07:51:42.8256: 
Write packet:
00000000  00 3a 00 00 01 00 00 00  01 38 01 2c 0c 01 ff ff  |.:.......8.,....|
00000010  ff ff 4f 98 00 00 00 01  00 ea 00 3a 00 00 00 00  |..O........:....|
00000020  04 04 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00000030  00 00 00 00 00 00 00 00  00 00                    |..........|

...

2020-11-22T07:51:42.8705: Query:
SELECT * FROM v$version
2020-11-22T07:51:42.8705: 
Write packet:
00000000  00 55 00 00 06 00 00 00  00 00 03 5e 00 02 81 21  |.U.........^...!|
00000010  00 01 01 17 01 01 0d 00  00 00 01 19 01 01 00 00  |................|
00000020  00 00 00 00 00 00 00 00  00 01 00 00 00 00 00 53  |...............S|
00000030  45 4c 45 43 54 20 2a 20  46 52 4f 4d 20 76 24 76  |ELECT * FROM v$v|
00000040  65 72 73 69 6f 6e 01 01  00 00 00 00 00 00 01 01  |ersion..........|
00000050  00 00 00 00 00                                    |.....|
2020-11-22T07:51:42.9094: 
Read packet:
00000000  01 a7 00 00 06 00 00 00  00 00 10 17 3f d5 ec 21  |............?..!|
00000010  d5 37 e0 67 cc 0f eb 03  cc c5 d1 d8 78 78 0b 15  |.7.g........xx..|
00000020  0c 21 20 01 50 01 01 51  01 80 00 00 01 50 00 00  |.! .P..Q.....P..|
00000030  00 00 02 03 69 01 01 50  01 06 01 06 06 42 41 4e  |....i..P.....BAN|
00000040  4e 45 52 00 00 00 00 01  07 07 78 78 0b 16 07 34  |NER.......xx...4|
00000050  2b 00 02 1f e8 01 0a 01  0a 00 06 22 01 01 00 01  |+.........."....|
00000060  19 00 00 00 07 49 4f 72  61 63 6c 65 20 44 61 74  |.....IOracle Dat|
00000070  61 62 61 73 65 20 31 31  67 20 45 78 70 72 65 73  |abase 11g Expres|
00000080  73 20 45 64 69 74 69 6f  6e 20 52 65 6c 65 61 73  |s Edition Releas|
00000090  65 20 31 31 2e 32 2e 30  2e 32 2e 30 20 2d 20 36  |e 11.2.0.2.0 - 6|
000000a0  34 62 69 74 20 50 72 6f  64 75 63 74 69 6f 6e 07  |4bit Production.|
000000b0  26 50 4c 2f 53 51 4c 20  52 65 6c 65 61 73 65 20  |&PL/SQL Release |
000000c0  31 31 2e 32 2e 30 2e 32  2e 30 20 2d 20 50 72 6f  |11.2.0.2.0 - Pro|
000000d0  64 75 63 74 69 6f 6e 15  01 01 01 07 1a 43 4f 52  |duction......COR|
000000e0  45 09 31 31 2e 32 2e 30  2e 32 2e 30 09 50 72 6f  |E.11.2.0.2.0.Pro|
000000f0  64 75 63 74 69 6f 6e 15  01 01 01 07 2e 54 4e 53  |duction......TNS|
00000100  20 66 6f 72 20 4c 69 6e  75 78 3a 20 56 65 72 73  | for Linux: Vers|
00000110  69 6f 6e 20 31 31 2e 32  2e 30 2e 32 2e 30 20 2d  |ion 11.2.0.2.0 -|
00000120  20 50 72 6f 64 75 63 74  69 6f 6e 15 01 01 01 07  | Production.....|
00000130  26 4e 4c 53 52 54 4c 20  56 65 72 73 69 6f 6e 20  |&NLSRTL Version |
00000140  31 31 2e 32 2e 30 2e 32  2e 30 20 2d 20 50 72 6f  |11.2.0.2.0 - Pro|
00000150  64 75 63 74 69 6f 6e 08  01 06 03 14 97 b7 00 01  |duction.........|
00000160  01 01 02 00 00 00 00 00  04 01 05 01 07 01 05 02  |................|
00000170  05 7b 00 00 01 01 00 03  00 01 20 00 00 00 00 00  |.{........ .....|
00000180  00 00 00 00 00 00 00 01  01 00 00 00 00 19 4f 52  |..............OR|
00000190  41 2d 30 31 34 30 33 3a  20 6e 6f 20 64 61 74 61  |A-01403: no data|
000001a0  20 66 6f 75 6e 64 0a                              | found.|
2020-11-22T07:51:42.9104: Summary: RetCode:1403, Error Message:"ORA-01403: no data found\n"
2020-11-22T07:51:42.9104: Row 0
2020-11-22T07:51:42.9104:   BANNER              : Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
2020-11-22T07:51:42.9104: Row 1
2020-11-22T07:51:42.9104:   BANNER              : PL/SQL Release 11.2.0.2.0 - Production
2020-11-22T07:51:42.9104: Row 2
2020-11-22T07:51:42.9104:   BANNER              : CORE	11.2.0.2.0	Production
2020-11-22T07:51:42.9104: Row 3
2020-11-22T07:51:42.9104:   BANNER              : TNS for Linux: Version 11.2.0.2.0 - Production
2020-11-22T07:51:42.9104: Row 4
2020-11-22T07:51:42.9104:   BANNER              : NLSRTL Version 11.2.0.2.0 - Production
2020-11-22T07:51:42.9114:
RefCursor

to use RefCursor follow these steps:

  • create the connection object and open
  • create NewStmt from connection
  • pass RefCursorParam
  • cast parameter to go_ora.RefCursor
  • call cursor.Query()
  • reterive records use for loop
code:
conn, err := go_ora.NewConnection(url)
// check error

err = conn.Open()
// check error

defer conn.Close()

cmdText := `BEGIN    
    proc_1(:1); 
end;`
stmt := go_ora.NewStmt(cmdText, conn)
stmt.AddRefCursorParam("1")
defer stmt.Close()

_, err = stmt.Exec(nil)
//check errors

if cursor, ok := stmt.Pars[0].Value.(go_ora.RefCursor); ok {
    defer cursor.Close()
    rows, err := cursor.Query()
    // check for error
    
    var (
        var_1 int64
        var_2 string
    )
    values := make([]driver.Value, 2)
    for {
        err = rows.Next(values)
        // check for error and if == io.EOF break
        
        if var_1, ok = values[0].(int64); !ok {
            // error
        }
        if var_2, ok = values[1].(string); !ok {
            // error
        }
        fmt.Println(var_1, var_2)
    }
}

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func CalculateKeysHash 

func CalculateKeysHash(verifierType int, key1 []byte, key2 []byte) ([]byte, error)

func EncryptPassword 

func EncryptPassword(password string, key []byte) (string, error)

func EncryptSessionKey 

func EncryptSessionKey(padding bool, encKey []byte, sessionKey []byte) (string, error)

func HexStringToBytes 

func HexStringToBytes(input string) ([]byte, error)

func PKCS5Padding 

func PKCS5Padding(cipherText []byte, blockSize int) []byte

func TZBytes 

func TZBytes() []byte

Types

type AuthObject 

type AuthObject struct {
	EServerSessKey string
	EClientSessKey string
	EPassword      string
	ServerSessKey  []byte
	ClientSessKey  []byte
	KeyHash        []byte
	Salt           string
	VerifierType   int
	// contains filtered or unexported fields
}

E infront of the variable means encrypted

func NewAuthObject 

func NewAuthObject(username string, password string, tcpNego *TCPNego, session *network.Session) (*AuthObject, error)

func (*AuthObject) VerifyResponse 

func (obj *AuthObject) VerifyResponse(response string) bool

func (*AuthObject) Write 

func (obj *AuthObject) Write(connOption *network.ConnectionOption, mode LogonMode, session *network.Session) error

type Connection 

type Connection struct {
	State     ConnectionState
	LogonMode LogonMode

	SessionProperties map[string]string

	NLSData NLSData
	// contains filtered or unexported fields
}

func NewConnection 

func NewConnection(databaseUrl string) (*Connection, error)

func (*Connection) Begin 

func (conn *Connection) Begin() (driver.Tx, error)

func (*Connection) Close 

func (conn *Connection) Close() (err error)

func (*Connection) GetNLS 

func (conn *Connection) GetNLS() (*NLSData, error)

func (*Connection) Open 

func (conn *Connection) Open() error

func (*Connection) Ping 

func (conn *Connection) Ping(_ context.Context) error

func (*Connection) Prepare 

func (conn *Connection) Prepare(query string) (driver.Stmt, error)

func (*Connection) SetStringConveter added in v1.2.0 

func (conn *Connection) SetStringConveter(conveter converters.IStringConverter)

type ConnectionState 

type ConnectionState int
const (
	Closed ConnectionState = 0
	Opened ConnectionState = 1
)

type ConnectionString 

type ConnectionString struct {
	DataSource            string
	Host                  string
	Port                  int
	SID                   string
	ServiceName           string
	InstanceName          string
	DBAPrivilege          DBAPrivilege
	EnList                EnList
	ConnectionLifeTime    int
	IncrPoolSize          int
	DecrPoolSize          int
	MaxPoolSize           int
	MinPoolSize           int
	Password              string
	PasswordSecurityInfo  bool
	Pooling               bool
	ConnectionTimeOut     int
	UserID                string
	PromotableTransaction PromotableTransaction
	ProxyUserID           string
	ProxyPassword         string
	ValidateConnection    bool
	StmtCacheSize         int
	StmtCachePurge        bool
	HaEvent               bool
	LoadBalance           bool
	MetadataBooling       bool
	ContextConnection     bool
	SelfTuning            bool
	ApplicationEdition    string
	PoolReglator          int
	ConnectionPoolTimeout int
	PasswordlessConString string
	Trace                 string // Trace file
	PrefetchRows          int
}

func NewConnectionString 

func NewConnectionString() *ConnectionString

type DBAPrivilege 

type DBAPrivilege int
const (
	NONE    DBAPrivilege = 0
	SYSDBA  DBAPrivilege = 0x20
	SYSOPER DBAPrivilege = 0x40
)

func DBAPrivilegeFromString 

func DBAPrivilegeFromString(s string) DBAPrivilege

type DBVersion 

type DBVersion struct {
	Info            string
	Text            string
	Number          uint16
	MajorVersion    int
	MinorVersion    int
	PatchsetVersion int
	// contains filtered or unexported fields
}

func GetDBVersion 

func GetDBVersion(session *network.Session) (*DBVersion, error)

type DataSet 

type DataSet struct {
	ColumnCount     int
	RowCount        int
	UACBufferLength int
	MaxRowSize      int
	Cols            []ParameterInfo
	Rows            []Row
	// contains filtered or unexported fields
}

func (*DataSet) Close 

func (dataSet *DataSet) Close() error

func (DataSet) ColumnTypeDatabaseTypeName 

func (dataSet DataSet) ColumnTypeDatabaseTypeName(index int) string

func (DataSet) ColumnTypeLength 

func (dataSet DataSet) ColumnTypeLength(index int) (length int64, ok bool)

func (DataSet) ColumnTypeNullable 

func (dataSet DataSet) ColumnTypeNullable(index int) (nullable, ok bool)

func (*DataSet) Columns 

func (dataSet *DataSet) Columns() []string

func (*DataSet) Next 

func (dataSet *DataSet) Next(dest []driver.Value) error

func (DataSet) Trace 

func (dataSet DataSet) Trace(t trace.Tracer)

type DataTypeNego 

type DataTypeNego struct {
	MessageCode        uint8
	Server             *TCPNego
	TypeAndRep         []int16
	RuntimeTypeAndRep  []int16
	DataTypeRepFor1100 int16
	CompileTimeCaps    []byte
	RuntimeCap         []byte
	DBTimeZone         []byte
}

type EnList 

type EnList int
const (
	FALSE   EnList = 0
	TRUE    EnList = 1
	DYNAMIC EnList = 2
)

func EnListFromString 

func EnListFromString(s string) EnList

type Lob 

type Lob struct {
	// contains filtered or unexported fields
}

type LogonMode 

type LogonMode int
const (
	NoNewPass LogonMode = 0x1
	//WithNewPass LogonMode = 0x2
	SysDba      LogonMode = 0x20 // no verify response from server
	SysOper     LogonMode = 0x40 // no verify response from server
	UserAndPass LogonMode = 0x100
)

type NLSData 

type NLSData struct {
	Calender        string
	Comp            string
	LengthSemantics string
	NCharConvExcep  string
	DateLang        string
	Sort            string
	Currency        string
	DateFormat      string
	IsoCurrency     string
	NumericChars    string
	DualCurrency    string
	Timestamp       string
	TimestampTZ     string
}

type OracleType 

type OracleType int
const (
	NCHAR            OracleType = 1
	NUMBER           OracleType = 2
	SB1              OracleType = 3
	SB2              OracleType = 3
	SB4              OracleType = 3
	FLOAT            OracleType = 4
	NullStr          OracleType = 5
	VarNum           OracleType = 6
	LONG             OracleType = 8
	VARCHAR          OracleType = 9
	ROWID            OracleType = 11
	DATE             OracleType = 12
	VarRaw           OracleType = 15
	BFloat           OracleType = 21
	BDouble          OracleType = 22
	RAW              OracleType = 23
	LongRaw          OracleType = 24
	UINT             OracleType = 68
	LongVarChar      OracleType = 94
	LongVarRaw       OracleType = 95
	CHAR             OracleType = 96
	CHARZ            OracleType = 97
	IBFloat          OracleType = 100
	IBDouble         OracleType = 101
	REFCURSOR        OracleType = 102
	NOT              OracleType = 108
	XMLType          OracleType = 108
	OCIRef           OracleType = 110
	OCIClobLocator   OracleType = 112
	OCIBlobLocator   OracleType = 113
	OCIFileLocator   OracleType = 114
	ResultSet        OracleType = 116
	OCIString        OracleType = 155
	OCIDate          OracleType = 156
	TimeStampDTY     OracleType = 180
	TimeStampTZ_DTY  OracleType = 181
	IntervalYM_DTY   OracleType = 182
	IntervalDS_DTY   OracleType = 183
	TimeTZ           OracleType = 186
	TimeStamp        OracleType = 187
	TimeStampTZ      OracleType = 188
	IntervalYM       OracleType = 189
	IntervalDS       OracleType = 190
	UROWID           OracleType = 208
	TimeStampLTZ_DTY OracleType = 231
	TimeStampeLTZ    OracleType = 232
)

func (OracleType) String 

func (i OracleType) String() string

type ParameterDirection 

type ParameterDirection int
const (
	Input  ParameterDirection = 1
	Output ParameterDirection = 2
	InOut  ParameterDirection = 3
	RetVal ParameterDirection = 9
)

type ParameterInfo 

type ParameterInfo struct {
	Name                 string
	Direction            ParameterDirection
	IsNull               bool
	AllowNull            bool
	ColAlias             string
	DataType             OracleType
	IsXmlType            bool
	Flag                 uint8
	Precision            uint8
	Scale                uint8
	MaxLen               int
	MaxCharLen           int
	MaxNoOfArrayElements int
	ContFlag             int
	ToID                 []byte
	Version              int
	CharsetID            int
	CharsetForm          int
	BValue               []byte
	Value                driver.Value
	// contains filtered or unexported fields
}

type ParameterType 

type ParameterType int
const (
	Number ParameterType = 1
	String ParameterType = 2
)

type PromotableTransaction 

type PromotableTransaction int
const (
	Promotable PromotableTransaction = 1
	Local      PromotableTransaction = 0
)

type QueryResult 

type QueryResult struct {
	// contains filtered or unexported fields
}

func (*QueryResult) LastInsertId 

func (rs *QueryResult) LastInsertId() (int64, error)

func (*QueryResult) RowsAffected 

func (rs *QueryResult) RowsAffected() (int64, error)

type RefCursor 

type RefCursor struct {
	MaxRowSize int
	// contains filtered or unexported fields
}

func (*RefCursor) Close 

func (cursor *RefCursor) Close() error

func (*RefCursor) Query 

func (cursor *RefCursor) Query() (*DataSet, error)

type Row 

type Row []driver.Value

type Stmt 

type Stmt struct {
	// contains filtered or unexported fields
}

func NewStmt 

func NewStmt(text string, conn *Connection) *Stmt

func (*Stmt) AddParam 

func (stmt *Stmt) AddParam(name string, val driver.Value, size int, direction ParameterDirection)

func (*Stmt) AddRefCursorParam 

func (stmt *Stmt) AddRefCursorParam(_ string)

func (*Stmt) Close 

func (stmt *Stmt) Close() error

func (*Stmt) Exec 

func (stmt *Stmt) Exec(args []driver.Value) (driver.Result, error)

func (*Stmt) NewParam 

func (stmt *Stmt) NewParam(name string, val driver.Value, size int, direction ParameterDirection) *ParameterInfo

func (*Stmt) NumInput 

func (stmt *Stmt) NumInput() int

func (*Stmt) Query 

func (stmt *Stmt) Query(args []driver.Value) (driver.Rows, error)

func (stmt *Stmt) reExec() (driver.Rows, error) {

}

type StmtInterface 

type StmtInterface interface {
	// contains filtered or unexported methods
}

type StmtType 

type StmtType int
const (
	SELECT StmtType = 1
	DML    StmtType = 2
	PLSQL  StmtType = 3
	OTHERS StmtType = 4
)

type TCPNego 

type TCPNego struct {
	MessageCode           uint8
	ProtocolServerVersion uint8
	ProtocolServerString  string
	OracleVersion         int
	ServerCharset         int
	ServerFlags           uint8
	CharsetElem           int
	ServernCharset        int
	ServerCompileTimeCaps []byte
	ServerRuntimeCaps     []byte
}

func NewTCPNego 

func NewTCPNego(session *network.Session) (*TCPNego, error)

type Transaction 

type Transaction struct {
	// contains filtered or unexported fields
}

func (*Transaction) Commit 

func (tx *Transaction) Commit() error

func (*Transaction) Rollback 

func (tx *Transaction) Rollback() error

Source Files

View all Source files

Directories

Path Synopsis
generatefloat command
examples
hello_ora command
hello_sql command
query command

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.