Implicit REF CURSOR Binding

Specifying REF CURSOR Bind and Metadata Information in the .NET Configuration File

Specify the REF CURSOR information in the oracle.dataacccess.client configuration section of the .NET configuration file. Use an <add> element for each piece of information. The add element uses name-value attributes to specify REF CURSOR information. Use the following format to specify bind information:

<add name="SchemaName.PackageName.StoredProcedureName.RefCursor.RefCursorParameterPositionOrName" 
value="implicitRefCursor bindinfo='mode=InputOutput|Output|ReturnValue'" />

Use the following format to specify metadata information:

<add name="SchemaName.PackageName.StoredProcedureName.RefCursorMetaData.RefCursorParameterPositionorName.Column.ColumnOrdinal" 
value="implicitRefCursor metadata=AttributesList" />

Each REF CURSOR column needs to have an add element defined for it. For example, if you have a REF CURSOR returning five columns, then you need to define five add elements in the config file.

Each add element contains the name and value attributes. The value attribute must begin with the word implicitRefCursor followed by the bindinfo or metadata attribute for specifying bind or metadata information.

The bindinfo information is used by ODP.NET for binding REF CURSOR parameters. The metadata information is used by ODP.NET to associate the schema information with the appropriate REF CURSOR. The metadata comprises of an attributes list that includes parameters together with their values.

The SchemaName, PackageName, and StoredProcedureName are case-sensitive. In order to run a stored procedure with implicit REF CURSOR binding, the SchemaName.PackageName.StoredProcedureName portion of the name attribute must exactly match the name specified in the data dictionary for that stored procedure.

If any schema, package, or stored procedure name in the database contains lowercase characters, then it must be enclosed within double quotation marks (") in the config file to preserve the case. Double quotation marks are used within the name attribute by using " when needed. For example, if the schema name is HrSchema, the package name is HrPackage, and the stored procedure name is HrStoredProcedure in the database, the config file should use the following:

<add name="&quot;HrSchema&quot;.&quot;HrPackage&quot;.&quot;HrStoredProcedure&quot;.RefCursorMetaData . . . />

By default, Oracle Database stores these names as uppercase characters. ODP.NET assumes default behavior, and converts all names to uppercase characters unless you explicitly preserve the case by using double quotation marks.

Depending on whether the application uses bind-by-name or bind-by-position, the RefCursorParameterPositionOrName portion of the name attribute must be set with the correct parameter position (for bind by position) or parameter name (for bind by name). For functions, the position is 0-based, where the position 0 represents the return value. For procedures, the position is 1-based, as there are no return values for procedures. For example, if a stored procedure accepts five parameters, returning only two REF CURSORs in the third and fifth parameter positions, then the .NET config REF CURSOR bind information should contain one entry for position 3 and one entry for position 5.

If bind-by-name is used, the attribute name is used to identify the REF CURSOR parameter. The name should use the same name and case as the one specified in the data dictionary for that stored procedure.

For bindinfo, the mode specifies the parameter direction of the parameter. The mode must be either InputOutput, Output, or ReturnValue.

For metadata, The AttributesList contains the list of parameters. Table 3-16 describes the parameters that can be included in the AttributesList.

Example 3-1 shows a sample add element that uses bindinfo. Here, the schema name is SCOTT and the stored procedure name is TESTPROC. The parameter name is parameter1. The mode is output.

<add name="SCOTT.TESTPROC.RefCursor.parameter1" value="implicitRefCursor
 bindinfo='mode=Output'" />

Example 3-2 shows a sample add element that uses metadata.

<add name="scott.TestProc.RefCursorMetaData.parameter1.Column.0" 
value="implicitRefCursor metadata='ColumnName=EMPNO;BaseColumnName=EMPNO;
BaseSchemaName=SCOTT;BaseTableName=EMP;NativeDataType=number;
ProviderType=Int32;DataType=System.Int32;ColumnSize=4;AllowDBNull=false;
IsKey=true'" />

Some of the attributes, listed in Table 3-16, automatically have their values set using the result set's metadata. Developers can override these default values by setting a value explicitly.

You may have to explicitly define some attributes listed as optional for certain operations. For example, updateable REF CURSOR requires the developer to define key information.

Sample Configuration File and Application

This section builds a sample application to illustrate implicit REF CURSOR binding. It contains the following topics:

• Sample Stored Procedure and Function

• Sample Application Configuration File

• Sample Application That Uses the Configuration File

Sample Stored Procedure and Function

CREATE OR REPLACE FUNCTION GETEMP (
  EMPID IN NUMBER) return sys_refcursor is
  emp sys_refcursor;
BEGIN
  OPEN emp FOR SELECT empno, ename FROM emp where empno = EMPID;
  return emp;
END;
/
 
CREATE OR REPLACE PROCEDURE "GetEmpAndDept" (
  EMPS OUT sys_refcursor,
  DEPTS OUT sys_refcursor) AS
BEGIN
  OPEN EMPS for SELECT empno, ename from emp;
  OPEN DEPTS for SELECT deptno, dname from dept;
END;
/

Sample Application Configuration File

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <oracle.dataaccess.client>
    <settings>
 
      <!-- The following is for SCOTT.GETEMP -->
      <add name="SCOTT.GETEMP.RefCursor.0" 
           value="implicitRefCursor bindinfo='mode=ReturnValue'" />
 
      <!-- The following is for SCOTT.GETEMP's REF CURSOR metadata -->
      <add name="SCOTT.GETEMP.RefCursorMetaData.0.Column.0"
           value="implicitRefCursor metadata='ColumnName=EMPNO;
           BaseColumnName=EMPNO;BaseSchemaName=SCOTT;BaseTableName=EMP;
           NativeDataType=number;ProviderType=Int32;ProviderDBType=Int32;
           DataType=System.Int32;ColumnSize=4;NumericPrecision=10;
           NumericScale=3;AllowDBNull=false;IsKey=true'" />
 
      <add name="SCOTT.GETEMP.RefCursorMetaData.0.Column.1"
           value="implicitRefCursor metadata='ColumnName=ENAME;
           BaseColumnName=ENAME;BaseSchemaName=SCOTT;BaseTableName=EMP;
           NativeDataType=varchar2;ProviderType=Varchar2;
           ProviderDBType=String;DataType=System.String;
           ColumnSize=10;AllowDBNull=true'" />
 
      <!-- The following is for "SCOTT"."GetEmpAndDept" -->
      <add name="SCOTT.&quot;GetEmpAndDept&quot;.RefCursor.EMPS" 
           value="implicitRefCursor bindinfo='mode=Output'" />
 
      <!-- The following is for SCOTT.GETEMP's EMPS REF CURSOR metadata -->
      <add name="SCOTT.&quot;GetEmpAndDept&quot;
           .RefCursorMetaData.EMPS.Column.0"
           value="implicitRefCursor metadata='ColumnName=EMPNO;
           BaseColumnName=EMPNO;BaseSchemaName=SCOTT;BaseTableName=EMP;
           NativeDataType=number;ProviderType=Int32;ProviderDBType=Int32;
           DataType=System.Int32;ColumnSize=4;NumericPrecision=10;
           NumericScale=3;AllowDBNull=false;IsKey=true'" />
 
      <add name="SCOTT.&quot;GetEmpAndDept&quot;
           .RefCursorMetaData.EMPS.Column.1"
           value="implicitRefCursor metadata='ColumnName=ENAME;
           BaseColumnName=ENAME;BaseSchemaName=SCOTT;BaseTableName=EMP;
           NativeDataType=varchar2;ProviderType=Varchar2;
           ProviderDBType=String;DataType=System.String;
           ColumnSize=10;AllowDBNull=true'" />
 
      <!-- The following is for SCOTT.GETEMP's DEPTS REF CURSOR metadata -->
      <add name="SCOTT.&quot;GetEmpAndDept&quot;.RefCursor.DEPTS" 
           value="implicitRefCursor bindinfo='mode=Output'" />
 
      <add name="SCOTT.&quot;GetEmpAndDept&quot;
           .RefCursorMetaData.DEPTS.Column.0"
           value="implicitRefCursor metadata='ColumnName=DEPTNO;
           BaseColumnName=DEPTNO;BaseSchemaName=SCOTT;BaseTableName=DEPT;
           NativeDataType=number;ProviderType=Int32;ProviderDBType=Int32;
           DataType=System.Int32;ColumnSize=4;NumericPrecision=10;
           NumericScale=3;AllowDBNull=false;IsKey=true'" />
 
      <add name="SCOTT.&quot;GetEmpAndDept&quot;
           .RefCursorMetaData.DEPTS.Column.1"
           value="implicitRefCursor metadata='ColumnName=DNAME;
           BaseColumnName=DNAME;BaseSchemaName=SCOTT;BaseTableName=DEPT;
           NativeDataType=varchar2;ProviderType=Varchar2;
           ProviderDBType=String;DataType=System.String;
           ColumnSize=10;AllowDBNull=true'" />
      </settings>
  </oracle.dataaccess.client>
</configuration>

Sample Application That Uses the Configuration File

using System;
using System.Data;
using Oracle.DataAccess.Client;
 
class Program
{
  static void Main(string[] args)
  {
    try
    {
      // Open a connection
      string constr =
        "User Id=scott;Password=tiger;Data Source=inst1";
      OracleConnection con = new OracleConnection(constr);
      con.Open();
 
      // Use implicit REF CURSOR binding 
      //   to execute SCOTT.GETEMP function
      // Use bind by position as configured 
      //   in app.config for SCOT.GETEMP
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = "SCOTT.GETEMP";
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.BindByName = false;
      OracleParameter empid = cmd.Parameters.Add("empid", 
        OracleDbType.Int32, ParameterDirection.Input);
      empid.Value = 7654;
 
      // Populate the DataSet
      OracleDataAdapter adapter = new OracleDataAdapter(cmd);
      DataSet ds = new DataSet();
      adapter.Fill(ds);
      Console.WriteLine("Retrieved {0} row from EMP", 
        ds.Tables[0].Rows.Count);
 
      // Use implicit REF CURSOR binding 
      //   to execute "SCOTT"."GetEmpAndDept" procedure
      // Use bind by name as configured 
      //   in app.config for "SCOTT"."GetEmpAndDept"
      cmd = con.CreateCommand();
      cmd.CommandText = "\"SCOTT\".\"GetEmpAndDept\"";
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.BindByName = true;
      adapter = new OracleDataAdapter(cmd);
      adapter.Fill(ds);
      Console.WriteLine("Retrieved {0} rows from DEPT", 
        ds.Tables[1].Rows.Count);
    }
    catch (Exception ex)
    {
      // Output the message
      Console.WriteLine(ex.Message);
      if (ex.InnerException != null)
      {
        // If any details are available regarding
        // errors in the app.config, print them out
        Console.WriteLine(ex.InnerException.Message);
        if (ex.InnerException.InnerException != null)
        {
          Console.WriteLine(
            ex.InnerException.InnerException.Message);
        }
      }
    }
  }
}

Usage Considerations

This section discusses the following usage considerations when using implicit REF CURSOR:

• CommandText Property Considerations

• Bind Considerations

• Overloaded Stored Procedures

• Type Initialization Exceptions

• Using Stored Functions with Function Import