Oracle DataPump via PL/SQL – DBMS_DATAPUMP

Oracle DataPump hat sich bei vielen Anwendern aufgrund seiner Vorteile als Import/Export-Werkzeug durchgesetzt. Neben den Werkzeugen impdp und expdp lässt sich die DataPump auch via PL/SQL steuern. Hierzu dient das Package DBMS_DATAPUMP. Die Verwendung für einen Schema-Export zeigt folgendes Beispiel:

DECLARE
  v_DPHandle_n      NUMBER;
  v_TimeStamp_vc    VARCHAR2(15);
  v_JobName_vc      VARCHAR2(30);
  v_DumpFilename_vc VARCHAR2(50);
  v_LogFilename_vc  VARCHAR2(50);
  v_JobStatus_vc    VARCHAR2(50);
  v_SchemaName_vc   VARCHAR2(8)  := 'SCOTT';
  
  -- dieses Oracle-Verzeichnis muss evtl. vorher angelegt werden
  --
  -- CREATE OR REPLACE DIRECTORY DPUMP_TEST_DIR AS '/tmp';
  --
  -- evtl. muessen auch Schreib-/Lese-Rechte gesetzt werden
  --
  -- GRANT READ, WRITE ON DIRECTORY DPUMP_TEST_DIR TO orauser;
  --
  v_DumpDir_vc      VARCHAR2(20) := 'DPUMP_TEST_DIR';
BEGIN
  v_TimeStamp_vc    := TO_CHAR(sysdate, 'YYYYMMDD-HH24MISS');
  v_JobName_vc      := v_TimeStamp_vc || '_' || v_SchemaName_vc;
  
  -- wichtig ist der Platzhalter %U, je nach Parallelitaet werden so
  -- mehrere Dump-Files erzeugt
  v_DumpFilename_vc := v_SchemaName_vc || '_' || v_TimeStamp_vc || '_%U.dmp';
  v_LogFilename_vc  :=  v_SchemaName_vc || '_' || v_TimeStamp_vc || '.log';
    
  -- Datapump-Job oeffnen  
  v_DPHandle_n := DBMS_DATAPUMP.open(
    operation   => 'EXPORT',
    job_mode    => 'SCHEMA',
    job_name    => v_JobName_vc
  );

  -- Dump-File fuer den Job definieren
  DBMS_DATAPUMP.add_file(
    handle    => v_DPHandle_n,
    filename  => v_DumpFilename_vc,
    directory => v_DumpDir_vc
  );

  -- Log-File definieren
  DBMS_DATAPUMP.add_file(
    handle    => v_DPHandle_n,
    filename  => v_LogFilename_vc,
    directory => v_DumpDir_vc,
    filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
  );

  -- welches Schema soll exportiert werden?
  DBMS_DATAPUMP.metadata_filter(
    handle => v_DPHandle_n,
    name   => 'SCHEMA_EXPR',
    value  => '= ''' || v_SchemaName_vc || ''''
  );
    
  -- Parallelitaet auf 4 setzen  
  DBMS_DATAPUMP.SET_PARALLEL(
    handle => v_DPHandle_n,
    degree => 4
  );
    
  -- eigene Eintraege im Log-File sind moeglich
  DBMS_DATAPUMP.LOG_ENTRY(
    handle => v_DPHandle_n,
    message => '---> Schema Export via PL/SQL'
  );

  -- Job starten
  DBMS_DATAPUMP.start_job(v_DPHandle_n);
 
  -- auf das Job Ende wird normalerweise nicht gewartet
  -- mit WAIT_FOR_JOB laesst sich dies aber bei Bedarf
  -- realisieren
  DBMS_DATAPUMP.WAIT_FOR_JOB(
    handle => v_DPHandle_n,
    job_state => v_JobStatus_vc
  );
  
END;
/

Ein allgemeiner Hinweis zu Oracle DataPump:
DataPump-Prozesse laufen immer auf dem Oracle-Server!
Dies hat in der Praxis folgende Auswirkungen:

  • Der Betriebssystem-Benutzer, dem die Oracle-Server Prozesse zugeordnet sind, braucht Schreib-Rechte auf dem gewünschten Export-Verzeichnis (läuft die Datenbank-Instanz als Windows-Dienst und dem Benutzer LocalSystem ist kein Schreiben auf Netzwerk-Shares möglich, da dieser Benutzer keine Netzwerk-Shares sieht)
  • Es gibt keinen Client-/Server-Modus (z.B. Export eines Schemas von einem entfernten Oracle Server über einen lokalen Oracle Client)

Links for Oracle Application Express

Mario a colleague of mine is starting to work on an project to extend an PL/SQL-based web application – so I thought it would be a good idea to give him a view links for Oracle Application Express – Que se divierta con ella.

Sometimes customers have the need for an ad hoc web development tool, especially when an PHP- or Java-based approach seams to be oversized. One solution provided from Oracle is called Application Express (APEX) – the former name was HTML-DB.

To get an first impression from APEX I recommend to view the APEX Quick Tour at the Oracle website.

The Official Home Page for Oracle Application Express can be found here.

To get you started with APEX and AJAX I have to links for you:

Well and for Charting you have a lot of options too:

Reading Excel Files with Perl

Sometimes you need to read data from Excel files with Perl. There are several Perl Modules (Spreadsheet::ParseExcel, Spreadsheet::ParseExcel::Simple) that make that job quite easy. Spreadsheet::ParseExcel::Simple is an lightweight abstraction of Spreadsheet::ParseExcel.

Sample Code:

#!/usr/bin/perl -w

use Spreadsheet::ParseExcel;

my $parser = new Spreadsheet::ParseExcel();
my $xls = $parser->Parse("PerlExcelTest.xls")
  or die "Can't read File!";

# Loop over all tables in Excel file
for(my $i = 0; $i < $xls->{SheetCount}; $i++) {
  $sheet = $xls->{Worksheet}[$i];

  print "\nSheet: " . $sheet->{Name} . "\n";

  # has the current sheet cells?
  next unless (exists ($sheet->{MaxRow}) and (exists ($sheet->{MaxCol})));

  # loop over the row of the current sheet
  foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
    
    # loop over the cells in the current row
    foreach my $column ($sheet->{MinCol} .. $sheet->{MaxCol}) {
      next unless (defined $sheet->{Cells}[$row][$column]);

      print $sheet->{Cells}[$row][$column]->Value . ' | ';
    }
    print "\n";
  }
}

During my tests I came across several issues:

  • If you create Excel files with OpenOffice numeric values show up as string GENERAL. There is a patch for that problem. Please note also that you could use $cell->Val instead of $cell->Value.
  • I also have problems to extract date values from the excel file. In that case I am not able to determine the type of a date cell. Normally I should see Date but I turns out to be Numeric.

Creating Images with Mono / C#

I just wrote some sample code on how to create GIF images in C#:

using System;
using System.Drawing;
using System.Drawing.Imaging;


public class Tester {
  static void Main() {
    Bitmap bmp = new Bitmap(79,121);
    Graphics graph = Graphics.FromImage(bmp);
    Font font = new Font("Times", 6, FontStyle.Italic);

    for(int i=0; i<10000; i++) {
      graph.Clear(Color.White);
      graph.DrawString(i + " - Test", font, SystemBrushes.WindowText, 1, 1); 
      bmp.Save(i + "foo.gif", ImageFormat.Gif);
    }
  }
}

To compile that code you have to make an reference:

$ mcs -r:System.Drawing pic01.cs

To create 10.000 images takes about 12 seconds on my notebook. If you prefer PNG it takes a little bit longer ~ 26s.

Creating Windows EXE-Files under Linux

Sometimes a cross-compile environment can be very handy. Sam Lantinga form the libsdl.org project made a shell script (build-cross.sh) that makes it very easy to setup an GCC cross-compile environment for MinGW.

I used the follwing changes for the script:

  • GCC_VERSION=3.4.2-20040916-1
  • BINUTILS=binutils-2.15.91-20040904-1
  • MINGW=mingw-runtime-3.3
  • W32API=w32api-2.5

The EXE-files are a little bit large in size, but when you strip them the size is reduced drastically.