Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:windows_dot_net_integration_plsql

Oracle 12c PL/SQL - Verwendung von .Net Libraries in PL/SQL - eine ".NET stored procedures" anlegen und aufrufen

Erstellt 09.2017

Aufgabe:

In einer 12c Datenbank unter MS Windows 2010 soll aus der Datenbank direkt in die Microsoft Message Queue ohne große Umwege geschrieben und gelesen werden. Da ginge zwar auch über einen REST Service, dann müsste aber der für die Microsoft Message Queue ein kompletter IIS installiert und betrieben werden.

Daher sollen nun die nativen .NET Klassen von Microsoft direkt in der Datenbank über einen PL/SQL Wrapper verwendet werden. Das kann über „.NET Stored Procedures“ durchgeführt werden.

.NET Stored Procedures - Übersicht

  • Können in jeder .Net compatiblen Sprache erstellt werden, wie C# und VB
  • Können aus PL/SQL und SQL im gesamten Datenbank Scope verwendet werden
  • Werden über das Package DBMS_CLR aufgerufen

 Übersicht Integration Oracle .NET Stored Procedures

Voraussetzung:

  • „Oracle Database Extensions for .NET“ installiert
  • Microsoft .NET Framework auf dem Datenbank Server installiert
  • „Oracle Database Extensions for .NET“ für die Datenbank aktiviert
  • Oracle CLR Host Service angelegt
  • Oracle Listener für den Aufruf des CLR konfiguriert

DB Umgebung für den Aufruf einer ".NET Stored Procedures" einrichten

Ist die Oracle Database Extensions for .NET" überhaupt installiert?

Bei der R1 der 12c wird ja alles gleich per Default mit installiert, aber nicht sofort aktiviert!

Prüfen ob das Werkzeug „oraclrctl.exe“ aufrufbar ist und die DLL „%ORACLE_HOME%\bin\oraclr12.dll“ existiert.

Damit sollte die Option auf dem System aktivierbar sein.

Ist ein passendes Microsoft .NET Framework auf dem Datenbank Server installiert?

Registry Key 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP\v4 abfragen.

Powershell Abfrage:

dir 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP\v4'

Oracle Database Extensions for .NET in der Datenbank aktivieren

Mit chopt die DB Option aktivieren:

# Datenbank Home setzen!
 
chopt enable ode_net
 
Writing to C:/oracle/products/12.1.0.2/dbhome_1/install/enable_ode_net.log...
mkdir C:\oracle\products\12.1.0.2\dbhome_1\bin\clr

Pürfen ob in der Datenbank das DB Directory „ORACLECLRDIR“ und das Package „DBMS_CLR“ existiert!

Falls nicht nachinstallieren mit „<Oracle Home>\rdbms\admin\DBMSClr.plb“ als SYSDBA über SQL*Plus!

Fehlt z.B. falls die 12c DB zuvor von einer 11g upgegraded wurde!

Den Oracle CLR Host Service anlegen

Passwort des Users, unter dem die Datenbank läuft , zuvor heraussuchen!

Mit oraclrctl -new den Oracle Database Extensions for .NET windows service anlegen

Administrative Powershell Session starten:

# Datenbank Home setzen!
 
oraclrctl -new
Enter password for orarun:
 
OracleOraDB12Home1ClrAgent service is being created.
OracleOraDB12Home1ClrAgent service was created successfully.
OracleOraDB12Home1ClrAgent service is starting..
OracleOraDB12Home1ClrAgent service was started successfully.

Es wird der Service OracleOraDB12Home1ClrAgent angelegt ( c:\oracle\products\12.1.0.2\dbhome_1\bin\OraClrAgnt.exe agent_sid=CLRExtProc max_dispatchers=2 tcp_dispatchers=0 max_task_threads=6 max_sessions=25 ENVS=\„EXTPROC_DLLS=ONLY:c:\oracle\products\12.1.0.2\dbhome_1\bin\oraclr12.dll\“)

Über die Registry lassen sich dann auch die Parameter konfigurieren.

Oracle Listener für den Aufruf des CLR konfigurieren

In der „%ORACLE_HOME%/network/admin/listener.ora“ kontrollieren, ob der CLRExtProc und der PLSExtProc Eintrag für den CLR Service auch eingetragen wurde.

listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oracle\products\12.1.0.2\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\oracle\products\12.1.0.2\dbhome_1\bin\oraclr12.dll")
    )
    (SID_DESC =
       (SID_NAME = PLSExtProc)
       (ORACLE_HOME = D:\oracle\product\12.1.0.2\dbhome_1)
       (PROGRAM = extproc)      
    )
    (SID_DESC =
      (GLOBAL_DBNAME = GPI)
      (ORACLE_HOME = C:\oracle\products\12.1.0.2\dbhome_1)
      (SID_NAME = GPI)
    )
  )
 
 
LISTENER=
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP) (HOST = 10.10.10.1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = 10.10.10.1)(PORT = 2484))
      (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1))
    )
  )

tnsnames.ora Einträge hinzufügen/prüfen:

tnsnames.ora:

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )
 
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )  
)
Test mit tnsping auf die ExtProc Einträge

Allerdings führt ein erster Test mit „tnsping“ zu einem „TNS-12541: TNS:no listener“

Lösung:

Darauf achten das der KEY in der Listener.ora und der tnsnames.ora auch den selben Wert haben!

Wie „EXTPROC1“ im obigen Beispiel!

Siehe:

 listener.ora und tnsnames.ora configuration for Dot .Net PL/SQL integratoin

Alle Einstellungen prüfen, der tnsping muss erfolgreich sein!


Ein .Net Library Objekt testen

⇒ Es sollte/muss für Deploy .Net Libarary der Oracle .Net Wizard verwendet werden !

Fehler ORA-28575 debuggen

Normalerweise:

ORA-28575 -  unable to open RPC connection to external procedure agent
Cause: tnsnames.ora and listener.ora files have not been correctly configured to use external procedures.

Auf das richtige Oracle Home im SID Descriptor achten!

Im Listner.log suchen ob gleichzeitig dort ein Fehler wie TNS-12518 auftritt!

Test:

GPI@GPI-saturn>SELECT callExternalDDLProc FROM dual;
SELECT callExternalDDLProc FROM dual
                                *
ERROR at line 1:
ORA-28575: unable TO OPEN RPC connection TO external PROCEDURE agent

Debug:

  • Tnsping auf ORACLR_CONNECTION_DATA und EXTPROC_CONNECTION_DATA durchführen, Connect muss erfolgreich sein
  • Testen ob das „ExtProc“ Programm auch aufgerufen werden kann
    PS D:\oracle\product\12.1.0.2\dbhome_1\BIN> .\extproc.exe
     
    Oracle Corporation --- TUESDAY   MAY 30 2017 18:26:13.160
     
    Heterogeneous Agent Release 12.1.0.2.0 - 64bit Production

hmm…

Könnte mit meinen Problem Listener Fehler bei einer Installation unter Windows 8.1 wie TNS-12546 - TNS-00516 - 64-bit Windows Error: 13: Permission denied zusammen hängen.

Fehler : ORA-28575: unable to open RPC connection to external procedure agent

Achtung!!

Darauf achten, das die TNSNAME.ora auch vom Oracle System User gelesen wird!

Nicht das dieser eine andere TNSNAMES.ora verwenden!

Ist man sich das alles richtig ist hilft nur weiter suchen.

Dieser manuelle Ansatz um den Fehler ORA-28575 „unable to open RPC connection to external procedure agent zu debuggen!

Ablauf:

  • DLL mit den .Net Klassen nach “%ORACLE_HOME%\BIN\clr„ kopieren, hier sucht der HOST CLR dann später nach den Dateien
  • Als SYS User ein Library Objekt anlegen
     sqlplus / AS sysdba
    CREATE OR REPLACE LIBRARY SKA_MQ_CONNECT_DDL AS 'C:\oracle\products\12.1.0.2\dbhome_1\BIN\clr\SKAMQConnect.dll';
  • Rechte an den gewünchten User vergeben
    GRANT EXECUTE ON SKA_MQ_CONNECT_DDL TO GPI;
  • PL/SQL Wrapper im User Schema für den Aufruf der DLL erstellen wie zum Beispiel:
    CREATE OR REPLACE FUNCTION callExternalDDLProc 
     RETURN VARCHAR2 AS LANGUAGE C 
     NAME "Class1.getData" 
     LIBRARY SYS.SKA_MQ_CONNECT_DDL ;
    /

Test auf einer anderen Maschine ergibt die folgende Fehlermeldung:

SCOTT@VDS-12cwin2012ora01>SELECT callExternalDDLProc FROM dual;
SELECT callExternalDDLProc FROM dual
                                *
ERROR at line 1:
ORA-06521: PL/SQL: Error mapping FUNCTION
ORA-06522: Unable TO LOAD symbol FROM DLL

D.h. hieße ja das es hier wenigstens bis zum „Anfassen“ der DLL klappt, diese aber noch nicht korrekt ist

Support:

  • ORA-06521 ORA-06522 When Using PL/SQL to Call Simple .Dll Files From C Programs (Doc ID 297906.1)
  • Master Note For PL/SQL External Procedures (Doc ID 1136408.1)
  • OERR: ORA-28575 „unable to open RPC connection to external procedure agent“ Reference Note (Doc ID 50062.1)
  • External Procedures - Troubleshooting ORA-28575 Errors (Doc ID 70638.1)

Debug: TRACE anlegen für ODE

Trace auf dem CLR Host einstellen mit Anpassen des Registry „HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_ ORACLE_HOME\ODE“ ⇒ TraceLevel auf 1 und TraceFileName so setzen das die Datei auch geschrieben werden kann.

 Trace auf dem CLR Host einstellen


Eine .Net Library erstellen

Wie erstelle ich nun aber eine passende Library?

Mir ist das zum Schluss nur über den Wizard der Oracle .Net Tools gelungen, ein manuelles Deployment scheitert an vielen Hürden.-( ..

Das Code für das erste Hello World Beispiel:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
 
namespace SKAMQConnect
{
    public class Class1
    {
        public static string getData()
        {
            string a = "Return Value from DDL";
            return a;
        } // getData
    }
}

Ablauf (eigene Windows 10 VM):

!Achtung! : Keine „normalen“ Oracle Client auf der Maschine installieren, die .Net Assemblies vertragen sich nicht!

Installation MS Visual Studio

Über die Microsoft Seite https://www.visualstudio.com/ die Comunity Edition herunterladen und installieren.

Um folgende Fehlermeldung zu vermeiden, „Use developer features - Developer mode aktiveren“ und neu booten.

2>DEP0100: Please ensure that target device has developer mode enabled. Could not obtain a developer license due to error 800704C7.
Installation der Oracle Developer Tools for Visual Studio

Keine anderen Oracle .Net Objekte auf der Visual Studio Maschine installieren!

Nicht den MSI Installer „ODTforVS2015_121025.exe“ verwenden! Hier ist das „Visual C# Oracle CLR Project templates“ gar nicht auffindbar, das wird nicht mit installiert!

Teste nun die Version ODAC 12c Release 4 and Oracle Developer Tools for Visual Studio (12.1.0.2.4) ⇒ ODTwithODAC121024.zip über http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html

Normaler Oracle Installer, alles nach „C:\oracle\product\12.1.0.2\odac_client_1“ installiert, Alles Default belassen!

Nun taucht auch im Visual Studio das „Visual C# Oracle CLR Project templates“ auf!

Ablauf:

  • Neues Projekt über File/New/Projekt  Ein neues Oracle CLR Projekt anlegen
  • Code erstellen
  • Mit „F5“ den Wizard starten  Den Oracle CLR Projekt Wizard starten
  • Falls noch keine SYS Connection zur DB existiert, diese anlegen  Den Oracle CLR Projekt Wizard SYS Connect starten
  • Dialog ausfüllen das assembly und stored proceduers angelegt werden sollen  Den Oracle CLR Projekt Wizard Assemby
  • Projekt und Library Name ausfüllen  Den Oracle CLR Projekt Wizard Namen vergeben
  • Da die DB unter 64bit läuft ⇒ Referenced assemlies auswählen!  Den Oracle CLR Projekt Wizard Assemblies auswählen
  • Methode aus der Library auswählen  Den Oracle CLR Projekt Wizard Metholden auswählen
  • Übersicht prüfen / Script bei Bedarf anzeigen lassen  Den Oracle CLR Projekt Wizard deployment starten
  • Mit „Finish“ beenden

Interessanterweise kann nun der Wizard tatsächlich die DDL's direkt über den SYS Connect zur Datenbank in das ORACLE_HOME/BIN/clr Verzeichnis kopieren und legt die Oracle Library auf die DLL + die passende PL/SQL Routine zum Aufruf des ganzen an. Die Magie dahinter steckt wohl in dem DBMS_CLR Package.

Für den Aufruf der Methode erzeugt der Wizard diesen Code:

FUNCTION GETDATA RETURN VARCHAR2 AUTHID CURRENT_USER IS
BEGIN
  DECLARE
  BEGIN
    DBMS_CLR.CLEARPARAMETERS;
    RETURN DBMS_CLR.EXECUTEVARCHARFUNCTION('SKAMQCONNECT_DLL', 'SKAMQConnect.Class1', 'getData', DBMS_CLR.UNSAFE, 'VSVER=4.0;');
  END;
END;

D.h. um hier die .Net CLR Host Methoden einzubinden wird massiv auf das Package DBMS_CLR zugegriffen und nicht direkt mit einer external PL/SQL Funktion gearbeitet.

Test nun in der Datenbank:

GPI@oragpi-saturn>SELECT gpi.getdata FROM dual;
 
GETDATA
----------------------------------------------------------------
RETURN VALUE FROM DDL

Fehler beim Deploy mit dem Oracle .Net Wizard

Leider können beim Depolyment relative viele Fehler auftreten.

Fehler - Wizard meldet Directory "ORACLECLRDIR" fehlt

Bei einer migrierten Oracle 11g nach 12 DB fehlt evtl. das ORACLECLRDIR Objekt!

Fehlender DB Directory Eintrag, neu anlegen:

connect / as sysdban
create directory ORACLECLRDIR as 'C:\oracle\products\12.1.0.2\dbhome_1\bin\clr';

Meist fehlt dann auch noch das DBMS_CLR Package

run “<Oracle Home>\rdbms\admin\DBMSClr.plb„ as SYSDBA using SQL*Plus:

cd <Oracle Home>\rdbms\admin
 
sqlplus sys/password AS sysdba
 
@DBMSClr.plb

Fehler - Wizard Connection Dialog "ORA-01017:invalid username/password; logon denied"

 Falscher User Wizard Deploy Oracle .Net ORA-01017:invalid username/password; logon denied

Hier MUSS zwingend der SYS User oder ein user mit SYSDBA Rechten angeben werden!

Es kann aber ein normaler User, der gut funktioniert, auch angegeben werden, die Fehlermeldung mit dem falschen Passwort (ORA-01017:invalid username/password; logon denied) ist dann sehr missverständlich und auf den ersten Schritt etwas irreführend!

Fehler - Could not load file or assembly

SELECT gpi.getdata FROM dual
       *
ERROR at line 1:
ORA-20100: System.BadImageFormatException
Could NOT LOAD file OR assembly 'file:///C:\oracle\products\12.1.0.2\dbhome_1\bin\clr\SKAMQConnect.dll' OR one OF its
dependencies. An attempt was made TO LOAD a program WITH an incorrect format.
ORA-06512: at "SYS.DBMS_CLR", line 243
ORA-06512: at "GPI.GETDATA", line 6

Das sieht nach einen 32/64 Bit Konflikt aus ….

Darauf achten das das Projekt auch für 64bit übersetzt wird und die assembly DLL auch mit ausgeliefert worden sind! ( im Deploy Wizard mit anwählen, muss markiert sein! )

Fehler - Wizard "Oracle Database Extensions for .Net does not support .Net Framework 4.0 or later"

Verliert Visual Studio die Connection zur DB (z.b. wenn die Datenbank neu gestartet werden muss), erfolgt beim Deployment die Fehlermeldung „Oracle Database Extensions for .Net does not support .Net Framework 4.0 or later“

 Fehler im Deploy Wizard

Visual Studio muss neu einfach gestartet werden!

Fehler beim Aufruf der PL/SQL Methode -ORA-20100: ODE-00008: Unspecified error in Delegate Invocation at line 1

Nächster Fehler ….

Prüfen welcher Patch eingespielt ist siehe Bug Bug 24911151 : ORA-20100: ODE-00008: UNSPECIFIED ERROR IN DELEGATE INVOCATION

Evlt. wurde beim Patch die Oracle ODAC componennten NICHT mit aktualiert! ( Stichwort oraprovcfg.exe /action:gac /providerpath:<assembly DLL> )

Aus dem Patch Dokument:

3.4 Oracle .NET Assembly Setup Instructions

If you are patching Oracle .NET assemblies and existing versions reside in the Global Assembly Cache (GAC), then you must remove them from the GAC prior to using OPatch. Oracle does not add its patched .NET assemblies to the GAC by default. If you require GACing, GAC after OPatch completes. The Oracle .NET assemblies include:

        Oracle.ManagedDataAccess.dll - For .NET 4
        Oracle.DataAccess.dll - For .NET 2 and .NET 4
        Oracle.Web.dll - For .NET 2 and .NET 4
        Oracle.Database.Extensions.dll - For .NET 2 and .NET 4

You can add and remove these assemblies to/from the GAC by following these steps:

1. Open a command prompt. (Open using "Run As Administrator").
2. Navigate to the directory that contains the assembly you wish to GAC. For example, the ODP.NET, Unmanaged Driver for .NET 4 is located in the %ORACLE_HOME%\ODP.NET\bin\4 directory.
3. Execute the following to unGAC the assembly:

        oraprovcfg.exe /action:ungac /providerpath:<assembly DLL>

This is an example of unGACing unmanaged ODP.NET:
   
        oraprovcfg.exe /action:ungac /providerpath:Oracle.DataAccess.dll

4. Execute the following to GAC the assembly
        oraprovcfg.exe /action:gac /providerpath:<assembly DLL>

This is an example of GACing unmanaged ODP.NET:
   
        oraprovcfg.exe /action:gac /providerpath:Oracle.DataAccess.dll

Security Probleme beim Aufruf komplexerer DDL's - File I/O Permission

In unseren Demo soll nun in der DLL ein Socket geöffnet werden.

Beim Deployment kann das Security Modell gewählt werden, SAVE, EXTERNAL, UNSAFE

siehe auch ⇒ https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/security/clr-integration-code-access-security

Auszug aus der Doku:

The security levels are:
■ Safe
In Safe level, the .NET stored procedure or function is allowed to access only database resources. Access to any external resources such as local files, networks, and so on, is not allowed. 

■ External
In External level, the .NET stored procedure or function is allowed to read or write
to local files, and to access network resources such as sockets and internet nodes, and so on.

■ Unsafe
In Unsafe level, the .NET stored procedure or function is allowed unrestricted execution including execution of unmanage d code. It is a superset of all other security levels.

Beim Deployment entsprechend setzen:

 Securtiy Mode setzen


Debug mit Visual Studio

Mit dem Remote Debugger von der DB Maschine aus

  • Remote Debuger auf DB Maschine vom Visual Studio kopieren
  • Remote Debuger als Admin starten
  • Auf „No Auth, allow all“ Security setzen
  • Einmal die Routine aufrufen
  • Damit wird ein extproc.exe Prozess vom OracleCLR gestartet
  • Auf diesem extproc Process kann nun mit dem Visual Studio „attach to Process“ anhängt werden
  • Hocheinmal die Routine aufrufen, Debugger springt auf den Break Point

Alternativ:

Siehe auch ⇒ http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56odp-087445.html


Quellen

Cookies helfen bei der Bereitstellung von Inhalten. Durch die Nutzung dieser Seiten erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Rechner gespeichert werden. Weitere Information
"Autor: Gunther Pipperr"
prog/windows_dot_net_integration_plsql.txt · Zuletzt geändert: 2018/04/24 00:26 von Gunther Pippèrr