GoLang as Oracle external procedure

This short article is a result of the following Twitter activity:

So let’s do it! First of all, you have to install GoLang: https://golang.org/doc/install

Once this is done, we can create a Go program to send emails. We will use "gopkg.in/mail.v2″ to make it simple.

Below you can find a simple GoLang code to send an email.

package main
import (
    "C"
    "crypto/tls"
    gomail "gopkg.in/mail.v2"
)

//Hardcoding those values may not be a good idea ;)
const (
    smtpHost string = "my.smtp.server.pl"
    smtpPort int    = 465
    smtpUser string = "my.user@ora-600.pl"
    smtpPass string = "Xtr3mly53c23tP@ssw02d"
)
//The following comment with export is crucial for this to work!!! Without it won't be visible as a function
//export sendEmail
func sendEmail(mailSendTo *C.char, mailSubject *C.char, mailBody *C.char) *C.char {
    //We have to use C package to give Oralce something it can understand when calling the function
    var sendTo = C.GoString(mailSendTo) //Because of that we have to do a convertion to GO types
    var subject = C.GoString(mailSubject)
    var body = C.GoString(mailBody)
    m := gomail.NewMessage()
    // Set E-Mail sender
    m.SetHeader("From", smtpUser)
    // Set E-Mail receivers
    m.SetHeader("To", sendTo)
    // Set E-Mail subject
    m.SetHeader("Subject", subject)
    // Set E-Mail body. You can set plain text or html with text/html
    m.SetBody("text/html", body)
    // Settings for SMTP server
    d := gomail.NewDialer(smtpHost, smtpPort, smtpUser, smtpPass)
    // This is only needed when SSL/TLS certificate is not valid on server.
    // In production this should be set to false.
    d.TLSConfig = &tls.Config{InsecureSkipVerify: true}
    // Now send E-Mail
    if err := d.DialAndSend(m); err != nil {
        return C.CString(err.Error())
    }
    return C.CString("ok")
}
func main() {}

I put explanation of the code in the comments.

Now, let’s build the package:

[oracle@b3124c94c1c7 ~]$ go mod init sendemail
go: creating new go.mod: module sendemail
go: to add module requirements and sums:
	go mod tidy
[oracle@b3124c94c1c7 ~]$ go mod tidy
go: finding module for package gopkg.in/mail.v2
go: downloading gopkg.in/mail.v2 v2.3.1
go: found gopkg.in/mail.v2 in gopkg.in/mail.v2 v2.3.1
go: finding module for package gopkg.in/alexcesaro/quotedprintable.v3
go: downloading gopkg.in/alexcesaro/quotedprintable.v3 v3.0.0-20150716171945-2caba252f4dc
go: found gopkg.in/alexcesaro/quotedprintable.v3 in gopkg.in/alexcesaro/quotedprintable.v3 v3.0.0-20150716171945-2caba252f4dc
[oracle@b3124c94c1c7 ~]$ go build -o sendemail.so -buildmode=c-shared

This operation should create the sendemail.so shared object and it’s header file sendemail.h:

[oracle@b3124c94c1c7 ~]$ file sendemail.so
sendemail.so: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked, BuildID[sha1]=f0eb77f5f5dd817b59837f7793d3afa43fe8fa0b, not stripped

We can copy sendemail.so to $ORACLE_HOME/lib or put it in some other directory and add the following line to the $ORACLE_HOME/hs/admin/extproc.ora file:

SET EXTPROC_DLLS=ONLY:/home/oracle/extproc/sendemail.so

This line will let use use your shared SO file.

Now, we can create the appropriate database objects:

SQL> create or replace library lib_sendemail as '/home/oracle/extproc/sendemail.so';
  2  /

Library created.

SQL> create or replace function f_sendemail(p_to varchar2, p_subcject varchar2, p_body varchar2) return varchar2 as
  2  external
  3  name "sendEmail"
  4  library lib_sendemail
  5  language C;
  6  /

Function created.

And we are ready to send the email from our external procedure! 😀

SQL> select f_sendemail('kamil@ora-600.pl', 'jebacpis', 'And that''s it folks!') from dual;

F_SENDEMAIL('KAMIL@ORA-600.PL','JEBACPIS','ANDTHAT''SITFOLKS!')
--------------------------------------------------------------------------------
ok

Integrating the power of GoLang and Oracle together may bring some fun features 😉 Have fun with external procedures!