- Use version 2 you will need to import github.com/sijms/go-ora/v2
- V2 is more preferred for oracle servers 10.2 and above
- I always update the driver fixing issues and add new features so
always ensure that you get latest release
- See examples for more help
- you can query to struct that contain basic types (int, float, string, datetime) or any types that implement sql.Scanner interface
- see query to struct example for more information
if you need to connect with proxy user pass following connection string
oracle://proxy_user:proxy_password@host:port/service?proxy client name=schema_owner
- Add new function go_ora.BuildJDBC
// program will extract server, ports and protocol and build
// connection table
connStr := `(DESCRIPTION=
(ADDRESS_LIST=
(LOAD_BALANCE=OFF)
(FAILOVER=ON)
(address=(PROTOCOL=tcps)(host=localhost)(PORT=2484))
(address=(protocol=tcp)(host=localhost)(port=1521))
)
(CONNECT_DATA=
(SERVICE_NAME=service)
(SERVER=DEDICATED)
)
(SOURCE_ROUTE=yes)
)`
// use urlOption to set other options like:
// TRACE FILE = for debug
// note SSL automatically set from connStr (address=...
// SSL Verify = need to cancel certifiate verification
// wallet path
databaseUrl := go_ora.BuildJDBC(user, password, connStr, urlOptions)
conn, err := sql.Open("oracle", databaseUrl)
if err != nil {
fmt.Println(err)
return
}
err = conn.Ping()
if err != nil {
fmt.Println(err)
return
}
- connect as sys and create directory object that refer to physical directory
grant read,write on directory 'dirName' to user
- put text file in the directory with name = fileName
// create and open connection before use BFile
conn, err := go_ora.NewConnection(connStr)
// check for error
err = conn.Open()
// check for error
defer conn.Close()
// Create BFile object
file, err := go_ora.BFile(conn, dirName, fileName)
// check for error
// before use BFile it must be opened
err = file.Open()
// check for error
defer file.Close()
// does the file exist
exists, err := file.Exists()
// check for error
if exists {
length, err := file.GetLength()
// check for error
// read all data
data, err := file.Read()
// read at position 2
data, err = file.ReadFromPos(2)
// read 5 bytes count start at position 2
data, err = file.ReadBytesFromPos(2, 5)
- you can pass BFile object as input parameter or receive it from query or output parameters for more detail see example bfile
you can use this option if server and client on same linux machine by specify the following url option
urlOptions := map[string]string{
// change the value according to your machine
"unix socket": "/usr/tmp/.oracle/sEXTPROC1"
}
you can pass input CLOB and BLOB with any data size up to data type limit
you can determine connection overall lifetime through url options
// set connection time for 3 second
urlOptions := map[string]string {
"CONNECTION TIMEOUT": "3"
}
databaseUrl := go_ora.BuildUrl(server, port, service, user, password, urlOptions)
see context example for more help about using context
- add support for oracle associative array as input and output parameter type
- add BulkInsert function which dramatically improve performance (> 10x) during insert
- add support for nullable type in DataSet.Scan function
- Bug fixes
- examples (bulk_insert and arrays) contain explanation of use of this 2 major features
// sqlText: sql text with parameters
// rowNum: number of rows to insert
// columns: each column contain array of driver.Value size of column should
// equal to rowNum
func (conn *Connection) BulkInsert(sqlText string, rowNum int, columns ...[]driver.Value) (*QueryResult, error)
now you can pass password hash of the user instead of real password
- windows registry
- create the hash by md4(unicode(password)) passing hash through url option as follow
urlOptions := map[string]string {
"OS HASH": "yourpasswordhash"
// or
"OS PassHash": "yourpasswordhash"
// or
"OS Password Hash": "yourpasswordhash"
}
you can use NTSAuthInterface
type YourCustomNTSManager struct {
NTSAuthDefault
}
func (nts *NTSAuthHash) ProcessChallenge(chaMsgData []byte, user, password string) ([]byte, error) {
// password = get (extract) password hash from Windows registry
return ntlmssp.ProcessChallengeWithHash(chaMsgData, user, password)
}
// now you can pass empty user and password to the driver
you can see windows_os_auth example for more detail
- NTS packets are supplied from the following github package: go-ntlmssp
- empty username or password will suppose OS Auth by default
AUTH TYPE: "OS"
optionalOS USER
optional if omit the client will use logon userOS PASS
is obligatory to make OS Auth using NTSDOMAIN
optional for windows domainAUTH SERV: "NTS"
optional as NTS is automatically added if the client running on Windows machineDBA PRIVILEGE: "SYSDBA"
optional if you need a SYSDBA access
urlOptions := map[string]string{
// automatically set if you pass an empty oracle user or password
// otherwise you need to set it
"AUTH TYPE": "OS",
// operating system user if empty the driver will use logon user name
"OS USER": user,
// operating system password needed for os logon
"OS PASS": password,
// Windows system domain name
"DOMAIN": domain,
// NTS is the required for Windows os authentication
// when you run the program from Windows machine it will be added automatically
// otherwise you need to specify it
"AUTH SERV": "NTS",
// uncomment this option for debugging
"TRACE FILE": "trace.log",
}
databaseUrl := go_ora.BuildUrl(server, port, service, "", "", urlOptions)
- you can make OS Auth on the same machine (Windows Server)
or different machine (Windows Server) and (Other Client) and in this situation you need to pass
AUTH SERV: "NTS"
as url parameter
- You can use custom NTS auth manager by implementing the following interface
type NTSAuthInterface interface {
NewNegotiateMessage(domain, machine string) ([]byte, error)
ProcessChallenge(chaMsgData []byte, user, password string) ([]byte, error)
}
- set newNTS auth manager before open the connection
go_ora.SetNTSAuth(newNTSManager)
- advantage of custom manager: you may not need to provide OS Password. for example using .NET or Windows API code as original driver
// CustomStream will take data from NegotiateStream and give it to the driver
// through NewNegotiateMessage
// Then take data form the driver (Challenge Message) to NegotiateStream
// And return back Authentication msg to the driver through ProcessChallenge
// as you see here CredentialCache.DefaultNetworkCredentials will take auth data
// (username and password) from logon user
new NegotiateStream(new YourCustomStream(), true).AuthenticateAsClient(CredentialCache.DefaultNetworkCredentials, "", ProtectionLevel.None, TokenImpersonationLevel.Identification);
now you can define url that contain ipv6
url := go_ora.BuildUrl("::1", 1521, "service", "user", "password", nil)
url = "oracle://user:password@[::1]:1521/service"
- support for nullable type in output parameters
- add more nullable type NullTimeStamp and NullNVarChar
- see example udt_pars for more help
- Add support for UDT as input parameter
- Add go_ora.Out struct with Size member to set output parameter size
- Add new types for output parameter which is
go_ora.Clob
andgo_ora.Blob
used for receiving Clob and Blob from output parameters see clob example for more details - Fix some issue related to reading output parameters
- Fix issue related to reading user defined type UDT
- Now Prefetch rows value is automatically calculated (when left with its default value = 25) according to column size
- Reading lob is retarded until all record has been read this fix error happen when you try to read lob with large PREFETCH_ROWS value
define multiple server in 2 way
- in url string options
// using url options
databaseURL := "oracle://user:pass@server1/service?server=server2&server=server3"
/* now the driver will try connection as follow
1- server1
2- server2
3- server3
*/
- using BuildUrl function
urlOptions := map[string] string {
"TRACE FILE": "trace.log",
"SERVER": "server2, server3",
"PREFETCH_ROWS": "500",
//"SSL": "enable",
//"SSL Verify": "false",
}
databaseURL := go_ora.BuildUrl(server1, 1521, "service", "user", "pass", urlOptions)
OracleError carry error message from the server
- this feature is now tested against these oracle versions 10.2, 12.2, 19.3.
- RegisterType function need extra parameter owner (oracle user who create the type).
to use make the following (oracle 12c)
- define custom type in the oracle
create or replace TYPE TEST_TYPE1 IS OBJECT
(
TEST_ID NUMBER(6, 0),
TEST_NAME VARCHAR2(10)
)
- define struct in go with tag
type test1 struct {
// note use int64 not int
// all tagged fields should be exported
// tag name:field_name --> case insensitive
Id int64 `oracle:"name:test_id"`
Name string `oracle:"name:test_name"`
}
- connect to database
databaseURL := go_ora.BuildUrl("localhost", 1521, "service", "user", "pass", nil)
conn, err := sql.Open("oracle", databaseURL)
// check for err
err = conn.Ping()
// check for err
defer func() {
err := conn.Close()
// check for err
}()
- register type
if drv, ok := conn.Driver().(*go_ora.OracleDriver); ok {
err = drv.Conn.RegisterType("owner", "TEST_TYPE1", test1{})
// check for err
}
- select and display data
rows, err := conn.Query("SELECT test_type1(10, 'test') from dual")
// check for err
var test test1
for rows.Next() {
err = rows.Scan(&test)
// check for err
fmt.Println(test)
}
- add function go_ora.BuildUrl to escape special characters
- add support for tcps. you can enable tcps through the following url options
- this link explain how to enable tcps in your server
wallet=wallet_dir // wallet should contain server and client certificates
SSL=true // true or enabled
SSL Verify=false // to bypass certificate verification
- now support auto-login oracle wallet (non-local)
- note: to use wallet you need to specify directory path for wallet the directory should contain cwallet.sso file "the file that will be used"
sqlQuery := "oracle://[email protected]:1522/service"
sqlQuery += "?TRACE FILE=trace.log"
sqlQuery += "&wallet=path_to_wallet_directory"
conn, err := sql.open("oracle", sqlQuery)
- now support data packet integrity check using MD5, SHA1, SHA256, SHA384, SHA512
- key is exchanged between server and client using Diffie Hellman method
- note: to enable data integrity check add the following line to sqlnet.ora of the server
# possible values ([accepted | rejected | requested | required])
SQLNET.CRYPTO_CHECKSUM_SERVER = required
# possible values ([MD5 | SHA1 | SHA256 | SHA384 | SHA512])
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = SHA512
- now support data packet encryption using AES.
- key is exchanged between server and client using Diffie Hellman method
- note: to enable AES encryption add the following line to sqlnet.ora of the server
# possible values ([accepted | rejected | requested | required])
SQLNET.ENCRYPTION_SERVER = required
# possible values for AES (AES256 | AES192 | AES128)
SQLNET.ENCRYPTION_TYPES_SERVER = AES256
- add new type go_ora.NVarChar now you can pass string parameter in 2 way:
_, err := conn.Exec(inputSql, "7586")
_, err := conn.Exec(inputSql, go_ora.NVarChar("7586"))
- support more charsets (0x33D, 0x33E, 0x33F, 0x340, 0x352, 0x353, 0x354)
- 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
import (
"database/sql"
_ "github.com/sijms/go-ora/v2"
)
conn, err := sql.Open("oracle", "oracle://user:pass@server/service_name")
// check for error
defer conn.Close()
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()
// 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()
for rows.Next() {
// define vars
err = rows.Scan(/*vars here */)
// check for error
}
// 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())
// 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
the benefit here is that you can use pl/sql and output parameters
conn, err := go_ora.NewConnection("oracle://user:pass@server/service_name")
// check for error
err = conn.Open()
// check for error
defer conn.Close()
stmt := go_ora.NewStmt("sql or pl/sql text", conn)
defer stmt.Close()
stmt.AddParam("name", value, size, go_ora.Input /* or go_ora.Output*/)
// note that size is need when you define string output parameters
The complete syntax of connection url is:
oracle://user:pass@server/service_name[?OPTION1=VALUE1[&OPTIONn=VALUEn]...]
Check possible options in connection_string.go
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:
Default value is 25 increase this value to higher level will significantly speed up the query
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
urlOptions := map[string] string {
"trace file": "trace.log" ,
}
databaseURL := go_ora.BuildUrl(server, port, service, user, password, urlOptions)
conn, err := sql.Open("oracle", databaseURL)
// check error
err = conn.Ping()
// check error
defer conn.Close()
cmdText := `BEGIN
proc_1(:1);
end;`
var cursor go_ora.RefCursor
_, err = conn.Exec(cmdText, sql.Out{Dest: &cursor})
//check errors
defer cursor.Close()
rows, err := cursor.Query()
// check for error
var (
var_1 int64
var_2 string
)
for rows.Next_() {
err = rows.Scan(&var_1, &var_2)
// check for error
fmt.Println(var_1, var_2)
}