Home

Awesome

go-ora

Pure go oracle client

note:

the original oracle drivers are very complex and contain many features which are difficult to add them at one time
your feedbacks are very important for this project to proceed
    - To use version 2 you should 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

Sponsors

<p> <a href="https://jb.gg/OpenSourceSupport" rel="noopener sponsored" target="_blank"><img height="128" width="128" src="https://resources.jetbrains.com/storage/products/company/brand/logos/jb_beam.png?_gl=1*txv9x8*_ga*MzY1MjAzNDI2LjE3MDAzMDc5NTg.*_ga_9J976DJZ68*MTcwNTE3NzM4My4zLjEuMTcwNTE3Nzg1MC40OS4wLjA.&_ga=2.97733338.412104364.1705177384-365203426.1700307958" alt="JetBrains" title="Essential tools for software developers and teams" loading="lazy" /></a> </p>

How To Use

Connect to Database

here we should pass urlOptions note that service name is empty

port := 1521
urlOptions := map[string]string {
  "SID": "SID_VALUE",
}
connStr := go_ora.BuildUrl("server", port, "", "username", "password", urlOptions)
conn, err := sql.Open("oracle", connStr)
// check for error

either pass a urlOption connStr with JDBC string server, port and service name will be collected from JDBC string

urlOptions := map[string]string {
  "connStr": "JDBC string",
}
connStr := go_ora.BuildUrl("", 0, "", "username", "password", urlOptions)
conn, err := sql.Open("oracle", connStr)
// check for error

or use go_ora.BuildJDBC

urlOptions := map[string] string {
	// other options
}
connStr := go_ora.BuildJDBC("username", "password", "JDBC string", urlOptions)
conn, err := sql.Open("oracle", connStr)
// check for error

to use ssl connection you should pass required url options.

port := 2484
urlOptions := map[string] string {
	"ssl": "true", // or enable
	"ssl verify": "false", // stop ssl certificate verification
	"wallet": "path to folder that contains oracle wallet",
}
connStr := go_ora.BuildUrl("server", port, "service_name", "username", "password", urlOptions)

connect to oracle using OS user instead of oracle user username and password parameters passed empty to BuildUrl see examples/windows_os_auth for more help

urlOptions := map[string]string {
    // optional as it will be automatically set 
	// if you pass an empty oracle user or password
    "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,
	// optional as it will be automatically set 
	// when you define AUTH TYPE=OS in windows
    "AUTH SERV": "NTS",
}
port := 1521
connStr := go_ora.BuildUrl("server", port, "service_name", "", "", urlOptions)

before run the code you should run command kinit user

Other Connection Options

<details> 
urlOptions := map[string]string {
	"server": "server2,server3",
}
connStr := go_ora.BuildUrl("server1", 1251, "service", "username", "password", urlOptions)
/* now the driver will try to connect as follows
1- server1
2- server2
3- server3
*/

this option give the client control weather to use encryption or not

urlOptions := map[string]string {
	// values can be "required", "accepted", "requested", and rejected"
	"encryption": "required",
}

this option give the client control weather to user data integrity or not

urlOptions := map[string]string {
    // values can be "required", "accepted", "requested", and rejected"
    "data integrity": "rejected",
}

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",
}

urlOptions := map[string]string {
	"TIMEOUT": "60",
}

urlOptions := map[string]string {
	"proxy client name": "schema_owner",
}
connStr := go_ora.BuildUrl("server", 1521, "service", "proxy_user", "proxy_password", urlOptions)

urlOptions := map[string]string {
	"dba privilege" : "sysdba", // other values "SYSOPER"
}

urlOptions := map[string]string {
	"lob fetch": "stream",
}

urlOptions := map[string]string {
    // you can use also 
    //"charset": "UTF8",
    "client charset": "UTF8",
}

urlOptions := map[string]string {
    "language": "PORTUGUESE",
    "territory": "BRAZILIAN",
}

this option used for logging driver work and network data for debugging purpose

urlOptions := map[string]string {
	"trace file": "trace.log",
}

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:

use this option if you want to pass your own CID started from v2.7.15

default CID

FulCid := "(CID=(PROGRAM=" + op.ProgramPath + ")(HOST=" + op.HostName + ")(USER=" + op.UserName + "))"
</details>

Execute SQL

execute a query follows standards that defined in go package database/sql you have Query used for query rows and Exec used for DML/DDL and PL/SQL

Exec example

// note no semicolon at the end
_, err := conn.Exec(`CREATE TABLE TABLE1(
ID number(10),
NAME varchar2(50),
DAT DATE
)`)
// check for errors

query example:

rows, err := conn.Query("SELECT ID, NAME, DAT FROM TABLE1")
// check for errors
defer rows.Close()
var (
	id int64
	name sql.NullString
	date sql.NullTime
)
for rows.Next() {
	err = rows.Scan(&id, &name, &date)
	// check for errors
}

PL/SQL

// note semicolon at the end
_, err := conn.Exec("begin DBMS_LOCK.sleep(7); end;")
// check for errors

complete example found in examples/crud

passing input parameters as defined by database/sql package.

parameter type

var(
	id int64
	name sql.NullString
	date sql.NullTime
)
_, err := conn.Exec("SELECT ID, NAME, DAT INTO :pr1, :pr2, :pr3 FROM TABLE1 WHERE ID=:pr4",
	sql.Out{Dest: &id},
	go_ora.Out{Dest: &name, Size: 100},
	go_ora.Out{Dest: &date},
	1)

input parameters

var1 := go_ora.Clob{String: "large string value"}
var2 := go_ora.Blob{Data: []byte("large []byte value")}
_, err := conn.Exec("insert into tb values(:1, :2)", var1, var2)

output parameters

var {
	var1 go_ora.NClob
	var2 go_ora.Blob
}
// set size according to size of your data
_, err := conn.Exec("BEGIN SELECT col1, col2 into :1, :2 FROM tb; END;",
	go_ora.Out{Dest: &var1, size: 100000},
	go_ora.Out{Dest: &var2, size: 300000})

you can pass a structure parameter to sql in one of the following situation

data in db tag can be recognized by its position or as key=value

type TEMP_TABLE struct {
  // tag by position: db:"name,type,size,direction"
  Id int  `db: "ID,number"`

  Name string `db:"type=varchar,name=NAME"`
}

struct with tag uses named parameters so you should pass at least the name of the parameter to use this feature.

Type is important in some situations for example if you have field with type time.Time and you want to pass timestamp to database so put type=timestamp

type can be one of the following

number      mapped to golang types integer, float, string, bool
varchar     mapped to any golang types that can converted to string
nvarchar    mapped to any golang types that can converted to string
date        mapped to golang types time.Time and string
timestamp   mapped to golang types time.Time and string
timestamptz mapped to golang types time.Time and string
raw         mapped to golang types []byte and string
blob        mapped to golang types []byte and string
clob        mapped to any golang types that can converted to string
nclob       mapped to any golang types that can converted to string

size and direction are required if the fields mapped to an output parameter

complete code can be found in examples/struct_par

passing array as a parameter is useful in the following situations

Bulk insert/merge will be activated when you passing all parameters as arrays of same size.

you can also pass an array of tagged structure to do same thing. complete code for bulk-insert/merge can be found in examples/merge

 type Customer struct {
   ID int `udt:"ID"`
   Name string `udt:"NAME"`
}

as an output parameter

  var cursor go_ora.RefCursor
  _, err = conn.Exec(`BEGIN PROC1(:1, :2); END;`, 1, sql.Out{Dest: &cursor})

you can use go_ora.WrapRefCursor(...) to convert *RefCursor into *sql.Rows started from v2.7.17

complete code for RefCursor as output parameter found in examples/refcursor

Map RefCursor to sql.Rows

// TEMP_FUNC_316 is sql function that return RefCursor
sqlText := `SELECT TEMP_FUNC_316(10) from dual`

// use Query and don't use QueryRow
rows, err := conn.Query(sqlText)
if err != nil {
	return err
}

// closing the parent rows will automatically close cursor
defer rows.Close()

for rows.Next() {
    var cursor sql.Rows
	err = rows.Scan(&cursor)
	if err != nil {
		return err
	}
	var (
        id   int64
        name string
        val  float64
        date time.Time
    )
	
    // reading operation should be inside rows.Next
    for cursor.Next() {
        err = cursor.Scan(&id, &name, &val, &date)
        if err != nil {
            return err
        }
        fmt.Println("ID: ", id, "\tName: ", name, "\tval: ", val, "\tDate: ", date)
    }
}

complete code for mapping refcursor to sql.Rows is found in example/refcursor_to_rows

  // Get a driver-specific connector.   
  connector, err := go_ora.NewConnector(connStr)
  if err != nil {
    log.Fatal(err)
  }

  // Get a database handle.
  db = sql.OpenDB(connector)

config, err := go_ora.ParseConfig(DSN)
	
// for using custom dial use RegisterDial
// start from v2.8.19
config.RegisterDial(func(ctx context.Context, network, address string) (net.Conn, error) {
    // your custom dial code
})

// modify config structure 

go_ora.RegisterConnConfig(config)
// now open db note empty DSN 
db, err := sql.Open("oracle", "")

  db, err := sql.Open("oracle", connStr)
  if err != nil {
	  // error handling
  }
  
  // call SetStringConverter before use db object
  // charset and nCharset are custom object that implement 
  // IStringConverter interface
  // if you pass nil for any of them then the driver will use 
  // default StringConverter
  go_ora.SetStringConverter(db, charset, nCharset)
  
  // rest of your code

releases

<details>

version 2.8.19

config, err := go_ora.ParseConfig(`yours DSN string`)
config.RegisterDial(func(ctx context.Context, network, address string) (net.Conn, error) {
    // your custom dial code
})
go_ora.RegisterConfig(config)
db, err := sql.Open("oracle", "")

version 2.8.12

version 2.8.8

version 2.8.7

version 2.8.6

version 2.8.5

go typeoracle type
time.TimeDATE
go_ora.TimeStampTIMESTAMP
go_ora.TimeStampTZTIMESTAMP WITH TIME ZONE

version 2.8.4

version 2.8.2

version 2.8.0

version 2.7.25

version 2.7.23

version 2.7.20

version 2.7.18

version 2.7.17

// conn is *sql.DB
// cursor comming from output parameter
rows, err := go_ora.WrapRefCursor(context.Background(), conn, cursor)

version 2.7.11

import (
  "database/sql"
  db_out "github.com/sijms/go-ora/dbms_output"
  _ "github.com/sijms/go-ora/v2"
  "os"
)

// create new output
// conn is *sql.DB
// bufferSize between 2000 and 32767
output, err := db_out.NewOutput(conn, 0x7FFF)

// close before exit
defer func() {
  err = output.Close()
}()

// put some line 
err = exec_simple_stmt(conn, `BEGIN
DBMS_OUTPUT.PUT_LINE('this is a test');
END;`)

// get data as string
line, err := output.GetOutput()

// or print it to io.StringWriter
err = output.Print(os.Stdout)

version 2.7.7:

version 2.7.4:

version 2.7.3: Use database/sql fail over

version 2.7.2: Use golang structure as an oracle (output) parameters

all rules used for input will be required for output plus:

number      mapped to sql.NullFloat64
varchar     mapped to sql.NullString
nvarchar    mapped to sql.NullNVarchar
date        mapped to sql.NullTime
timestamp   mapped to NullTimeStamp
timestamptz mapped to NullTimeStampTZ
raw         mapped to []byte
clob        mapped to Clob
nclob       mapped to NClob
blob        mapped to Blob

all fields that support driver.Valuer interface will be passed as it is

version 2.7.1: Use golang structure as an oracle (input) parameters

type TEMP_TABLE struct {
	// tag by position: db:"name,type,size,direction"
	Id    int      `db:"ID,number"`
	// tag as key=value: db:"size=size,name=name,dir=directiontype=type"
	Name  string   `db:"type=varchar,name=NAME"`
}

number      mapped to golang types integer, float, string, bool
varchar     mapped to any golang types that can converted to string
nvarchar    mapped to any golang types that can converted to string
date        mapped to golang types time.Time and string
timestamp   mapped to golang types time.Time and string
timestamptz mapped to golang types time.Time and string
raw         mapped to golang types []byte and string
blob        mapped to golang types []byte and string
clob        mapped to any golang types that can converted to string
nclob       mapped to any golang types that can converted to string

version 2.6.17: Implement Bulk(Insert/Merge) in ExecContext

version 2.6.16: Map RefCursor to sql.Rows

// TEMP_FUNC_316 is sql function that return RefCursor
sqlText := `SELECT TEMP_FUNC_316(10) from dual`

// use Query and don't use QueryRow
rows, err := conn.Query(sqlText)
if err != nil {
	return err
}

// closing the parent rows will automatically close cursor
defer rows.Close()

for rows.Next() {
    var cursor sql.Rows
	err = rows.Scan(&cursor)
	if err != nil {
		return err
	}
	var (
        id   int64
        name string
        val  float64
        date time.Time
    )
	
    // reading operation should be inside rows.Next
    for cursor.Next() {
        err = cursor.Scan(&id, &name, &val, &date)
        if err != nil {
            return err
        }
        fmt.Println("ID: ", id, "\tName: ", name, "\tval: ", val, "\tDate: ", date)
    }
}

version 2.6.14: Add Support for Named Parameters

version 2.6.12: Add Client Charset option

urlOptions := map[string]string {
	// you can use also 
	//"charset": "UTF8",
	"client charset": "UTF8",
	"trace file": "trace.log",
}
connStr := go_ora.BuildUrl("server", 1521, "service", "", "", urlOptions)

version 2.6.9: Re-Code Failover

version 2.6.8: Fix return long data type with lob prefetch option:

version 2.6.5: Add New Url Options (Language and Territory)

urlOptions := map[string]string {
"language": "PORTUGUESE",
"territory": "BRAZILIAN",
}
url := go_ora.BuildUrl(server, port, service, user, password, urlOptions)

version 2.6.4: Add Support for TimeStamp with timezone

version 2.6.2: Add Support for Lob Prefetch

urlOptions := map[string]string {
  "TRACE FILE": "trace.log",
  "LOB FETCH": "PRE", // other value "POST"
}
connStr := go_ora.BuildUrl("server", 1521, "service", "", "", urlOptions)

version 2.5.33: Add Support for Client Authentication

CREATE USER "SSLCLIENT" IDENTIFIED EXTERNALLY AS 'CN=ORCLCLIENT';

SQLNET.AUTHENTICATION_SERVICES=(TCPS,NTS)
SSL_CLIENT_AUTHENTICATION=TRUE

urlOptions := map[string]string {
  "TRACE FILE": "trace.log",
  "AUTH TYPE":  "TCPS",
  "SSL": "TRUE",
  "SSL VERIFY": "FALSE",
  "WALLET": "PATH TO WALLET"
}
connStr := go_ora.BuildUrl("server", 2484, "service", "", "", urlOptions)

version 2.5.31: Add BulkCopy using DirectPath (experimental)

version 2.5.19: Add Support for Kerberos5 Authentication

urlOptions := map[string]string{
    "TRACE FILE": "trace.log",
    "AUTH TYPE":  "KERBEROS",
}
// note empty password
connStr := go_ora.BuildUrl("server", 1521, "service", "krb_user", "", urlOptions)

type KerberosAuth struct{}
func (kerb KerberosAuth) Authenticate(server, service string) ([]byte, error) {
    // see implementation in examples/kerberos
}
advanced_nego.SetKerberosAuth(&KerberosAuth{})

version 2.5.16: Add Support for cwallet.sso created with -auto_login_local option

version 2.5.14: Failover and wallet update

version 2.5.13: Add Support For Failover (Experimental)

urlOptions := map[string]string{
	"FAILOVER": "5",
	"TRACE FILE": "trace.log",
}
databaseUrl := go_ora.BuildUrl(server, port, service, user, password, urlOptions)

version 2.4.28: Binary Double And Float Fix

version 2.4.20: Query To Struct

version 2.4.18: Add support for proxy user

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

version 2.4.8: JDBC connect string

    // 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
	}

version 2.4.5: Support BFile

// 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)

version 2.4.4: Support for unix socket IPC

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"
}

version 2.4.3: Input Parameter CLOB and BLOB Accept Large Data Size

you can pass input CLOB and BLOB with any data size up to data type limit

version 2.4.1: Add support for connection time out + context read and write

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

version 2.4.0: Add support for Arrays

// 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)

version 2.3.5: Add support for OS Auth (Windows) With Password Hash

now you can pass password hash of the user instead of real password

source of hash:

urlOptions := map[string]string {
	"OS HASH": "yourpasswordhash"
	// or
	"OS PassHash": "yourpasswordhash"
	// or
	"OS Password Hash": "yourpasswordhash"
}

note:

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

version 2.3.3: Add support for OS Auth (Windows)

you can see windows_os_auth example for more detail

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)

note (Remote OS Auth):

note (advanced users):

type NTSAuthInterface interface {
	NewNegotiateMessage(domain, machine string) ([]byte, error)
	ProcessChallenge(chaMsgData []byte, user, password string) ([]byte, error)
}

go_ora.SetNTSAuth(newNTSManager)

// 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);

version 2.3.1: Fix issue related to use ipv6

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"

version 2.3.0: Add support for Nullable types

version 2.2.25: Add support for User Defined Type (UDT) as input and output parameter

version 2.2.23: User Defined Type (UDT) as input parameters

version 2.2.22: Lob for output parameters

version 2.2.19: improve lob reading with high prefetch rows value

version 2.2.9: add support for connect to multiple servers

define multiple server in 2 way

// 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
*/

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)

version 2.2.8: add OracleError class

OracleError carry error message from the server

version 2.2.7: Add support for user defined types

version 2.2.6 (pre-release - experimental): Add support for user defined types

to use make the following (oracle 12c)

create or replace TYPE TEST_TYPE1 IS OBJECT 
( 
    TEST_ID NUMBER(6, 0),
    TEST_NAME VARCHAR2(10)
)

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"`
}

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
}()

if drv, ok := conn.Driver().(*go_ora.OracleDriver); ok {
    err = drv.Conn.RegisterType("owner", "TEST_TYPE1", test1{})
    // check for err
}

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)
}

version 2.2.5

version 2.2.4

wallet=wallet_dir // wallet should contain server and client certificates
SSL=true          // true or enabled
SSL Verify=false  // to bypass certificate verification

version 2.1.23

sqlQuery := "oracle://user@127.0.0.1:1522/service"
sqlQuery += "?TRACE FILE=trace.log"
sqlQuery += "&wallet=path_to_wallet_directory"
conn, err := sql.open("oracle", sqlQuery)
server:port/service ---> should be supplied when using wallet
user ---> is optional when omitted the reader will return first matched dsn
password ---> should be empty as it will be supplied from wallet

version 2.1.22

# possible values ([accepted | rejected | requested | required])
SQLNET.CRYPTO_CHECKSUM_SERVER = required
# possible values ([MD5 | SHA1 | SHA256 | SHA384 | SHA512])
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = SHA512

version 2.1.21

# possible values ([accepted | rejected | requested | required])
SQLNET.ENCRYPTION_SERVER = required
# possible values for AES (AES256 | AES192 | AES128)
SQLNET.ENCRYPTION_TYPES_SERVER = AES256

version 2.1.20

    1- varchar string:
_, err := conn.Exec(inputSql, "7586")
   2- nvarchar string:
_, err := conn.Exec(inputSql, go_ora.NVarChar("7586"))

version 2.1.19

version 2.0-beta

</details>