Extending the SQL and PL/SQL with custom external functions and procedures

Published in

CodeX

6 min readJan 31, 2022

Many DBMSs have distinctive features. For example, one can extend with custom functions that can be used both in procedures and directly in SQL queries.

When do we need custom functions? For instance, when we need to implement non-standard calculations, encoding, encryption, conversion. Such a function might also be helpful to interact with external systems.

It will look like this:

I have had to repeatedly extend in this way in different DBMSs. Today I will tell and show you how to do it in Oracle. It proved to be working in 11g to 19c Oracle versions.

I’d like to note that it is not a hidden function. It is legal and developed by Oracle. Moreover, Oracle itself recommends applying it in some cases.

How to extend SQL and PL/SQL with custom functions

The whole process is quite simple, you only need to follow four steps:

Write and compile a library that implements this function.
Set up Oracle so it can detect the library.
Create a Library object in Oracle that will refer to our library.
Create a procedure \ function wrapper in Oracle that you will use to access the written library in SQL queries.

For experiments, I usually use ready-made instances of virtual machines, which already have a configured Linux, an Oracle database, and everything necessary for work. Download the image here, import it into VirtualBox (download here) and after 5 minutes we get a ready-to-use virtual machine with Oracle 19c.

First, let’s go to our virtual machine.

Step 1: Writing the Library

This is probably the most difficult part of all that we must do. But if you follow my instructions, everything will work out.

The library for this example is written in C for the Linux platform, as we have a virtual machine with Linux on board.

Note: Oracle allows you to include libraries written in C, C++, COBOL, Java, .NET, etc.

The terminal is already open for us, let’s start.

We check that we are in the /home/oracle directory:

[oracle@localhost ~]$ pwd/home/oracle

Create a directory where we will place our library. Let’s call it, for example, mask. Then open it:

[oracle@localhost ~]$ mkdir mask[oracle@localhost ~]$ cd mask[oracle@localhost mask]$

Create a file with the extension “c” — this is a file with source code in C. Let’s call it just as simple — mask.c:

[oracle@localhost mask]$ nano mask.c

The editor will open. Then you can type your text or paste mine. My code might not be elegant, but is an example of how to implement two functions and one procedure:

The echo function simply returns the string that had been passed to it. We will use it to check that the function works before we include it in Oracle.

The mask function takes a string and returns it masked, replacing the characters from 7th to the last one with the character “X”.

The logit procedure will write the passed string to the log.txt file. Yes, you shouldn’t do this. We do it for the sake of demonstration.

After inserting, close the editor (press Ctrl-X, then Y, Enter) and agree to save it under this name (press Enter).

Create another mask.h file:

[oracle@localhost mask]$ nano mask.h

The same as before, type your own text or copy the text below and save:

Make sure both files have been created:

[oracle@localhost mask]$ ls -ltotal 8-rw-r — r — . 1 oracle oinstall 456 Jan 20 09:46 mask.c-rw-r — r — . 1 oracle oinstall 141 Jan 20 09:57 mask.h[oracle@localhost mask]$

Compile the source files into a dynamic library:

[oracle@localhost mask]$ gcc -shared -o libmask.so -fPIC mask.c[oracle@localhost mask]$

No errors, great! Make sure the library has been created. It should be named libmask.so :

[oracle@localhost mask]$ ls -ltotal 20-rwxr-xr-x. 1 oracle oinstall 8416 Jan 20 10:02 libmask.so-rw-r — r — . 1 oracle oinstall 458 Jan 20 10:02 mask.c-rw-r — r — . 1 oracle oinstall 143 Jan 20 10:02 mask.h[oracle@localhost mask]$

Now we need to check that the library we wrote and compiled operates. To do this, we will write a small program that will call our library:

[oracle@localhost mask]$ nano testlib.c

Paste this code:

Close, save, compile:

[oracle@localhost mask]$ gcc -Wall -L/home/oracle/mask -o testlib testlib.c -lmask[oracle@localhost mask]$

It’s ok so far. We make sure that the file we need has been created:

[oracle@localhost mask]$ ls -ltotal 36-rwxr-xr-x. 1 oracle oinstall 8416 Jan 20 10:02 libmask.so-rw-r — r — . 1 oracle oinstall 458 Jan 20 10:02 mask.c-rw-r — r — . 1 oracle oinstall 143 Jan 20 10:02 mask.h-rwxr-xr-x. 1 oracle oinstall 8472 Jan 20 10:14 testlib-rw-r — r — . 1 oracle oinstall 83 Jan 20 10:11 testlib.c[oracle@localhost mask]$

Don’t forget to set the environment variable:

[oracle@localhost mask]$ export LD_LIBRARY_PATH=/home/oracle/mask:$LD_LIBRARY_PATH[oracle@localhost mask]$

Next, run:

[oracle@localhost mask]$ ./testlib[oracle@localhost mask]$

Check the execution result. A log.txt file should have been created with the line “echo testing…” in it:

[oracle@localhost mask]$ ls -ltotal 40-rwxr-xr-x. 1 oracle oinstall 8416 Jan 20 10:02 libmask.so-rw-r — r — . 1 oracle oinstall 16 Jan 20 10:18 log.txt-rw-r — r — . 1 oracle oinstall 458 Jan 20 10:02 mask.c-rw-r — r — . 1 oracle oinstall 143 Jan 20 10:02 mask.h-rwxr-xr-x. 1 oracle oinstall 8472 Jan 20 10:14 testlib-rw-r — r — . 1 oracle oinstall 83 Jan 20 10:11 testlib.c[oracle@localhost mask]$ cat log.txtecho testing…[oracle@localhost mask]$

Here it is. This means that our library works, and we can now start setting up Oracle.

Step 2: Set up Oracle so that it can detect the library

To do this, write the path to our library in a special configuration file — extproc.ora. To do this, open it:

[oracle@localhost mask]$ nano $ORACLE_HOME/hs/admin/extproc.ora

In the opening window, add a line with the path to our library at the end:

SET EXTPROC_DLLS=ONLY:/home/oracle/mask/libmask.so

Close and save.

Now you can move on to creating the necessary objects directly in the Oracle database itself.

Step 3: Create objects in the database

For clarity, we will use the Oracle SQL Developer application, which is already installed on the virtual machine. Let’s run it.

The password is the standard “oracle”.

Open a blank SQL Worksheet (Alt-F10) and paste the command to create the Library:

Execute (Ctrl-Enter). We see the message that the library has been created:

Library LIB_MASK compiled

Step 4: Create PL\SQL functions and procedures that we will use to call our functions and procedures written in C.

To do this, we create the pkg_mask package, in which we describe one function and one procedure. Paste the code into the worksheet and execute:

Check that the package has been created:

Package PKG_MASK compiled

Now we can check how everything works. To do this, we execute the ordinary select, which call the package function:

select pkg_mask.mask(‘1234567890ABCD’) from dual;

Here is the result — a masked string.

Everything works fine ))

Note: If suddenly an error occurs, try to close sql developer and open it again.

Let’s also check how the second logit procedure works, which writes text to the file:

call pkg_mask.logit(‘Test write to log’);

The procedure was completed successfully. Let’s check that there is a log entry. To do this, go back to the terminal and see:

[oracle@localhost mask]$ cat log.txt‘Test write to log’

So we got the expected result. Great!

Conclusion

We have discussed with you an example of how to create and include external libraries to Oracle and easily use them in queries and procedures. If you decide to implement something with this approach yourself, then be sure to check the Oracle documentation — there may be minor nuances in various versions.

I love helping organizations to empower their businesses with data, so I’d love to hear your thoughts on the above in the comments, and feel free to connect with me on LinkedIn.