Connect to Oracle Database using sql.Open("godror", dataSourceName)
where
dataSourceName
contains options such as the user credentials, the database
connection string, and other configuration settings.
It should be a logfmt-encoded parameter list.
For example:
db, err := sql.Open("godror", `user="scott" password="tiger" connectString="dbhost:1521/orclpdb1"
poolSessionTimeout=42s configDir="/tmp/admin"
heterogeneousPool=false standaloneConnection=false`)
Other connection and driver options can also be used:
db, err := sql.Open("godror", `user="scott" password="tiger"
connectString="dbhost:1521/orclpdb1?connect_timeout=2"
poolSessionTimeout=42s configDir="/opt/oracle/configdir"
heterogeneousPool=false standaloneConnection=false
timezone="Europe/Berlin"`)
[!TIP] On MacOS, when setting
(DY)_LD_LIBRARY_PATH
for the Oracle Instant Client lib files does not work (Variables LD_LIBRARY_PATH / DYLD_LIBRARY_PATH are not passed to the environment of a child process on macOS if System Integrity Protect (SIP) is enabled.), you can put that into the connection string’slibDir
parameter.See this issue.
All godror parameters should also be logfmt-ted.
You can provide all possible options with ConnectionParams
:
var P godror.ConnectionParams
P.Username, P.Password = "scott", godror.NewPassword("tiger")
P.ConnectString = "dbhost:1521/orclpdb1?connect_timeout=2"
P.SessionTimeout = 42 * time.Second
P.SetSessionParamOnInit("NLS_NUMERIC_CHARACTERS", ",.")
P.SetSessionParamOnInit("NLS_LANGUAGE", "FRENCH")
P.Timezone = time.Local
fmt.Println(P.StringWithPassword())
db := sql.OpenDB(godror.NewConnector(P))
Or if you really want to build it “by hand”, use connstr.AppendLogfmt
:
var buf strings.Builder
connstr.AppendLogfmt(&buf, "user", "scott")
connstr.AppendLogfmt(&buf, "password", "tiger")
connstr.AppendLogfmt(&buf, "connectString", "dbhost:1521/orclpdb1?connect_timeout=2")
connstr.AppendLogfmt(&buf, "timezone", "Europe/Berlin")
fmt.Println(buf.String())
Note ConnectionParams.String()
redacts the password (for security, to avoid
logging it - see https://github.com/go-goracle/goracle/issues/79). If you
need the password, then use ConnectionParams.StringWithPassword()
.
The sql.Open()
data source name connectString
parameter or
ConnectionParams
field ConnectString
can be one of:
An Easy Connect String
Easy Connect strings
like the one shown above are most convenient to use. The syntax allows a
number of options to be set without requiring an external tnsnames.ora
configuration file. A more complex example usable with Oracle Client
libraries 19c or later is:
connectString="tcps://salesserver1:1521/sales.us.example.com?ssl_server_cert_dn=\"cn=sales,cn=Oracle Context Server,dc=us,dc=example,dc=com\"&sdu=8128&connect_timeout=60"
Common options are connect_timeout
to return an error if connection takes
too long, and expire_time
to make sure idle connections are not closed by
firewalls.
Note that connect_timeout
requires at least 19c client.
The technical article Oracle Database 19c Easy Connect Plus Configurable Database Connection Syntax contains more information.
An Oracle Net Connect Descriptor String
Full descriptors can be used, such as
connectString="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orclpdb1)))"
An Oracle Net Service Name
Connect Descriptor Strings are commonly stored in a tnsnames.ora
file and
associated with a Net Service Name. This name can be used directly for
connectString
. For example, given a tnsnames.ora
file with the following
contents:
ORCLPDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbhost.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclpdb1)
)
)
then you could connect using connectString=ORCLPDB1
See “Optional Oracle Net Configuration Files” below.
Optional Oracle Net configuration files are used by the Oracle Client libraries
during the first call to sql.Open
. The directory containing the files can be
specified in the sql.Open()
data source name with the configDir
option.
The common files are:
tnsnames.ora
: A configuration file that defines databases aliases for
establishing connections.
sqlnet.ora
: A profile configuration file that may contain information on
features such as connection failover, network encryption, logging, and
tracing. See Oracle Net Services
Reference
for more information.
The files should be in a directory accessible to godror, not on the database server host.
If the configDir
connection option is not used, then a search heuristic is
used to find the directory containing the files.
The search includes:
$TNS_ADMIN
/opt/oracle/instantclient_19_8/network/admin
if Instant Client is in /opt/oracle/instantclient_19_8
./usr/lib/oracle/19.8/client64/lib/network/admin
if Oracle 19.8 Instant Client RPMs are used on Linux.$ORACLE_HOME/network/admin
if godror is using libraries from a database installation.See https://blogs.oracle.com/opal/how-connect-to-oracle-autonomous-cloud-databases for ADB-specific guide.
Set standaloneConnection=0
- this is the default. The old advice of setting
db.SetMaxIdleConns(0)
are obsolete with Go 1.14.6. It does no harm, but the
revised connection pooling (synchronous ResetSession before pooling the
connection) eliminates the need for it.
To use heterogeneous pools, set heterogeneousPool=1
and provide the username
and password through godror.ContextWithUserPassw
or godror.ContextWithParams
.
WARNING if you cannot use Go 1.14.6 or newer, then either set standaloneConnection=1
or
disable Go connection pooling by db.SetMaxIdleConns(0)
- they do not work well together, resulting in stalls!
For backward compatibility, you can still provide ANYTHING as the dataSourceName, if it is one line, and is not logfmt-encoded, then it will be treated as a connectString. So
scott@tcps://salesserver1:1521/sales.us.example.com?ssl_server_cert_dn="cn=sales,cn=Oracle Context Server,dc=us,dc=example,dc=com"&sdu=8128&connect_timeout=60
scott/tiger@salesserver1/sales.us.example.com
oracle://scott:tiger@salesserver1/sales.us.example.com&poolSessionTimeout=42s
scott@tcps://salesserver1:1521/sales.us.example.com?ssl_server_cert_dn="cn=sales,cn=Oracle Context Server,dc=us,dc=example,dc=com"&sdu=8128&connect_timeout=60
works, too.