Eksport ~32 KB BLOBów z bazy Oracle przy użyciu IntelliJ

W poprzednim artykule pokazałem jak z poziomu IntelliJ IDE, w szybki sposób przygotować eksport danych typu CLOB omijając typowe błędy ORA-01704 i SP2-0027. Tym razem na tapet weźmy typ danych BLOB. Bez zastanawiania się, czy taka kolumna zawiera dane tekstowe, czy nie, jej zawartość zawsze możemy wypisać w postaci szesnastkowej.

Tekst w postaci heksadecymalnej będzie zajmował dwa razy więcej miejsca, zwiększając prawdopodobieństwo wystąpienia wcześniej wspomnianych błędów. Dodatkowo w odróżnieniu od kolumny typu CLOB, standardowy operator konkatenacji || nie zadziała dla danych typu BLOB. Nastąpi rzutowanie na VARCHAR2 i niespodziewanie otrzymamy błąd ORA-01489: result of string concatenation is too long.

Do obsłużenia takiego eksportu możemy posłużyć się własną procedurą PL/SQL. Sprawdźmy jednak, czy nie znajdziemy jakiegoś rozwiązania przy użyciu dostępnych pakietów. Z funkcji zwracających binarny typ danych pasować będzie UTL_RAW.CONCAT. Przyjmuje ona do 12 parametrów typu RAW i pozwala na konkatenację, niestety tylko do 32 KB danych.

~32 KB BLOB Data Extractor

Jeśli satysfakcjonuje nas eksport danych binarnych z limitem 32 KB, to możemy przejść do implementacji ekstraktora danych w IntelliJ. Do tego rozwiązania wystarczy, że zmodyfikujemy nieco ekstraktor CLOB z poprzedniego artykułu.

Przede wszystkim zmieńmy separator z konkatenacji z || na ogranicznik , pomiędzy parametrami. Drugą modyfikacją będzie zmiana typu danych poprzez podstawienie funkcji UTL_RAW.CONCAT(HEXTORAW(:varchar2)) w miejsce dotychczasowego TO_CLOB(:varchar2):

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

Zapisując ekstraktor z suffiksem .sql.groovy, sprawimy, że typ danych BLOB domyślnie zostanie wypisany w postaci szesnastkowej (funkcja wbudowana RAWTOHEX).

Poprawne wygenerowanie eksportu wymaga jeszcze paru dodatkowych zmian, a mianowicie:

1. Sprawdzenia, czy eksportowana kolumna jest typu BLOB;
2. Wyliczenie liczby konkatenacji;
3. Obsłużenie przypadku gdy BLOB mieści się w limicie znaków – UTL_RAW.CONCAT wymaga przynajmniej dwóch parametrów.
4. Domknięcie nawiasów zagnieżdzonych wywołań UTL_RAW.CONCAT.

Punkt 4 wymaga tu dokładniejszego wyjaśnienia. Algorytm moglibyśmy napisać przy użyciu jednego wywołania UTL_RAW.CONCAT, jednak ze względu na limit 12 argumentów konkatenacji i limit znaków 2000 (bezpieczna wartość dla SP2-0027) eksport byłby ograniczony do 24 KB danych.

Zamiast tego skorzystamy z zagnieżdżonego wywołania UTL_RAW.CONCAT(HEXTORAW(:a), UTL_RAW.CONCAT(HEXTORAW(:b), HEXTORAW('')). Nie jest to zbyt optymalne rozwiązanie, ale pozwala na osiągnięcie zamierzonego celu.

        /*...*/
        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) : "")
        /*...*/

Pamiętając o tym, żeby parametr MAX_STRING_SIZE był podzielny przez 2, wybieramy nasz nowy ekstraktor i kopiujemy wiersze (bądź korzystamy z ekstrakcji całej tabeli):

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

Przekraczając limit 32 KB, powinniśmy się spodziewać błędu ORA-06502: PL/SQL: numeric or value error: raw variable length too long.

Podsumowanie

Ostateczna implementacja ekstraktora małych BLOBów wygląda następująco:

/*
 * 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) }