如何使用Flyway插入CLOB/BLOB数据? [英] How to Insert CLOB/BLOB data using Flyway?

问题描述

我的要求是使用Flyway工具通过插入脚本插入CLOB/BLOB数据.

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 ?

推荐答案

我们需要插入XML文件(作为Blob)，因此，脚本需要通过两种方式在我们的Oracle Flyway脚本中插入/更新和操作Blob:

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:

一个:通过存储过程迁移(即Flyway *.sql文件中的PL-SQL而非普通SQL).我们使用Oracle的utl_raw.cast_to_raw()函数.

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;

此方法要求BLOB数据是Flyway *.sql文件的一部分，并进行硬编码，这很适合我们的需求.它是测试数据！.

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!.

注意:我们还以这种方式插入二进制文件(PDF和图像).好的，因此我们需要预先手动准备数据(通过Cygwin的base64编码器实用程序运行文件，以便将其转换为base64字符串).然后将该字符串与2个Oracle函数一起使用，如下所示:

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'))

两个:通过Java迁移. 我们从这篇文章中学到了很多东西如何编写/更新Oracle blob是否以可靠的方式提供?，并且还在插入/更新测试和测试?以这种方式开发数据.

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.

注意:我们的构建将我们需要的所有测试文件复制到类路径上，然后将它们作为资源流进行访问，这有助于Jenkins Continuous构建，并且不对以下内容进行任何基于位置的假设文件.

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.

这篇关于如何使用Flyway插入CLOB/BLOB数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！