Skip Headers
Oracle® Data Provider for .NET Developer's Guide
10g Release 2 (10.2)

Part Number B14307-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

Introducing .NET Stored Procedure Execution Using ODP.NET

Oracle Data Provider for .NET classes and APIs provide data access to the Oracle Database from a .NET client application and from .NET stored procedures and functions.

However, some limitations and restrictions exist when Oracle Data Provider for .NET is used within a .NET stored procedure. These are discussed in the next section.

The following is a simple .NET stored procedure example.

using System;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
public class CLRLibrary1
{
    // .NET Stored Function returning the DEPTNO of the employee whose
    // EMPNO is 'empno'
    public static uint GetDeptNo(uint empno)
    {
        uint deptno = 0;
 
        // Check for context connection
        OracleConnection conn = new OracleConnection();
        if( OracleConnection.IsAvailable == true )
        {
            conn.ConnectionString = "context connection=true";
        }
        else
        {
            //set connection string for a normal client connection
            conn.ConnectionString = "user id=scott;password=tiger;" + 
            "data source=oracle";
        }
        
        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 = (uint)rdr.GetInt32(0);
        rdr.Close();
        cmd.Dispose();
        conn.Close();
        return deptno;
    } // GetDeptNo
} // CLRLibrary1