BLOBs ins Dateisystem "entladen" - nur mit einer "SQL Abfrage"

"unload" a bunch of BLOBs to a filesystem folder

Wolltet Ihr schonmal eine Tabelle mit BLOBs in ein Verzeichnis "ausladen" ... wobei jeder BLOB in eine eigene Datei geschrieben werden soll ...? Bei mir war es heute wieder mal soweit ... und natürlich hätte man dazu ein wenig PL/SQL-Code mit UTL_FILE schreiben können - ich wollte es aber schnell haben. Also habe ich mein Package für Betriebssystem-Kommandos und Dateisystem-Zugriffe genommen. Meine Tabelle sah so aus ...

Did you ever have the situation where you wanted to "unload" a table with BLOBs into a folder of the local filesystem - as a individual file for each BLOB. Today I had to do so ... and of course - we could write some PL/SQL code using UTL_FILE - but I did not have that much time. So I took my package for operating system commands and filesystem access and used that. My table looked as follows ...

SQL> desc target_table
 Name                                      Null?    Typ
 ----------------------------------------- -------- ------------------------
 ID                                                 NUMBER
 FILENAME                                           VARCHAR2(4000)
 FILECONTENT                                        BLOB

Das "Entladen" konnte ich mit einem einfachen SQL SELECT machen:

And this SQL "query" did the trick ...

SQL> select 
  2  filename, 
  3  file_pkg.get_file('/home/oracle/test/'||filename).make_file().append_to_file(filecontent) bytes_written 
  4 from target_table;

FILENAME                                           BYTES_WRITTEN
-------------------------------------------------- -------------
2010-003a_yahoo_quote.html                                  3305
2010-003b-bestdbproject.html                                1433
2010-007_charset_nls_iana.html                              3125
2010-009_emalert_rss.html                                  17047
2010-009_emalert_rss_01.png                               171007
2010-009_emalert_rss_02.png                                38661
2010-009_emalert_rss_03.png                               141388
2010-010_blob-remote.html                                  14636
2010-011_imp_dataonly.html                                  3471
2010-012_recursive-table-functions.html                     4916
2010-013_romannumber.html                                   1153
2010-014-doag-sig-spatial.html                              1025
2010-015-dbms-applicationinfo.html                          7539
2010-016-addmonths.html                                     3842
2010-017-zip.html                                          29251
:                                                              :

50 rows selected.

Elaped: 00:00:00.17

Danach sah das Verzeichnis /home/oracle/test so aus ...

The filesystem folder looked like this afterwards ...

[oracle@sccloud038 test]$ ls -lah
total 752K
drwxr-xr-x 2 oracle oinstall 4.0K Feb 23 13:13 .
drwxr-xr-x 3 oracle oinstall 4.0K Feb 23 13:12 ..
-rw-r--r-- 1 oracle oinstall 2.8K Feb 23 13:13 2010-001a_apex-plugins.html
-rw-r--r-- 1 oracle oinstall 8.9K Feb 23 13:13 2010-001_dbms_datapump_exp.html
-rw-r--r-- 1 oracle oinstall 3.6K Feb 23 13:13 2010-002a_oscomm_0.9.html
-rw-r--r-- 1 oracle oinstall 8.0K Feb 23 13:13 2010-002_dbms_datapump_imp.html
-rw-r--r-- 1 oracle oinstall 3.3K Feb 23 13:13 2010-003a_yahoo_quote.html
-rw-r--r-- 1 oracle oinstall 1.4K Feb 23 13:13 2010-003b-bestdbproject.html
-rw-r--r-- 1 oracle oinstall  12K Feb 23 13:13 2010-003_dbms_stat_funcs.html
-rw-r--r-- 1 oracle oinstall 7.7K Feb 23 13:13 2010-004_miniskript_lo.html
-rw-r--r-- 1 oracle oinstall  11K Feb 23 13:13 2010-005_resumable.html
-rw-r--r-- 1 oracle oinstall 6.5K Feb 23 13:13 2010-006_video.html
-rw-r--r-- 1 oracle oinstall 3.1K Feb 23 13:13 2010-007_charset_nls_iana.html
-rw-r--r-- 1 oracle oinstall  29K Feb 23 13:13 2010-008-recursive_with.html
-rw-r--r-- 1 oracle oinstall 167K Feb 23 13:13 2010-009_emalert_rss_01.png

Ist doch nett, oder ...? Alles im allem habe ich für das Schreiben dieses Postings mehr Zeit gebraucht ...

Creating this blog posting actually took more time than doing this piece of work ...

SQL-Aggregatsfunktion "PRODUCT" fehlt ...? Kein Problem - wir bauen sie selbst!

Aggregate function for PRODUCT ...? No problem!

Auf einer Veranstaltung in München, während es um das Thema Analytische Funktionen ging, fragte mich jemand nach einer Aggregatsfunktion für die Bildung eines Produktes - also nicht das Aufsummieren der Werte wie bei SUM, sondern das multiplizieren - und in der Tat: eine solche Funktion gibt es in Oracle nicht.

Some days ago, at an event in Munich (the topic was about analytic functions I was asked whether there is an aggregation function for a "product" - that means not summing up all the values, but multiplying them. And indeed: such a function is not available within Oracle.

Nun kann man das mit PL/SQL natürlich auf einfachste Weise nachprogrammieren - aber eine solche Funktion lässt sich nicht in SQL-Abfragen mit GROUP BY nutzen. Berechnungen für Datengruppen in einer Tabelle müssen dann mühsam mit PL/SQL nachgebildet werden.

OK ... no problem ... this is an easy task for a PL/SQL developer. But wait: An own function could not be used in SQL GROUP BY clauses. If I wanted to calculate products for groups of table rows I'd have to implement this all by myself.

Aber das muss nicht sein. Es ist kaum bekannt, aber man kann eigene Aggregatsfunktionen in der Datenbank hinterlegen. Das habe ich in einem sehr frühen Blog-Posting schonmal genutzt: Mit der dort beschriebenen LIST-Funktion kann man VARCHAR2-Spalten auch von vor Oracle11g zusammenfassen (in Oracle11g gibt es mit LISTAGG ja eine eingebaute Funktion). Um ein User Defined Aggregate zu bauen, muss man eine Schnittstelle ausprogrammieren. Und hier ist der Code für AGG_PRODUCT.

But there are user defined aggregates: Virtually nobody seems to know them but they are so powerful. I already used this technology in an old blog posting: The LIST function aggregates VARCHAR2 values - in 10g and before (In Oracle11g the built-in function LISTAGG was introduced). To create an user defined aggregate, an interface must be implemented. This interface has cumbersome function names but is very simple to implement. Here we go for AGG_PRODUCT.

drop function agg_product;
drop type agg_product_t;

create type agg_product_t as object(
  v_agg_product number,

  static function ODCIAggregateInitialize(
    sctx IN OUT agg_product_t
  ) return number,
  member function ODCIAggregateIterate(
    self IN OUT agg_product_t, value IN number
  ) return number,
  member function ODCIAggregateTerminate(
    self IN agg_product_t, returnValue OUT number, flags IN number
  ) return number,
  member function ODCIAggregateMerge(
    self IN OUT agg_product_t, ctx2 IN agg_product_t
  ) return number
 );
/
sho err

create or replace type body agg_product_t is

static function ODCIAggregateInitialize(sctx IN OUT agg_product_t)
return number is
begin
  sctx := agg_product_t(1);
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(
  self IN OUT agg_product_t, 
  value IN number
) return number is
begin
  self.v_agg_product:=self.v_agg_product * nvl(value, 1);
  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(
  self IN agg_product_t, 
  returnValue OUT number, 
  flags IN number
) return number is
begin
  returnValue := self.v_agg_product;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT agg_product_t, ctx2 IN agg_product_t) return number is
begin
  if ctx2.v_agg_product is null and self.v_agg_product is not null then
    null;
  elsif ctx2.v_agg_product is not null and self.v_agg_product is not null then
    self.v_agg_product := self.v_agg_product * ctx2.v_agg_product;
  elsif ctx2.v_agg_product is not null and self.v_agg_product is null then
    self.v_agg_product := ctx2.v_agg_product * 1;
  elsif ctx2.v_agg_product is null and self.v_agg_product is null then
    null;
  end if;
  return ODCIConst.Success;
end;
end;
/
sho err

CREATE or replace FUNCTION agg_product (input number) RETURN number
PARALLEL_ENABLE AGGREGATE USING agg_product_t;
/
sho err

Die Funktion könnt Ihr danach sofort verwenden.

You can use the new function immediately.

SQL> select deptno, agg_product(sal) product_sal from emp
  2  group by deptno

    DEPTNO PRODUCT_SAL
---------- ------------------------------
        10 15925000000
        20 23562000000000000
        30 10153125000000000000

Auch als analytische Funktion kann sie verwendet werden.

It can also be used as an analytic function. This is very powerful.

select 
  ename, 
  agg_product(sal) over (order by sal desc rows between unbounded preceding and current row) product_sal 
from emp;

ENAME          PRODUCT_SAL
---------- ---------------
KING                  5000
FORD              15000000
SCOTT          45000000000
JONES      133875000000000
BLAKE      3,815437500E+17
CLARK      9,347821875E+20
:                        :

Das geht mit einer normalen PL/SQL-Funktion nun wirklich nicht; die ganze analytische "Power" von SQL kann so ausgenutzt werden. Nicht jeder braucht wirklich eine AGG_PRODUCT-Funktion; aber wenn Ihr Aggregate mit eigenen Funktionen bilden müsst, sind die User Defined Aggregates auf jeden Fall einen Blick wert.

A user defined aggregate has a big advantage over a plain PL/SQL function. Foremost it can be used in SQL GROUP BY and analytic clauses. All the analytic power of SQL can be used with your own code for aggregations.