### Work with BFile in go-ora

Source: https://github.com/sijms/go-ora/blob/master/README.md

This example demonstrates creating, opening, and reading from a BFile object. Ensure the directory object is created in Oracle and granted read/write permissions. The BFile must be opened before use.

```golang
// 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)
}
```

--------------------------------

### Example Driver Log Output

Source: https://github.com/sijms/go-ora/blob/master/README.md

Illustrates the format of log output generated by the go-ora driver, including connection details, packet data, and executed queries.

```text
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|
```

--------------------------------

### Define Multiple Servers for Connection

Source: https://github.com/sijms/go-ora/blob/master/README.md

Configure the driver to attempt connections to a list of servers in sequence. This is useful for high availability setups.

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

--------------------------------

### Build JDBC Connection String

Source: https://context7.com/sijms/go-ora/llms.txt

Creates a connection URL from a JDBC-style connection descriptor. Enables failover and load balancing configurations with multiple server addresses. Useful for complex network setups.

```go
package main

import (
    "database/sql"
    "fmt"
    go_ora "github.com/sijms/go-ora/v2"
)

func main() {
    // JDBC connection descriptor with failover
    jdbcStr := `(DESCRIPTION=
        (ADDRESS_LIST=
            (LOAD_BALANCE=OFF)
            (FAILOVER=ON)
            (ADDRESS=(PROTOCOL=TCP)(HOST=primary-server)(PORT=1521))
            (ADDRESS=(PROTOCOL=TCP)(HOST=standby-server)(PORT=1521))
        )
        (CONNECT_DATA=
            (SERVICE_NAME=myservice)
            (SERVER=DEDICATED)
        )
    )`

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

    connStr := go_ora.BuildJDBC("myuser", "mypassword", jdbcStr, urlOptions)

    db, err := sql.Open("oracle", connStr)
    if err != nil {
        panic(err)
    }
    defer db.Close()

    err = db.Ping()
    if err != nil {
        panic(err)
    }
    fmt.Println("Connected with JDBC descriptor!")
}
```

--------------------------------

### Select and Display Data with UDT

Source: https://github.com/sijms/go-ora/blob/master/README.md

Query data from Oracle that involves a User Defined Type (UDT) and scan the results into a Go struct. This example shows how to select a UDT from dual and scan it into the defined struct.

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

--------------------------------

### Format Custom CID in go-ora

Source: https://github.com/sijms/go-ora/blob/master/README.md

Use this option to pass your own CID starting from v2.7.15. This example shows how to construct a default CID string.

```golang
FulCid := "(CID=(PROGRAM=" + op.ProgramPath + ")(HOST=" + op.HostName + ")(USER=" + op.UserName + "))"
```

--------------------------------

### Configure Client Authentication with TCPS in Go-ora

Source: https://github.com/sijms/go-ora/blob/master/README.md

Enables client authentication using TCPS, requiring server and client certificate stores, and a working TCPS communication setup. Ensure the Oracle user is created with 'IDENTIFIED EXTERNALLY' and sqlnet.ora is configured for client authentication.

```Go
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)
```

--------------------------------

### Set Connection Timeout in go-ora

Source: https://github.com/sijms/go-ora/blob/master/README.md

Determine the connection's overall lifetime using the 'CONNECTION TIMEOUT' URL option. This example sets the timeout to 3 seconds.

```golang
// set connection time for 3 second
urlOptions := map[string]string {
    "CONNECTION TIMEOUT": "3"
}
databaseUrl := go_ora.BuildUrl(server, port, service, user, password, urlOptions)
```

--------------------------------

### Basic SQL Operations with go-ora

Source: https://context7.com/sijms/go-ora/llms.txt

Demonstrates creating a table, inserting data using both positional and named parameters, and querying data with a condition. Requires the database/sql and go-ora packages.

```go
package main

import (
    "database/sql"
    "fmt"
    "time"
    go_ora "github.com/sijms/go-ora/v2"
)

func main() {
    connStr := go_ora.BuildUrl("localhost", 1521, "ORCLPDB1", "myuser", "mypassword", nil)
    db, err := sql.Open("oracle", connStr)
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Create table
    _, err = db.Exec(`CREATE TABLE employees (
        id NUMBER(10) PRIMARY KEY,
        name VARCHAR2(100),
        salary NUMBER(10,2),
        hire_date DATE
    )`)
    if err != nil {
        fmt.Println("Table may already exist:", err)
    }

    // Insert with positional parameters
    _, err = db.Exec("INSERT INTO employees (id, name, salary, hire_date) VALUES (:1, :2, :3, :4)",
        1, "John Doe", 75000.50, time.Now())
    if err != nil {
        panic(err)
    }

    // Insert with named parameters
    _, err = db.Exec("INSERT INTO employees (id, name, salary, hire_date) VALUES (:id, :name, :salary, :hireDate)",
        sql.Named("id", 2),
        sql.Named("name", "Jane Smith"),
        sql.Named("salary", 82000.00),
        sql.Named("hireDate", time.Now()))
    if err != nil {
        panic(err)
    }

    // Query rows
    rows, err := db.Query("SELECT id, name, salary, hire_date FROM employees WHERE salary > :1", 70000)
    if err != nil {
        panic(err)
    }
    defer rows.Close()

    for rows.Next() {
        var id int
        var name string
        var salary float64
        var hireDate time.Time

        err = rows.Scan(&id, &name, &salary, &hireDate)
        if err != nil {
            panic(err)
        }
        fmt.Printf("ID: %d, Name: %s, Salary: %.2f, Hired: %s\n", id, name, salary, hireDate.Format("2006-01-02"))
    }
}
```

--------------------------------

### Simple Database Connection using database/sql

Source: https://github.com/sijms/go-ora/blob/master/README.md

Establishes a basic connection to an Oracle database using the standard database/sql package. Requires server, port, service name, username, and password.

```golang
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
```

--------------------------------

### Register Dial Function for Go-Ora Configuration

Source: https://github.com/sijms/go-ora/blob/master/README.md

Register a custom dial function for a Go-Ora configuration starting from v2.8.19. This allows for custom network connection logic.

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

--------------------------------

### Simple Database Connection using go-ora package directly

Source: https://github.com/sijms/go-ora/blob/master/README.md

Establishes a basic connection using the go-ora package's NewConnection and Open methods. Requires server, port, service name, username, and password.

```golang
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 to Multiple Servers via URL Options

Source: https://github.com/sijms/go-ora/blob/master/README.md

Connect to multiple database servers by specifying them in the URL options. The driver will attempt connections in the order provided.

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

--------------------------------

### Custom NTS Authentication with .NET/Windows API

Source: https://github.com/sijms/go-ora/blob/master/README.md

Example of using .NET or Windows API for custom NTS authentication, where CredentialCache.DefaultNetworkCredentials retrieves authentication data from the logon user.

```C#
// 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);
```

--------------------------------

### Connect to Multiple Servers using BuildUrl

Source: https://github.com/sijms/go-ora/blob/master/README.md

Connect to multiple database servers using the BuildUrl function by passing server names in the urlOptions map. Other options like TRACE FILE and PREFETCH_ROWS can also be configured.

```Golang
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)
```

--------------------------------

### Define Oracle User Defined Type (UDT) in SQL

Source: https://github.com/sijms/go-ora/blob/master/README.md

Example of defining a User Defined Type (UDT) in Oracle SQL. This type can then be used with the go-ora driver.

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

--------------------------------

### Handle CLOB and BLOB Data in Go

Source: https://context7.com/sijms/go-ora/llms.txt

Demonstrates inserting and retrieving CLOB and BLOB data using go-ora's Clob and Blob types. Configure LOB prefetch mode via URL options.

```go
package main

import (
    "database/sql"
    "fmt"
    go_ora "github.com/sijms/go-ora/v2"
)

func main() {
    urlOptions := map[string]string{
        "LOB FETCH": "PRE", // "PRE" (inline) or "POST" (stream)
    }
    connStr := go_ora.BuildUrl("localhost", 1521, "ORCLPDB1", "myuser", "mypassword", urlOptions)

    db, err := sql.Open("oracle", connStr)
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Create table with LOB columns
    _, _ = db.Exec(`CREATE TABLE documents (
        id NUMBER PRIMARY KEY,
        content CLOB,
        binary_data BLOB
    )`)

    // Insert CLOB and BLOB data
    largeText := "This is a large text content that would be stored as CLOB..."
    binaryData := []byte{0x89, 0x50, 0x4E, 0x47, 0x0D, 0x0A, 0x1A, 0x0A} // PNG header

    _, err = db.Exec("INSERT INTO documents (id, content, binary_data) VALUES (:1, :2, :3)",
        1,
        go_ora.Clob{String: largeText, Valid: true},
        go_ora.Blob{Data: binaryData})
    if err != nil {
        panic(err)
    }

    // Read CLOB and BLOB
    var content go_ora.Clob
    var data go_ora.Blob

    err = db.QueryRow("SELECT content, binary_data FROM documents WHERE id = :1", 1).Scan(&content, &data)
    if err != nil {
        panic(err)
    }

    fmt.Printf("CLOB content: %s\n", content.String)
    fmt.Printf("BLOB size: %d bytes\n", len(data.Data))

    // CLOB as output parameter
    var outClob go_ora.Clob
    _, err = db.Exec("BEGIN SELECT content INTO :1 FROM documents WHERE id = :2; END;",
        go_ora.Out{Dest: &outClob, Size: 100000},
        1)
    if err != nil {
        panic(err)
    }
    fmt.Printf("Output CLOB: %s\n", outClob.String)
}
```

--------------------------------

### Handling Output Parameters in Stored Procedures

Source: https://context7.com/sijms/go-ora/llms.txt

Illustrates calling stored procedures with output parameters using `go_ora.Out` and handling `SELECT INTO` statements with output parameters. Ensure `Size` is set for string output parameters.

```go
package main

import (
    "database/sql"
    "fmt"
    go_ora "github.com/sijms/go-ora/v2"
)

func main() {
    connStr := go_ora.BuildUrl("localhost", 1521, "ORCLPDB1", "myuser", "mypassword", nil)
    db, err := sql.Open("oracle", connStr)
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Create procedure
    _, err = db.Exec(`CREATE OR REPLACE PROCEDURE get_employee_info(
        p_id IN NUMBER,
        p_name OUT VARCHAR2,
        p_salary OUT NUMBER
    ) AS
    BEGIN
        SELECT name, salary INTO p_name, p_salary FROM employees WHERE id = p_id;
    END;`)
    if err != nil {
        panic(err)
    }

    // Call procedure with output parameters
    var name string
    var salary float64

    _, err = db.Exec("BEGIN get_employee_info(:1, :2, :3); END;",
        1, // input: employee ID
        go_ora.Out{Dest: &name, Size: 100},  // output: name with max size
        go_ora.Out{Dest: &salary})            // output: salary
    if err != nil {
        panic(err)
    }

    fmt.Printf("Employee: %s, Salary: %.2f\n", name, salary)

    // SELECT INTO with output parameters
    var empID int
    var empName sql.NullString
    var empDate sql.NullTime

    _, err = db.Exec("SELECT id, name, hire_date INTO :1, :2, :3 FROM employees WHERE id = :4",
        sql.Out{Dest: &empID},
        go_ora.Out{Dest: &empName, Size: 100},
        go_ora.Out{Dest: &empDate},
        1) // input parameter
    if err != nil {
        panic(err)
    }
    fmt.Printf("Found: ID=%d, Name=%s\n", empID, empName.String)
}
```

--------------------------------

### Build URL with IPv6 Address

Source: https://github.com/sijms/go-ora/blob/master/README.md

Demonstrates building a connection URL that includes an IPv6 address. The BuildUrl function can handle IPv6 addresses, and a direct URL format is also shown.

```Golang
url := go_ora.BuildUrl("::1", 1521, "service", "user", "password", nil)
url = "oracle://user:password@[::1]:1521/service"
```

--------------------------------

### Create Custom Connector

Source: https://context7.com/sijms/go-ora/llms.txt

Creates a driver-specific connector for custom configurations like TLS, custom dialers, or Kerberos authentication. Allows using the driver without the default singleton.

```go
package main

import (
    "context"
    "database/sql"
    "fmt"
    go_ora "github.com/sijms/go-ora/v2"
    "time"
)

func main() {
    connStr := go_ora.BuildUrl("localhost", 1521, "ORCLPDB1", "myuser", "mypassword", nil)

    // Create connector for custom configuration
    connector := go_ora.NewConnector(connStr)

    // Get database handle from connector
    db := sql.OpenDB(connector)
    defer db.Close()

    // Use context with timeout
    ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
    defer cancel()

    err := db.PingContext(ctx)
    if err != nil {
        panic(err)
    }
    fmt.Println("Connected via custom connector!")
}
```

--------------------------------

### Execute with Output Parameters

Source: https://github.com/sijms/go-ora/blob/master/README.md

Pass output parameters as pointers within `go_ora.Out` or `sql.Out` structures. Specify size for string-like output parameters.

```golang
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)
```

--------------------------------

### OS Authentication Connection (Windows)

Source: https://github.com/sijms/go-ora/blob/master/README.md

Connects to Oracle using the operating system user's credentials on Windows. Pass empty username and password to BuildUrl and set 'AUTH TYPE' to 'OS' in urlOptions.

```golang
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)
```

--------------------------------

### Connect to Database using JDBC string with BuildUrl

Source: https://github.com/sijms/go-ora/blob/master/README.md

Connects to an Oracle database by providing a JDBC connection string within urlOptions. Server, port, and service name are extracted from the JDBC string.

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

--------------------------------

### Enable Driver Logging

Source: https://github.com/sijms/go-ora/blob/master/README.md

Configure logging for driver operations and network data for debugging purposes by specifying a 'trace file' name.

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

--------------------------------

### SSL Connection to Database

Source: https://github.com/sijms/go-ora/blob/master/README.md

Establishes an SSL connection to the Oracle database. Requires specifying 'ssl: "true"' and potentially 'ssl verify: "false"' and wallet path in urlOptions.

```golang
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 Multiple Databases using Go-Ora

Source: https://github.com/sijms/go-ora/blob/master/README.md

To connect to multiple databases, create a separate driver for each by using go_ora.NewConnector instead of the default sql.Open 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)
```

--------------------------------

### Connect with SSL/TLS and Wallet Authentication

Source: https://context7.com/sijms/go-ora/llms.txt

Establish secure connections using SSL/TLS by configuring connection options, including SSL verification and wallet paths. Supports auto-login wallets for passwordless authentication.

```go
package main

import (
    "database/sql"
    "fmt"
    go_ora "github.com/sijms/go-ora/v2"
)

func main() {
    // SSL connection with wallet
    urlOptions := map[string]string{
        "SSL":        "true",
        "SSL VERIFY": "false",                    // Set to "true" in production
        "WALLET":     "/path/to/wallet",          // Directory containing cwallet.sso
    }
    connStr := go_ora.BuildUrl("dbserver", 2484, "myservice", "myuser", "mypassword", urlOptions)

    db, err := sql.Open("oracle", connStr)
    if err != nil {
        panic(err)
    }
    defer db.Close()

    err = db.Ping()
    if err != nil {
        panic(err)
    }
    fmt.Println("Connected with SSL!")

    // Auto-login wallet (no password needed)
    // Wallet must contain credentials for the service
    walletOptions := map[string]string{
        "WALLET": "/path/to/wallet",
    }
    // User/password from wallet - pass empty strings
    walletConnStr := go_ora.BuildUrl("dbserver", 1521, "myservice", "", "", walletOptions)

    walletDb, err := sql.Open("oracle", walletConnStr)
    if err != nil {
        panic(err)
    }
    defer walletDb.Close()
}
```

--------------------------------

### Enable TCPS Connection in go-ora

Source: https://github.com/sijms/go-ora/blob/master/README.md

Configure TCPS connections by setting URL options for SSL, wallet directory, and verification. Ensure the wallet contains server and client certificates.

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

--------------------------------

### Configure Lob Fetch Option in Go-ora

Source: https://github.com/sijms/go-ora/blob/master/README.md

Allows controlling how LOB data is retrieved. 'PRE' (default) fetches data before the locator, while 'POST' fetches it after, requiring an additional network call.

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

--------------------------------

### Configure Kerberos Authentication in go-ora

Source: https://github.com/sijms/go-ora/blob/master/README.md

Use this to configure Kerberos authentication. Ensure you have run 'kinit user' before executing this code. The Kerberos server and Oracle server must be properly configured.

```golang
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{})
```

--------------------------------

### Connect to Database using JDBC string with BuildJDBC

Source: https://github.com/sijms/go-ora/blob/master/README.md

Connects to an Oracle database using the BuildJDBC helper function, which simplifies constructing the connection string from a JDBC string and additional options.

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

--------------------------------

### Connect with Auto-Login Oracle Wallet

Source: https://github.com/sijms/go-ora/blob/master/README.md

Use the auto-login Oracle wallet by specifying its directory path, which must contain the cwallet.sso file. The user is optional, and the password is not needed as it's supplied from the wallet.

```go
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)
```

--------------------------------

### Implement Custom NTS Authentication Interface

Source: https://github.com/sijms/go-ora/blob/master/README.md

Implement the NTSAuthInterface to create a custom NTS authentication manager. This is useful for advanced users who may not need to provide OS passwords.

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

--------------------------------

### Connect to Database and Register UDT

Source: https://github.com/sijms/go-ora/blob/master/README.md

Connect to an Oracle database using go_ora.BuildUrl and register a User Defined Type (UDT) with the driver. The RegisterType function requires the owner of the type.

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

--------------------------------

### Connect to Database using SID

Source: https://github.com/sijms/go-ora/blob/master/README.md

Connects to an Oracle database using the SID instead of the service name. Pass SID in urlOptions.

```golang
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
```

--------------------------------

### Use Custom Configurations for Go-Ora Connection

Source: https://github.com/sijms/go-ora/blob/master/README.md

Configure connection settings using ParseConfig and RegisterDial for custom dial implementations. Register the configuration and then open the database with an empty DSN.

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

--------------------------------

### Configure OS Authentication (Windows) in go-ora

Source: https://github.com/sijms/go-ora/blob/master/README.md

Enable OS authentication by setting 'AUTH TYPE' to 'OS'. Provide 'OS USER' and 'OS PASS' if not using the logon user. 'AUTH SERV: "NTS"' is required for remote OS authentication.

```golang
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)
```

--------------------------------

### Map Go Struct Fields to Oracle Input Parameters

Source: https://github.com/sijms/go-ora/blob/master/README.md

Use the `db` tag in Go structs to define input parameters for Oracle. Tags can specify parameter position or use key-value pairs for name, type, size, and direction. The driver can infer types or use specified Oracle types.

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

```go
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
```

--------------------------------

### Map RefCursor to sql.Rows in Go

Source: https://github.com/sijms/go-ora/blob/master/README.md

Demonstrates how to query a SQL function returning a RefCursor and iterate over its results using sql.Rows. Ensure the parent rows are closed to automatically close the cursor.

```golang
// 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)
    }
}
```

--------------------------------

### Client Authentication Connection (TCPS)

Source: https://github.com/sijms/go-ora/blob/master/README.md

Connects using client certificates over TCPS. Requires server and client certificate wallets, and server-side configuration for client authentication. Set 'AUTH TYPE' to 'TCPS' and provide wallet path.

```golang
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)
```

--------------------------------

### Access External Files with BFile in Go

Source: https://context7.com/sijms/go-ora/llms.txt

Utilize BFile for read access to files on the Oracle server's file system via directory objects. Ensure the directory object is created and granted read permissions.

```go
package main

import (
    "database/sql"
    "fmt"
    go_ora "github.com/sijms/go-ora/v2"
)

func main() {
    connStr := go_ora.BuildUrl("localhost", 1521, "ORCLPDB1", "myuser", "mypassword", nil)
    db, err := sql.Open("oracle", connStr)
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Note: Directory object must be created by DBA
    // CREATE DIRECTORY mydir AS '/path/to/files';
    // GRANT READ ON DIRECTORY mydir TO myuser;

    // Create BFile reference
    bfile, err := go_ora.CreateBFile(db, "MYDIR", "document.pdf")
    if err != nil {
        panic(err)
    }

    // Open file for reading
    err = bfile.Open()
    if err != nil {
        panic(err)
    }
    defer bfile.Close()

    // Check if file exists
    exists, err := bfile.Exists()
    if err != nil {
        panic(err)
    }

    if exists {
        // Get file length
        length, _ := bfile.GetLength()
        fmt.Printf("File size: %d bytes\n", length)

        // Read entire file
        data, err := bfile.Read()
        if err != nil {
            panic(err)
        }
        fmt.Printf("Read %d bytes\n", len(data))

        // Read from specific position
        partialData, _ := bfile.ReadBytesFromPos(100, 50) // 50 bytes starting at position 100
        fmt.Printf("Partial read: %d bytes\n", len(partialData))
    }
}
```

--------------------------------

### Configure DBA Privilege

Source: https://github.com/sijms/go-ora/blob/master/README.md

Set the DBA privilege level for the connection. Options include 'SYSDBA' and 'SYSOPER'. Connecting as user 'sys' automatically sets this to 'SYSDBA'.

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

--------------------------------

### Map Go Struct Fields to Oracle Output Parameters

Source: https://github.com/sijms/go-ora/blob/master/README.md

Define Go structs with `db` tags to map them to Oracle output parameters. Ensure the struct is passed as a pointer and the tag includes `direction` as 'output' or 'inout'. Field types are mapped to specific `sql.Null` types or Oracle types.

```go
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
```

--------------------------------

### Configure Unix Socket Connection

Source: https://github.com/sijms/go-ora/blob/master/README.md

Enable connections via Unix domain sockets when the client and server are on the same Linux machine. Specify the socket path using the 'unix socket' option.

```golang
urlOptions := map[string]string{
	// change the value according to your machine 
	"unix socket": "/usr/tmp/.oracle/sEXTPROC1",
}
```

--------------------------------

### Configure OS Password Hash Authentication in go-ora

Source: https://github.com/sijms/go-ora/blob/master/README.md

Pass a user's password hash instead of the actual password for OS authentication. Supported keys are 'OS HASH', 'OS PassHash', or 'OS Password Hash'.

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

--------------------------------

### Define Client Language and Territory

Source: https://github.com/sijms/go-ora/blob/master/README.md

Specify the language and territory for server messages. Use 'language' and 'territory' options with appropriate string values.

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

--------------------------------

### Use Named Parameters

Source: https://github.com/sijms/go-ora/blob/master/README.md

Wrap parameters in `sql.Named` to use named parameters (e.g., `:pr1`). Order is not important, and it's useful when a value is used multiple times.

```golang
conn.Exec("SELECT * FROM table WHERE id = :id AND name = :name", sql.Named("id", 1), sql.Named("name", "test"))
```

--------------------------------

### Execute Query with VARCHAR Parameter

Source: https://github.com/sijms/go-ora/blob/master/README.md

Pass a string parameter as a standard VARCHAR type to the Exec function.

```go
_, err := conn.Exec(inputSql, "7586")
```

--------------------------------

### Configure Proxy User

Source: https://github.com/sijms/go-ora/blob/master/README.md

Specify a proxy client name using the 'proxy client name' option when connecting as a proxy user.

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

--------------------------------

### Implement Bulk Insert/Merge with go-ora ExecContext

Source: https://github.com/sijms/go-ora/blob/master/README.md

Perform bulk insert or merge operations using `ExecContext` by declaring SQL text and passing parameters as arrays. The number of rows affected is determined by the shortest array length. Named parameters are also supported.

```go
examples/merge
```

--------------------------------

### Configure Unix Socket IPC in go-ora

Source: https://github.com/sijms/go-ora/blob/master/README.md

Use this option when the server and client are on the same Linux machine. Specify the Unix socket path in urlOptions.

```golang
urlOptions := map[string]string{
	// change the value according to your machine
	"unix socket": "/usr/tmp/.oracle/sEXTPROC1"
}
```

--------------------------------

### Register and Use Oracle UDTs in Go

Source: https://context7.com/sijms/go-ora/llms.txt

Map Oracle UDTs to Go structs using the `udt` tag. Ensure types are registered with the driver before use, with nested types registered first.

```go
package main

import (
    "database/sql"
    "fmt"
    go_ora "github.com/sijms/go-ora/v2"
)

// Go struct mapped to Oracle UDT
type Address struct {
    Street  string `udt:"STREET"
    City    string `udt:"CITY"
    ZipCode string `udt:"ZIP_CODE"
}

type Person struct {
    ID      int64   `udt:"ID"
    Name    string `udt:"NAME"
    Address Address `udt:"ADDRESS" // nested UDT
}

func main() {
    connStr := go_ora.BuildUrl("localhost", 1521, "ORCLPDB1", "myuser", "mypassword", nil)
    db, err := sql.Open("oracle", connStr)
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Create Oracle types (run as schema owner)
    _, _ = db.Exec(`CREATE OR REPLACE TYPE ADDRESS_TYPE AS OBJECT (
        STREET VARCHAR2(100),
        CITY VARCHAR2(50),
        ZIP_CODE VARCHAR2(10)
    )`)

    _, _ = db.Exec(`CREATE OR REPLACE TYPE PERSON_TYPE AS OBJECT (
        ID NUMBER,
        NAME VARCHAR2(100),
        ADDRESS ADDRESS_TYPE
    )`)

    // Register types with driver - nested type first
    err = go_ora.RegisterType(db, "ADDRESS_TYPE", "", Address{})
    if err != nil {
        panic(err)
    }

    err = go_ora.RegisterType(db, "PERSON_TYPE", "", Person{})
    if err != nil {
        panic(err)
    }

    // Query returning UDT
    rows, err := db.Query(`SELECT PERSON_TYPE(1, 'John Doe',
        ADDRESS_TYPE('123 Main St', 'New York', '10001')) FROM DUAL`)
    if err != nil {
        panic(err)
    }
    defer rows.Close()

    for rows.Next() {
        var person Person
        err = rows.Scan(&person)
        if err != nil {
            panic(err)
        }
        fmt.Printf("Person: %s, Address: %s, %s %s\n",
            person.Name, person.Address.Street, person.Address.City, person.Address.ZipCode)
    }
}
```

--------------------------------

### Set Session Parameters with go-ora

Source: https://context7.com/sijms/go-ora/llms.txt

Configure NLS parameters like language and territory during connection string building or add/remove session parameters after establishing a connection using AddSessionParam and DelSessionParam.

```go
package main

import (
    "database/sql"
    "fmt"
    go_ora "github.com/sijms/go-ora/v2"
)

func main() {
    // Set NLS parameters via URL options
    urlOptions := map[string]string{
        "language":  "AMERICAN",
        "territory": "AMERICA",
    }
    connStr := go_ora.BuildUrl("localhost", 1521, "ORCLPDB1", "myuser", "mypassword", urlOptions)

    db, err := sql.Open("oracle", connStr)
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Add session parameter after connection
    err = go_ora.AddSessionParam(db, "NLS_DATE_FORMAT", "'YYYY-MM-DD HH24:MI:SS'")
    if err != nil {
        panic(err)
    }

    err = go_ora.AddSessionParam(db, "NLS_NUMERIC_CHARACTERS", "'.',''")
    if err != nil {
        panic(err)
    }

    // Query with formatted date
    var dateStr string
    db.QueryRow("SELECT SYSDATE FROM DUAL").Scan(&dateStr)
    fmt.Printf("Current date: %s\n", dateStr)

    // Remove session parameter
    go_ora.DelSessionParam(db, "NLS_DATE_FORMAT")
}
```

--------------------------------

### Query Rows from Table

Source: https://github.com/sijms/go-ora/blob/master/README.md

Use `Query` to fetch rows. Remember to close the rows and scan the data. Handles null values using `sql.NullString` and `sql.NullTime`.

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

--------------------------------

### Build JDBC Connection String with go-ora

Source: https://github.com/sijms/go-ora/blob/master/README.md

Use BuildJDBC to construct a connection string from a JDBC-style description. This function extracts server, ports, and protocol to build the connection table. SSL is automatically configured if specified in the connection string.

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