Learning is not a spectator sport

Many people were not super impressed when the following announcement came out in Oracle 19C

Rest assured it is not as if we are sitting here at Oracle thinking “Hey let’s come up with ways to make life more difficult for people” 😀. There were a number of issues with continuing the support of the multimedia facilities based on library licencing, security, and ensuring that customers data was always well safe-guarded. A lot of the multimedia facilities were based in Java and one merely only needs to look at the log4J security drama that came up last year to know guaranteeing the robustness of any Java libraries, especially those in the open source arena, requires a lot of resources, and even with the best intentions of many people in the open source community, sometimes nasty bugs can slip through.

If you are an APEX user then this blog post by Menno talks about some of the excellent options available if you’re using APEX solely to manage your multimedia assets. However if you are not an APEX user ideally you would like a native facility inside the database. I don’t have a native solution but hopefully I’ve got something which you’ll find is the next best thing.

There are plenty of tools out there that let you manipulate images and videos etc. I regularly use the FFmpeg tool to perform modifications on images, or on the videos I produce for my YouTube channel. But of course, these are all tools that run at operating system level not within the database.

Accessing those OS level tools from inside the database in the past has required the use of a Java program, and thus we are back to the same issue of looking after Java code and ensuring that its secure and regularly patched etc. It’s just another language and set of utilities that you have to support and look after.

However a few months back I did a video showing how we can exploit external tables with its pre-processor option to run a host command directly from the database just by issuing a select statement.

Thus as long as the operating system tool FFmpeg can see or access the multimedia object I need to manipulate then we should be good to go. Based on that premise it’s relatively simple to put together a facility that can be run “inside” the database that will let you manipulate blobs as if the multimedia facilities were natively available. All I need to do is

• write my BLOB out to a file on the operating system,
• use my external table to call FFmpeg to manipulate the file,
• read the file back from the file system into the destination BLOB.

It is definitely a little more clunky than doing it all inside the database but for many applications I imagine this should be perfectly adequate. If anything, we are getting more power than an inbuilt multimedia facility because we have access to the entire set of options that are available with tools such as FFmpeg. I refer you to the documentation for FFmpeg to show you just how extremely flexible and powerful it is.

So here is a quick walk through of my new “multimedia engine” 😁. I need a sequence for my temporary filenames.

SQL> create sequence seq;

Sequence created.

You pass in a incoming BLOB which contains the multimedia, an outgoing BLOB which will contain the manipulated result, a set of parameters that will be passed to the FFmpeg routine,  and also the file extension because the file extension helps FFmpeg determine the type of object that it will be dealing with. I’ve defaulted it to JPG on the assumption that most of the time you’ll be dealing with images, but you can pass in all of the normal multimedia file types (png, tif, avi, mp4, mp3, etc etc).

SQL> create or replace
  2  procedure multimedia_engine(
  3               p_blob in out nocopy blob,
  4               p_parms varchar2,
  5               p_new_blob out blob,
  6               p_extension varchar2 default 'jpg') is
  7    l_chunk     raw(32767);
  8    l_chunksize int := 32767;
  9    l_file      utl_file.file_type;
 10    l_offset    int := 1;
 11    l_bsize     int;
 12    l_seq       int;
 13    l_bfile     bfile;
 14
 15    l_parms     varchar2(512) := p_parms;
 16    l_new_file  varchar2(256);
 17
 18    l_tgt_idx   int := 1;
 19    l_src_idx   int := 1;
 20    l_new_blob  blob;
 21

Next we simply read the incoming BLOB, and use UTL_FILE in its raw binary mode to spit out the BLOB as a temporary file so we have access to it from the operating system level. I am simply using a sequence to generate a unique file name every time we unload a BLOB to the file system

 22  begin
 23    l_seq   := seq.nextval;
 24    l_bsize := dbms_lob.getlength(p_blob);
 25    l_file  := utl_file.fopen('TEMP','tmp'||l_seq||'.'||p_extension,'wb', 32767);
 26
 27    while l_offset <= l_bsize loop
 28      dbms_lob.read(p_blob, l_chunksize, l_offset, l_chunk);
 29      utl_file.put_raw(l_file, l_chunk, true);
 30      l_offset := l_offset + l_chunksize;
 31    end loop;
 32
 33    utl_file.fclose(l_file);
 34
 35    l_file := utl_file.fopen('TEMP','tmp'||l_seq||'.txt','w');
 36    utl_file.put_line(l_file,'tmp'||l_seq||'.'||p_extension||';'||l_parms);
 37    utl_file.fclose(l_file);

Now I can use an external table in order to initiate an FFmpeg command. I don’t even need to define the table because we have the implicit EXTERNAL clause that lets us do an external table definition on the fly with a single query. So whilst it just looks like I’m doing a simple SELECT statement the real magic occurs when I call my run_ffmpeg.bat batch file sitting inside the pre-processor option. That is going to do all the magic here.

 39    execute immediate replace(q'{
 40    select ffmpeg_name
 41    from   external (
 42             ( ffmpeg_name varchar2(512) )
 43             type oracle_loader
 44             default directory "TEMP"
 45             access parameters
 46             ( records delimited by newline
 47               preprocessor  bin:'run_ffmpeg.bat'
 48               nobadfile
 49               nologfile
 50               nodiscardfile
 51              )
 52              location ( '@@@' )
 53       reject limit unlimited ) ext
 54    }','@@@','tmp'||l_seq||'.txt') into l_new_file;
 55

Inside that batch file is some simple MS-DOS processing of the passed parameters (it would be far easier in shell but I’m building this on my Windows machine first). In reality all I am doing is extracting the file name on which we’re going to operate on, and the parameters that were passed that we’re going to put into our FFmpeg command

@echo off
set SystemRoot=C:\WINDOWS
set PATH=C:\bin\ffmpeg\bin;%PATH%
cd /d c:\tmp
SETLOCAL ENABLEDELAYEDEXPANSION
FOR /F "delims=; tokens=1,2" %%i IN ( %1 ) DO (
set FNAME=%%i
set PARMS=%%j
)
del /q ffmpeg-!FNAME! 2>nul
C:\bin\ffmpeg\bin\ffmpeg -i !FNAME! !PARMS! ffmpeg-!FNAME!  >nul 2>nul
echo ffmpeg-!FNAME!

The FFmpeg command runs and outputs a new file with a FFmpeg prefix to the existing file name.

Back in my PL/SQL procedure I now just use DBMS_LOB to grab that processed file, load it back into a BLOB and pass it back as my outgoing BLOB..

 56    dbms_lob.createtemporary(l_new_blob,true);
 57    l_bfile := bfilename('TEMP', l_new_file);
 58    dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly);
 59    dbms_lob.loadblobfromfile(
 60      dest_lob=>l_new_blob,
 61      src_bfile=>l_bfile,
 62      amount=>dbms_lob.lobmaxsize,
 63      dest_offset=>l_tgt_idx,
 64      src_offset =>l_src_idx
 65      );
 66    dbms_lob.fileclose(l_bfile);
 67    p_new_blob := l_new_blob;
 68    dbms_lob.freetemporary(l_new_blob);
 69
 70    utl_file.fremove('TEMP','tmp'||l_seq||'.txt');
 71    utl_file.fremove('TEMP','tmp'||l_seq||'.'||p_extension);
 72    utl_file.fremove('TEMP','ffmpeg-tmp'||l_seq||'.'||p_extension);
 73  exception
 74    when others then
 75      if utl_file.is_open(l_file) then
 76        utl_file.fclose(l_file);
 77      end if;
 78      raise;
 79  end;
 80  /

Procedure created.

Now it’s time to see my multimedia engine in action. First of all I need a BLOB that I can work with so I’ll create a simple table called T which contains a BLOB and I’ll load in a picture called team.jpg.

SQL> create table t ( b blob );

Table created.

SQL> declare
  2    l_bfile   bfile;
  3    l_blob    blob;
  4    l_tgt_idx int := 1;
  5    l_src_idx int := 1;
  6  begin
  7    dbms_lob.createtemporary(l_blob,true);
  8    l_bfile := bfilename('TEMP', 'team.jpg');
  9    dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly);
 10    dbms_lob.loadblobfromfile (
 11      dest_lob    => l_blob,
 12      src_bfile   => l_bfile,
 13      amount      => dbms_lob.lobmaxsize,
 14      dest_offset => l_tgt_idx,
 15      src_offset  => l_src_idx);
 16    dbms_lob.fileclose(l_bfile);
 17    delete t; insert into t values (l_blob);
 18    commit;
 19    dbms_lob.freetemporary(l_blob);
 20  end;
 21  /

PL/SQL procedure successfully completed.

Here is the original JPG file you can see as I’ve loaded it.

For this demo I’m going to rotate the picture by 90 degrees as well as shrink its size down, so I go look up the FFmpeg documentation to find the appropriate parameters and I simply pass them into my procedure call.

The new BLOB will be stored in my table called T1

SQL> create table t1 ( b blob );

Table created.

SQL> declare
  2    b1 blob;
  3    b2 blob;
  4  begin
  5    select b into b1 from t;
  6    insert into t1 values ( empty_blob() )
  7    returning b into b2;
  8
  9    multimedia_engine(b1,'-vf transpose=1,scale=240:320',b2);
 10
 11    update t1 set b = b2;
 12    commit;
 13  end;
 14  /

and when I download that it looks like the following.

Voila!

Lets try a video. I’ll upload a video of a gas station fire I saw on Twitter

SQL> declare
  2    l_bfile   bfile;
  3    l_blob    blob;
  4    l_tgt_idx int := 1;
  5    l_src_idx int := 1;
  6  begin
  7    dbms_lob.createtemporary(l_blob,true);
  8    l_bfile := bfilename('TEMP', 'petrol_station.mp4');
  9    dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly);
 10    dbms_lob.loadblobfromfile (
 11      dest_lob    => l_blob,
 12      src_bfile   => l_bfile,
 13      amount      => dbms_lob.lobmaxsize,
 14      dest_offset => l_tgt_idx,
 15      src_offset  => l_src_idx);
 16    dbms_lob.fileclose(l_bfile);
 17    delete t; insert into t values (l_blob);
 18    commit;
 19    dbms_lob.freetemporary(l_blob);
 20  end;
 21  /

PL/SQL procedure successfully completed.

but if I want that video to be smaller and without audio, I’ll use my engine to do that.

SQL> declare
  2    b1 blob;
  3    b2 blob;
  4  begin
  5    select b into b1 from t;
  6    insert into t1 values ( empty_blob() )
  7    returning b into b2;
  8
  9    multimedia_engine(b1,'-vf scale=404:720 -an',b2,'mp4');
 10
 11    update t1 set b = b2;
 12    commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

And now we have our video ready!

So yes, the native multimedia facilities are gone in 19c onwards. But with a little DBMS_LOB, a little PL/SQL and the amazing power of external tables, we can be “back in business” with our multimedia needs in the database!

Feel free to extend this as much as you want. For example, FFmpeg supports file conversion as well (eg MP4 to GIF) so could add both incoming and outgoing file extension as parameters.