Samstag, 19. August 2017
Tipp/Trick
.NET Stored-Procedures in Oracle erstellen

Die neuen Oracle Developer Tools für Visual Studio .NET (Version 10.2) stellen eine neue Projektvorlage namens "Oracle Project" für Sie bereit. Beim Erstellen eines neuen C#-Projekts können Sie nun diese Projektart auswählen, um Oracle Stored-Procedures und Stored-Functions in C# zu entwickeln. Für das Oracle-Projekt wird neben der normalen Assembly-Erstellung eine zusätzliche Konfiguration für das Deployment (die Verteilung) in die Oracle-Datenbank angelegt.

Das Grundgerüst eines Oracle-Projekts besteht aus einer Klasse und einer statischen Methode, die Sie nach dem Erstellen natürlich auch umbenennen können. Für dieses Beispiel heißt das Projekt sowie die Namespace "ScottDaten", die Klasse trägt den Namen "Dept". Die Projektvorlage hat sich auch schon um die Referenzen auf die Oracle-Assemblies gekümmert:

Hide
using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace ScottDaten {
public class Dept {
public static int GetCount() { return 4711; } } }

Unsere Test-Methode heißt GetCount() und soll später die Anzahl der Abteilungen (also aus der Tabelle DEPT im Schema SCOTT) ermitteln. Da wir so gespannt auf unsere erste C# Stored-Funktion sind, fangen wir mit einem simplen Rückgabewert von 4711 an, also erst mal ohne Datenzugriff.

Nach der erfolgreichen Kompilierung der Assembly rufen wir den neuen "Oracle Deployment Wizard für .NET" mit dem Menübefehl "Build / Deploy ScottDaten" auf.

Die Verteilung der .NET-Assembly zur Oracle-Datenbank erfordert eine Verbindung zur Datenbank mit SYSDBA-Zugriffsrechten. Da wir im Visual Studio Oracle-Explorer noch keine Connections erstellt haben, können wir dies direkt im ersten Schritt des Deployment-Wizards tun. Wählen Sie zunächst die "Data source name", die in tnsnames.ora eingetragen ist. Für eine Verbindung als SYSDBA reicht normalerweise die "Windows integrated authentication" aus. Dies wäre das gleiche Verfahren, als wenn Sie sich in SQL*Plus mit "connect / as sysdba" anmelden würden. Zuletzt geben Sie der Verbindung einen beliebigen Namen, z.B. "SYSDBA".

Im nächsten Schritt wählen Sie die Art der Verteilung aus. Beim ersten Deployment müssen wir uns sowohl die .NET-Assembly kopieren als auch die Stored-Function erstellen lassen. Bei späteren Versionen können wir bloß die Komponente, die sich tatsächlich geändert hat, austauschen lassen.

Im nächsten Schritt können wir die Assembly auswählen sowie den Namen der Library in der Oracle-Datenbank ändern. Wir belassen die Standardwerte und klicken auf "Next". Die Kopieroptionen belassen wir auch, später können wir die Option zum Überschreiben älterer Versionen aktivieren, falls die Assembly bereits vorhanden ist.

Im nächsten Schritt wird spezifiziert, welche Klassen und Methoden Sie verteilen möchten. Für jede ausgewählte Methode gibt es weitere Optionen, wie z.B. in welches Schema die Stored-Function kopiert werden soll (SCOTT), sowie den Namen der PL/SQL-Funktion (GETDEPTCOUNT). Falls Sie die .NET Datentypen zu deren Oracle-Gegenwerten anders zuordnen möchten, können Sie dies mit der Schaltfläche "Parameter Type Mapping" erledigen. Unsere Oracle-Funktion wird einen "BINARY_INTEGER" zurückgeben, der einem "int" in C# entspricht. Die Sicherheitsstufe legen wir auf "Safe" fest, denn wir wollen nicht auf externe Ressourcen (z.B. das Dateisystem) zugreifen.

Nach der Zusammenfassung der Aufgaben des Deployment-Wizards können Sie die Sache mit "Finish" vollbringen. Die Assembly ScottDaten.dll wird in den Ordner $ORACLE_HOME\BIN\CLR kopiert, und es wird eine neue Funktion namens GETDEPTCOUNT im Scott-Schema angelegt.

Mit SQL*Plus können wir nun schauen, ob alles wirklich in Ordnung ist:

Hide
:@> connect scott/tiger@sheila
Connected.
<10.2>:SCOTT@sheila> select getdeptcount from dual;


GETDEPTCOUNT ------------ 4711

Anstatt dem erwarteten Ergebnis 4711 könnte an dieser Stelle eine Fehlermeldung erscheinen, die unsere ganze Bemühungen zunichte macht, wie in folgender Sitzung zu sehen ist:

Hide
:@> connect scott/tiger@sheila
Connected.
<10.2>:SCOTT@sheila> select getdeptcount from dual;
select getdeptcount from dual * FEHLER in Zeile 1: ORA-28575: RPC-Verbindung mit externer Prozedurverarbeitung kann nicht geöffnet werden ORA-06512: in "SYS.DBMS_CLR", Zeile 143 ORA-06512: in "SCOTT.GETDEPTCOUNT", Zeile 6

Die Ursache hierfür ist höchstwahrscheinlich, dass tnsnames.ora bzw. listener.ora noch ergänzt werden müssen. Eventuell muß der Listener und/oder der CLR-Agent neu gestartet werden. Details dazu finden Sie in unserem Tipp zur .NET-Konfiguration bzw. in der Oracle-Dokumentation.

An sich funktioniert die C# Stored-Function also. Nun wollen wir die Methode mit etwas sinnvollerem füllen. Uns stehen nämlich die ganze Funktionalität des Oracle Data Providers für .NET zur Verfügung, so dass wir Connections, Commands, DataReaders usw. erstellen und verwenden können.

Die zweite Version von GetCount sieht dann so aus:

Hide
public static int GetCount() {
try { OracleConnection conn = new OracleConnection("context connection=true"); conn.Open();
OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "select count(*) from dept"; cmd.CommandType = CommandType.Text;
int dept_count = 0;
OracleDataReader reader = cmd.ExecuteReader();
if (reader.Read()) { // count(*) ist Decimal für einen DataReader, // deshalb GetDecimal() dept_count = (int)reader.GetDecimal(0); }
reader.Close(); cmd.Dispose();
return dept_count; } catch { return 0; } }

Der neue Connection-String "context connection=true" ist hier auffällig. Diese Art von Verbindung wurde aus der Java-Welt übernommen und bedeutet, es soll eine interne Verbindung zur Datenbank aufgebaut werden, die im selben Kontext läuft wie die Stored-Function selber.

Nach erneutem Deployment erhalten wir die tatsächliche Anzahl der Abteilungen:

Hide
<10.2>:SCOTT@sheila> select getdeptcount from dual;
GETDEPTCOUNT ------------ 4

Die Flexibilät von Oracle Stored-Procedures wird durch die neuen C# Stored-Procedures deutlich erhöht. Es ist nicht mehr eine Wahl zwischen PL/SQL und Java, sondern Sie können Ihre Stärken in C# auch seitens der Datenbank voll ausnutzen.

Eine Sache in diesem Konzept, die aus unserer Sicht noch fehlt, ist die Verteilung von C# Stored-Procedures nach Oracle Packages. Dann wäre eine sinnvolle Zuordnung zwischen .NET-Klassen und Oracle-Packages, sowie zwischen .NET-Methoden und Package-Prozeduren, auch noch möglich.

Leserbrief / Frage zu Oracle

 
www.trinidat.de 
 
www.fulldotnet.de 
Ein Service der TriniDat Software-Entwicklung GmbH - Am Wehrhahn 45 - 40211 Düsseldorf