7 Replies Latest reply: Dec 24, 2008 9:15 AM by 675337 RSS

    .NET stored procedures with VS 2008

    675337
      Howdy,

      I have been having troubles getting .NET stored procedures to work with VS 2008 and Oracle XE 10.2.

      I am able to develop and deploy the stored procedure without any issues. I just can't seem to execute the stored procedure without receiving an error.

      Here is the error message:
      ORA-20100:
      ORA-06512: at "SYS.DBMS_CLR", line 234
      ORA-06512: at "<USERNAME>.<STORED_PROC_NAME>", line 7
      ORA-06512: at line 1

      My installation was as follows:
      1. Installed Visual Studio 2008
      2. Installed Oracle XE 10.2
      3. Installed ODTwithODAC1110621

      I have spent several hours trying to find the remedy, with no luck. Some posts state that the problem is with references to Oracle.DataAccess.dll others state that .NET stored procedures will not work with XE 10.2 and VS 2008.

      I really need to get .NET stored procedures working with VS2008 (it is the only version of VS that I have). Can anyone suggest how I can get .NET stored procedures to work with VS 2008? I am willing to install a different Oracle DB version if needed.

      Let me know if you require more information.

      Thanks!
        • 1. Re: .NET stored procedures with VS 2008
          502182
          Since you are using ODT 11.1.0.6.21, .NET Stored Procedures developed with this version of ODT will work with Oracle Database 11g. Please make sure that you install ODE (Oracle Database Extensions for .NET) for .NET 2.x in your database server home from the same installer ODTwithODAC1110621.
          • 2. Re: .NET stored procedures with VS 2008
            675337
            Thanks for the response, I will give 11g a try.

            It will take a little time for me to download and install 11g, my internet connection is pretty slow (~40kB/s).

            I will let you know how this works out.
            • 3. Re: .NET stored procedures with VS 2008
              675337
              Sorry for the delayed update, I am still experiencing problems with .NET stored procedures and Oracle.

              All installations are on a single machine, my laptop.
              1. Installed Oracle 11g database (Enterprise) (C:\app\product\11.1.0\db_1)
              2. Installed Oracle ODTwithODAC1110621 for client (C:\app\product\11.1.0\client_1)
              3. Installed Oracle ODTwithODAC1110621 for server (installed to existing database instance i.e.: C:\app\product\11.1.0\db_1)
              4. Followed instructions for "Configuring the .NET Stored Procedures Environment" (http://www.oracle.com/technology/obe/10gr2_db_single/install/odpinst/odpinst.htm)
              5. Followed instructions for " Developing and Deploying a .NET Stored Function" (http://www.oracle.com/technology/obe/10gr2_db_single/develop/vs2/vs2.htm)
              6. Stored Procedure appears to be deployed successfully and no errors reported.

              Originally I was getting the following error:
              ORA-20100: ODE-00005: Could not create type: Oracle.Database.Extensions.OracleAppDomainFactory, Oracle.Database.Extensions, Version=2.111.6.0, Culture=neutral, PublicKeyToken=89b483f429c47342
              ORA-06512: at "SYS.DBMS_CLR", line 243
              ORA-06512: at "HR.GETDEPARTMENTNO", line 7
              ORA-06512: at line 1

              From one of skj's post I realized that the referenced oracle.database.extensions.dll was version 1.111.6.0.

              I used gacutil -i c:\app\product\11.1.0\db_1\ode.net\2.x\bin\oracle.database.extensions.dll to add the reference to version 2.111.6.0

              I am now getting the following error when I try to execute the stored procedure:

              ORA-20100: Oracle.DataAccess.Client.OracleException
              ORA-00942: table or view does not exist
              at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
              at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
              at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
              at Oracle.DataAccess.Client.OracleCommand.ExecuteReader()
              at Project4.Class1.getDepartmentno(Int32 employee_id)
              ORA-06512: at "SYS.DBMS_CLR", line 152
              ORA-06512: at "HR.GETDEPARTMENTNO", line 7
              ORA-06512: at line 1

              I have tried creating and executing the stored procedure with the SYSTEM user and other users. I always receive the same messages.

              Any idea what to try next?

              Edited by: user10675413 on Dec 22, 2008 8:27 AM
              • 4. Re: .NET stored procedures with VS 2008
                502182
                Your .NET stored procedure configuration looks correct.

                Regarding the error you are getting - the .NET stored procedure code seems to be trying to access some table which does not exist in the schema being used. For example, you are using context connection in .NET SP; Invoking it with HR user and trying to access EMP table then you will get this error since EMP is available in SCOTT schema and not in HR schema. This is an application error.
                • 5. Re: .NET stored procedures with VS 2008
                  675337
                  Maybe i am not understanding what you are saying, here's an example that I tried that also failed with error:
                  1. Created a user called 'Keith' with dba priveledges
                  2. Created a new table called 'gifts' (2 columns: GiftID (PK) as Number, GiftName as VarChar(200))
                  3. Created a simple stored proc called GetGiftName in VS 2008 (it retrieves a GiftName based on a GiftID)
                  4. Successfully deployed the stored proc using connection Keith.ORCL
                  5. In VS2008 the Server Explorer pane shows connection Keith.ORCL
                  6. I went to "Keith.ORCL->Schemas->Keith->Tables" and ensured table GIFTS exists
                  7. I go to "Keith.ORCL->Schemas->Keith->Functions->GetGiftName" right click and select "Run"
                  8. I enter a value for the giftID
                  9. Error Message is displayed

                  ORA-20100: Oracle.DataAccess.Client.OracleException
                  ORA-00942: table or view does not exist
                  at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
                  at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
                  at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
                  at Oracle.DataAccess.Client.OracleCommand.ExecuteReader()
                  at MyStoredProcedure.Class1.GetGiftName(Decimal GiftID)
                  ORA-06512: at "SYS.DBMS_CLR", line 243
                  ORA-06512: at "KEITH.GETGIFTNAME", line 7
                  ORA-06512: at line 1

                  From my understanding, what I described should have worked. The Gifts Table belonged to the Keith Schema as did the GetGiftName stored procedure. I deployed the stored procedure using a connection with Keith's credentials.

                  Then again maybe I misinterpreted the meaning of your last reply.
                  • 6. Re: .NET stored procedures with VS 2008
                    502182
                    What if you access your table from within .NET SP as "Keith.GIFTS" instead of "GIFTS"?
                    • 7. Re: .NET stored procedures with VS 2008
                      675337
                      Awesome, it is now working without error. You rock skj.

                      It's been years since I have written data-access code. Thanks for helping me to get .NET stored procs to work with Oracle. Also, thanks for the data-access refresher.

                      Rock On!