Externe Tabelle anbinden

[Chriss]

Hallo,

ich habe eine CSV-Datei, deren Inhalt ich um 2 Spalten aus einer Oracle-Datenbank ergänzen muss.
Mein Ansatz hier war, die Datei als externe Tabelle einzubinden und dann über ein Select die benötigten Daten zu selectieren und anschliessend das Ergebnis in eine neue csv schreiben.

Die Datei als Externe Tabelle einbinden funktioniert auch ... wenn ich es normal im Developer aufrufe. Über eine Prozedur funktioniert es nicht:
[highlight=sql]create or replace

PROCEDURE P21_FAB

(

v_JOBID IN VARCHAR2

) AS



BEGIN



execute immediate 'CREATE OR REPLACE DIRECTORY P21_FAB_FOLDER as ''\\path\to_share\''';



execute immediate

'CREATE TABLE P21_FAB ( ' ||

'IK varchar2(255), ' ||

'EntlStandort varchar2(255), ' ||

'Entgeldbereich varchar2(255), ' ||

'HKintKennz varchar2(255), ' ||

'FAB varchar2(255), ' ||

'FABaufnDat varchar2(255), ' ||

'FabentlDat varchar2(255) ' ||

') ' ||

'ORGANIZATION EXTERNAL ( ' ||

'TYPE oracle_loader ' ||

'DEFAULT DIRECTORY P21_FAB_FOLDER ' ||

'ACCESS PARAMETERS (fields terminated by '';'') ' ||

'LOCATION (''FAB.csv'') ' ||

') ' ||

'REJECT LIMIT UNLIMITED;';

END P21_FAB;[/highlight]
Ich bekomme den Fehler dass eine rechte Klammer fehlt.

Ich finde den Fehler aber nicht. kann mir hier jemand helfen?

Vielen Dank!

[defo]

Deine Proc und die Tabelle, die darin erzeugt werden, heißen gleich.
Außerdem beim 2. Exxecute Statement das Semicolon entfernen, also das eigentliche Statement ohne Semicolon, wie im ersten Execute Statement.

[Chriss]

so funktionierts, vielen Dank!

wenn ich nun aber die Prozedur aus einem Trigger heraus aufrufen möchte, mekkert er, dass ein Trigger kein Commit ausführen darf.

[defo]

so funktionierts, vielen Dank!

wenn ich nun aber die Prozedur aus einem Trigger heraus aufrufen möchte, mekkert er, dass ein Trigger kein Commit ausführen darf.

Jede DDL Aktion bedeutet ein vorheriges, implizites Commit in Oracle, auch wenn es nirgends steht.
Der Triggerfehler ist sozusagen ein Schutz gegen unbeabsichtigte Commits.
Ich erlaube bspw. nirgendwo ein Commit im "normalen" Business Code, weil es die Transaktionsprinzipien zerstört und aus einem Rollback nach Fehler nur noch ein teilweises Rollback wird. Am Ende ist nichts mehr nachvollziehbar.

Du musst Dir also irgendwas anderes für Deinen Ablauf überlegen. Muss es unbedingt dynamisch sein? Können die Tabellen nicht separat angelegt werden?
Trigger sind sowieso so ziemlich das intransparenteste Konstrukt, das man wählen kann in einer DB.

Wie wäre es mit einer echten Operation, die von irgendjemand gestartet wird. Wie sieht der Kontext aus, in dem das geschehen soll?

Falls Du gar keine andere Möglichkeit findest außer Trigger, schau mal nach Autonomen Transaktionen. Ich bin mir aber spontan nicht sicher, ob das bei DDL auch so geht.

[Chriss]

Hallo Defo,

der Hintergrund ist: Ein Anwender klickt auf einen Button im Programm und erhält dann die ergänzte csv-Datei.
Ich hatte mir das so gedacht, dass ein kleines VB-Programm einen Eintrag in eine Tabelle schreibt und ein Trigger dann den Rest erledigt.
Es sollte eben die csv-Dateinach einer User-Interaktion geändert werden. Bitte beachten, der Benutzer hat mit der Datenbank nichts zu tun, er möchte nur in seinem Programm auf einen Button klicken.

Für Vorschläge bin ich offen

[defo]

Hallo Defo,

der Hintergrund ist: Ein Anwender klickt auf einen Button im Programm und erhält dann die ergänzte csv-Datei.
Ich hatte mir das so gedacht, dass ein kleines VB-Programm einen Eintrag in eine Tabelle schreibt und ein Trigger dann den Rest erledigt.
Es sollte eben die csv-Dateinach einer User-Interaktion geändert werden. Bitte beachten, der Benutzer hat mit der Datenbank nichts zu tun, er möchte nur in seinem Programm auf einen Button klicken.

Für Vorschläge bin ich offen

Dass der Anwender damit nichts zu tun haben möchte, sollte klar sein.
Ich nehme an, es gibt einen Dateidialog zur Auswahl der Datei. Oder der CSV Dateiname ist dem Programm bereits bekannt.
So oder so, ich würde eine Stored Procedure (in einem Package) anlegen, die die gesamte Verarbeitung übernimmt. Da kannst Du ohne Probleme DDL Statements laufen lassen.
Wenn sowieso eine Aktion aus dem UI gestartet wird (Button), frage ich mich auch woher die Triggeraktion kommen sollte?

Jenachdem wie statisch die CSV Datei strukturell ist, müsste es ja nicht einmal immer eine neue externe Tabelle sein. Wenn es ein Standardvorgang ist, einfach einmal anlegen und immer wieder verwenden.

[Chriss]

Hallo defo,

die Datei wird dann ganz normal über den Internetexplorer ausgewählt.
Die Triggeraktion habe ich wie folgt angelegt: es wird in eine Tabelle eine Art Log geschrieben, welcher Benutzer wann den Button gedrückt hat, und dieser Eintrag aktiviert den Trigger.

Die Struktur der csv-Datei ist immer die selbe, sie muss dann nur um 2 Spalten ergänz werden.
Da jeden Tag eine neue csv-Datei erstellt wird, muss ich den Namen auch aus einer Tabelle holen, aber das hatte ich in dem Trigger schon vorbereitet. Der Dateiname wird dann an die Prozedur als Parameter übergeben.

Mit Packages kenne ich mich leider nicht aus. Kann ich daraus auch Prozeduren aufrufen?

[defo]

Hallo defo,

die Datei wird dann ganz normal über den Internetexplorer ausgewählt.
Die Triggeraktion habe ich wie folgt angelegt: es wird in eine Tabelle eine Art Log geschrieben, welcher Benutzer wann den Button gedrückt hat, und dieser Eintrag aktiviert den Trigger.

Die Struktur der csv-Datei ist immer die selbe, sie muss dann nur um 2 Spalten ergänz werden.
Da jeden Tag eine neue csv-Datei erstellt wird, muss ich den Namen auch aus einer Tabelle holen, aber das hatte ich in dem Trigger schon vorbereitet. Der Dateiname wird dann an die Prozedur als Parameter übergeben.

Mit Packages kenne ich mich leider nicht aus. Kann ich daraus auch Prozeduren aufrufen?

Ok, also es ist keine lokale DB und Du musst user- oder sogar session abhängige "Instanzen" der Tabelle erzeugen.
Ich würde das mit dem Trigger einfach vergessen. Ein package ist nichts anderes als eine Sammlung von Prozeduren und Funktionen.
Prozeduren hast Du ja schon angelegt.
Mit

Create package ..

für das Interface
bzw.

Create package body..

für die Implementierung
bist du sofort dabei.

Es geht auch ohne, mit verschachtelten Prozeduren (oder einen großen Prozedur), ist aber nicht so "schön".

In Deinem Button rufst Du einfach so eine Prozedur (oder PackageProcedure ) per Query oder Command auf:
[highlight=SQL]
begin
P21_FAB ( v_JOBID ); -- create table
end;
[/highlight]

oder mehrere nacheinander
[highlight=SQL]
begin
P21_FAB ( v_JOBID ); -- create table
..
P21_import ( <parameter>);
P21_export ( <parameter>);
end;
[/highlight]

Hast Du ein Package erstellt, muss bloß der Packagename davor:
[highlight=SQL]
begin
MyIOPackage.P21_FAB ( v_JOBID ); -- create table
MyIOPackage. Do Something ..
end;
[/highlight]

hier findest Du Beispiele:
http://psoug.org/definition/PACKAGE.htm

[dimitri]

Ich finde den Denkansatz etwas verquer. Die Daten werden ausserhalb von Oracle in einer csv verwaltet, dann nach Oracle importiert, dort um Daten ergänzt, dann wieder exportiert?
Hol dir die benötigten Daten doch per Select aus der DB und ergänze dann die csv ausserhalb der DB, oder noch besser verlagere die komplette Datenhaltung da wo sie hingehört - in die Datenbank.

Alternativ lass die externe Tabelle doch einfach fest bestehen und tausche nur die darunterliegende Datei aus. Multiuserfähig ist das eh nicht.

[defo]

Naja, es gibt einfach heterogene Systeme.
Ich finde es nicht so verkehrt sowas in der DB zu machen, besonder bei großen Datenmengen.
Aber vielleicht hast Du auch Recht und es ist unnötig kompliziert.

Aber wieso soll das nicht multiuserfähig sein?

[Chriss]

Hol dir die benötigten Daten doch per Select aus der DB und ergänze dann die csv ausserhalb der DB, oder noch besser verlagere die komplette Datenhaltung da wo sie hingehört - in die Datenbank.

naja, wenn es so einfach wäre ...

Alternativ lass die externe Tabelle doch einfach fest bestehen und tausche nur die darunterliegende Datei aus. Multiuserfähig ist das eh nicht.

geht das? es wäre kein Problem, die tabelle bestehen zu lassen.
kann man die csv auch als inhalt in eine tabelle einlesen?

[defo]

naja, wenn es so einfach wäre ...

geht das? es wäre kein Problem, die tabelle bestehen zu lassen.
kann man die csv auch als inhalt in eine tabelle einlesen?

Wenn die externe Tabelle einmal definiert ist, brauchst Du nur die Datei auszutauschen (bei gleichem Dateinamen). Habe ich oben schon vorgeschlagen. Die external Table definiert nur das Interface auf die CSV Datei.

Ach und wenn wir schon dabei sind:
Sofern die CSV Datei nicht maschinell erstellt ist, würde ich mit Fehlern rechnen. Da empfiehlt sich die Log Files bei der External Table Deklaration mit zu definieren und zu prüfen.

[Chriss]

ja, das Problem ist, dass die Datei nicht immer den selben Namen hat ... die Datei wird maschinell erstellt.
kann man bei einer externen Tabelle, die Datei darunter austauschen? also durch eine mit anderem Namen?

[defo]

ja, das Problem ist, dass die Datei nicht immer den selben Namen hat ... die Datei wird maschinell erstellt.
kann man bei einer externen Tabelle, die Datei darunter austauschen? also durch eine mit anderem Namen?

Du kannst das External Table Interface ändern:
alter table externeTabelle location ('neu.csv');

oder Du kannst die Datei umbennen mit utl_file.frename

Wie kommt denn die Datei dahin? Wenn sowieso irgendwelche Scripte laufen, kann sie vielleicht auch darüber einheitlich benannt werden.

[Chriss]

das habe ich mir auch schon gedacht, ich hätte dann die Daten aus der db gleich abgefragt, das Problem ist, dass ich keinen Zugang zu den Programmen habe, die die Files bereitstellen. Aber, wenn das geht, dass das Datenfile einer externen Tabelle ausgetauscht werden kann, dann könnte ich das auch so machen. Wobei ich hier nicht verstehe, warum es dann nicht gut ist, die Tabelle erst an dieser Stelle zu erstellen.
Wenn ich das File ausgetauscht habe, kann ich anschliessend gleich mit einem Select Daten abfragen, oder muss hier vorher noch etwas gemacht werden?

Thx!

Nachtrag:
Der Hintergrund der unterschiedlichen Dateinamen gründet darin, dass diese durch Jobs angestartet werden und dann die Jobnummer im Dateinamen haben. Die Jobnummern lassen sich aus der Datenbank abfragen.