Python is awesome. I like Python very much, but there are some cases where Python’s performance is just not enough. What other options do we have?
Well, there’s for example GoLang
Problem with GoLang is that there is not a lot on the Internet about using it with Oracle Databases. Let me show you some basic steps for configuring the environment.
The first step is to install GoLang – instructions can be found in here: https://golang.org/doc/install
To connect to Oracle, you need Oracle Client (it can be Oracle Instant Client).
I will configure my environment on my MAC and use Oracle Instant Client for MAC
piclerick:~ inter$ mkdir oracle
piclerick:~ inter$ cd oracle
piclerick:oracle inter$ ls
piclerick:oracle inter$ unzip ~/Downloads/instantclient-basic-macos.x64-18.1.0.0.0.zip
Archive: /Users/inter/Downloads/instantclient-basic-macos.x64-18.1.0.0.0.zip
inflating: instantclient_18_1/BASIC_README
inflating: instantclient_18_1/adrci
inflating: instantclient_18_1/genezi
linking: instantclient_18_1/libclntsh.dylib -> libclntsh.dylib.18.1
inflating: instantclient_18_1/libclntsh.dylib.18.1
inflating: instantclient_18_1/libclntshcore.dylib.18.1
inflating: instantclient_18_1/libnnz18.dylib
linking: instantclient_18_1/libocci.dylib -> libocci.dylib.18.1
inflating: instantclient_18_1/libocci.dylib.18.1
inflating: instantclient_18_1/libociei.dylib
inflating: instantclient_18_1/libocijdbc18.dylib
inflating: instantclient_18_1/libons.dylib
inflating: instantclient_18_1/liboramysql18.dylib
creating: instantclient_18_1/network/
inflating: instantclient_18_1/ojdbc8.jar
inflating: instantclient_18_1/ucp.jar
inflating: instantclient_18_1/uidrvci
inflating: instantclient_18_1/xstreams.jar
creating: instantclient_18_1/network/admin/
inflating: instantclient_18_1/network/admin/README
finishing deferred symbolic links:
instantclient_18_1/libclntsh.dylib -> libclntsh.dylib.18.1
instantclient_18_1/libocci.dylib -> libocci.dylib.18.1
piclerick:oracle inter$ unzip ~/Downloads/instantclient-sqlplus-macos.x64-18.1.0.0.0.zip
Archive: /Users/inter/Downloads/instantclient-sqlplus-macos.x64-18.1.0.0.0.zip
inflating: instantclient_18_1/SQLPLUS_README
inflating: instantclient_18_1/glogin.sql
inflating: instantclient_18_1/libsqlplus.dylib
inflating: instantclient_18_1/libsqlplusic.dylib
inflating: instantclient_18_1/sqlplus
piclerick:oracle inter$ unzip ~/Downloads/instantclient-tools-macos.x64-18.1.0.0.0.zip
Archive: /Users/inter/Downloads/instantclient-tools-macos.x64-18.1.0.0.0.zip
inflating: instantclient_18_1/TOOLS_README
inflating: instantclient_18_1/exp
inflating: instantclient_18_1/expdp
inflating: instantclient_18_1/imp
inflating: instantclient_18_1/impdp
inflating: instantclient_18_1/libnfsodm18.dylib
inflating: instantclient_18_1/sqlldr
inflating: instantclient_18_1/wrc
piclerick:oracle inter$ unzip ~/Downloads/instantclient-sdk-macos.x64-18.1.0.0.0-2.zip
Archive: /Users/inter/Downloads/instantclient-sdk-macos.x64-18.1.0.0.0-2.zip
creating: instantclient_18_1/sdk/
creating: instantclient_18_1/sdk/admin/
inflating: instantclient_18_1/sdk/admin/oraaccess.xsd
creating: instantclient_18_1/sdk/demo/
inflating: instantclient_18_1/sdk/demo/cdemo81.c
inflating: instantclient_18_1/sdk/demo/demo.mk
inflating: instantclient_18_1/sdk/demo/occidemo.sql
inflating: instantclient_18_1/sdk/demo/occidemod.sql
inflating: instantclient_18_1/sdk/demo/occidml.cpp
inflating: instantclient_18_1/sdk/demo/occiobj.cpp
inflating: instantclient_18_1/sdk/demo/occiobj.typ
inflating: instantclient_18_1/sdk/demo/oraaccess.xml
inflating: instantclient_18_1/sdk/demo/setuporamysql.sh
creating: instantclient_18_1/sdk/include/
inflating: instantclient_18_1/sdk/include/ldap.h
inflating: instantclient_18_1/sdk/include/nzerror.h
inflating: instantclient_18_1/sdk/include/nzt.h
inflating: instantclient_18_1/sdk/include/occi.h
inflating: instantclient_18_1/sdk/include/occiAQ.h
inflating: instantclient_18_1/sdk/include/occiCommon.h
inflating: instantclient_18_1/sdk/include/occiControl.h
inflating: instantclient_18_1/sdk/include/occiData.h
inflating: instantclient_18_1/sdk/include/occiObjects.h
inflating: instantclient_18_1/sdk/include/oci.h
inflating: instantclient_18_1/sdk/include/oci1.h
inflating: instantclient_18_1/sdk/include/oci8dp.h
inflating: instantclient_18_1/sdk/include/ociap.h
inflating: instantclient_18_1/sdk/include/ociapr.h
inflating: instantclient_18_1/sdk/include/ocidef.h
inflating: instantclient_18_1/sdk/include/ocidem.h
inflating: instantclient_18_1/sdk/include/ocidfn.h
inflating: instantclient_18_1/sdk/include/ociextp.h
inflating: instantclient_18_1/sdk/include/ocikpr.h
inflating: instantclient_18_1/sdk/include/ociver.h
inflating: instantclient_18_1/sdk/include/ocixmldb.h
inflating: instantclient_18_1/sdk/include/ocixstream.h
inflating: instantclient_18_1/sdk/include/odci.h
inflating: instantclient_18_1/sdk/include/orastruc.h
inflating: instantclient_18_1/sdk/include/oratypes.h
inflating: instantclient_18_1/sdk/include/oraxml.h
inflating: instantclient_18_1/sdk/include/oraxml.hpp
inflating: instantclient_18_1/sdk/include/oraxmlcg.h
inflating: instantclient_18_1/sdk/include/oraxsd.h
inflating: instantclient_18_1/sdk/include/oraxsd.hpp
inflating: instantclient_18_1/sdk/include/ori.h
inflating: instantclient_18_1/sdk/include/orid.h
inflating: instantclient_18_1/sdk/include/orl.h
inflating: instantclient_18_1/sdk/include/oro.h
inflating: instantclient_18_1/sdk/include/ort.h
inflating: instantclient_18_1/sdk/include/xa.h
inflating: instantclient_18_1/sdk/include/xml.h
inflating: instantclient_18_1/sdk/include/xml.hpp
inflating: instantclient_18_1/sdk/include/xmlctx.hpp
inflating: instantclient_18_1/sdk/include/xmldav.h
inflating: instantclient_18_1/sdk/include/xmldf.h
inflating: instantclient_18_1/sdk/include/xmlerr.h
inflating: instantclient_18_1/sdk/include/xmlev.h
inflating: instantclient_18_1/sdk/include/xmlotn.h
inflating: instantclient_18_1/sdk/include/xmlotn.hpp
inflating: instantclient_18_1/sdk/include/xmlproc.h
inflating: instantclient_18_1/sdk/include/xmlsch.h
inflating: instantclient_18_1/sdk/include/xmlsoap.h
inflating: instantclient_18_1/sdk/include/xmlsoap.hpp
inflating: instantclient_18_1/sdk/include/xmlsoapc.hpp
inflating: instantclient_18_1/sdk/include/xmlurl.h
inflating: instantclient_18_1/sdk/include/xmlxptr.h
inflating: instantclient_18_1/sdk/include/xmlxsl.h
inflating: instantclient_18_1/sdk/include/xmlxvm.h
inflating: instantclient_18_1/sdk/ott
extracting: instantclient_18_1/sdk/ottclasses.zip
inflating: instantclient_18_1/sdk/SDK_README
To connect to Oracle, we will use goracle – it is a package which is a database/sql/driver.Driver for connecting to Oracle DB, using Anthony Tuininga’s excellent OCI wrapper, ODPI-C.
Let’s get the package:
piclerick:~ inter$ go get gopkg.in/goracle.v2
Now we can set the environment variables and create our source code:
piclerick:~ inter$ export ORACLE_HOME=/Users/inter/oracle/instantclient_18_1 piclerick:~ inter$ export DYLD_LIBRARY_PATH=/Users/inter/oracle/instantclient_18_1 piclerick:~ inter$ vim chatt_with_oracle.go
The base for our script will look like this:
package main
import (
"fmt"
"database/sql"
"log"
_ "gopkg.in/goracle.v2"
)
func main() {
}
Please note that we have to import goracle a bit differently than other packages – with "_" prefix. That’s because we will connect to the database like this: sql.Open("goracle", conn). So the "goracle" won’t be used directly in a code but as a string literal to SQL driver.
Let’s create a connection to the database and execute a simple query
package main
import (
"fmt"
"database/sql"
"log"
_ "gopkg.in/goracle.v2"
)
func main() {
//connect to database
db, err := sql.Open("goracle", "hr/hr@192.168.56.18:1521/rokoko")
//close database connection at the end of program execution
defer db.Close()
//check if there were some errors during connection
if err != nil {
log.Panic(err)
}
//execute a query and get pointer to the rows
rows, err := db.Query("select count(1) from employees")
//check if there were some errors during execution
if err != nil {
log.Panic(err)
}
//define variable for the output
var emps_cnt int
//if there are some rows - put the output to the variable address by reference
if rows.Next() {
rows.Scan(&emps_cnt)
}
fmt.Println(emps_cnt)
}
Now we can compile our program and execute it:
piclerick:~ inter$ go build chatt_with_oracle.go piclerick:~ inter$ ./chatt_with_oracle 107
Great! Let’s add more rows to the output and additional bind variable:
package main
import (
"fmt"
"database/sql"
"log"
_ "gopkg.in/goracle.v2"
)
func main() {
//connect to database
db, err := sql.Open("goracle", "hr/hr@192.168.56.18:1521/rokoko")
//close database connection at the end of program execution
defer db.Close()
//check if there were some errors during connection
if err != nil {
log.Panic(err)
}
//execute a query and get pointer to the rows
deptId := 20
rows, err := db.Query("select first_name, last_name, salary from employees where department_id=:1", deptId)
//check if there were some errors during execution
if err != nil {
log.Panic(err)
}
//define variable for the output
var salary int
var fname, lname string
//if there are some rows - put the output to the variable address by reference
for rows.Next() {
rows.Scan(&fname, &lname, &salary)
fmt.Println(fname, lname, salary)
}
}
And here’s the result:
piclerick:~ inter$ ./chatt_with_oracle Michael Hartstein 26000 Pat Fay 12000
If you want to execute PL/SQL function or procedure and get the results, you can use sql.Out interface. For example, let’s create a sample PL/SQL function:
SQL> ed Zapisano file afiedt.buf 1 create or replace function f_get_dept_avg_sal(p_dept_id number) return number is 2 v_avg_sal number; 3 begin 4 select avg(salary) into v_avg_sal 5 from employees e 6 where e.department_id=p_dept_id; 7 return v_avg_sal; 8* end; 9 / Utworzono funkcje.
Now we can add a simple PL/SQL block to our GO function and use Exec method and sql.Out interface:
package main
import (
"fmt"
"database/sql"
"log"
_ "gopkg.in/goracle.v2"
)
func main() {
//connect to database
db, err := sql.Open("goracle", "hr/hr@192.168.56.18:1521/rokoko")
//close database connection at the end of program execution
defer db.Close()
//check if there were some errors during connection
if err != nil {
log.Panic(err)
}
//execute a query and get pointer to the rows
deptId := 20
rows, err := db.Query("select first_name, last_name, salary from employees where department_id=:1", deptId)
//check if there were some errors during execution
if err != nil {
log.Panic(err)
}
//define variable for the output
var salary int
var fname, lname string
//if there are some rows - put the output to the variable address by reference
for rows.Next() {
rows.Scan(&fname, &lname, &salary)
fmt.Println(fname, lname, salary)
}
//define variable for average salary
var avgSal float64
//execute anonymous block and use sql.Out interface for extracting bind variable value
db.Exec("begin :1 := f_get_dept_avg_sal(:2); end;", sql.Out{Dest: &avgSal}, deptId)
fmt.Printf("AVG salary in department %d is %f\n", deptId, avgSal)
}
And here’s the result:
piclerick:~ inter$ go build chatt_with_oracle.go piclerick:~ inter$ ./chatt_with_oracle Michael Hartstein 26000 Pat Fay 12000 AVG salary in department 20 is 19000.000000
And it’s simple as that 🙂