This demonstration uses Oracle Developer Tools for Visual Studio extensively although some processes can be performed with other Oracle tools. Also, the demonstration refers to the following components of Oracle Developer Tools for Visual Studio:

• Oracle Explorer

• Oracle Project

• Oracle Deployment Wizard for .NET

In this demonstration, you will develop and deploy a .NET stored function named GetDeptNo, with a PL/SQL wrapper, GETDEPTNO. The GetDeptNo function accepts an employee number (EMPNO), performs a query, and returns the department number (DEPTNO) of the employee.

1. Open Visual Studio and connect as scott/password. See Oracle Developer Tools for Visual Studio Help for information about connecting.
2. From the Visual Studio menu, select File, then New Project.
3. To create an Oracle Project template, select the project type Visual C# Projects, and select Oracle Project.
4. Name the project CLRLibrary1 and provide a location for it.

   A class named CLRLibrary1.Class1 appears. It contains a template for a stored procedure.

   
   Description of the illustration oracleproject.gif
5. Copy the following code over the base class and save.

   using System;
   // use the ODP.NET provider
   using Oracle.DataAccess.Client;
   using Oracle.DataAccess.Types;
    
   namespace CLRLibrary1
   {
     // Sample .NET stored function returning department number for 
     // a given employee number
     public class Class1
     {
       public static int GetDeptNo(int empno)
       {
         int deptno = 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 DEPTNO FROM EMP WHERE EMPNO = :1";
    
         cmd.Parameters.Add(":1",OracleDbType.Int32,empno, 
           System.Data.ParameterDirection.Input);
    
         OracleDataReader rdr = cmd.ExecuteReader();
    
         if (rdr.Read())
           deptno = rdr.GetInt32(0);
    
         rdr.Close();
         cmd.Dispose();
         conn.Close();
    
         return deptno;
    
       } // GetDeptNo
     } // Class1
   } // CLRLibrary1
   

6. From the Build menu, select Build Solution or Build CLRLibrary1.

   This builds the code into an assembly.

7. Save.

1. From the Build menu, select Deploy CLRLibrary1.
   
   Description of the illustration deploymenu.gif
2. When the Welcome window appears, select Do not show this page again, if you want.

   Then, click Next.

   
   Description of the illustration titlepage.gif
3. The Configure your OracleConnection window appears.

   Choose your connection from the drop-down list, and click Next or click New Connection, if you are not connected.

   You must choose or add a SYSBA connection.

   
   Description of the illustration usingdeploywizard1.gif
4. If you have selected New Connection, the Add Connection window appears.

   In the Connection Details tab, select the Data source name from the drop-down list.You can select an option to Use Windows integrated authentication or an option Use a specific user name and password, and enter that information.If you want, select Save password.The option for Role shows SYSDBA, which is the only available option.If you want to test the connection, click Test connection.Click OK,

   
   Description of the illustration sysdbaaddconnection.gif
5. The Specify your deployment option window appears.

   The first time you run the deployment wizard, select Copy assembly and generate stored procedures. If you later modify your function or stored procedure, you can run the deployment wizard again, and choose to perform just one of these operations.Click Next.

   
   Description of the illustration usingdeploywizard2.gif
6. The Specify an assembly and library name window appears.

   To specify the assembly, select the project from the drop-down list, or select File, and click Browse to navigate to the one you want.

   To specify the name of the library database object to be used for the selected assembly, accept the default, select the name from the drop-down list, or enter a new name.

   For this demonstration, accept the default project and library name and click Next.

   
   Description of the illustration usingdeploywizard3.gif
7. The Specify Copy Options window appears.

   To specify the dependent assemblies to copy to the database, select them from the list. The list displays all possible dependent assemblies. In this case, the assemblies displayed have already been copied to the database and, therefore, there is no need to copy them. To deploy the assembly to a directory other than the default bin\clr directory, modify the destination path. The destination must be a bin\clr directory or one of its existing subdirectories.

   For this demonstration, do not select any dependent assemblies, and do not modify the destination path.

   If you want to, select Overwrite the file if it already exists in the database.

   Then, click Next.

   
   Description of the illustration copyoptions.gif

1. Choose GetDeptNo() from the list of procedures and functions contained within that assembly.
2. The schema initially says SYS. Change it to Scott, so that you can deploy it in the scott schema.
3. Accept the default security level. You can either click Next to continue, or you can click Parameter Type Mapping... to view the type mappings.
   
   Description of the illustration specifymethoddetails.gif

   See Also:

   Step 3: Choose the Procedure or Function to Deploy and Security Level

1. Review the summary.
2. To verify SQL commands, select Show Script.
   
   Description of the illustration summary.gif
3. When the Show Sql window appears, review the code for the PL/SQL wrapper and click OK to return to the Summary window.
   
   Description of the illustration showsqldialog.gif
4. Click Finish to deploy the GetDeptNo() function.