Gunther Oracle APEX und Datenbank Wiki

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.

• 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

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

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.

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

Powershell Abfrage:

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

Mit chopt die DB Option aktivieren ( vor 19c!):

# 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!

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.

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)
    )  
)

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:

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

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

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
• Beide Dienste CLR/Listener stoppen und neu starten
• 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

Siehe auch : Configuring Listener To Spawn EXTPROC Agent Is Not Working in 12.2, EXTPROC_CONNECTION_DATA Is Not Getting Resolved. (Doc ID 2326592.1)

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)

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.

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):

• Installation MS Visual Studio 2015 über https://www.visualstudio.com/
• Installation der Oracle Developer Tools for Visual Studio 2015 über http://www.oracle.com/technetwork/developer-tools/visual-studio/overview/index-097110.html

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

Ü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.

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
• Code erstellen
• Mit „F5“ den Wizard starten
• Falls noch keine SYS Connection zur DB existiert, diese anlegen
• Dialog ausfüllen das assembly und stored proceduers angelegt werden sollen
• Projekt und Library Name ausfüllen
• Da die DB unter 64bit läuft ⇒ Referenced assemlies auswählen!
• Methode aus der Library auswählen
• Übersicht prüfen / Script bei Bedarf anzeigen lassen
• 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

Leider können beim Depolyment relative viele Fehler auftreten.

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

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!

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! )

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“

Visual Studio muss neu einfach gestartet werden!

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

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:

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

Oracle Dokumentation:

Oracle® Database Extensions for .NET Developer's Guide 12c Release 1 (12.1) for Microsoft Windows

• https://docs.oracle.com/cd/E48297_01/doc/win.121/e18596/toc.htm#BEGIN

Oracle® Database Extensions for .NET Developer's Guide for Microsoft Windows

• 11g ⇒ https://docs.oracle.com/cd/E11882_01/win.112/e17724.pdf
• 12c ⇒ https://docs.oracle.com/database/121/CLRNT/E18596-03.pdf

Database Platform Guide Microsoft Windows

• https://docs.oracle.com/database/121/NTQRF/apps.htm#NTQRF190

Microsoft

Message Queuing (MSMQ)

• https://msdn.microsoft.com/en-us/library/ms711472(v=vs.85).aspx

Installation MSMQ

• https://msdn.microsoft.com/en-us/library/aa967729(v=vs.110).aspx

.Net

• https://msdn.microsoft.com/en-us/library/ms978430.aspx
• https://www.codeproject.com/Articles/3944/Programming-MSMQ-in-NET-Part
• https://stackoverflow.com/questions/11076790/the-bare-minimum-needed-to-write-a-msmq-sample-application
• https://www.codeproject.com/Articles/5830/Using-MSMQ-from-C

Software:

• https://www.visualstudio.com/

Oracle Examples ⇒ siehe http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html

Weitere Ideen:

http://www.oracle.com/technetwork/database/enterprise-edition/calling-shell-commands-from-plsql-1-1-129519.pdf

Wird in 12c nicht mehr unterstützt ⇒ https://oracle-base.com/articles/8i/com-automation-8i