DevOps, CI/CD and Automation

I can create and deploy a .NET stored procedure but when I run it either from Visual Studio 2005 Oracle Explorer or TOAD or SQLPlus I get the following:

ORA-20100: The system cannot find the file specified.
ORA-06512: at "SYS.DBMS_CLR", line 211
ORA-06512: at "PROD.GETSEGADDRNO", line 7
ORA-06512: at line 1

It is basically the same C# code as the OTN sample except for accessing a table in my database.

I found a forum thread dated 2006 with basically the same error message and it said the problem was fixed in Oracle Database patched to 10.2.0.2 but I can't find such a patch for 10g R2 Enterprise.

This is my first time setting up Server 2003, Oracle 10g and ODT for .NET

(I successfully created and deployed an external procedures for Oracle 8.1.7 on Windows 2000 a couple of years ago)

This is the code:
--------------------------------------
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
// use the ODP.NET provider
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace CLRLibrary1
{
// Sample .NET stored function returning address number (ADDR_NO) for
// a given segment number (SEG_NO)
public class Class1
{
public static int GetSegAddrNo(int segno)
{
int addrno = 0;

// Check for context connection
OracleConnection conn = new OracleConnection();
if (OracleConnection.IsAvailable == true)
{
conn.ConnectionString = "context connection=true";
}
else
{
throw new InvalidOperationException("context connection" +
"not available");
}

conn.Open();
// Create and execute a command
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT ADDR_NO FROM SEG WHERE SEG_NO = :1";

cmd.Parameters.Add(":1", OracleDbType.Int32, segno,
System.Data.ParameterDirection.Input);

OracleDataReader rdr = cmd.ExecuteReader();

if (rdr.Read())
addrno = rdr.GetInt32(0);

rdr.Close();
cmd.Dispose();
conn.Close();

return addrno;

} // GetSegAddrNo
} // Class1
} // CLRLibrary1
--------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
was installed from the Oracle downloads site: 10201_database_win32.zip

On Windows Server 2003 R2 Standard Edition SP2

Oracle .NET tools was installed using: ODTwithODAC1020221.exe

The CLR Service is running

Deployment says that it is successfull.
The DLL is in the expected Oraclehome\BIN\CLR folder

The deployment script is:
-----------------------------------
CREATE OR REPLACE LIBRARY "SYS"."ORATEST6_DLL" AS '$ORACLE_HOME\bin\clr\OraTest6.dll';
/
GRANT EXECUTE ON "SYS"."ORATEST6_DLL" TO PROD;
GRANT EXECUTE ON "SYS"."DBMS_CLR" TO PROD;
GRANT EXECUTE ON "SYS"."DBMS_CLRTYPE" TO PROD;
GRANT EXECUTE ON "SYS"."DBMS_CLRPARAMTABLE" TO PROD;
CREATE OR REPLACE FUNCTION PROD.GETSEGADDRNO wrapped
a000000
1f
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
147 138
ePHIr6kvu6nZ9gYrxgs40akg18owg1zQf/ZqZ3QC2vjqaIs25soMp1nBmBfBj1ZboVvNkdPT
8qhpuEVdmv4n2nlF5ynrhLsm3F0h9ZGrTQ4tGYIybWjMFxYNlDDeBOx1i7O0GviP8AxHys23
kW8aTVA2xV8IuLNlwP7KWFw70tGbg+lUIlRhYo5WmLW6N9wcjOCeLZK6W6s6tvd4zKAQykzc
sIsaneE0FWyV9Q1BD0yJpLoCfWH+f6fVXsne5Feyxu68k5geCJ8F5FCLm2ycvxIiKNKTIpiD
q4wshbsYLTTPHX3F6pA=

/
-----------------------------------
The parameter mapping is:
return value int32 number
segno int32 number
-----------------------------------

Bob Sorrell
bsorrell@level8systems.com
863-662-5524