Golang Oracle Database Integration Essentials

To integrate your Go application with an Oracle database, you'll need to use the Oracle Database Driver for Go, also known as the Oracle Go Driver.

The Oracle Go Driver provides a simple way to connect to an Oracle database using the Go programming language. It's a native Go driver, which means it's written in Go and doesn't require any external dependencies.

To get started, you'll need to install the Oracle Go Driver using the Go get command. This will download and install the driver, making it available for use in your Go application.

The Oracle Go Driver supports a wide range of Oracle database features, including connections, statements, and result sets. It's a powerful tool for integrating your Go application with an Oracle database.

A fresh viewpoint: Golang vs Go

To connect to an Oracle database, you need to provide the server name or IP, port, service name, username, and password. This information can be passed to the driver using the `go_ora.BuildUrl` function.

The connection string can be in the format "server:port/service_name" or an URL like "oracle://user:passw@service_name". You can also use the `ConnectionParams` function to provide all possible options.

To use SSL connections, you need to pass the required URL options, such as "AUTH TYPE": "TCPS" and "SSL": "enable".

You can also connect to Oracle using OS user instead of Oracle user by passing empty username and password parameters to `BuildUrl`.

Here are some examples of connection strings:

Note that the `ConnectionParams` function redacts the password, so you should use `ConnectionParams.StringWithPassword()` instead.

To use heterogeneous pools, set `heterogeneousPool=1` and provide the username/password through `oracledb.ContextWithUserPassw`.

Readers also liked: Golang Use Cases

You can pass input CLOB and BLOB with any data size up to the data type limit in version 2.4.3.

With version 2.7.2, you can use a Go structure as an Oracle output parameter. Structure fields that support the driver.Valuer interface will be passed as is.

To use a Go structure as an Oracle output parameter, you need to pass the structure as a pointer and specify the tag direction as "output" or "inout". Some types, like strings, may require the "size" attribute.

Broaden your view: Golang Go

You can query to a struct that contains basic types like int, float, string, and datetime, or any types that implement the sql.Scanner interface starting from version 2.4.20.

The go_ora.BuildJDBC function was added in version 2.4.20.

You can map a RefCursor to sql.Rows for select/scan operations with version 2.6.16. This is useful for working with large datasets.

Here's a summary of the data types you can use with the Go structure as an Oracle output parameter:

Data assigned back to structure fields after execution will set the field value to reflect.Zero if a null value is read.

Mapping a RefCursor to Rows is a powerful feature that allows you to work with data in a more flexible way.

In version 2.6.16, the go_ora library introduced the ability to map a RefCursor to sql.Rows, which works with select/scan.

This means you can use RefCursor as if it were a regular query result set. The complete code for this feature is available in the examples/refcursor_to_rows/main.go file.

To make the most of this feature, keep in mind that mapping RefCursor to sql.Rows will work with select/scan.

You can pass input CLOB and BLOB with any data size up to data type limit. This is a significant improvement, especially for large datasets.

In version 2.4.3, the input parameter CLOB and BLOB can handle large data sizes. This means you can work with bigger datasets without worrying about size constraints.

To determine the connection's overall lifetime, you can use URL options. This helps you manage your connections more efficiently.

Here are some key takeaways for working with CLOB and BLOB:

Working with User Defined Types (UDT) as input parameters can be a bit tricky, but it's actually quite straightforward once you understand the basics.

In version 2.2.23, support for UDT as input parameters was added. This means you can now pass UDT as input parameters to your SQL queries.

To use UDT as input parameters, you'll need to define a custom type in the Oracle database. This is a requirement for using UDT in this way.

If you're using a UDT, you'll also need to use the go_ora.Out struct with a Size member to set the output parameter size. This is a new feature introduced in version 2.2.23.

Here are the supported types for input parameters:

Note that the full list of supported types is not provided in the article section, but you can refer to the article section for more information.

To connect to an Oracle database, you need to provide the server name or IP, port, service name, username, and password.

You can use the go_ora.BuildUrl function to build the connection string, or pass a URL option with a JDBC string to collect the server and service name from it.

To execute SQL queries, you can use the Query function for querying rows and the Exec function for DML/DDL and PL/SQL.

You can pass input parameters as defined by the database/sql package, including int64, float64, string, time.Time, and any type that supports the Valuer interface.

Here are some specific data types that can be passed as input parameters:

You can retrieve data from stored procedures using cursors. To do this, use the ExecContext function and pass an interface{} or a database/sql/driver.Rows as the sql.Out destination.

When executing a stored procedure, you can use the driver.Rows interface to iterate over the results. Alternatively, you can transform the driver.Rows interface into a regular *sql.Rows with oracledb.WrapRows. This allows you to work with the results in a more familiar way.

For example, if you have a stored procedure that returns a cursor, you can use the following code to retrieve the data:

```sql

result, err := db.ExecContext(ctx, "BEGIN my_procedure; END;")

if err != nil {

// handle error

}

rows, err := oracledb.WrapRows(result)

if err != nil {

// handle error

}

defer rows.Close()

```

This code executes the stored procedure and retrieves the cursor using the WrapRows function. The result is a *sql.Rows object that can be iterated over to retrieve the data.

Note that since Go 1.12, you can also use the Scan function to directly scan the cursor into a *sql.Rows object. This simplifies the code and eliminates the need to use the WrapRows function.

Discover more: Golang Source Code

Execute SQL is a crucial part of database operations, and it's essential to understand how to execute queries efficiently.

You can use the `Query` function to execute queries that retrieve rows and the `Exec` function for DML/DDL and PL/SQL operations. The `Exec` function is used for executing statements that modify the database, such as inserting, updating, or deleting data.

The `Exec` function takes input parameters, which can be of various types, including `int64`, `float64`, `string`, `time.Time`, and any type that implements the `Valuer` interface. The `Valuer` interface is used to convert a value to a database-specific value.

You can also pass a structure parameter to SQL in certain situations, such as when the structure implements the `Valuer` interface or when it's an Oracle user-defined type (UDT). Additionally, you can pass a struct with a `db` tag, which allows you to use named parameters.

When passing a struct with a `db` tag, you should pass at least the name of the parameter to use this feature. You can also specify the type of the field, such as `timestamp`, to ensure that the correct type is used.

Intriguing read: Golang Template Struct

Here's a list of the types that can be used as input parameters:

Passing array parameters is also useful in certain situations, such as multiple inserts or associative arrays. You can pass an array of tagged structures to do the same thing.

Bulk insert/merge operations are activated when you pass all parameters as arrays of the same size. This can improve performance and efficiency.

From version 2.8.24, bulk update/delete operations are also supported. This feature allows you to update or delete multiple rows in a single operation.

To use this feature, you need to create an Oracle type and map it to a Go struct type with the `udt` tag. You then need to register the type with the `go_ora.RegisterType` function and pass it as a parameter to the `Exec` function.

Here's a list of the types that can be used in bulk operations:

Vector data can be stored in a field called `Vector.Data`, which is an interface type. You can cast this interface to a specific type, such as `[]uint8`, `[]float32`, or `[]float64`.

The `go_ora.WrapRefCursor` function can be used to convert a `*RefCursor` to a `*sql.Rows` started from version 2.7.17.

You can also use the `go_ora.AddSessionParameter` function to add session parameters to the database. This function takes the database connection and the key-value pair as arguments.

On a similar theme: Golang Func Type

Bulk Copy via DirectPath (Experimental) is a feature that allows you to insert large amounts of rows into a table or view. It uses Oracle's Direct Path feature, which is still experimental.

This feature is not implemented for certain types, but you can find more information about using it by returning to the bulk_copy example.

To use Bulk Copy via DirectPath, you'll need to pass all parameters as arrays of the same size. This can be done by passing an array of tagged structures, which is useful for Multiple insert/merge and Associative Array scenarios.

Here are some scenarios where Bulk Copy via DirectPath is useful:

Note that this feature is still experimental, so you may encounter some issues or limitations.

The 2.8.6 version of our database operations tool is a significant update with several key features.

This version adds support for nested user-defined type (UDT) arrays, making it easier to work with complex data structures.

A testing file called TestIssue/nested_udt_array_test.go has been added, showcasing a 2-level nesting scenario.

This update also fixes an issue related to dates with time zones, which can occur on some Oracle servers.

Now, you can correctly read Oracle dates with local time zones as output columns or parameters.

More testing has been done for Oracle date/time types, allowing you to pass time.Time{} as input/output for these types, except in certain cases.

A testing file called TestIssue/time_test.go has been added to demonstrate this new functionality.

A fresh viewpoint: Oracle Azure Licensing

The 2.7.18 update brings some exciting new features to database operations. A new function called go_ora.NewDriver has been added, which allows for more flexibility in connecting to databases.

This update also includes the go_ora.NewConnector function, making it easier to establish connections. The go_ora.SetStringConverter function has been added, which enables custom conversion for unsupported character sets.

This function takes a *sql.DB object and an IStringConverter interface as parameters. If you want to use the default converter, you can pass nil. The update also adds support for charset ID 846.

Here's a quick rundown of the new functions and their parameters:

These updates are sure to make database operations even more efficient and convenient.

To connect to an Oracle Database with an Oracle Wallet, you need to specify the path to the directory that contains the file cwallet.sso. This file is typically provided in a zip-archive, so you'll need to extract it and move it to a location accessible from your Go application.

The connection details for the autonomous database include the database service name, which can be found in the tnsnames.ora file in the wallet zip file or on the ATP DB Connection page in the OCI Console. The server property, available as the host in these locations, is also required.

To configure the go-ora driver, you need to include the wallet location in the connection string and configure the secure communication protocol. This involves replacing the localDB reference in the call to GetSqlDBWithPureDriver with autonomousDB.

The godror driver uses a slightly different setup for working with an Oracle Wallet, requiring changes to the function GetSqlDBWithGoDrOrDriver in the godror-based-oracle-database-client.go file.

Intriguing read: Oracle Cloud

Connecting to an Oracle database using Go is a straightforward process. You'll need to specify the server name or IP, port, service name, username, and password to establish a connection.

To connect to a database, you can use the go-ora driver, which provides a simple way to build a connection string. The go-ora.BuildUrl function takes in several parameters, including the server name, port, service name, username, and password. You can also pass in additional options, such as a wallet location, to enable secure communication.

Using a wallet location requires extracting the cwallet.sso file from the wallet zip file and moving it to a location accessible by the Go application. The wallet location is then passed to the driver, which uses it to establish a secure connection to the database.

The connection details for an autonomous database are similar to those for a local database, with the database service name and server property available in the tnsnames.ora file or the OCI Console. The properties are gathered and added to a map definition in the oracle-database-client-app.go file.

Here are the parameters required to connect to a database using the go-ora driver:

Alternatively, you can use the go-ora.BuildJDBC function to build a JDBC string, which can be used to connect to the database. This function collects the server and service name from the JDBC string.

To connect to an Oracle database using Go, you'll need to specify the required parameters and use a driver such as go-ora to establish a connection. This can be done using the go-ora.BuildUrl function or the go-ora.BuildJDBC function, depending on your specific needs.

Oracle Linux offers support for Go, but the way you install it varies depending on the version of Oracle Linux you're using.

For Oracle Linux 8 and 9, Go packages are provided via AppStream modules and are covered under Oracle Linux support.

The support timelines for these packages can be found in the Managing Software in Oracle Linux manual.

Recommended read: Golang Support

To install Go on Oracle Linux 7, you need to install oracle-golang-release-el7 first to configure yum settings.

This step is crucial because it allows you to install the most recent Go version listed.

You can install oracle-golang-release-el7 and Go with the following commands: $ sudo yum install -y oracle-golang-release-el7 and $ sudo yum install -y golang.

Recommended read: Yum Install Golang

Error handling is crucial in Golang, and it's essential to understand how to handle errors properly. In Golang, errors are values that can be returned from functions to indicate that something went wrong.

The `errors` package in Golang provides a way to create and handle errors. This package includes a function called `New` that allows you to create a new error value. For example, `err := errors.New("something went wrong")`.

To handle errors in Golang, you can use a `defer` statement to catch and handle errors. This is especially useful when working with databases or file operations. The `defer` statement allows you to execute a function after the surrounding function has completed.

Expand your knowledge: Golang Create Error

You can install Go on various Oracle Linux versions, including Oracle Linux 9 and 8.

Go version 1.23 is available for Oracle Linux 9 on both x86_64 and aarch64 architectures.

Go version 1.22 is also available for Oracle Linux 9 on both x86_64 and aarch64 architectures.

Go version 1.21 is available for Oracle Linux 9 on both x86_64 and aarch64 architectures, as well as for Oracle Linux 8 on x86_64 and aarch64 architectures.

You can install Go version 1.20 on Oracle Linux 9, Oracle Linux 8, and Oracle Linux 7.

Go version 1.19 is available for Oracle Linux 9, Oracle Linux 8, and Oracle Linux 7.

Oracle Linux 7 offers Go versions 1.18 and 1.17, while Oracle Linux 8 offers Go versions 1.18 and 1.17.

In version 2.7.17, a significant update was made to enhance error handling and performance.

The WrapRefCursor feature was added, allowing the conversion of *RefCursor into *sql.Rows.

This update provides better protection against block read/write operations by activating a global timeout value if no timeout context is specified.

Suggestion: Golang Mod Update

The default timeout value is set to 120 seconds, but you can change it by passing one of the following: ["TIMEOUT", "CONNECT TIMEOUT", "CONNECTION TIMEOUT"].

This feature is particularly useful when dealing with long-running operations that may exceed the default timeout period.

Here's a summary of the timeout options:

This update demonstrates the importance of considering timeout values in your application to prevent potential issues.

In version 2.7.7, a significant update was made to support CLOB/BLOB in UDT.

This update allows for more flexibility when working with user-defined types (UDT) in databases.

The go-ora.RegisterType function was added to enable the use of UDT with the database/sql package.

The function now accepts an arrayTypeName input, which can be empty, to support UDT arrays.

Examples of how to use UDT arrays can be found in the examples/udt_array directory.

The parameter encode/decode was completely re-coded from scratch.

The uint64 truncation issue was fixed in this version.

Additionally, some other issues were addressed in the update.

In version 2.1.20, a new data type was added: go_ora.NVarChar. This allows for passing string parameters in two ways.

With this update, developers can now handle errors more effectively in their applications. The new data type is a significant improvement over previous versions.

Here are the key changes in version 2.1.20:

This new data type provides more flexibility when working with string parameters, making it easier to handle different types of data.