Als PDF Downloaden!

Tipps & Tricks: Januar 2016
Bereich:
DBA, PL/SQL
Erstellung:
01/2016 MP
Versionsinfo:
ab 12.1
Letzte Überarbeitung:
01/2016 MP
Dynamisch Netzwerkdateien in SQL auslesen
Wie oft möchte man den einen oder anderen Netzwerkparameter aus den Dateien tnsnames.ora, listener.ora
oder sqlnet.ora von einem Unix-Server auslesen?
Was also tun?
1. Putty zur Datenbank-Maschine öffnen
2. Anmelden als oracle
3. Wechseln ins ORACLE_HOME/network/admin Verzeichnis
4. cat listener.ora absetzen
Oder in einem SQL-Client folgenden SQL-Befehl absetzen?
SELECT system.get_net_files('LISTENER.ORA') FROM dual;
Wenn Sie sich für die zweite Lösung entschieden haben, hier der Code zur Funktion:
Hinweis: Der Inhaber der Funktion muss folgende Rechte direkt besitzen: CREATE TABLE, CREATE ANY
DIRECTORY und DROP ANY DIRECTORY
Also wenn der Benutzer SYSTEM die Funktion bekommen soll:
GRANT CREATE ANY DIRECTORY TO system;
GRANT DROP ANY DIRECTORY TO system;
PL/SQL Funktions-Code:
CREATE OR REPLACE FUNCTION get_net_files(file_name IN VARCHAR2)
RETURN sys.ODCIVarchar2List PIPELINED
IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_path VARCHAR2(2001);
v_sql VARCHAR2(2001);
v_text VARCHAR2(4000);
curs sys_refcursor;
BEGIN
-- Nur diese drei Dateien dürfen verwendet werden
IF upper(file_name) not in ('TNSNAMES.ORA','LISTENER.ORA','SQLNET.ORA') THEN
RAISE_APPLICATION_ERROR(-20500,'Invalid Filename: Allowed only:
TNSNAMES.ORA,LISTENER.ORA,SQLNET.ORA');
END IF;
-- Pfad für die Netzwerdateien verwenden. Windows akzeptiert auch Slashes (Unix aber
kein Backslashes :-) )
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 1 von 3
SELECT sys_context('userenv','ORACLE_HOME')||'/network/admin'
INTO v_path from dual;
-- Temp Directory anlegen
v_sql:='CREATE OR REPLACE DIRECTORY my_network_path$ AS '''||v_path||'''';
EXECUTE IMMEDIATE v_sql;
-- Falls Tabelle schon vorhanden ist, löschen
BEGIN
v_sql:='DROP TABLE system.network_temp_tab';
EXECUTE IMMEDIATE v_sql;
EXCEPTION WHEN OTHERS THEN NULL;
END;
-- External Tabelle anlegen. Diese nimmt den Inhalt einer der Netzwerkdateien auf
-- Wir verwenden keine Logfiles (da würden sonst Fehler für jede leere Zeile stehen)
-- Auch Bad und Discarddateien ersparen wir uns
-- Das Trennzeichen $$ gibt es in den Dateien nicht, also wird die komplette Zeile in die
-- Spalte text gelesen
v_sql:=q'!CREATE TABLE system.network_temp_tab
(text VARCHAR2(4000))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY my_network_path$
ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE
NOBADFILE NODISCARDFILE NOLOGFILE
FIELDS TERMINATED BY '$$' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS)
LOCATION ('!'||lower(file_name)||q'!'))
REJECT LIMIT 5000!';
EXECUTE IMMEDIATE v_sql;
-- Refcursor muss verwendet werden, weil Tabelle zum Kompilierungszeitpunkt noch nicht
vorhanden ist
OPEN curs FOR 'SELECT text FROM system.network_temp_tab';
LOOP
FETCH curs INTO v_text;
EXIT WHEN curs%NOTFOUND OR curs%NOTFOUND IS NULL;
PIPE ROW (v_text);
END LOOP;
CLOSE curs;
-- Temp Directory und Tabelle wieder aufräumen
v_sql:='DROP TABLE system.network_temp_tab';
EXECUTE IMMEDIATE v_sql;
v_sql:='DROP DIRECTORY my_network_path$';
EXECUTE IMMEDIATE v_sql;
COMMIT;
END;
/
Im SQL Developer bekommt man dann bei folgenden Select...
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 2 von 3
SELECT system.get_net_files('LISTENER.ORA') FROM dual;
...die Ausgabe:
PL/SQL-Grundlagen erlernt man bei uns im PL/SQL-Kurs. Die Pipelined Table Function kommt dann im PL/SQL
II-Kurs dazu. Sie sehen, machmal braucht man auch als DBA PL/SQL-Kenntnisse :-)
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 3 von 3