PLSQL: oracle->file ohne [CR] möglich ?

Computer: Software & Programmierung SQL & Datenbank - Programmierung
1

Hallo,

Für Datenträgeraustausch mit Banken möchte ich direkt in ein File auf der Platte schreiben. Dabei darf in der gesamten Datei kein [CR] vorkommen.
Spätestens beim CLOSE kriege ich jedoch einen [CR] verpaßt; geht das auch ohne ?

Danke für Anregungen

eljot

2

PLSQL und Dateien schreiben: External Routines
Hi eljot,

wenn ich Dich richtig verstehe, dann nutzt Du im Moment das Package UTL_FILE, um Dateien auf das Filesystem zu schreiben. UTL_FILE ist zwar „ganz nett“, hat aber eine Menge ärgerlicher Einschränkungen; über eine bist Du ja bereits gestolpert…

Wirklich sauber kriegst Du das nur hin:

  • mit einem eigenen Programm das direkt auf die Datenbank zugreift und die Dateien schreibt
  • oder Du nutzt External Routines

External Routines sind eigene, in C implementierte dynamic link libraries (UNIX: *.so, NT: *.dll). Ab Oracle 8 kann man derartige libraries direkt von PL/SQL aus ansprechen. Bei meinen Applikationen wird inzwischen die komplette „Interaktion“ zwischen DB/Applikationslogik und Betriebssystem (Dateizugriff, Bildverarbeitung, email, …) über External Routines abgewickelt.

Wenn das Deine Anforderung trifft, dann stelle ich Dir gerne ein paar Tipps zusammen…

ciao,
Bernhard

3

Hi, Bernhard
wäre es möglich das ich auch von dir Zusammenstellung von Tipps bekomme, da ich ein fast gleiches Problem habe?

Cu
Dmitri

4

Hi Bernhard,

Wenn das Deine Anforderung trifft, dann stelle ich Dir gerne
ein paar Tipps zusammen…

stell sie doch bitte auf eine Webseite, oder schick sie mir alternativ zu - ich möchte sie auch haben!

Gruß

J.

5

Wäre auch daran interessiert im CC zu landen. :smile:

Grüße, Robert

6

PLSQL und External Routines: sorry dauert noch…
Hi zusammen,

bin ja ganz erstaunt über das Interesse.
Im Moment bin ich leider total Land unter…
Bitte Geduld bis zum Wochenende!

ciao,
Bernhard

7

Oracle External Procedures / External Routines
Oracle External Procedures / External Routines

  • Was ist das
  • Was kann man damit machen
  • Beispiele / Hinweise für NT

  1. Datenbank-Version
    ================================================================================
    Oracle8 oder höher

  2. Was sind External Procedures
    ================================================================================
    External Procedures oder External Routines bezeichnet die Methode, wie von
    PL/SQL aus Code aufgerufen werden kann, der in einer shared library liegt.
    Unter Windows NT sind das xxx.DLL, unter UNIX xxx.so. Man kann eigene
    Funktionalität in C implementieren, in einer DLL ablegen und diese
    Funktionen / Prozeduren von PL/SQL aus aufrufen.

Bei Windows NT werden auch Betriebsystemfunktionen über DLLs bereitgestellt
(Beispiel: c:\winnt\system32\kernel32.dll). Diese Funktionen können direkt
aufgerufen werden.

  1. In welcher Umgebung wird die External Procedure ausgeführt?
    ================================================================================
    Eine External Procedure wird aufgerufen. Was passiert?
  • Oracle weiß, in welcher DLL diese External Procedure liegt.
  • Oracle teilt dem Listener mit, diese External Procedure auszuführen.
  • Der Listener ruft die DLL nicht selbst auf, sondern startet den Prozess
    EXTPROC.EXE und stellt die Kommunikation zwischen Oracle und EXTPROC sicher.
    Ab diesem Zeitpunkt kommunizieren Oracle und EXTPROC eigenständig miteinander.
  • EXTPROC lädt die DLL dynamisch und ruft die entsprechende Funktion auf.
    Rückgabewerte und Werte von in/out- bzw. out-Parametern werden an Oracle
    zurückgegeben.
  • EXTPROC läuft über die ganze Session, nach Beendigung der Session wird auch
    EXTPROC beendet. Für jede Session, die External Procedure verwendet, gibt
    es einen EXTPROC-Prozess.

Warum ist das so kompliziert? Bei einem Fehler in der DLL (protection fault, o.ä.)
stirbt ggf. der Prozess, der die DLL einbindet. Wenn EXTPROC stirbt, ist das nicht
weiter schlimm - der Fehler hat keine Auswirkung auf die Stabilität des Oracle-Kernels.

Die External Procedure hat gegenüber dem Betriebssystem die gleichen Ausführungsrechte
wie die aufrufende Prozess EXTPROC, der wiederum hat die Ausführungsrechte vom
Listener geerbt. Unter NT ist der Listener ein Systemdienst und läuft als User
LocalSystem. Dieser User hat nur Rechte auf der lokalen Maschine. Möchte man
über External Procedures auf Netzwerklaufwerke, etc. zugreifen, dann muss man den
Listener-Dienst unter einem spezifischen User laufen lassen. (Hinweis: Dieser User
muss dazu das Recht ‚Anmelden als Systemdienst‘ haben.)

  1. Beipiel mit kernel32.dll / WinExec
    ================================================================================

  • Listener konfigurieren (tnsnames.ora, listener.ora)
    Mit Oracle 8.0.5 oder höher ist die Standardkonfiguration ok.
    Wenn Probleme auftauchen, dann nachschauen in:
    Net8 Administrator’s Guide - 8 Enabling Advanced Net8 Features -
    Configuring Connections to Non-Oracle Database Services - Configuring Net8 for External Procedures

  • Zuerst muss man der Oracle bekanntgeben, wo die DLL liegt: CREATE LIBRARY …

  • dann braucht man eine PL/SQL-Funktion, die den Aufruf der External Procedure definiert.
    hier werden Funktionsname, Parameter, Datentypen festgelegt

  • folgendes Beispiel: SQL*Plus, user: scott/tiger

    – Aufruf von NT-Shell-Kommandos

    – Was passiert bei dem folgenden Beispiel:
    – - PL/SQL-Funktion WinExec eine Kommandozeile als Parameter.
    – Beispiel: WinExec (‚cmd /q /c mkdir c:\temp\xxx‘)
    – - die PL/SQL-Funktion WinExec ruft die Win32-API-Funktion WinExec auf,
    – die in kernel32.dll liegt.
    – - die Win32-API-Funktion startet CMD.EXE (asynchron)
    – - CMD.EXE führt die Kommandozeile aus und terminiert

    – grant create library to user
    CONNECT SYSTEM
    GRANT CREATE LIBRARY TO scott
    /

    – do all the following stuff as user scott
    CONNECT scott/tiger
    CREATE OR REPLACE LIBRARY kernel32 AS ‚c:\winnt\system32\kernel32.dll‘
    /

    – MODUL : WinExec
    – BESCHR: runs the specified application
    – RETURN: 32 - Ok

    CREATE OR REPLACE FUNCTION WinExec (
    lpCmdLine IN VARCHAR2, – address of command line
    uCmdShow IN PLS_INTEGER := 1 – window style for new application (1 for SW_SHOWNORMAL)
    )
    RETURN PLS_INTEGER – If the function succeeds, the return value is greater than 31
    IS
    EXTERNAL LIBRARY kernel32 NAME „WinExec“
    LANGUAGE C CALLING STANDARD PASCAL
    PARAMETERS (
    lpCmdLine STRING, – pass lpCmdLine as STRING
    uCmdShow LONG, – pass uCmdShow as LONG
    RETURN LONG – pass return value as LONG
    );
    /

    – now you can use WinExec
    – take care: this function will be executed asynchronously…
    SET SERVEROUTPUT ON
    DECLARE
    v_ret NUMBER;
    BEGIN
    v_ret := WinExec (‚cmd /q /c mkdir c:\temp\xxx‘);
    DBMS_OUTPUT.PUT_LINE (‚1st command: v_ret=‘ || v_ret);

    v_ret := WinExec (‚cmd /q /c xcopy c:\winnt\system32\kernel32.dll c:\temp\xxx*.*‘);
    DBMS_OUTPUT.PUT_LINE (‚2nd command: v_ret=‘ || v_ret);
    END;
    /

  1. eigene Tipps zur Implementierung einer eigenen DLL
    ================================================================================
  • Die DLL muss eine C-Schnittstelle nach außen anbieten.
  • Innerhalb der DLL kann durchaus auch mit C++ gearbeitet werden.
  • Die Funktionen, die als External Procedure verwendet werden sollen, müssen korrekt
    exportiert werden, z.B. durch Eintrag in die EXPORTS-section in das DEF-file
    oder Deklaration als __declspec(dllexport). Unter NT 4.0 kann man sich die
    exportierten Funktionen anzeigen lassen über rechte Maustaste / Schnellansicht.
  • Debuggen: Soll laut Oracle zwar gehen. Ich teste meine DLLs lieber mit einer
    kleinen Test-EXE, bevor sie zum Einsatz kommen. Außerdem habe ich eine
    Log-Funktionalität eingebaut, die sich per define ein/ausschalten lässt.
    Damit schreibt die DLL auf eine Textdatei, was so alles passiert. In komplexen,
    operativen Systemen die letzte Rettung, um seltsamen Verhalten auf die
    Spur zu kommen.
  • Beim Design von External Procedures sollte man bedenken, dass die DLL nicht
    im Speicher bleiben muss. Es ist also durchaus möglich, dass zwischen zwei
    Aufrufen von External Procedures EXTPROC und die DLL gestorben sind und wieder
    neu gestartet wurden. Daten, die nur im NT-Prozesskontext relevant sind
    (z.B. FileHandles, etc.) haben deshalb auf der PL/SQL-Seite nichts verloren.
  1. Standard-Datentypen, LOBs, Object Types, Collections
    ================================================================================
  • von Oracle werden nur Standard-Datentypen direkt unterstützt. Wie das Mapping
    von Parametern und Datentypen von PL/SQL nach C und zurück funktioniert, wird
    unter ‚Oracle8i Application Developer’s Guide - Fundamentals - 10 External Routines‘
    beschrieben.
  • Da nur Standard-Datentypen unterstützt werden, können die meisten Win32-API-Calls
    nicht direkt genutzt werden, da dort häufig Strukturen verwendet werden.
  • LOBs erfordern Einsatz von OCI
  • Eigene, zusammengesetzte Datentypen können NUR über OCI genutzt werden. Dazu muss
    ein OBJECT TYPE verwendet werden: CREATE TYPE … IS OBJECT …;
    Auf PL/SQL-Ebene deklarierte Objecttype-Variablen können an External Procedures
    übergeben werden. Auf C-Ebene gibt es dann ein entsprechendes typedef.
    Der Zugriff erfolgt über Oracle Call Interface (OCI) - Funktionen.
  • Übergabe von Arrays sind nur über Collections möglich:
    CREATE TYPE … IS TABLE / VARARRAY … OF …
    Auf PL/SQL-Ebene deklarierte Collection-Variablen können an External Procedures
    übergeben werden. Auf C-Ebene gibt es dann ein entsprechendes typedef.
    Der Zugriff erfolgt über Oracle Call Interface (OCI) - Funktionen.
    Beipiel siehe Oracle Metalink-Dokument:
    Note: 100082.1 Extproc: Manipulating Collection Parameters in External Procedures
  1. weitere Themen
    ================================================================================
  • Für OBJECT TYPEs können member functions deklariert werden.
    Auch dort sind External Procedures erlaubt.
  • Von einer External Procedure sind auch Callbacks nach Oracle möglich, um
    z.B. ein SELECT abzusetzen. Beipiel siehe Oracle Metalink-Dokument:
    Note: 50598.1 Building External C Procedures using the Callback Method
  1. Dokumentation (Angaben zu Oracle-Doku beziehen sich auf 8.1.7)
    ================================================================================

  • Oracle Demo liegt unter: ORACLE_HOME\RDBMS\extproc

  • Oracle8i Application Developer’s Guide - Fundamentals - 10 External Routines

  • Oracle Call Interface Programmer’s Guide

  • Net8 Administrator’s Guide - 8 Enabling Advanced Net8 Features -
    Configuring Connections to Non-Oracle Database Services - Configuring Net8 for External Procedures

  • Nette site mit Tipps und Tricks: http://www.akadia.com/html/tipstricks-frame.html
    nach External Procedures suchen

  • Oracle Doku aus MetaLink (in etwa in Reihenfolge der Schwwierigkeit):
    Note: 70678.1 PL/SQL Example: Bitwise Operations using External Procedures
    Note: 68061.1 Creating External Procedures on Windows NT
    Note: 99136.1 Calling Operating System Commands from PL/SQL using External Procedures
    Note: 100082.1 Extproc: Manipulating Collection Parameters in External Procedures
    Note: 50598.1 PL/SQL 8.0: Building External C Procedures using the Callback Method

03.05.2001, Bernhard Schaefers
[email protected]