Home > ExcelGen, PL/SQL > PL/SQL Generator for CFBF files

PL/SQL Generator for CFBF files

As a preliminary step towards adding password encryption to ExcelGen (see previous post), I have enhanced my existing CFBF reader with file generation capabilities.
And since it is not a reader anymore, I have also renamed it to “CDFManager” in my GitHub repository.





  hdl  xutl_cdf.cdf_handle;
  hdl := xutl_cdf.new_file(XUTL_CDF.V4);
  xutl_cdf.add_stream(hdl, '/stream_01.txt', sampledata(1000));
  xutl_cdf.add_stream(hdl, '/a/stream_02.txt', sampledata(2000));
  xutl_cdf.add_stream(hdl, '/a/stream_03.txt', sampledata(4000));
  xutl_cdf.add_stream(hdl, '/a/b/stream_04.txt', sampledata(10000));
  xutl_cdf.add_stream(hdl, '/a/b/stream_05.txt', sampledata(100000));
  xutl_cdf.add_stream(hdl, '/a/b/c/stream_06.txt', sampledata(1000000));
  xutl_cdf.write_file(hdl, 'TEST_DIR', 'cdf.dat');

Line 7 :
Creates a new file using version 4 format (the default).
Version 3 is also available (XUTL_CDF.V3), the main difference with V4 is the FAT sector size, which is 512 bytes for V3 and 4096 bytes for V4.

Lines 9 – 14 :
Adds streams (binary files) to the compound file.
The target path name must be an absolute path. Intermediate storage entries (folders) are created on the fly if they don’t exist.
In this example, I used a little PL/SQL function sampledata() to generate a BLOB of a given size, but of course any BLOB can be used as data source.

Line 16 :
Writes the compound file to disk, using given directory and file name.

Line 18 :
Closes the file (releases handle and associated resources).

The resulting file is available here.



The hierarchical relationships between file entries (storage and stream objects) in a compound file are stored in a directory structure.
Sibling objects in a given level of the hierarchy are arranged in a Red-Black tree.
My implementation in XUTL_CDF is a direct PL/SQL port of the C++ sample code available in the Wikipedia article, but it was fun to do nonetheless.

Here’s the source of my sampledata() function.
I use it very often to generate binary data of arbitrary size.
The generated content starts with a ‘A’, ends with ‘Z’, with the necessary amount of ‘x’ in-between, so that I can quickly and visually check whether the data was correctly read or written by the consuming application.

function sampledata (sz in integer) 
return blob 
  data       blob;
  remaining  integer;
  amount     pls_integer;
  buf        raw(32767) := utl_raw.copies('78',32767);
  dbms_lob.createtemporary(data, true);
  if sz != 0 then
    dbms_lob.writeappend(data, 1, '41');
    if sz > 1 then
      remaining := sz - 2;
      while remaining != 0 loop
        amount := least(remaining, 32767);
        dbms_lob.writeappend(data, amount, buf);
        remaining := remaining - amount;
      end loop;
      dbms_lob.writeappend(data, 1, '5A');
    end if;
  end if;
  return data;


Categories: ExcelGen, PL/SQL Tags:
  1. Maria Jose Gonzalez
    July 6, 2020 at 01:19

    Hello, I am interested in using the getSheets function but I am not sure how it is used. Can you give an example? Thank you very much.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: