Package ora implements an Oracle database driver.
### Golang Oracle Database Driver ###
#### TL;DR; just use it ####
Call stored procedure with OUT parameters:
An Oracle database may be accessed through the database/sql(http://golang.org/pkg/database/sql) package or through the
ora package directly. database/sql offers connection pooling, thread safety,
a consistent API to multiple database technologies and a common set of Go types.
The ora package offers additional features including pointers, slices, nullable
types, numerics of various sizes, Oracle-specific types, Go return type configuration,
and Oracle abstractions such as environment, server and session.
The ora package is written with the Oracle Call Interface (OCI) C-language
libraries provided by Oracle. The OCI libraries are a standard for client
application communication and driver communication with Oracle databases.
The ora package has been verified to work with:
* Oracle Standard 11g (184.108.40.206.0), Linux x86_64 (RHEL6)
* Oracle Enterprise 12c (220.127.116.11.0), Windows 8.1 and AMD64.
* [Data Types](https://github.com/rana/ora#data-types)
* [SQL Placeholder Syntax](https://github.com/rana/ora#sql-placeholder-syntax)
* [Working With The Sql Package](https://github.com/rana/ora#working-with-the-sql-package)
* [Working With The Oracle Package Directly](https://github.com/rana/ora#working-with-the-oracle-package-directly)
* [Test Database Setup](https://github.com/rana/ora#test-database-setup)
* [API Reference](http://godoc.org/github.com/rana/ora#pkg-index)
Minimum requirements are Go 1.3 with CGO enabled, a GCC C compiler, and
Oracle 11g (18.104.22.168.0) or Oracle Instant Client (22.214.171.124.0).
Install Oracle or Oracle Instant Client.
Copy the [oci8.pc](contrib/oci8.pc) from the `contrib` folder
(or the one for your system, maybe tailored to your specific locations) to a folder
in `$PKG_CONFIG_PATH` or a system folder, such as
The ora package has no external Go dependencies and is available on GitHub and
*WARNING*: If you have Oracle Instant Client 11.2, you'll need to add "=lnnz11"
to the list of linked libs!
Otherwise, you may encounter "undefined reference to `nzosSCSP_SetCertSelectionParams' "
Oracle Instant Client 12.1 does not need this.
The ora package supports all built-in Oracle data types. The supported Oracle
built-in data types are NUMBER, BINARY_DOUBLE, BINARY_FLOAT, FLOAT, DATE,
TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE,
INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND, CHAR, NCHAR, VARCHAR, VARCHAR2,
NVARCHAR2, LONG, CLOB, NCLOB, BLOB, LONG RAW, RAW, ROWID and BFILE.
SYS_REFCURSOR is also supported.
Oracle does not provide a built-in boolean type. Oracle provides a single-byte
character type. A common practice is to define two single-byte characters which
represent true and false. The ora package adopts this approach. The oracle
package associates a Go bool value to a Go rune and sends and receives the rune
to a CHAR(1 BYTE) column or CHAR(1 CHAR) column.
The default false rune is zero '0'. The default true rune is one '1'. The bool rune
association may be configured or disabled when directly using the ora package
but not with the database/sql package.
Within a SQL string a placeholder may be specified to indicate where a Go variable
is placed. The SQL placeholder is an Oracle identifier, from 1 to 30
characters, prefixed with a colon (:). For example:
Placeholders within a SQL statement are bound by position. The actual name is not
used by the ora package driver e.g., placeholder names :c1, :1, or :xyz are
The `database/sql` package provides a LastInsertId method to return the
last inserted row's id. Oracle does not provide such functionality,
but if you append `... RETURNING col /*LastInsertId*/` to your SQL, then it will
be presented as LastInsertId. Note that you have to mark with a `/*LastInsertId*/`
(case insensitive) your `RETURNING` part, to allow ora to return the last column
as `LastInsertId()`. That column must fit in `int64`, though!
You may access an Oracle database through the database/sql package. The database/sql
package offers a consistent API across different databases, connection
pooling, thread safety and a set of common Go types. database/sql makes working
with Oracle straight-forward.
The ora package implements interfaces in the database/sql/driver package enabling
database/sql to communicate with an Oracle database. Using database/sql
ensures you never have to call the ora package directly.
When using database/sql, the mapping between Go types and Oracle types may be
changed slightly. The database/sql package has strict expectations on Go return
types. The Go-to-Oracle type mapping for database/sql is:
The "ora" driver is automatically registered for use with sql.Open, but you can
call ora.SetCfg to set the used configuration options including
statement configuration and Rset configuration.
When configuring the driver for use with database/sql, keep in mind that
database/sql has strict Go type-to-Oracle type mapping expectations.
The ora package allows programming with pointers, slices, nullable types,
numerics of various sizes, Oracle-specific types, Go return type configuration, and
Oracle abstractions such as environment, server and session. When working with the
ora package directly, the API is slightly different than database/sql.
When using the ora package directly, the mapping between Go types and Oracle types
may be changed. The Go-to-Oracle type mapping for the ora package is:
An example of using the ora package directly:
Pointers may be used to capture out-bound values from a SQL statement such as
an insert or stored procedure call. For example, a numeric pointer captures an
A string pointer captures an out parameter from a stored procedure:
Slices may be used to insert multiple records with a single insert statement:
The ora package provides nullable Go types to support DML operations such as
insert and select. The nullable Go types provided by the ora package are Int64,
Int32, Int16, Int8, Uint64, Uint32, Uint16, Uint8, Float64, Float32, Time,
IntervalYM, IntervalDS, String, Bool, Binary and Bfile. For example, you may insert
nullable Strings and select nullable Strings:
The `Stmt.Prep` method is variadic accepting zero or more `GoColumnType`
which define a Go return type for a select-list column. For example, a Prep
call can be configured to return an int64 and a nullable Int64 from the same
Go numerics of various sizes are supported in DML operations. The ora package
supports int64, int32, int16, int8, uint64, uint32, uint16, uint8, float64 and
float32. For example, you may insert a uint16 and select numerics of various sizes:
If a non-nullable type is defined for a nullable column returning null, the Go
type's zero value is returned.
GoColumnTypes defined by the ora package are:
When Stmt.Prep doesn't receive a GoColumnType, or receives an incorrect GoColumnType,
the default value defined in RsetCfg is used.
EnvCfg, SrvCfg, SesCfg, StmtCfg and RsetCfg are the main configuration structs.
EnvCfg configures aspects of an Env. SrvCfg configures aspects of a Srv. SesCfg
configures aspects of a Ses. StmtCfg configures aspects of a Stmt. RsetCfg
configures aspects of Rset. StmtCfg and RsetCfg have the most options to
configure. RsetCfg defines the default mapping between an Oracle select-list
column and a Go type. StmtCfg may be set in an EnvCfg, SrvCfg, SesCfg and StmtCfg.
RsetCfg may be set in a Stmt.
EnvCfg.StmtCfg, SrvCfg.StmtCfg, SesCfg.StmtCfg may optionally be specified to
configure a statement. If StmtCfg isn't specified default values are applied.
EnvCfg.StmtCfg, SrvCfg.StmtCfg, SesCfg.StmtCfg cascade to new descendent structs.
When ora.OpenEnv() is called a specified EnvCfg is used or a default EnvCfg is
created. Creating a Srv with env.OpenSrv() will use SrvCfg.StmtCfg if
it is specified; otherwise, EnvCfg.StmtCfg is copied by value to SrvCfg.StmtCfg.
Creating a Ses with srv.OpenSes() will use SesCfg.StmtCfg if it is specified;
otherwise, SrvCfg.StmtCfg is copied by value to SesCfg.StmtCfg. Creating a Stmt
with ses.Prep() will use SesCfg.StmtCfg if it is specified; otherwise, a new
StmtCfg with default values is set on the Stmt. Call Stmt.Cfg() to change a Stmt's
An Env may contain multiple Srv. A Srv may contain multiple Ses. A Ses may
contain multiple Stmt. A Stmt may contain multiple Rset.
Setting a RsetCfg on a StmtCfg does not cascade through descendent structs.
Configuration of Stmt.Cfg takes effect prior to calls to Stmt.Exe and
Stmt.Qry; consequently, any updates to Stmt.Cfg after a call to Stmt.Exe
or Stmt.Qry are not observed.
One configuration scenario may be to set a server's select statements to return
nullable Go types by default:
Another scenario may be to configure the runes mapped to bool values:
Oracle-specific types offered by the ora package are ora.Rset, ora.IntervalYM,
ora.IntervalDS, ora.Raw, ora.Lob and ora.Bfile. ora.Rset represents an Oracle
SYS_REFCURSOR. ora.IntervalYM represents an Oracle INTERVAL YEAR TO MONTH.
ora.IntervalDS represents an Oracle INTERVAL DAY TO SECOND. ora.Raw represents
an Oracle RAW or LONG RAW. ora.Lob may represent an Oracle BLOB or Oracle CLOB.
And ora.Bfile represents an Oracle BFILE. ROWID columns are returned as strings and
don't have a unique Go type.
The default for SELECTing [BC]LOB columns is a safe Bin or S,
which means all the contents of the LOB is slurped into memory and returned
as a byte or string.
The DefaultLOBFetchLen says LOBs are prefetched only a minimal way, to minimize
extra memory usage - you can override this using
If you want more control, you can use ora.L in Prep, Qry or
`ses.SetCfg(ses.Cfg().SetBlob(ora.L))`. But keep in mind that Oracle restricts
the use of LOBs: it is forbidden to do ANYTHING while reading the LOB!
No another query, no exec, no close of the Rset - even *advance* to the next record
in the result set is forbidden!
Failing to adhere these rules results in "Invalid handle" and ORA-03127 errors.
You cannot start reading another LOB till you haven't finished reading the previous
LOB, not even in the same row! Failing this results in ORA-24804!
For examples, see [z_lob_test.go](z_lob_test.go).
Rset is used to obtain Go values from a SQL select statement. Methods Rset.Next,
Rset.NextRow, and Rset.Len are available. Fields Rset.Row, Rset.Err,
Rset.Index, and Rset.ColumnNames are also available. The Next method attempts to
load data from an Oracle buffer into Row, returning true when successful. When no data is available,
or if an error occurs, Next returns false setting Row to nil. Any error in Next is assigned to Err.
Calling Next increments Index and method Len returns the total number of rows processed. The NextRow
method is convenient for returning a single row. NextRow calls Next and returns Row.
ColumnNames returns the names of columns defined by the SQL select statement.
Rset has two usages. Rset may be returned from Stmt.Qry when prepared with a SQL select
Or, *Rset may be passed to Stmt.Exe when prepared with a stored procedure accepting
an OUT SYS_REFCURSOR parameter:
Stored procedures with multiple OUT SYS_REFCURSOR parameters enable a single Exe call to obtain
The types of values assigned to Row may be configured in StmtCfg.Rset. For configuration
to take effect, assign StmtCfg.Rset prior to calling Stmt.Qry or Stmt.Exe.
Rset prefetching may be controlled by StmtCfg.PrefetchRowCount and
StmtCfg.PrefetchMemorySize. PrefetchRowCount works in coordination with
PrefetchMemorySize. When PrefetchRowCount is set to zero only PrefetchMemorySize is used;
otherwise, the minimum of PrefetchRowCount and PrefetchMemorySize is used.
The default uses a PrefetchMemorySize of 134MB.
Opening and closing Rsets is managed internally. Rset does not have an Open
method or Close method.
IntervalYM may be be inserted and selected:
IntervalDS may be be inserted and selected:
Transactions on an Oracle server are supported. DML statements auto-commit
unless a transaction has started:
Ses.PrepAndExe, Ses.PrepAndQry, Ses.Ins, Ses.Upd, and Ses.Sel are convenient
Ses.PrepAndExe offers a convenient one-line call to Ses.Prep and Stmt.Exe.
Ses.PrepAndQry offers a convenient one-line call to Ses.Prep and Stmt.Qry.
Ses.Ins composes, prepares and executes a sql INSERT statement. Ses.Ins is useful
when you have to create and maintain a simple INSERT statement with a long
list of columns. As table columns are added and dropped over the lifetime of
a table Ses.Ins is easy to read and revise.
Ses.Upd composes, prepares and executes a sql UPDATE statement. Ses.Upd is useful
when you have to create and maintain a simple UPDATE statement with a long list
of columns. As table columns are added and dropped over the lifetime of
a table Ses.Upd is easy to read and revise.
Ses.Sel composes, prepares and queries a sql SELECT statement. Ses.Sel is useful
when you have to create and maintain a simple SELECT statement with a long
list of columns that have non-default GoColumnTypes. As table columns are added
and dropped over the lifetime of a table Ses.Sel is easy to read and revise.
The Ses.Ping method checks whether the client's connection to an
Oracle server is valid. A call to Ping requires an open Ses. Ping
will return a nil error when the connection is fine:
The Srv.Version method is available to obtain the Oracle server version. A call
to Version requires an open Ses:
Further code examples are available in the [example file](https://github.com/rana/ora/blob/master/z_example_test.go), test files and [samples folder](https://github.com/rana/ora/tree/master/samples).
The ora package provides a simple ora.Logger interface for logging. Logging is
disabled by default. Specify one of three optional built-in logging packages to
enable logging; or, use your own logging package.
ora.Cfg().Log offers various options to enable or disable logging of specific
ora driver methods. For example:
To use the standard Go log package:
which produces a sample log of:
Messages are prefixed with 'ORA I' for information or 'ORA E'
for an error. The log package is configured to write to os.Stderr by default.
Use the ora/lg.Std type to configure an alternative io.Writer.
To use the glog package:
which produces a sample log of:
To use the log15 package:
which produces a sample log of:
See https://github.com/rana/ora/tree/master/samples/lg15/main.go for sample
code which uses the log15 package.
Tests are available and require some setup. Setup varies depending on whether
the Oracle server is configured as a container database or non-container database.
It's simpler to setup a non-container database. An example for each setup is
Non-container test database setup steps:
Container test database setup steps:
Some helpful SQL maintenance statements:
Run the tests.
database/sql method Stmt.QueryRow is not supported.
Go 1.6 introduced stricter cgo (call C from Go) rules, and introduced runtime checks.
This is good, as the possibility of C code corrupting Go code is almost completely eliminated,
but it also means a severe call overhead grow.
this can be 22x the go 1.5.3 call time!
So if you need performance more than correctness, start your programs with
"GODEBUG=cgocheck=0" environment setting.
Copyright 2017 Rana Ian, Tamás Gulácsi. All rights reserved.
Use of this source code is governed by The MIT License
found in the accompanying LICENSE file.