Home > PL/SQL, SQL > Oracle 12.2 – TO_CLOB and TO_BLOB enhancements

Oracle 12.2 – TO_CLOB and TO_BLOB enhancements

Loading an external file from an Oracle directory to a BLOB, or a CLOB (for plain text) has always been relatively easy.
DBMS_LOB API provides procedures to do so :

Oracle 12.2 makes it even more easy by extending TO_BLOB and TO_CLOB functions with the ability to directly convert a BFILE to the corresponding LOB pointer.
TO_CLOB is also overloaded to accept a BLOB input (and a charset ID).

 

1. TO_BLOB enhancement : loading file to BLOB

As easy as :

TO_BLOB(BFILENAME(directory_name, file_name))

e.g.

SQL> select banner from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE	12.2.0.1.0	Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
 
SQL> 
SQL> select to_blob(bfilename('DATA_DIR', 'clickhere.png'))
  2  from dual;
 
TO_BLOB(BFILENAME('DATA_DIR','
------------------------------
<BLOB>
 

 

2. TO_CLOB enhancement : converting BFILE/BLOB to CLOB

– File to CLOB :

TO_CLOB(BFILENAME(directory_name, file_name), charset_id)

e.g.

SQL> set long 5000
SQL> 
SQL> select to_clob(bfilename('DATA_DIR', 'test.xml'), nls_charset_id('AL32UTF8'))
  2  from dual;
 
TO_CLOB(BFILENAME('DATA_DIR','
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<test>This is a test XML file with a € (EURO) sign encoded in UTF-8</test>
 

– BLOB to CLOB :

TO_CLOB(blob_value, charset_id)

The charset_id argument is optional and defaults to the database character set if omitted or explicitly set to 0 (zero).

For some reasons, Oracle did not overload TO_BLOB to convert from CLOB to BLOB.
We still have to resort to DBMS_LOB.CONVERTTOBLOB in this case.

 

3. DBMS_LOB enhancement : Writing CLOB to file

DBMS_LOB has been extended with a built-in CLOB2FILE procedure to write CLOB content to file.
It supersedes DBMS_XSLPROCESSOR.CLOB2FILE available in prior releases, which now just redirects to the DBMS_LOB routine :

DBMS_LOB.CLOB2FILE(clob_value, directory_name, file_name [, charset_id, open_mode])

– The charset_id argument is optional and defaults to the database character set if omitted or explicitly set to 0 (zero).
– The open_mode argument is optional and defaults to ‘wb’ (write binary).

On a side note, comments in the package specs also mention a possible ‘ab’ value for open_mode :

/**
 *       [...]
 *       openmode   -> (optional) mode to open the output file in.
 *                     wb -- write byte mode
 *                     ab -- append byte mode
 *                     default is wb
 */

but it appears the feature is not implemented.
The official documentation does not mention it either.

Sample usage :

DECLARE

  l_xml  clob := 
  '<?xml version="1.0" encoding="UTF-8"?>
<test>This is a test XML file with a € (EURO) sign encoded in UTF-8</test>';

BEGIN
  
  dbms_lob.clob2file(
    l_xml
  , 'DATA_DIR'
  , 'test.xml'
  , nls_charset_id('AL32UTF8')
  ); 
  
END;
/

And again, for some unknown reasons, still no built-in BLOB2FILE procedure…
But it’s easy enough to roll out our own :

procedure writeblob2file (
  p_directory in varchar2
, p_filename  in varchar2
, p_data      in blob
)
is

  l_file   utl_file.file_type;
  l_pos    integer := 1;
  l_amt    pls_integer := dbms_lob.getchunksize(p_data);
  l_buf    raw(32767);
  
begin

  l_file := utl_file.fopen(p_directory, p_filename, 'wb', 32767);
  loop
    begin
      dbms_lob.read(p_data, l_amt, l_pos, l_buf);
    exception
      when no_data_found then
        exit;
    end;
    utl_file.put_raw(l_file, l_buf);
    l_pos := l_pos + l_amt;
  end loop;
  utl_file.fclose(l_file);
  
end;

 

Advertisements
Categories: PL/SQL, SQL Tags: , , ,
  1. No comments yet.
  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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: