13735

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 ?

Answer1:

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:

<strong>One</strong>: 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 PL-SQL file, and hardcoded which is fine for our needs.. it is test data!.

<strong>Note</strong>: 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'))

<strong>Two</strong>: Via Java Migrations. We learnt a lot from this post <a href="https://stackoverflow.com/questions/8348427/how-to-write-update-oracle-blob-in-a-reliable-way" rel="nofollow">How to write / update Oracle blob in a reliable way?</a> and are also inserting/updating test & dev data in this way.

<strong>Note</strong>: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.

Recommend

  • Reading Arabic text from Oracle database encoded in WE8ISO8859P1 using java
  • Convert NVARCHAR2 to MD5 Hash in Oracle DBMS_OBFUSCATION_TOOLKIT.MD5
  • Get all code statements in specific code element with EnvDte
  • Using RegEx in View
  • Converting a data frame into named object in R
  • Windows batch curl to variable
  • Get count of created entries for each day
  • iPhone dealing with xml vs soap vs JSON vs RESTful
  • quick pandas groupby calculations with cumprod
  • How to Compose OSGi Based project with C++ based project?
  • Accessing 3rd level of JSON with Angular ng-repeat
  • How can I tell Phusion Passenger which python to use?
  • Laravel lmutator $this->attributes return 'Undefined index: id'
  • How to set an entity field that does not exist on the table but does exists in the raw SQL as an ali
  • Count from each distinct date, fill in missing dates with zero
  • How can I let users share their location in Bot Framework webchat channel?
  • Splash Screen will not display
  • Using Generics on right hand side in Java 6?
  • Xamarin Android | Layout style
  • HttpURLConnection Closing IO Streams
  • How to 'create temp table as select' in Slick?
  • How to get current document uri in XSLT?
  • JBoss External Properties Files in Classpath
  • Parsing a CSV string while ignoring commas inside the individual columns
  • Why Encoding.ASCII != ASCIIEncoding.Default in C#?
  • Play WS (2.2.1): post/put large request
  • Bad request using file_get_contents for PUT request in PHP
  • When to use `image` and when to use `Matrix` in Emgu CV?
  • Encrypt data by using a public key in c# and decrypt data by using a private key in php
  • Uncaught Error: Could not find module `ember-load-initializers`
  • SSO with signing and signature validation doesn't work
  • Deserializing XML into class C#
  • Is there a mandatory requirement to switch app.yaml?
  • How to include full .NET prerequisite for Wix Burn installer
  • costura.fody for a dll that references another dll
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • java string with new operator and a literal
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize