The PL/SQL Ninja: PL/SQL Can Do WHAT?

Codetalk (ODTUG Feb. 2017)

By Morten Egan

currently working as: Solution Architect

i blog here: codemonth.dk

and i tweet from: @kidrac

npg the pl/sql package manager: plsql.ninja

source code stored at: github.com/morten-egan

-- required personal stuff: originally from ; currently living in ; father of three; --

Programming languages

A lot of options
some languages more fashionable than others

node.js,Ruby,R,Haskell,Python,Perl,Javascript,Go

... and pl/sql is not on the sexy list

but is that really fair? - I dont think so.

Is pl/sql missing that much? - Nah-ah.

or hidden behind a big database? - maybe.

how can we change that perception?

first let us see what the cornerstones of other languages are.

the core packages

what is it, that makes their world tick.

Node

Package name Functionality
Lodash Language improvements. Easily work with arrays, numbers, objects, strings
Request Simple and easy way to make http(s) requests.
Async Helper functions to work with asynchronous javascript.
Underscore Underscore.js is a utility-belt library for JavaScript that provides support for the usual functional suspects (each, map, reduce, filter...) without extending any core JavaScript objects.
Express Fast, unopinionated, minimalist web framework.
debug A tiny node.js debugging utility modelled after node core's debugging technique.

Ruby

Package name Functionality
Bundler Bundler manages an application's dependencies.
Rake Tasks and dependencies.
JSON This is a JSON implementation as a Ruby extension in C.
activesupport Rich support for multibyte strings, internationalization, time zones, and testing.
Nokogiri Nokogiri (鋸) is an HTML, XML, SAX, and Reader parser.
Rack Rack provides a minimal, modular, and adaptable interface for developing web applications in Ruby.
Passenger an application server that allows you to securely operate web apps, microservices & APIs with outstanding reliability, performance and control.

Perl

Package name Functionality
DBI Database independent interface for Perl.
JSON This module converts Perl data structures to JSON and vice versa.
DateTime DateTime is a class for the representation of date/time combinations.
Text::CSV Text::CSV provides facilities for the composition and decomposition of comma-separated values.
Net::SMTP This module implements a client interface to the SMTP and ESMTP protocol.
HTTP::Response The HTTP::Response class encapsulates HTTP style responses.

Python

Package name Functionality
simplejson Simple, fast, extensible JSON encoder/decoder for Python.
setuptools Download, build, install, upgrade, and uninstall Python packages.
requests Python HTTP for Humans.
python-dateutil Extensions to the standard python 3.0+ datetime module.
certifi Mozilla's SSL Certs.

What is the common theme here?

Application Servers Running applications or serving restful services

Helper utilities parallelity, arrays, cryptography, IO, scheduling

Parsing functions parse text, dates, xml, json, csv

Protocols http, jdbc, smtp ...

So with that in mind what are the PL/SQL core packages?

What should make your world tick?

Application servers

Need I say more than APEX and ORDS?

Helper utilities (1/3)

If we look at the helper utilities of others, they exist to make core functionality more easy to use. PL/SQL gives you DBMS_PARALLEL_EXECUTE or Parallel enabled pipelined function. Array manipulation is perhaps one area where PL/SQL is bit behind others, but it does provide basic operators and comparison methods.

Helper utilities (2/3)

For IO, there is UTL_FILE for standard file operations and DBMS_PIPE for inter-process communication. Queueing is no problem either with Advanced Queues. For smaller IO related tasks, you can even use global contexts or DBMS_ALERT. For scheduling there is DBMS_SCHEDULER, which even includes stuff like file watchers and advanced task dependencies.

Helper utilities (3/3)

For security and cryptography there is DBMS_CRYPTO and DBMS_NETWORK_ACL_ADMIN.

Parsing functions

We can parse text using the builtin regular expression functions, parse, work and manipulate XML using DBMS_XMLPARSER , DBMS_XMLDOM and DBMS_XSLPROCESSOR. Oracle's builtin date and timestamp functionality combined with the TO_CHAR formatting options, gives at least as much time/date functionality if not more. For JSON, 12c have just added JSON native capability inside the database. CSV is just as easy to deal with inside PL/SQL. We have the DBMS_UTILITY or SQLLDR or External tables.

Protocols

Plenty of core protocol packages available such as DBMS_LDAP for ldap access , UTL_TCP for socket programming , UTL_HTTP for easy http access , UTL_SMTP for low level STMP programs , UTL_MAIL for high level email functionality. With these packages pl/sql can cover most of todays requirements for REST, point-to-point communication, web sockets and much much more.

but what about non-oracle packages

There is already a large amount of packages that will enable you to do even more advanced stuff, then what the standard packages in the database can deliver. There is the Alexandria Library compiled by Morten Braaten, which includes PayPal, Amazon, Google, Excel, PDF integrations and much more.

For logging, your best option is probably Logger by Martin Giffy D'Souza forked from the original Logger version by Tyler Muth.

And there is much much more out there. At the end of the presentation I will have a bigger list of projects that I know of, and their capabilities.

and of course I have built a couple of packages also :)

So let's take a look at where it started, and some of the stuff I've built.