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

Autor
Damian
Terlecki
11 minut
Inne

Jeśli wiesz już jak przygotować szybki eksport kolumn CLOB i BLOB do 32 KB to z pewnością może Cię zainteresować analogiczny proces dla BLOBów powyżej 32 KB. O ile nie jest to optymalne rozwiązanie, to może się czasami przydać do przygotowania importu pojedynczych obiektów bazy danych bez wykorzystania dodatkowych narzędzi podczas samego importowania.

Zrzut ekranu konsoli IntelliJ przedstawiający eksportowany/importowany obraz BLOB

W poprzednim przykładzie naszym ograniczeniem była wbudowana funkcja UTL_RAW.CONCAT, która pozwalała na scalanie BLOBów do maksymalnego rozmiaru 32 KB. Aby zbytnio się nie napracować, spróbujmy zaimplementować własny odpowiednik funkcji, który nie będzie obarczony wspomnianym limitem. Jednocześnie ograniczmy ingerencję w implementację samego ekstraktora BLOBów (IntelliJ Data Extractor) opisanego w poprzednim artykule.

Funkcja concat_blobs

Dla przypomnienia przykładowy import wygenerowany przez nasz poprzedni ekstraktor wygląda następująco:

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

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

Do zapewnienia kompatybilności wystarczy więc, że składnia naszej nowej funkcji będzie zwracała typ danych BLOB i przyjmowała dwa argumenty tego samego typu:

concat_blob (  
   r1  IN BLOB DEFAULT NULL,
   r2  IN BLOB DEFAULT NULL) 
  RETURN BLOB;

Tym razem skorzystamy ze wbudowanego pakietu DBMS_LOB. Do konkatenacji świetnie sprawdzi się procedura DBMS_LOB.APPEND, która pozwala na dopisanie danych do pierwszego BLOBa:

DBMS_LOB.APPEND (
   dest_lob IN OUT  NOCOPY BLOB, 
   src_lob  IN             BLOB);

Mając na uwadze konieczność podania prawidłowego i istniejącego wskaźnika na dane, nowy BLOB utworzymy w pamięci (drugi parametr), korzystając z procedury DBMS_LOB.CREATETEMPORARY:

CREATE OR REPLACE FUNCTION concat_blobs(p_blob1 BLOB DEFAULT NULL, p_blob2 BLOB DEFAULT NULL) RETURN BLOB IS
    v_out_blob BLOB;
BEGIN
    IF p_blob1 IS NULL AND p_blob2 IS NULL THEN
        RETURN NULL;
    END IF;
    DBMS_LOB.CREATETEMPORARY(v_out_blob, TRUE);
    IF p_blob1 IS NOT NULL THEN
        DBMS_LOB.APPEND(v_out_blob, p_blob1);
    END IF;
    IF p_blob2 IS NOT NULL THEN
        DBMS_LOB.APPEND(v_out_blob, p_blob2);
    END IF;
    RETURN v_out_blob;
END;
/

Po utworzeniu takiej funkcji na bazie wystarczy, że podmienisz UTL_RAW.CONCAT na concat_blob w zmiennej BLOB_PREFIX ekstraktora z poprzedniego artykułu i po jego wybraniu skopiujesz dane z konsoli bądź wyeksportujesz tabelę przy użyciu IntelliJ. Taki eksport możesz przetestować na danych binarnych o rozmiarze większym niż 32 KB, np. na zdjęciach PNG czy archiwum ZIP.

32~ KB BLOB Data Extractor

Do w pełni pudełkowego rozwiązania brakuje nam pozbycia się konieczności uprzedniego utworzenia na bazie danych funkcji concat_blobs. Możemy ją sprytnie upchnąć do anonimowego bloku kodu PL/SQL tak, by wygenerowany import wyglądał następująco:

DECLARE
    FUNCTION concat_blobs(p_blob1 BLOB DEFAULT NULL, p_blob2 BLOB DEFAULT NULL) RETURN BLOB IS
        --...
    END;
BEGIN
    insert into FOO (ID, BAR) values (1, concat_blobs());
    insert into FOO (ID, BAR) values (2, concat_blobs());
    --...
end;
/

Przechodzimy do implementacji eksportu BLOB z poprzedniego artykułu i wokół linii ROWS.each { row -> record(COLUMNS, row) } generującej inserty dodajemy brakujący kod. W celu jego wypisania korzystamy z domyślnej zmiennej OUT i funkcji append. Ostatecznie nasz eksporter może 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 concat_blob(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)
}


OUT.append("""DECLARE
    FUNCTION concat_blobs(p_blob1 BLOB DEFAULT NULL, p_blob2 BLOB DEFAULT NULL) RETURN BLOB IS
        v_out_blob BLOB;
    BEGIN
        IF p_blob1 IS NULL AND p_blob2 IS NULL THEN
            RETURN NULL;
        END IF;
        DBMS_LOB.CREATETEMPORARY(v_out_blob, TRUE);
        IF p_blob1 IS NOT NULL THEN
            DBMS_LOB.APPEND(v_out_blob, p_blob1);
        END IF;
        IF p_blob2 IS NOT NULL THEN
            DBMS_LOB.APPEND(v_out_blob, p_blob2);
        END IF;
        RETURN v_out_blob;
    END;
BEGIN
""")

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

OUT.append("""
END;
/
""")

Przedstawiony przykład nie jest optymalnym rozwiązaniem przygotowywania importów. Sprawdza się jedynie w przypadku relatywnie niewielkich pojedynczych BLOBów. W innych przypadkach wielokrotne tworzenie tymczasowych BLOBów spowoduje znaczne (rekursja) spowolnienie i zużycie pamięci bądź I/O (w zależności od parametryzacji procedury DBMS_LOB.CREATETEMPORARY). W takich przypadkach warto rozważyć rozwiązania oparte na JDBC, SQLLDR bądź Oracle Data Pump.

< Eksport ~32 KB BLOBów z bazy Oracle przy użyciu IntelliJWeryfikacja czcionek kompatybilnych z danym językiem w Javie >