8. Februar 2010

Betriebssystem-Zugriff mit SQL und PLSQL: Neue Version verfügbar

New version 0.9 available: Executing OS commands from the database

Nach über einem Jahr habe ich heute eine neue Version des PL/SQL-Pakets für den Zugriff auf Dateien, Verzeichnisse und Betriebssystem-Kommandos veröffentlicht. Download wie immer über die Projektseite auf sourceforge.net. Neue Features sind:
  • Neue Pipelined Functions für das Listing von Verzeichnisinhalten: Wesentlich bessere Performance
  • Komplexe Shell-Kommandos (besonders auf Unix wichtig) werden nun unterstützt; siehe OS_COMMAND.SET_EXEC_IN_SHELL.
  • Eine SQL Developer-Erweiterung ist nun ebenfalls dabei.
The last version was more than a year ago - today I released the new version 0.9 of my package for file access and operating system interaction. You can download it from the project website at sourceforge.net. Some new features:
  • There are new Pipelined Functions for directory listings - these have better performance and are more memory-efficient.
  • Complex Shell-Commands (which are common on Unix platforms) are now supported - see the OS_COMMAND.SET_EXEC_IN_SHELL call.
  • An extension for Oracle SQL Developer is also included.
Was ist das Besondere an diesem Paket - es gibt doch UTL_FILE? Ganz einfach: Schaut euch das hier an:
Why such a package - there is UTL_FILE? The answer is below:
SQL> select file_name, file_size, last_modified 
  2  from table(file_pkg.get_file_list_p(file_pkg.get_file('/'))) 
  3  order by 1;

FILE_NAME                       FILE_SIZE LAST_MODIFIED
------------------------------ ---------- ----------------
.autofsck                               0 08.02.2010 09:42
bin                                  4096 18.01.2010 18:20
boot                                 1024 18.01.2010 18:17
dev                                  3240 08.02.2010 09:46
etc                                  4096 08.02.2010 09:46
home                                 4096 18.01.2010 18:20
lib                                  4096 18.01.2010 18:20
lost+found                          16384 05.05.2009 20:17
media                                4096 09.01.2009 02:19
misc                                    0 08.02.2010 09:42
mnt                                  4096 09.01.2009 02:19
net                                     0 08.02.2010 09:42
opt                                  4096 08.02.2010 09:43
proc                                    0 08.02.2010 09:41
root                                 4096 08.02.2010 10:16
sbin                                12288 18.01.2010 18:20
selinux                                 0 08.02.2010 09:41
srv                                  4096 09.01.2009 02:19
stage                                   5 10.11.2009 14:10
swapfile                       1610612736 08.02.2010 09:43
sys                                     0 08.02.2010 09:41
tmp                                  4096 08.02.2010 11:16
usr                                  4096 18.01.2010 18:17
var                                  4096 18.01.2010 18:18
wget-log                              495 08.02.2010 09:46

25 Zeilen ausgewählt.
Feedback ist wie immer sehr erwünscht ...
Feedback is very appreciated ...

Kommentare:

Maren hat gesagt…

Kann man mit der Funktion OS_COMMAND.SET_EXEC_IN_SHELL auch wildcards benutzen, z.B. rm *?

Carsten Czarski hat gesagt…

Hallo,

ja - das geht; das Package führt all das aus, was man auch auf der OS-Shell ausführen kann ... der DBA man muss demnach mit den Privilegien entsprechend vorsichtig sein; "rm" ist ein sehr mächtiges Kommando ...

Grüße

-Carsten

all_exed hat gesagt…

Hallo,

kann das Package auch unter Windows getestet werden?

Grüße, Alex

Carsten Czarski hat gesagt…

Hallo Alex,

klar, es sollte auf Windows laufen; ich selbst habe es aber nur rudimentär getestet. Ich weiss, dass der eine oder andere es schon auf Win zum Laufen gebracht hat ...

Grüße

-Carsten

Alex hat gesagt…

Hallo Carsten,

ja, die Installation auf Windows hat sehr gut geklappt. Ob alles in allen Einzelheiten funktioniert müsste ich auch noch testen.

Wofür brauchst dieses Package in der Praxis? zum Beispiel?

Grüße, Alex

Xenofon hat gesagt…

Obwohl ich mir das durch Nachlesen wahrscheinlich erschließen, können Sie mir einfach erklären, ob man damit theoretisch auch (oder nur?) Zugriff auf das Filesystem des Clients hat?

Ich interessiere mich für client-seitige file-Operationen. Denn UTL_FILE funktioniert nur serverseitig, mit DIRECTORY Objekt. In bräuchte clientseitige filesystem-Schreib- und Lesezugrifffunktionalität, ohne das mir ein Oracle-Client (etwa sql*plus) zur Verfügung steht...

Carsten Czarski hat gesagt…

Hallo "Xenofon",

nein - das gibt es nicht; die Datenbank hat über die Standard-Packages oder über das hier vorgestellte Paket nur Zugriff auf das Filesystem des Servers. Clientseitig wird immer der Oracle-Client bzw. eine Applikation benötigt ...

Hilft das weiter ...?

Beste Grüße

-Carsten

Xenofon hat gesagt…

Du brauchst meinen Vornamen nicht in Anführungszeichen zu setzen, das ist eben mein Name :-)

Deine Antwort ist klar, auch wenn nicht erfreulich. Ich werde in einem Projekt mit der Situation konfrontiert, dass in einem "Java-Projekt" mit ca. 1:10 Oracle-zu-Java Entwickler Verhältnis, in den Computern gar kein Oracle-Client installiert ist. Ich habe gehofft, was ich nicht mit sql*plus spooling machen, eventuell mit Java in der Datenbank zu realisieren. Aber dann geht das wohl nicht. Oder hat jemand von Euch Erfahrung mit solchen Situationen?

Man denkt sich halt, dass der sqldev ausreichend sei und der ja keinen Oracle client bräuchte... Was soll man da sagen?

Carsten Czarski hat gesagt…

Hallo Xenofon,

naja es gibt noch den "Oracle Instant Client", der im Gegensatz zum "normalen" wesentlich leichter installiert ist -> hier muss nur ein ZIPFile ausgepackt werden ...

Einfach aus dem OTN herunterladen - auspacken - fertig.

Beste Grüße

-Carsten

Xenofon hat gesagt…

Danke Carsten,

der Instant Client ist das Richtige, Du hast recht. Einziger Wermutstropfen ist, dass der sql*loader darin nicht verfügbar. Zwar kann im Netz finden, wie man den sql*loader im IC lauffähig machen kann, aber nur für Linux, für win-clients scheint das nicht möglich zu sein.

Ich werde den IC mit in die Entwicklungsumgebung für Eclipse packen und notwendigen SQL-Skripte mittels IC in Eclipse einbinden, sodass die Java-Entwickler sie benutzen können.

Das soll continous integration von Java + Oracle Sourcen ermöglichen.

Dimce hat gesagt…

Hallo Carsten,

ich brauche ein SQL-Script (insert, update...) aus dem PL/SQL heraus aufzurufen. Dann habe ich die Version 0.91 von dem OS_COMMAND-PAckage sowie die anderen Objekten.

Wenn ich aber folgendes ausführe:
DECLARE
res_sql_scr_exec NUMBER;
BEGIN
res_sql_scr_exec := os_command.exec ('c:\oracle\product\10.2.0\db_1\BIN\sqlplus.exe user/****@instanz1 @c:\oracle\OedAnw\EXTTables\STD_SCRIPT.SQL');
END;
/

dann bekomme ich die Fehlermeldung:
DECLARE
*
FEHLER in Zeile 1:
ORA-29532: Java-Aufruf durch nicht abgefangene Java-Exception beendet:
java.lang.IllegalThreadStateException
ORA-06512: in "OTTO.OS_COMMAND", Zeile 70
ORA-06512: in Zeile 12

Die JAVA-Grants habe ich per Script java_grants.sql angelegt. Darüber hinaus habe ich noch folgende Java-Grants angelegt:
exec dbms_java.grant_permission( 'OTTO', 'SYS:java.io.FilePermission', 'c:\oracle\product\10.2.0\db_1\BIN\sqlplus.exe', 'execute' )
exec dbms_java.grant_permission( 'OTTO', 'SYS:java.io.FilePermission', 'c:\oracle\OedAnw\EXTTables\STD_SCRIPT.SQL', 'read' )


Ich konnte keine Verweise auf dieser Fehlermeldnug gefunden, und deswegen die Bitte an Dich ob Du mir weiterhelfen kannst.

Gruß,
Dimce

Carsten Czarski hat gesagt…

Hallo Dimce,

wenn ein Java-Fehler auftritt, entsteht in der User-dump-destination eine Travedatei mit dem kompletten Java-Stack. Die bräuchte ich ... Mailadresse findest Du im Impressum.

Ansonsten wäre höchstens die Frage, ob man Insert bzw. Update-Anweisungen nicht eher per DBlink absetzt

Beste Grüße

-Carsten

Dimce hat gesagt…

Hi nochmal Carsten,

ich habe es probiert auf Windows files mit wildcard zu kopieren etwa wie copy D:\*.TXT E:\ es klappt aber nicht.

Geht es überhaupt mit der "COPY"-Function?

Gruß,
Dimce

Carsten Czarski hat gesagt…

Hi Dimce,

was kommt denn für eine Fehlermeldung?

Es macht u.U. Sinn, vorher SET_SHELL bzw. SET_EXEC_IN_SHELL aufzurufen ...

Beste Grüße

-Carsten

Dimce hat gesagt…

Erstmal den PL/SQL Block:
-----------------------
DECLARE
file_source file_type;
file_destin file_type;
p_dump_file_source VARCHAR2 (100) :=
'D:\OedAnw\wms\STD_SCRIPT\*.PRT';
p_dump_file_destin VARCHAR2 (100) :=
'\\SITA-DB2\Meldung\wme.fact_Import';
BEGIN
file_source := file_pkg.get_file (p_dump_file_source);
file_destin := file_pkg.get_file (p_dump_file_destin);
DBMS_OUTPUT.put_line (file_source.file_path || ' ' || file_source.file_exists);
DBMS_OUTPUT.put_line (file_destin.file_path || ' ' || file_destin.file_exists);
file_source := file_source.COPY (file_destin);
END;
/
-----------------------

dann die Ausgabe des DBMS_OUTPUT.PUT_LINE:
-----------------------
D:\OedAnw\wms\STD_SCRIPT\*.PRT N
\\SITA-DB2\Meldung\wme.fact_Import Y
-----------------------

und zum Schluss die Fehlermeldung-en:
-----------------------
DECLARE
*
FEHLER in Zeile 1:
ORA-29532: Java-Aufruf durch nicht abgefangene Java-Exception beendet:
java.io.FileNotFoundException: Invalid argument
ORA-06512: in "OTTO.FILE_TYPE", Zeile 25
ORA-06512: in Zeile 22
-----------------------

Dimce

Carsten Czarski hat gesagt…

Hallo Dimce,

wenn ich Deine Aussage richtig lese, ist es so, dass die Quelldatei vor dem Copy nicht existiert während die Zieldatei bereits vorhanden ist ... müsste es nicht umgekehrt sein ...?

Beste Grüße

-Carsten

Dimce hat gesagt…

Hallo Carsten,

bei der Quelldatei handelt es sich eigentlich um die Quelldateien:
D:\OedAnw\wms\STD_SCRIPT\*.PRT

und dementsprechend bei der Zieldatei handelt es sich auch um ein Verzeichnis:
\\SITA-DB2\Meldung\wme.fact_Import, also "wme.fact_Import" ist ein Verzeichnis, und da möchte ich die *.prt -Dateien kopieren. Auf dem File-System ist alles da.

Ich habe auch mit get_file_list probiert, um den File Handle für alle .prt dateien zu bekommen, da es sich hier um eine Gruppe von Dateien handelt. Aber das kriege ich auch nicht hin.

Gruß,
Dimce

Carsten Czarski hat gesagt…

Hallo Dimce,

jetzt habe ich es - die Wildcards funktionierten mit der Java File API nicht. Du könntest wie folgt vorgehen ... das Witzige ist, dass ein einfaches SELECT ausreicht ...

select value(e).copy(file_pkg.get_file('/home/oracle/trctest/'||e.file_name)).file_path
from table(file_pkg.get_file_list(file_pkg.get_file('/opt/oracle/diag/rdbms/orcl/orcl/trace'))) e
where e.file_name like '%.trc'
/

Wenn Du eine "One-Shot-Operation" mit Wildcards möchtest, würde das nur mit OS_COMMAND gehen ...

Beste Grüße

-Carsten

Dimce hat gesagt…

Hi Carsten,

Klasse! So geht es. Es reicht mir völlig aus.

Vielen Dank,
Dimce

Beliebte Postings