{"id":144,"date":"2011-03-02T07:50:11","date_gmt":"2011-03-02T06:50:11","guid":{"rendered":"http:\/\/jal-bs.de\/blog\/?p=144"},"modified":"2012-01-02T07:14:38","modified_gmt":"2012-01-02T06:14:38","slug":"oracle-11g-r2-pre-processing-external-tables","status":"publish","type":"post","link":"https:\/\/jal-bs.de\/blog\/?p=144","title":{"rendered":"Oracle 11G R2 – Pre-Processing external tables"},"content":{"rendered":"

Mit Oracle 11g R2 ist es m\u00f6glich bei Einlesen von externen Tabellen ein Pre-Processing (Skript) zwischen zu schalten.
\nDie Anwendungsbereiche innerhalb des Skriptes sind vielf\u00e4ltig z.B. Daten komprimieren, modifizieren, etc. oder schlicht dieses Feature dazu nutzen, Skripte auszuf\u00fchren und den Output weiterzuverarbeiten.<\/p>\n

Beispiel:<\/strong><\/p>\n

create\u00a0 directory\u00a0 list_dir as\u00a0 ‚\/tmp‘;
\ngrant read, write on directory list_dir\u00a0 to testuser;<\/p>\n

create directory execdir as ‚\/tmp‘;
\ngrant execute on directory execdir to testuser;<\/p>\n

–drop table directory_listing;
\ncreate table directory_listing
\n(
\n\u00a0 permission char(10),
\n\u00a0 owner\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 char(8),
\n\u00a0 gruppe\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 char(8),
\n\u00a0 mod\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 char(12),
\n\u00a0 filename\u00a0\u00a0\u00a0\u00a0 char(100)
\n)
\norganization external
\n(
\ntype oracle_loader
\ndefault directory list_dir
\naccess parameters
\n(
\nrecords delimited by newline
\npreprocessor execdir:’list.sh‘
\nskip 1
\nload when (permission\u00a0 != blanks)
\nfields
\n(
\npermission\u00a0 position (01:10),
\nowner\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 position (15:22),
\ngruppe\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 position (24:31),
\nmod\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 position (40:51),
\nfilename\u00a0\u00a0\u00a0\u00a0\u00a0 position (53:120)
\n)
\n)
\nlocation (‚list.sh‘)
\n)
\nreject limit unlimited
\n\/\u00a0<\/p>\n

—
\n— select from this table
\n—
\nselect * from directory_listing;<\/p>\n

PERMISSION OWNER\u00a0\u00a0\u00a0 GRUPPE\u00a0\u00a0 MOD\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FILENAME
\n———- ——– ——– ———— ——————————————————————————————
\n…
\n-rw-rw-rw- root\u00a0\u00a0\u00a0\u00a0 root\u00a0\u00a0\u00a0\u00a0 Feb 20 13:18 blup
\n…
\n56 rows selected.<\/p>\n

—
\n— Inhalt\u00a0 list.sh
\n—
\n#!\/bin\/ksh
\n\/bin\/ls -la\u00a0\u00a0 \/tmp<\/p>\n","protected":false},"excerpt":{"rendered":"

Mit Oracle 11g R2 ist es m\u00f6glich bei Einlesen von externen Tabellen ein Pre-Processing (Skript) zwischen zu schalten. Die Anwendungsbereiche innerhalb des Skriptes sind vielf\u00e4ltig z.B. Daten komprimieren, modifizieren, etc. oder schlicht dieses Feature dazu nutzen, Skripte auszuf\u00fchren und den … Weiterlesen →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[14],"_links":{"self":[{"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/144"}],"collection":[{"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=144"}],"version-history":[{"count":8,"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/144\/revisions"}],"predecessor-version":[{"id":222,"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/144\/revisions\/222"}],"wp:attachment":[{"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=144"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=144"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jal-bs.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=144"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}