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

How To Use

Connect to Database

• Simple Connection

  this connection require server name or IP, port, service name, username and password

  • using database/sql

    port := 1521
    connStr := go_ora.BuildUrl("server", port, "service_name", "username", "password", nil)
    conn, err := sql.Open("oracle", connStr)
    // check for error
    err = conn.Ping()
    // check for error

  • using package directly

    port := 1521
    connStr := go_ora.BuildUrl("server", port, "service_name", "username", "password", nil)
    conn, err := go_ora.NewConnection(connStr)
    // check for error
    err = conn.Open()
    // check for error

• Connect using SID

  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

• Connect using JDBC string

  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

• SSL Connection

  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)

• OS Auth (for windows)

  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)

• Proxy Auth

  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

• Client Auth

  you should have server and client certificate store in wallets + working TCPS communication

  create oracle user as follows:

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

  configure sqlnet.ora in the server to use client authentication

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

  connect

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

• KERBEROS5 Auth

  note that kerberos need an intact dns system and 3 separate machines to test it

• kerberos server you can use this link to install on ubuntu

• oracle server you can configure it from this link

• client which contain our gocode using package gokrb5

• Complete code found in examples/kerberos

  urlOptions := map[string]string{
    "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{})

  before run the code you should run command kinit user

  Other Connection Options

Execute SQL

• simple query

  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

• input parameters

  • parameters in oracle should start with : for example :pr1

    passing input parameters as defined by database/sql package.

    parameter type

    • int64 / float64 and their equivalent
    • string
    • time.Time
    • any type that support Valuer interface
    • NVarChar
    • TimeStamp
    • TimeStampTZ
    • sql.Null and go_ora.Null for all the above
    • Clob, NClob and Blob

• output parameters

  • passing parameter to Exec to return a value.
  • output parameter should be passed as pointers.
  • all output parameter should be passed inside go_ora.Out or sql.Out structures
  • output parameters like strings should be passed in go_ora.Out to define max size.

    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)

• Lob Types

  • Blob, Clob and NClob
  • Clob use database charset and NClob use database ncharset for string encoding and decoding
  • complete code is found in examples/clob

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

• BFile (v2.7.23)

  • BFile require oracle directory object name + file name
  • create new BFile object by calling CreateBFile, CreateBFile2 or CreateNullBFile
  • CreateBFile create BFile using *sql.DB while CreateBFile2 take *go_ora.Connection
  • to create null BFile call CreateNullBFile or create object with valid=false
  • you can read BFile value from database either by Query or Exec (output par) using *BFile object
  • You can use object functions after creation or read from database, but you should test for null first
  • BFile functions:
  • Open: will open the file (file object returned from database unopened)
  • Exists: check if the file exists
  • GetLength: return file length
  • Read: read entire file content
  • ReadFromPos: start read from specific position till end of the file
  • ReadBytesFromPos: read portion of the file
  • Close: close file
  • IsOpen: check if the file opened
  • GetDirName: return directory object name
  • GetFileName: return file name
  • complete code for BFile found in examples/bfile

• Named Parameters

  • to use named parameters just wrap all you parameters inside sql.Named
  • if one of the parameters doesn't have name driver will switch to positional mode
  • parameter named :pr1 in sql should be passed as sql.Named("pr1", 1)
  • Named parameter is useful if you have one value passed in sql multiple times.
  • order is not important
  • complete code for named parameters found in examples/named_pars

• structures with tag

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

• structure that implement Valuer interface

• oracle user defined type UDT

• struct with tag db

  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

• Arrays

  passing array as a parameter is useful in the following situations

  • Multiple insert/merge
  • Associative Array. You can find complete code in examples/array
  • UDT array. You can find complete code in examples/udt_array

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

• Oracle Objects (user defined types)

  • Created inside oracle using create type
  • objects are mapped to golang struct type defined with tag udt followed by field name.

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

  • each object should register with go_ora.RegisterType or go_ora.RegisterTypeWithOwner passing typeName, arrayTypeName (for that type or empty) and empty instance of struct
  • driver support nested type start from (v2.8.5)
  • for array of regular type (v2.8.7) pass regular type name, arrayTypeName and nil for object instance
  • regular type supported
    • number
    • varchar2
    • nvarchar2
    • date
    • timestamp
    • timestamp with local time zone
    • RAW
    • BLOB
    • CLOB
    • NCLOB
  • to pass oracle object as a parameter efficiently use go_ora.Object{Owner: owner, Name: name, Value: val}
  • regular type array should be passed wrapped inside go_ora.Object otherwise it will be interpreted as associative array
  • complete code is found in
    • examples/UDT
    • examples/nested_udt_array a complete example of nested objects (2 level), nested arrays as input/output parameters
    • examples/regular_type_array a complete example for all supported regullar type arrays
    • examples/null_udt example represent null object as input and output
    • examples/varray for varray types

• RefCursor

  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](https://github.com/sijms/go-ora/blob/master/examples/refcursor_to_rows/main.go)

* ### Connect to multiple database
  * note that `sql.Open(...)` will use default driver so it will be suitable for one database projects.
  * to use multiple database you should create a separate driver for each one (don't use default driver) 
```golang
  // 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)

• use custom configurations for connection

  • another way to set connection configuration instead of using connection string (DSN)
  • for custom dial you should implement DialerContext interface or simply use config.RegisterDial function (start from v2.8.19)
  • example code:

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

* ### Use Custom String encode/decode
  * if your database charset is not supported you can create a custom object that implement IStringConverter interface and pass it to the driver as follows
```golang
  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

• Session Parameters

  • you can update session parameter after connection as follow

    db, err := sql.Open("oracle", connStr)
    if err != nil {
    // error handling
    }
    // pass database, key, value
    err = go_ora.AddSessionParameter(db, "nls_language", "english")
    if err != nil {
    // error handling
    }

//: # ()

releases