Dynamisch Netzwerkdateien in SQL auslesen

03.
Januar
2016
Veröffentlicht von: Marco Patzwahl

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?

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 Datenbankmaschine ö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 :-) )
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...

SELECT system.get_net_files('LISTENER.ORA') FROM dual;

...die Ausgabe:

 

PL/SQL-Grundlagen erlernt man bei uns im Opens internal link in current windowPL/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 :-)

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.