Using .NET Stored Procedures in Oracle 10gR2

1

From the OTN Series on “Mastering .NET Application Development with Oracle”, this installment on taking advantage of .NET stored procedures in your applications. See Using .NET Stored Procedures in Oracle

PL/SQL stored procedures and functions run in the same process as the Oracle Database and are stored inside of Oracle. A .NET stored procedure, on the other hand, runs in an external process and the .NET code is compiled into a “.NET assembly,” which is a dynamic link library (DLL) file stored in the file system (usually on the same machine as the database). The .NET assembly is loaded into and executed inside of a “CLR host” external process named extproc.exe, which is spawned by the Windows service named ClrAgnt. When a .NET stored procedure call is made, Oracle communicates with this external process, passing in the arguments and retrieving the results. This communication is handled by the Oracle multithreaded agent architecture. To the end user, a .NET stored procedure call appears to be no different than any other type of stored procedure call. In fact, a .NET stored procedure can be called from any environment where you could call a PL/SQL or Java stored procedure.

If you are using Oracle Database 10g Express Edition, .NET stored procedures are automatically installed and configured—no additional configuration is required. In the Standard and Enterprise Editions, however, .NET stored procedures are not installed and configured by default. Note: you need Oracle Database 10.2 or later for the Windows platform. (Note: .NET stored procedures are not supported on any other platform!)

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

1 Comment

  1. I´m having the following problem using Oracle .Net procedues.

    I´ve made a function that return an accent a (á) but when I call it, returns another value.

    Public Shared Function x1() As String
    Return “á”
    End Function

    Public Shared Function x2() As String
    Dim oConDB As New DoConexionOracle(Constants.STRCON)
    Return oConDB.ObtenerEscalarFromSQL(“select ‘á’ FROM DUAL”)
    End Function

    SELECT x1() FROM DUAL
    á

    SELECT x2() FROM DUAL
    á

    Any idea?
    TIA,
    Christian