4. Juni 2008

BLOBs als SQL-Skript exportieren

English title: Exporting BLOBs as a SQL script

Ich installiere Datenbankobjekte gerne mit SQL-Skripten - mit einem oder mehreren SQL*Plus-Aufrufen kann man Tabellen und Views erzeugen, PL/SQL Packages installieren und so ziemlich alles in die Datenbank packen, was man für seine Anwendung braucht ... Naja ... bis auf BLOBs. Und das hatte ich schon recht häufig, denn häufig ist auch das eine oder andere Bild bei den sog. Seed-Daten einer Anwendung dabei.
I like to install my database objects using SQL scripts - using one or more SQL*Plus calls I can create my tables, views, PL/SQL packages and the other database stuff the application needs. But BLOB data (at the first glance) cannot be installed that way. I need this quite often since some (small) images or other files are frequently part of an applications' seed data
Gut, man kann nun einen Export (klassisch oder mit der Data Pump) erzeugen - aber damit haben wir schon wieder ein zweites Tool; außerdem muss man beim Importieren schon wieder was tun, wenn das Schemanamen oder Tablespaces sich ändern. Man könnte auch hingehen und die Dateien aus einem Directory laden - dann muss man die Dateien aber zunächst auf den Datenbankserver bringen. Das sind alles keine echten Probleme - aber lästig.
OK - I could use Export/Import (traditional or data pump) for that. But Now I have a second tool and if there are differences regarding the schema or the used tablespaces I have to do something here. Another option is to load the files from the file system using directory objects - but I have to copy the files to the database server for that. These are no problems but it is cumbersome - All I want to do is to run a SQL script which creates all database objects and inserts all seed data.
Von Application Express habe ich mir abgeguckt, dass man LOBs mit Hilfe von HEXTORAW und dem RAW-Datentypen auch in Hexcodes umwandeln kann - und dieses Textformat ließe sich in SQL-Skripten nutzen (so macht APEX es ja auch). Ergebnis meiner Überlegungen ist diese kleine Funktion, welche einen BLOB als PL/SQL-Skript exportiert.
Looking at APEX I learned that BLOBs can be installed with a plain SQL Insert using the RAW datatype and the HEXTORAW function. The result of my thoughts is this tiny PL/SQL function which exports a LOB - as a SQL script.
create or replace function export_blob(
  p_blob          in blob, 
  p_variable_name in varchar2 default 'v_blob',
  p_fullscript    in boolean  default true
) return clob
is
  v_sql       clob;
  v_blobchunk raw(32767);
  v_sqlline   varchar2(32767);
  v_lobpos    pls_integer := 1;
  v_amount    pls_integer := 512;
  v_lines     pls_integer := 0;
  v_blocksize pls_integer;
begin
  v_blocksize := trunc(32767 / v_amount);
  dbms_lob.createtemporary(v_sql, true, dbms_lob.session);
  if p_fullscript then 
    v_sqlline := 'declare'||chr(10)||
                 '  v_raw raw('||v_blocksize * v_amount||') := '''';'||chr(10)|| 
                 '  '||p_variable_name||' blob;'||chr(10)||
                 'begin'||chr(10)||
                 '  dbms_lob.createtemporary('||p_variable_name||', true, DBMS_LOB.SESSION);'||chr(10)||
                 '  dbms_lob.open('||p_variable_name||', dbms_lob.LOB_READWRITE);'||chr(10);
    dbms_lob.writeappend(v_sql, length(v_sqlline), v_sqlline);
  end if;
  while v_lobpos < dbms_lob.getlength(p_blob) loop
    v_blobchunk := dbms_lob.substr(p_blob, v_amount, v_lobpos);
    v_lobpos := v_lobpos + v_amount; 
    v_lines := v_lines + 1;
    v_sqlline := '  v_raw := v_raw || hextoraw('''||rawtohex(v_blobchunk)||''');'||chr(10);
    dbms_lob.writeappend(v_sql, length(v_sqlline), v_sqlline);
    if mod(v_lines, v_blocksize) = 0 then 
      v_sqlline := '  dbms_lob.writeappend('||p_variable_name ||', utl_raw.length(v_raw), v_raw);'||chr(10)||
                   '  v_raw := '''';'||chr(10);
      dbms_lob.writeappend(v_sql, length(v_sqlline), v_sqlline);
    end if;
  end loop;
  if mod(v_lines, v_blocksize) > 0 then 
    v_sqlline := '  dbms_lob.writeappend('||p_variable_name ||', utl_raw.length(v_raw), v_raw);'||chr(10);
    dbms_lob.writeappend(v_sql, length(v_sqlline), v_sqlline);
  end if;
  if p_fullscript then 
    v_sqlline := '/* '||chr(10)||
                 '   the blob contents are now in the PL/SQL variable "'||p_variable_name||'"'||chr(10)||
                 '   your code goes here ...'||chr(10)||
                 ' */'||chr(10)||
                 '  dbms_lob.close('||p_variable_name||');'||chr(10)||
                 '  dbms_lob.freetemporary('||p_variable_name||');'||chr(10)||
                 'end;'||chr(10)||
                 '/';
    dbms_lob.writeappend(v_sql, length(v_sqlline), v_sqlline);
  end if;
  return v_sql;
end;
/
Die Parameter sind eigentlich selbsterklärend. Zum Ausprobieren nehmen wir das Logo von www.oracle.de - die URL ist http://oracleimg.com/admin/images/ocom/hp/logos/oralogo_de.gif. Das sieht dann so aus:
The function parameters should be self-explaining. Let's try it: We'll use the HTTPURITYPE function to grab the Oracle logo from the german web page. It's URL is http://oracleimg.com/admin/images/ocom/hp/logos/oralogo_de.gif. The result of the function is the following:
SQL> select export_blob(httpuritype('http://oracleimg.com/admin/images/ocom/hp/logos/oralogo_de.gif').getblob()) from dual

EXPORT_BLOB(HTTPURITYPE('HTTP://ORACLEIMG.COM/ADMIN/IMAGES/OCOM/HP/LOGOS/ORALOGO
--------------------------------------------------------------------------------
declare
  v_raw raw(32256) := '';
  v_blob blob;
begin
  dbms_lob.createtemporary(v_blob, true, DBMS_LOB.SESSION);
  dbms_lob.open(v_blob, dbms_lob.LOB_READWRITE);
  v_raw := v_raw || hextoraw('47494638396185002600F70000FFFFFFFF0000FFE8E8A9A9A9
E8E8E8C0C0C0FFDADAFFF6F6FF2F2FFF9393D7D7D7FF9493F9F9F9FF0100FFFAFAB4B4B4EEEEEEFF
FEFEFF3232FF302FBABABAFF3333FFFCFCFFCCCCFF2C2CFFF7F7FFB8B8FFE9E9FFB2B2CBCBCBFF6F
6FFFD7D7FF3332D1D1D1FFAAAAFF5E5EFFF4F4F4F4F4FFFDFDDDDDDDFFFBFBAFAFAFFF2726FFF2F2
FF4D4CFF0202FFDFDFFFD2D2E2E2E2FF0101FF0707FF3535FF908FFF0504FFAFAFFFEFEFFF4949FF
4141FF7272FFF5F5FFF8F8FF1514FFDDDDFF8F8EFF6262FF6464FF0404FFE5E5FF2A2AFF6E6EFF0F
0EFF3030FF2222C6C6C6FF1717FFCFCFFFD5D5FF1F1FFF7979FF3635FFB4B4FFD9D9FF6868FFC3C2
FFE2E2FFB9B9FF5251FFA6A6FF1616FFDBDBFFC6C6FF5F5EFF5C5CFF1615FFA7A7FFEBEBFF9494FF
9595FF1313FFD6D6FF1110FF3433FF7776FF3C3CFF1212FF4443FF8181FF3838FF1D1CFFD8D8FFBA
BAFFA09FFFB3B3FF0D0DFFEDEDFFB6B6FFADADFF9E9EFFF3F3FF5555FFC9C9FFECECFF7D7DFF2020
FF4747FF5D5DFF9797FF2524FFC0C0FF3B3AFF3636FFDEDEFF8888FF2929FFBFBEFF0A09FF3131FF
F1F1FFC4C3FF0605FF3F3FFF0C0BFFA4A4FF2828FFF0F1FF8A89FFB0B0FF0302FF8E8DFFE4E4FF2E
2EFF0505FF100FFF2121FF3837FF3C3BFF6B6AFF1E1EFFCACAFF3434FFA2A2FF4E4EFF9C9CFF1C1B
FF1A1AFF8686FF');
  v_raw := v_raw || hextoraw('0201FFA8A8FFD7D6FF8483FF9191FF9292FFF1F2FF0303FF07
06FFF5F6FF1E1DFF9392FF5958FF6867FFDADBFF2D2DFF2D2CFF1211FFFDFEFFDFE0FF0F10FF1817
FF9897FF8C8CFFA7A6FF9898FFF6F5FFB4B3FF8B8AFFB2B3FFB9B8FFA6A5FF5F5F00000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000021F90400000000002C00000000850026000008FF0001081C48B0A0C18308132A5CC8B0A1
C38710234A9C48B1A245893EE68049B06041028E1C3D2610C1C4A08B8F223B7A5C99208C17400D2F
7CD4D2D00EA03A203BA2140986C78B8F222C48BC6023E44A960B7A193061439506A102137990D028
80D5AB58AF1231E383A088AC60B16209C24A6116245647E151E820168E1E61B332E241C82A920310
3570C92424EE55192F2EA808D0648940379BFCFA2D64632087000D1467CD2107E18E209023231280
708815C957D96C00F0C3EA8C0C0F4161016D950E0028B36005BA0060101AAC082448A800A2826EDF
651E2DBA6A2498');
  v_raw := v_raw || hextoraw('C0C701E2582AA31B8484DE157CAFB6EA04C5C15398B2AA3938
040756329A7643FFE70D02912381A5033C41DD304C9CAB4298477FDE5BC223330E00A0928040C585
1D6B58D50217509070C0810826B8432277B460D51A5D2187C112095678C0183358C50609066D4084
557B886155279E1464422457ED42C70A162208CC40E909C2DE42599061950C3ADCD0E2813B982050
041F1072810536080804870EE90059007ABC6615066D2C24898301B86090074B52224955015881A4
402F34A1216D0ED160D57A0D7162551C6F50C482552A74F5100F3958C5C20A733CF9C14286B86295
22055D70550E430000C4557E10E4C88D5340646600A731E4821156496181039866AA69A602F84850
760DE8104144575875880FC45865CB180B2919800C5F02FF80C28701F4502200951C121906540894
C1A101ACB10244E9A1B9D063913122C508CC8EB005B37D6CF1EC167754611D414A04608A28126941
2513553CE9C30B70685085B91A68E00607402C12591A3302A00795411054C75541F000801CDE31E9
6999A6C58B9006AC61D544A105651B43A211011243644C101C00062E2CF1091A5DF490B1C6B75C35
C914A30AA4888D01FCC1D94026BC19C0251C007083CA52E8FB50B1021F44700391B126C241D906A0
83442806400A15124369C12068C575C9CE03BDA2F221A00060C1D4536B715B008590108113708EF6
D0A33232247100');
  v_raw := v_raw || hextoraw('3938E1810745A08D76DA1E48514A65068562D51F643AE4C21E
56E570439E13EFFF09C006675885B30C4D20614C1405FD725528669411C819676C724620225AF5F3
3057F1E268C00CAD903417760010C1E8A493FEAF41455A8583950D6DD00764318401001C7A0E9448
1F7D05B0C71507BD30580062BC508C6435B891CB2856295137438F6AE2505D5601B1839B578120C2
125918A0FDF6DA67C184087C2C69C50D0048E8F78F5C5B154302A73BA072006D5E16400D3508417F
0D325C85C00A60C46095116A98C220B847C0287CE000ABB00A225E40C0066A0F0F85B8CA08A0400B
076ECF8006A1421AB0928923200001130061081170045A45460C512A5FED088202614CE22A451896
40E8B0241CE042205F108517AE70052F78C11722E082FFFF1AB01D355D850C9F98C0083FF8C109D4
820F02F0C3FF2C21C2252EF1831CB840B6221383235C1184225401180E1285CF14EC2A69A0C97156
589030884975A3198320AC8285B52C041247B04A0FA66087F4B1063F9438A3D08665880CB1A613AC
33C80DDE7087DC49860F75F09A4052A702561D84095DD04A2564719554846C21706845DEF2608125
7880528AF9038734A08B33D66D083FC8A3625A6088851C600302C8A52E77A9CB58098407B9CCC3B5
0E92075D5201055FD0657E1C824B016CE05A0E48262F79493EA949739AD8340824B0B94B495EE49B
E00CA738C749CE');
  v_raw := v_raw || hextoraw('729AF39CE84CA73AD7C9CE76BAF39DF08CA73CE749CF7ADAF3
9EF8CCA73ECB09FF01021080010261803F09000100FC5320FFECE74005D24F8240A0A00080400910
CA0081168400138DE8414B50D085EE53220578400106D001830EA0000508010006400081B0340423
A540010050000A502005154DC20328F000061440012EF5E700080283013C40202305AA02663A009B
0EE0041F85C84F233A00181060A803616950B77A55802AA0040AE829003A0083A9AE54A804E94012
0650D0027400A74B3D2B001430008046B52166FDE95551AA52ADCA55AE0CE06907A09A04A00E24A4
2865E955099202185040A53FFD695CFDEAD7BB2EC4AC1450C055FD5950CAB6F4AF060D410A4E6056
89BA55A86815C80958DA81A3EAF501936D2903D86A5986644C95AE2558EC401EA05218E074AB61AD
E8034E70820794A004C335AB62B10A009D2616025345EC5919D0DADADA76004685EA55B13B540850
A0A930C8AA6CD7CA5B818836052555AE50B13B52883E76AA45652A76290051EBDAF7BEF8CDAF7EF7
4B908000003B');
  dbms_lob.writeappend(v_blob, utl_raw.length(v_raw), v_raw);
/*
   the blob contents are now in the PL/SQL variable "v_blob"
   your code goes here ...
 */
  dbms_lob.close(v_blob);
  dbms_lob.freetemporary(v_blob);
end;
/
Hmmm ... und das soll wirklich das richtige Bild sein ... Aus das lässt sich testen: Anstelle der Kommentarzeilen ein wenig Code einfügen, welcher den BLOB in eine Tabelle oder in eine Datei schreibt und testen ...
And this is really the correct image? Just test it: Replace the comments at the end of the script with some PL/SQL code writing the BLOB to a table or to a file and run the script.
Der Weg eignet sich nicht für wirklich große BLOBs: Ein Test mit einem 100k großen ZIP-Archiv lief sehr gut - mit einem 15MB großen Skript bekam ich Probleme - aber dafür ist es auch nicht gedacht. Kleinere Bilder und Dateien, die zum "Grunddatenbestand" (Seeddaten) einer Anwendung gehören, lassen sich so recht elegant per SQL-Skript installieren ...
This procedure is not very well suited for really large lobs. A zip archive of 100k worked very well but I got problems with a 15M one. But those are not the LOBs this is intended for. Smaller images and icons which are part of an applications' seed data can be easy installed using SQL scripts.

1 Kommentar:

Paul Bär hat gesagt…

Wow gute Arbeit.
Besten dank dafür.

Beliebte Postings