How to Insert CLOB/BLOB data using Flyway?

Question

My requirement would be to insert CLOB/BLOB data through insert script using Flyway tool.

I could not able to find out anything to achieve this so could any one please guide me how can I achieve this ?

Answer 1

We have a need to insert XML files (as blobs), and so, have scripts inserting/updating and manipulating blobs in our Oracle Flyway scripts in two ways:

One: via a stored procedure migration (ie just PL-SQL in the Flyway *.sql files instead of plain SQL). We use Oracle's utl_raw.cast_to_raw() function.

declare
  p_surveyBlob raw(10000);
begin

 p_surveyBlob := utl_raw.cast_to_raw('<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?>
<Survey>
    <UserOnSite="yes">
        <Role>cooper</Role>
    . . . and so on...
</Survey>'); 

Insert into SURVEY (FILE_SIZE,FILE_DATA. . . .) 
values (dbms_lob.getlength(p_surveyBlob),p_surveyBlob. . . .);

COMMIT;
end;

This method requires the BLOB data to be part of the Flyway *.sql file, and hardcoded which is fine for our needs.. it is test data!.

Note: We also insert binary files, (pdfs & images) this way. Ok, so we've to prep the data manually beforehand( by running the file through Cygwin's base64 encoder utility in order to convert it to a base64 string). That string is then used with 2 Oracle functions as follows:

utl_encode.base64_decode(utl_raw.cast_to_raw('the base64 string returned by cygwins base64 function'))

Two: Via Java Migrations. We learnt a lot from this post How to write / update Oracle blob in a reliable way? and are also inserting/updating test & dev data in this way.

Note:Our build copies all the test files we need onto the class path and then we access them as resource streams, which helps the Jenkins Continuous build, and does not make any location based assumptions about the files.