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!