Learning is not a spectator sport

If you are hoping for a long, carefully reasoned, philosophical dissertation on why I make blog posts..well, you have come to wrong place 😀. But rest assured, with literally just a couple of paragraphs I can justify my motivations for blogging, and more importantly, prove to you why you should be blogging as well.…

Back in the day, and for me, that means back around the version 8 timeframe, if you had a binary file and you wanted to load it into the database, you’d use DBMS_LOB.LOADFROMFILE.…

For example, if I had a video file I wanted to upload into table T, I’d write a little block like this

SQL> create table t ( b blob);

Table created.

SQL> declare
  2    bf  bfile;
  3    b   blob;
  4  begin
  5    insert into t
  6    values (empty_blob())
  7    returning b into b;
  8
  9    bf := bfilename('TEMP', 'petrol_station.mp4');
 10    dbms_lob.fileopen(bf, dbms_lob.file_readonly);
 11    dbms_lob.loadfromfile(b, bf, dbms_lob.getlength(bf));
 12    dbms_lob.fileclose(bf);
 13
 14    commit;
 15  end;
 16  /

PL/SQL procedure successfully completed.

This is the kind of script that is just long enough that you can’t remember it off the cuff, so you’d file it away so that you could just cut/paste it and change the table/column names whenever you needed it in a “real” program.

Then of course, came along this gem in the documentation:

If the character set is varying width, UTF-8 for example, the LOB value is stored in the fixed-width UCS2 format. Therefore, if you are using DBMS_LOB.LOADFROMFILE, the data in the BFILE should be in the UCS2 character set instead of the UTF-8 character set

Back in version 8 and before, most of use were happily cruising along in our pre-internet US7ASCII databases, and thus none of the above sentence made much sense 😀, but quickly enough as our databases became global, we realised that this was a non-trivial thing. Oracle realised this too, and deprecated the procedure!

So now, I had to write a new version of the routine – a slightly longer one, using the supported procedure.

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

PL/SQL procedure successfully completed.

Once again, this is something you would just file away in your script library to be brought out whenever you needed it. Which is exactly what I did, when I wrote my multimedia blog post a few days back.

And then, this comment came in from Mark: (I’ll paraphrase slightly)

We can load files directly with TO_BLOB to save about 50 lines from script.

Wait…what?!

And yes indeed, you can load BLOBs waaayyy easier than how my code does. You just nest your BLOB location reference in a TO_BLOB and you’re done!

SQL> insert into t values (to_blob(bfilename('TEMP','petrol_station.mp4')));

1 row created.

So if you really want to learn things about the technology you use on a daily basis…blog about it.

It really is as simple as that.