Exporting .pgp data outside of Oracle database

This chapter illustrates the correct way to export .pgp data produced by DidiSoft ORA_PGP to files outside of the Oracle database.

Once we have produced OpenPGP encrypted content, a common scenario is to export the result .pgp file outside of the Oracle database for sending it via some channel (like SFTP) to a partner organization.

A problem that we have faced at this point and we find it worth mentioning is that due to a specifics in the implementation of the PL/SQL UTL_FILE package your output files may contain additional new line characters and this can cause the .pgp files to be rejected by the OpenPGP software implementation of your partners. The most important thing is that the file mode of the output files must be ‘wb’ (write in binary), because the more commonly used ‘w’ will write the files in text mode where additional new line characters will be added automatically at each new line and this ruins the message if it is in ASCII armored format.

Exporting OpenPGP data stored in VARCHAR2

DECLARE
 file_name varchar2(1000); 
 folder varchar2(1000);
 l_file      UTL_FILE.FILE_TYPE;
 
 public_key VARCHAR(2000);
 encrypted_message VARCHAR(10000);
BEGIN  
  folder := 'DATA_FILE_DIR';
  public_key := 'c:\Projects\PGPKeys\public_key.asc';
 
  encrypted_message := ORA_PGP.ENCRYPT('Hello World', public_key);  
 
  file_name := 'data.pgp';
  l_file := UTL_FILE.fopen(folder,file_name,'wb'); -- very important to use binary mode
  -- write to the file
  UTL_FILE.put_raw(l_file, UTL_I18N.string_to_raw(encrypted_message));      
  -- Close the file.
  UTL_FILE.fclose(l_file);  
END;
/

Exporting BLOB and CLOB fields

For BLOB and CLOB fields you can use the routine below just by filling the parameters of the output folder and output file name.

CREATE OR REPLACE PROCEDURE dump_blob(l_blob BLOB, folder varchar2, file_name varchar2)
AS
  l_file      UTL_FILE.FILE_TYPE;
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
--  l_blob      BLOB;
  l_blob_len  INTEGER;
BEGIN
  l_blob_len := DBMS_LOB.getlength(l_blob);
 
  -- Open the destination file.
  l_file := UTL_FILE.fopen(folder,file_name,'wb', 32767);
 
  -- Read chunks of the BLOB and write them to the file
  -- until complete.
  WHILE l_pos < l_blob_len LOOP
    DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
    UTL_FILE.put_raw(l_file, l_buffer, TRUE);
    l_pos := l_pos + l_amount;
  END LOOP;
 
  -- Close the file.
  UTL_FILE.fclose(l_file);  
END;
/

Summary

This chapter illustrated how to export OpenPGP data out of the Oracle database to external files. Although this may seem like a trivial thing a small detail could break the OpenPGP message and make it unacceptable by other OpenPGP implementations.