IntelliJ Data Exporter for up to 32 KB Oracle BLOBs

In the previous article, I've shown how to prepare a lengthy CLOB export using the IntelliJ IDE and work around the typical ORA-01704 and SP2-0027 errors. This time, let's see how to handle the BLOB type. Regardless of whether a column contains text data or not, you can always output binary contents in hexadecimal format.

The hexadecimal form takes up twice as much space, increasing the likelihood of the previously mentioned errors. In addition, unlike the CLOB type, the standard concatenation operator || will not work for BLOBs. Implicitly there is a cast to VARCHAR2, and you can end up with the ORA-01489: result of string concatenation is too long error.

You can use your own PL/SQL procedure to handle such an export. However, let's see if we can find a solution using the built-in packages. Out of the functions that return a binary data type, UTL_RAW.CONCAT fits this almost perfectly. It accepts up to 12 parameters of type RAW and allows concatenation, unfortunately only up to 32 KB of data.

~32 KB BLOB Data Extractor

If you're sure your data won't hit the 32 KB limit, you can easily prepare a suitable data extractor in IntelliJ. For this solution, we need to slightly modify the CLOB extractor from the previous article.

First of all, change the separator from concatenation with || to a function parameters delimiter ,. Next, change the data type by using UTL_RAW.CONCAT(HEXTORAW(:varchar2)) in place of the existing TO_CLOB(:varchar2):

CONCAT_SEPARATOR = ', '
BLOB_PREFIX = '\n UTL_RAW.CONCAT(HEXTORAW('

Naming the extractor file with .sql.groovy the suffix, will cause the BLOBs to be formatted in the hexadecimal format (same as using the built-in function RAWTOHEX).

The extractor requires a few more changes. Namely, it needs to:

1. Check whether the exported column is of type BLOB;
2. Calculate the number of concatenations;
3. Handle the case when the BLOB length is within the character limit – UTL_RAW.CONCAT requires at least two parameters.
4. Match the ending of the nested parentheses of UTL_RAW.CONCAT.

Point 4 requires further explanation. You could write the algorithm using a single call to UTL_RAW.CONCAT, however, the export would be limited to 24 KB of data. It is due to a limit of 12 concatenation arguments and the chosen character limit of 2000 (a safe value for SP2-0027).

Instead, you can nest this concatenation: UTL_RAW.CONCAT(HEXTORAW(:a), UTL_RAW.CONCAT(HEXTORAW(:b), HEXTORAW('')). It is not a very optimal solution, but it achieves the intended purpose.

        /*...*/
        if (isStringLiteral && DIALECT.getDbms().isMysql()) stringValue = stringValue.replace("\\", "\\\\")

        def isOracleBlob = value != null && FORMATTER.getTypeName(value, column) == "BLOB" && DIALECT.getDbms().isOracle() // #1
        if (isOracleBlob) {
            separatorsCount = Math.ceil(stringValue.length() / MAX_STRING_SIZE) // #2
            stringValue = stringValue
                    .replace(QUOTE, "")
                    .replaceAll("(.{" + MAX_STRING_SIZE + "})", "\$1" + QUOTE + ') ' + CONCAT_SEPARATOR + BLOB_PREFIX + QUOTE)
            OUT.append(STRING_PREFIX + BLOB_PREFIX + QUOTE) // #3
                    .append(stringValue)
                    .append(QUOTE + ")\n")
                    .append(", HEXTORAW('')") // #3
                    .append(")" * separatorsCount) // #4
                    .append(idx != columns.size() - 1 ? SEP : "")
            return
        }

        OUT.append(isStringLiteral ? (STRING_PREFIX + QUOTE) : "")
        /*...*/

Remember that the MAX_STRING_SIZE parameter must be divisible by 2. As in the previous article, select your new extractor in IntelliJ and copy the rows (or use the extraction for the entire table).

CREATE TABLE foo
(
    id  NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    bar BLOB
);

--MAX_STRING_SIZE = 6
insert into DEMO.FOO (ID, BAR) values (7, 
 UTL_RAW.CONCAT(HEXTORAW('746573') , 
 UTL_RAW.CONCAT(HEXTORAW('742C31') , 
 UTL_RAW.CONCAT(HEXTORAW('2C32')
, HEXTORAW('')))));

Exceeding the 32 KB limit, you will end up with the ORA-06502: PL/SQL: numeric or value error: raw variable length too long error.

Summary

The final implementation of the small BLOB data extractor can look as follows:

/*
 * Available context bindings:
 *   COLUMNS     List<DataColumn>
 *   ROWS        Iterable<DataRow>
 *   OUT         { append() }
 *   FORMATTER   { format(row, col); formatValue(Object, col); getTypeName(Object, col); isStringLiteral(Object, col); }
 *   TRANSPOSED  Boolean
 * plus ALL_COLUMNS, TABLE, DIALECT
 *
 * where:
 *   DataRow     { rowNumber(); first(); last(); data(): List<Object>; value(column): Object }
 *   DataColumn  { columnNumber(), name() }
 */

SEP = ", "
QUOTE     = "\'"
STRING_PREFIX = DIALECT.getDbms().isMicrosoft() ? "N" : ""
NEWLINE   = System.getProperty("line.separator")

KEYWORDS_LOWERCASE = com.intellij.database.util.DbSqlUtil.areKeywordsLowerCase(PROJECT)
KW_INSERT_INTO = KEYWORDS_LOWERCASE ? "insert into " : "INSERT INTO "
KW_VALUES = KEYWORDS_LOWERCASE ? ") values (" : ") VALUES ("
KW_NULL = KEYWORDS_LOWERCASE ? "null" : "NULL"
MAX_STRING_SIZE = 2000
CONCAT_SEPARATOR = ', '
BLOB_PREFIX = '\n UTL_RAW.CONCAT(HEXTORAW('

def record(columns, dataRow) {
    OUT.append(KW_INSERT_INTO)
    if (TABLE == null) OUT.append("MY_TABLE")
    else OUT.append(TABLE.getParent().getName()).append(".").append(TABLE.getName())
    OUT.append(" (")

    columns.eachWithIndex { column, idx ->
        OUT.append(column.name()).append(idx != columns.size() - 1 ? SEP : "")
    }

    OUT.append(KW_VALUES)
    columns.eachWithIndex { column, idx ->
        def value = dataRow.value(column)
        def stringValue = value == null ? KW_NULL : FORMATTER.formatValue(value, column)
        def isStringLiteral = value != null && FORMATTER.isStringLiteral(value, column)
        if (isStringLiteral && DIALECT.getDbms().isMysql()) stringValue = stringValue.replace("\\", "\\\\")

        def isOracleBlob = value != null && FORMATTER.getTypeName(value, column) == "BLOB" && DIALECT.getDbms().isOracle()
        if (isOracleBlob) {
            def separatorsCount = Math.ceil(stringValue.length() / MAX_STRING_SIZE)
            stringValue = stringValue
                    .replace(QUOTE, "")
                    .replaceAll("(.{" + MAX_STRING_SIZE + "})", "\$1" + QUOTE + ') ' + CONCAT_SEPARATOR + BLOB_PREFIX + QUOTE)
            OUT.append(STRING_PREFIX + BLOB_PREFIX + QUOTE)
                    .append(stringValue)
                    .append(QUOTE + ")\n")
                    .append(", HEXTORAW('')")
                    .append(")" * separatorsCount)
                    .append(idx != columns.size() - 1 ? SEP : "")
            return
        }

        OUT.append(isStringLiteral ? (STRING_PREFIX + QUOTE) : "")
                .append(isStringLiteral ? stringValue.replace(QUOTE, QUOTE + QUOTE) : stringValue)
                .append(isStringLiteral ? QUOTE : "")
                .append(idx != columns.size() - 1 ? SEP : "")
    }
    OUT.append(");").append(NEWLINE)
}


ROWS.each { row -> record(COLUMNS, row) }