Thursday, December 27, 2007

ODAC 11g Released

ODAC 11g Version 11.1.0.6.20 (including the Oracle Developer Tools for Visual Studio .NET and ODP.NET) has now been released to production.

Get your download here and check out the new features list!

The Bulk Copy and User-Defined Types (UDT) features are likely to be well received along with the Instant Client support.

Wednesday, December 12, 2007

Dynamically Creating a Variable In-List

Frequently SQL statements in code use an "in-list". That is, SQL that resembles the following:

select   employee_id,
         first_name,
         last_name
from     employees
where    employee_id in (102,127,206)

Here I am providing an in-list for the employee_id column. Using the HR sample that ships with Oracle Database, the above query returns the following results:

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        102 Lex                  De Haan
        206 William              Gietz
        127 James                Landry

Of course you may wish to vary the number of items in the in-list as well as the actual values. When the value of an item in the list changes, you may wish to use a bind variable for that item. See my Oracle Magazine column here for more details on bind variables and why you might want to use them.

The above SQL statement re-factored to use bind variables would look like:

select   employee_id,
         first_name,
         last_name
from     employees
where    employee_id in (:0,:1,:2)

In this re-factored statement I am still using 3 values in the in-list. This is definitely not a requirement: the in-list can contain anywhere from 1 to 1000 items. See the official Oracle documentation here for more details.

When attempting to use an in-list with bind variables you may be tempted to supply the comma-delimited values as a single string argument to the in-list. This may resemble:

select   employee_id,
         first_name,
         last_name
from     employees
where    employee_id in (:0)

In this case you may try to pass the string "102,127,206" as the value for the bind variable. However, this will not work as you might expect. You will receive "ORA-1722: invalid number" if you try this because the string "102,127,206" can not be converted to a numeric value. Instead you must create the statement with a separate bind variable for each item you wish to pass in the in-list.

One technique to do this uses a StringBuilder object to create the SQL statement and dynamically build the in-list items. Here is some sample code that illustrates this technique:

using System;
using System.Data;
using System.Text;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace VariableInListDemo
{
  class Program
  {
    static void Main(string[] args)
    {
      // change connection string for your environment
      string constr = "User Id=hr;" +
                      "Password=hr;" +
                      "Data Source=orademo;" +
                      "Pooling=false;" +
                      "Enlist=false";

      // the "base" sql statement to which the in-list will be appended
      StringBuilder sbSQL = new StringBuilder("select employee_id, " +
                                              "first_name, " +
                                              "last_name " +
                                              "from employees " +
                                              "where employee_id in (");

      // if no command-line arguments specified, simply return
      if (args.Length == 0)
      {
        Console.WriteLine("No employee_id values specified...");
        Console.WriteLine("Please specify values such as: 102,127,206");
        return;
      }
      // create and open connection
      OracleConnection con = new OracleConnection(constr);
      con.Open();

      // split the command-line argument into separate tokens
      // the command-line argument should be similar to:
      // 102,127,206
      string[] EmpNos = args[0].Split(',');

      // build the in-list part of the sql
      for (int i = 0; i < EmpNos.Length; i++)
      {
        // append the bind variables into the sql
        sbSQL.Append(":" + i.ToString());

        // add a "," after all but the last bind variable
        if (i < EmpNos.Length - 1)
        {
          sbSQL.Append(",");
        }
      }

      // close the in-list with the trailing ")"
      sbSQL.Append(")");

      // create the command object
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = sbSQL.ToString();

      // create an array of OracleParameter objects for each
      // empno specified on the command-line
      OracleParameter[] EmpNoParams = new OracleParameter[EmpNos.Length];

      // set the properties for each parameter
      // and add to parameters collection
      for (int i = 0; i < EmpNos.Length; i++)
      {
        EmpNoParams[i] = new OracleParameter();
        EmpNoParams[i].OracleDbType = OracleDbType.Decimal;
        EmpNoParams[i].Value = Convert.ToDecimal(EmpNos[i]);

        cmd.Parameters.Add(EmpNoParams[i]);
      }

      // create a data reader
      OracleDataReader dr = cmd.ExecuteReader();

      // iterate over the reader and display to console
      while (dr.Read())
      {
        Console.WriteLine(dr[0].ToString() + ", " +
                          dr[1].ToString() + ", " +
                          dr[2].ToString());
      }

      // add clean up code as necessary for your environment

      // prevent console from closing when run from VS environment
      Console.WriteLine();
      Console.WriteLine("ENTER to continue...");
      Console.ReadLine();
    }
  }
}

Running the above sample from a console window using the values "102,127,206" produces the following results:

C:\>VariableInListDemo.exe 102,127,206
102, Lex, De Haan
206, William, Gietz
127, James, Landry

ENTER to continue...

If you need to use a variable in-list in your application, I encourage you to experiment with this technique using different values and numbers of items as a possible solution.

Saturday, November 24, 2007

Enable Database Auditing

I was recently asked about a "trick" I sometimes use to help troubleshoot issues in connecting to a database - in particular, when using an Operating System authenticated connection. The "trick" is really very simple: enable database auditing, audit connections, attempt to connect as the user having the issue, and then review the results.

Oracle have, of course, much more thoroughly documented auditing than I will do here. So, as usual, I recommend taking a look at the official documentation for 9i Release 2, 10g Release 2, and/or 11g Release 1 if needed.

Beginning with the 11g release, auditing is enabled by default and the audit stream is, also by default, captured in the database in a sys-owned table called "aud$". The audit information can be captured into the operating system audit trail rather than the database if desired. Auditing is enabled (or disabled) by the audit_trail parameter. The Database Reference Guide for 9i Release 2, 10g Release 2, and 11g Release 1 contains the valid settings for this parameter as well as a description for each setting.

Verify if Auditing is Enabled

To verify if auditing is enabled in the database, connect to the database as a dba (or sysdba) user via SQL*Plus. I will use a sysdba user here as I already know that auditing is not enabled and I will, therefore, need to enable it and restart the instance.

SQL*Plus: Release 10.2.0.3.0 - Production on Sat Nov 24 15:00:05 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> connect / as sysdba
Connected.
SQL> show parameter audit_trail

NAME        TYPE        VALUE
----------- ----------- -----
audit_trail string      NONE

Enable Auditing if Necessary

Since the audit_trail parameter is NONE, I need to enable auditing by setting the value to DB, stopping the instance, starting the instance, and then verifying the change:

SQL> alter system set audit_trail='DB' scope=spfile;

System altered.

SQL> shutdown immediate;

[ database shutdown ]

SQL> startup open;

[ database opened ]

SQL> show parameter audit_trail

NAME        TYPE        VALUE
----------- ----------- -----
audit_trail string      DB

Enable Connection Auditing

In order to audit the connection attempts to the database, I need to tell Oracle that I want to do so:

SQL> audit connect;

Audit succeeded.

Attempt to Connect

To illustrate the auditing of connections, I will connect as a valid user then attempt to connect as a non-existent user. Both of these connection attempts are from a new SQL*Plus session:

SQL> connect hr
Enter password:
Connected.
SQL> connect bad_user
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Review the Results

Using a SQL*Plus session as a DBA user (here I am using my sysdba session previously started) you can query the sys.aud$ table for the results of the connection auditing:

SQL> select userid, returncode from sys.aud$;

USERID   RETURNCODE
-------- ----------
HR                0
BAD_USER       1017

2 rows selected.

Here you can see that my "good" connection attempt was done as the HR user and that the return code was 0 while the "bad" connection attempt was done as the BAD_USER user (which does not exist) and the return code was 1017. The 1017 return code corresponds to the "ORA-01017: invalid username/password; logon denied" error message.

Being able to capture and query this information can often times be helpful in determining the user id of an Operating System authenticated user that is having difficulty in establishing a connection to the database. Once you know the user id being used, you can ensure that you have properly created that user in the database. In addition, the dba_audit_session view presents information related to audited connect/disconnect operations. See your respective Oracle documentation for more details on this view.

Turning off the Connection Auditing

After performing the auditing steps above, you may wish to disable the connection auditing. To do so, simply enter the following command in SQL*Plus as a DBA user:

SQL> noaudit connect;

Noaudit succeeded.

Thursday, November 15, 2007

Cha-cha-cha-changes

With apologies to David Bowie.

Friday, 16 November 2007 marks the end (at least of this phase) of my tenure with Oracle Corporation. I will be turning in my badge. I worked with a fantastic and dedicated bunch of individuals. I wish them all the very best of luck and success. I will, of course, continue to be an Oracle advocate and will continue my relationship with Oracle "from the outside".

Tuesday, November 13, 2007

Oracle 11g and ORA-01017: invalid username/password; logon denied

If you have recently created an 11g database one behavior change that you may find confusing (if you are not aware of it) is that passwords are now case-sensitive by default. This behavior is controlled by the SEC_CASE_SENSITIVE_LOGON parameter. This behavior is easily demonstrated in SQL*Plus:

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Nov 13 19:36:05 2007

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

SQL> connect system/oracle
Connected.
SQL> show parameter sec_case_sensitive_logon

NAME                     TYPE    VALUE
------------------------ ------- -----
sec_case_sensitive_logon boolean TRUE

SQL> create user test identified by demo;

User created.

SQL> grant create session to test;

Grant succeeded.

As you can see, on my system the value of the "sec_case_sensitive_logon" logon parameter is the default of "true". This means that passwords are case-sensitive. Therefore, in order to connect as the test user I need to specify the password in lower case:

SQL> connect test/demo
Connected.

If I specify the password in anything other than the lower case I used when the user was created I will receive an error:

SQL> connect test/DEMO
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> connect test/Demo
ERROR:
ORA-01017: invalid username/password; logon denied

If you use SQL*Plus for your administrative tasks it is also possible to encounter a potentially confusing situation even if you are aware of this password case-sensitivity:

SQL> connect system/oracle
Connected.
SQL> drop user test cascade;

User dropped.

SQL> create user test identified by demo;

User created.

SQL> grant create session to test;

Grant succeeded.

SQL> connect test/demo
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

I just demonstrated that the password is case-sensitive and I am entering the password in lower case as I specified when I created the user. Or did I?

SQL> connect test/DEMO
Connected.

Well, clearly I can connect by specifying the password in upper case, but I created the user with the password in lower case and I know the value of the sec_case_sensitive_logon parameter is true...

The culprit in this case is a SQL*Plus system variable: sqlcase

SQL> show sqlcase
sqlcase UPPER

Ah ha! Because the value of the sqlcase system variable was UPPER, SQL*Plus automatically converted my SQL (including the password) to upper case. So, while I did enter the password in lower case, it was transparently converted to upper case before the statement was submitted to the database; therefore, my user was created with an upper case password!

For more information on password case sensitivity see:

What Are the Oracle Database Built-in Password Protections?
http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authentication.htm#CHDBBGFG

Enabling or Disabling Password Case Sensitivity
http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authentication.htm#CHDJDCGI

Saturday, November 10, 2007

Configuring Windows Authentication

In order to connect to Oracle running on Windows using an operating system authenticated account, there are surprisingly few steps to take:

1. Ensure the sqlnet.ora file has a required entry

2. Verify (or set) a database/instance parameter

3. Create a database user

When connecting to the database using operating system authentication, you do not need to specify the "normal" user id -- instead you simply use a "/" character (without the quotes) and no password (the whole point!). Oracle allows for two classes of operating system authenticated users: enterprise users and external users. I am illustrating how to create and connect as an external user (i.e. everything is on the same machine). Creating and using an enterprise user requires a directory server and additional coordination between the database administrator and the operating system/directory server administrator.

The sqlnet.ora file Entry

When Oracle is initially installed and configured on the Windows platform the sqlnet.ora file is created in the %ORACLE_HOME%\network\admin folder. There may be multiple entries in this file; however, in order to connect using o/s authentication the file must have an entry similar to:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

If this entry is not present in the sqlnet.ora file you are using then a connection attempt using o/s authentication will fail.

The Database/Instance Parameter

The os_authent_prefix parameter controls whether or not Oracle adds a prefix to the user id being presented during the o/s authentication process. By default the value of this parameter is "OPS$" though it is not uncommon for it to be changed or simply be set to an empty string (""). However, in order to create the user in the database, the value of this parameter must be known. Fortunately, determining the value is as simple as connecting to the database as a capable of successfully executing the SQL*Plus "show parameter" command:



SQL> show parameter os_authent_prefix


NAME TYPE VALUE
----------------- ----------- -----
os_authent_prefix string OPS$


As you can see, on my system this value has not been changed from the default value. This means that Oracle will prefix the "OPS$" value to the beginning of my Windows user name when I attempt to connect using o/s authentication. The Windows user name presented will be of the form DOMAIN or HOSTNAME\USERNAME. On my laptop (which is not part of a domain) my Windows user name will be presented to Oracle as "LIVERPOOL\MARKWILL".

Creating the Database User

Knowing that the value of the os_authent_prefix parameter is "OPS$" and that my Windows user name is "LIVERPOOL\MARKWILL" a database user named "OPS$LIVERPOOL\MARKWILL" will need to be created in the database:



SQL*Plus: Release 11.1.0.6.0 - Production on Sat Nov 10 14:07:33 2007


Copyright (c) 1982, 2007, Oracle. All rights reserved.


SQL> connect / as sysdba
Connected.
SQL> create user "OPS$LIVERPOOL\MARKWILL" identified externally
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users;


User created.


SQL> grant connect, alter session to "OPS$LIVERPOOL\MARKWILL";


Grant succeeded.


That's it!

Now I am ready to test my freshly created user that will connect to the database via my Windows-authenticated account (notice the connect string is just the "/" -- I am connecting to the database specified by the ORACLE_SID value on my system):


SQL*Plus: Release 11.1.0.6.0 - Production on Sat Nov 10 14:11:14 2007


Copyright (c) 1982, 2007, Oracle. All rights reserved.


SQL> connect /
Connected.
SQL> COL USERNAME FORMAT A32
SQL> COL DATABASE FORMAT A24
SQL> SELECT A.USERNAME,
2 B.GLOBAL_NAME DATABASE
3 FROM USER_USERS A,
4 GLOBAL_NAME B;


USERNAME DATABASE
-------------------------------- ------------------------
OPS$LIVERPOOL\MARKWILL LT11GR1.SAND


1 row selected.

If you wish to connect to a database by explicitly specifying a tns alias the connect string would look like:

connect /@tnsalias

For example:

connect /@lt11gr1

Specifying an Operating System Authenticated Account in ODP.NET

Just as the connect string in SQL*Plus consists of a "/" and possibly a tns alias, the connect string in an ODP.NET application can be as simple as:

"User Id=/"

or

"User Id=/; Data Source=mydatasource"

NOTE: There is a behaviour change from ODP.NET versions prior to 11g and the 11g release. In previous versions connection pooling was not used for operating system authenticated connections regardless of the setting of the "pooling" parameter in the connection string. Beginning with the 11g release, connection pooling is now supported using operating system authenticated connections.

Happy connecting!

Friday, October 19, 2007

ODP.NET Tip: Using Pipelined Functions

When using PL/SQL to return data to your ODP.NET client application typically that data is returned using a REF CURSOR or using PL/SQL Associative Arrays. I have written about using REF CURSORs here and about using PL/SQL Associative Arrays here.

However, another method is also available: using pipelined functions. Since the official documentation covers pipelined functions well, I will not go over them here.

The following simple PL/SQL code can be used to demonstrate this technique.

Note: I am using version 10.2.0.3 of all components in this sample.

-- the pl/sql package
create or replace package pipeline_test as
  -- output rows will be of this type
  type output_type is record (
    first_name varchar2(16),
    last_name  varchar2(16),
    instrument varchar2(16)
  );
 
  -- the type the function will return
  type output_set is table of output_type;
 
  -- the pipelined function that returns the rows
  function get_rows return output_set pipelined;
end;
 
-- the pl/sql package body
create or replace package body pipeline_test as
  function get_rows return output_set pipelined is
    -- used to build each row
    this_rec output_type;
  begin
    -- add row for John
    this_rec.first_name := 'John';
    this_rec.last_name := 'Lennon';
    this_rec.instrument := 'Guitar';
    pipe row(this_rec);
 
    -- add row for Paul
    this_rec.first_name := 'Paul';
    this_rec.last_name := 'McCartney';
    this_rec.instrument := 'Bass';
    pipe row(this_rec);
 
    -- add row for George
    this_rec.first_name := 'George';
    this_rec.last_name := 'Harrison';
    this_rec.instrument := 'Guitar';
    pipe row(this_rec);
 
    -- add row for Ringo
    this_rec.first_name := 'Ringo';
    this_rec.last_name := 'Starr';
    this_rec.instrument := 'Drums';
    pipe row(this_rec);
 
    return;
  end;
end;


Once you have created the PL/SQL Package and Package Body you can test the functionality from within SQL*Plus as follows:

SQL> select * from table(pipeline_test.get_rows());
 
FIRST_NAME       LAST_NAME        INSTRUMENT
---------------- ---------------- ----------------
John             Lennon           Guitar
Paul             McCartney        Bass
George           Harrison         Guitar
Ringo            Starr            Drums
 
4 rows selected.


The output is precisely what we want: a "normal" table.

After having created the PL/SQL code and verifying it functions as expected, the ODP.NET code is equally straight-forward:

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
 
namespace PipelineTest
{
  class Program
  {
    // connection string - change as appropriate
    private const string constr = "User Id=markwill;" +
                                  "Password=oracle;" +
                                  "Data Source=otndemo;" +
                                  "Pooling=false;" +
                                  "Enlist=false";
 
    static void Main(string[] args)
    {
      // create and open connection
      OracleConnection con = new OracleConnection(constr);
      con.Open();
 
      // create and setup command
      // the "table" keyword is used with the pipelined function
      // in the pl/sql package body to get the rows
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = "select * from table(pipeline_test.get_rows())";
 
      // get a data reader
      OracleDataReader dr = cmd.ExecuteReader();
 
      // loop through the data reader printing the data to the console
      while (dr.Read())
      {
        Console.WriteLine("{0} {1} - {2}", dr[0].ToString(), dr[1].ToString(), dr[2].ToString());
      }
 
      Console.WriteLine();
 
      // clean up
      dr.Dispose();
      cmd.Dispose();
      con.Dispose();
 
      // prevent console window from automatically closing
      Console.WriteLine("ENTER to continue...");
      Console.ReadLine();
    }
  }
}

Friday, September 21, 2007

ODP.NET: Using ObjectDataSource, Ref Cursors, and Custom Classes

When creating applications such as ASP.NET applications, often times the application is split into various conceptual or physical layers. There are typically three layers in such a scheme and they may generally be broken out as the presentation layer, the business logic layer, and the data access layer. Depending on your specific needs, standards, etc. sometimes the layers may be folded into a single layer. For example, you may choose to implement your business logic using stored procedures and co-locate the business logic layer with the data access layer in the database.

There are plusses and minuses (or trade-offs) associated with the architectural decisions for an application and I don't intend to propose any one being better than another. Instead, I would like to illustrate a simple method of creating a presentation layer and a data access layer using ObjectDataSource controls, Ref Cursors, and custom classes. There is no business logic layer in this simple example as it would merely be a proxy or wrapper of the data access layer. The data access layer returns collections of custom classes to the presentation layer using the .NET Framework 2.0 System.Collections.Generic.List generic type.

The sample uses a simple ASP.NET page:




In order to provide the data access layer with data from the database the following PL/SQL Package and Package Body should be created as the HR user in the database you plan to use:

create or replace package human_resources as
  procedure get_departments (p_departments out sys_refcursor);
 
  procedure get_department_employees(p_department_id in employees.department_id%type,
                                     p_employees out sys_refcursor);
end;
/
 
create or replace package body human_resources as
  procedure get_departments (p_departments out sys_refcursor) is
  begin
    open p_departments for
      select   department_id,
               department_name
      from     departments
      order by department_name;
  end;
 
  procedure get_department_employees(p_department_id in employees.department_id%type,
                                     p_employees out sys_refcursor) is
  begin
    open p_employees for
    select   employee_id,
             first_name,
             last_name
    from     employees
    where    department_id = p_department_id
    order by last_name,
             first_name;
  end;
end;
/



This sample uses a single ASP.NET web page (default.aspx) and three C# code files:

Department.cs -- the department class in the .NET code (get_departments procedure)

Employee.cs -- the employee class in the .NET code (get_department_employees procedure)

EmployeeDAL.cs -- the data access layer. This code accesses the database and returns the collection of either Department or Employee objects to the presentation layer.

The components that make up the Visual Studio Solution are as follows in the Solution Explorer:



As with the PL/SQL code in the database, the C# code is simple and omits many normal features such as error handling. In addition, the classes only implement "get" functionality for the class properties in order to keep the code as short and as simple as possible.

The Department class has DepartmentID and DepartmentName properties and is as follows:

public class Department {
  private int _department_id;
  private string _department_name;
 
  public int DepartmentID {
    get {
      return _department_id;
    }
  }
 
  public string DepartmentName {
    get {
      return _department_name;
    }
  }
 
  public Department(int DepartmentID, string DepartmentName) {
    _department_id = DepartmentID;
    _department_name = DepartmentName;
  }
}



The Employee class exposes EmployeeID, FirstName, and LastName properties:

public class Employee {
  private int _employee_id;
  private string _first_name;
  private string _last_name;
 
  public int EmployeeID {
    get {
      return _employee_id;
    }
  }
 
  public string FirstName {
    get {
      return _first_name;
    }
  }
 
  public string LastName {
    get {
      return _last_name;
    }
  }
 
  public Employee(int EmployeeID, string FirstName, string LastName) {
    _employee_id = EmployeeID;
    _first_name = FirstName;
    _last_name = LastName;
  }
}



The EmployeesDAL class handles connecting to the database, executing the stored procedures, building the collection of objects, and returning those objects to the caller:

public class EmployeesDAL {
  private static string constr = "User Id=hr; Password=hr; Data Source=lt10gr2; Enlist=false";
 
  public List<Department> GetDepartments()
  {
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    OracleCommand cmd = con.CreateCommand();
    cmd.CommandText = "human_resources.get_departments";
    cmd.CommandType = CommandType.StoredProcedure;
 
    OracleParameter p_ref_cur = new OracleParameter();
    p_ref_cur.OracleDbType = OracleDbType.RefCursor;
    p_ref_cur.Direction = ParameterDirection.Output;
 
    cmd.Parameters.Add(p_ref_cur);
 
    OracleDataReader dr = cmd.ExecuteReader();
 
    List<Department> Departments = new List<Department>();
 
    while (dr.Read())
    {
      Department thisDept = new Department(dr.GetInt32(0), dr.GetString(1));
 
      Departments.Add(thisDept);
    }
 
    dr.Dispose();
    cmd.Dispose();
    con.Dispose();
 
    return Departments;
  }
 
  public List<Employee> GetDepartmentEmployees(int p_department_id) {
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    OracleCommand cmd = con.CreateCommand();
    cmd.CommandText = "human_resources.get_department_employees";
    cmd.CommandType = CommandType.StoredProcedure;
 
    OracleParameter p_dept_id = new OracleParameter();
    p_dept_id.OracleDbType = OracleDbType.Decimal;
    p_dept_id.Value = p_department_id;
 
    OracleParameter p_ref_cur = new OracleParameter();
    p_ref_cur.OracleDbType = OracleDbType.RefCursor;
    p_ref_cur.Direction = ParameterDirection.Output;
 
    cmd.Parameters.Add(p_dept_id);
    cmd.Parameters.Add(p_ref_cur);
 
    OracleDataReader dr = cmd.ExecuteReader();
 
    List<Employee> Employees = new List<Employee>();
 
    while (dr.Read()) {
      Employee thisEmp = new Employee(dr.GetInt32(0), dr.GetString(1), dr.GetString(2));
 
      Employees.Add(thisEmp);
    }
 
    p_dept_id.Dispose();
    p_ref_cur.Dispose();
    dr.Dispose();
    cmd.Dispose();
    con.Dispose();
 
    return Employees;
  }
}



The ASP.NET page consists of simple text to denote the Department data which is presented in a DropDownList, a GridView control to display the employees in the selected department, and two ObjectDataSource controls. This is the Default.aspx page at design time:



To configure the ObjectDataSource1 control select "Configure Data Source..." from the quick tasks configuration:

Choose a business object:



Click Next and then select the GetDeparments method for the SELECT tab and click Finish:



Next, configure the DropDownList1 control which displays the Department information. Ensure that the EnableAutoPostBack option is checked in the quick task configuration or that the AutoPostBack property is True in the Properties window. To configure the Data Source for the control, select "Choose Data Source..." from the quick task configuration and complete as follows:



The ObjectDataSource2 control should be configured next by clicking the "Configure Data Source..." option on the quick tasks configuration:

Again, choose a Business Object:



Followed by the GetDepartmentEmployees method for the SELECT tab:



The GetDepartmentEmployees method takes an input parameter that specifies the Department ID for which employees should be retrieved. Therefore, it is necessary to configure the parameter input value so that the Department ID selected by the user in the DropDownList1 control is passed to the GetDepartmentEmployees method:




Finally, associate the ObjectDataSource2 control with the GridView1 control by choosing "ObjectDataSource2" in the "Choose Data Source" drop-down list for the GridView quick tasks. Of course you may also choose to enable paging and formatting as you desire.

Now that both the DropDownList1 and the GridView1 web controls are "hooked up" to their respective ObjectDataSource controls, and the ObjectDataSource controls are, in turn, configured to expose the data returned by the EmployeesDAL class, it is time to run the sample page!

Sunday, August 12, 2007

ODP.NET Tip: Bind Variables and the BindByName Property

Note: For a brief introduction to bind variable usage in an ODP.NET application, see my September/October 2005 Oracle Magazine article "The Values That Bind".

When using bind variables in the CommandText property of an OracleCommand object, it is important to understand the meaning of the value of the BindByName property for the OracleCommand object. This property is a read/write boolean property which defaults to false. The fact that this property defaults to false means that ODP.NET operates, by default, in what is known as "bind by position" mode. In this mode, when OracleParameter objects are added to the parameters collection for an OracleCommand object, they must be added to the collection in the same order in which they appear in the CommandText property.

For example, say you have the following:

// assumes cmd is an object of type OracleCommand
cmd.CommandText = "select employee_id from hr.employees where first_name = :1 and last_name = :2"

In this sample, there are 2 bind variables used (denoted by :1 and :2 in the text). Using the default setting (false) for the BindByName property this means that the OracleParameter object for the first_name placeholder should be added to the parameters collection first followed by the OracleParameter object for the last_name placeholder.

In contrast to this, if you set the BindByName property to true, you may add the OracleParameter objects to the parameters collection in any order you wish. Of course, if you set the property to true, you also need to ensure that the ParameterName property of the OracleParameter object matches the name used in the CommandText (without the ":").

Another area where the value of the BindByName property may be the source of confusion is when using a PL/SQL function. For example, say you have a function that returns a value and accepts 2 parameters as input. When you use this function in your code, the following pseudo-code represents what is sent to the database:

:return_value := some_function (:parameter1, :parameter2)

Notice that the return value is the first bind variable used. Therefore, in bind by position mode (the default!), you must add the OracleParameter object for the return value to the parameters collection first (not last!), followed by the parameter object for "parameter1" and then "parameter2".

Thursday, May 10, 2007

ODP.NET Tip: Ref Cursors and FetchSize

One useful technique for increasing performance in your ODP.NET applications is controlling the fetch size. In fact, I discussed this technique in the ODP.NET column in the July/August 2006 issue of Oracle Magazine. In terms of performance, fetching the data from the database to the client tier (or a middle tier) can be one of the more expensive operations in an application’s execution. By controlling the fetch size (i.e. the amount of data fetched during each round-trip to the database) you can dramatically decrease (or increase!) the amount of time taken to fetch the data.

However, if you are working with Ref Cursors rather than "plain" SQL statements, the ability to control the fetch size is not readily available. This is because the RowSize property of an OracleCommand object is set to 0 (zero) when working with PL/SQL code, as would be the case with Ref Cursor data. A simple example will illustrate why this is so. The RowSize property is an integer property. Let’s say you have the following Anonymous PL/SQL code block associated with an OracleCommand object:


begin
  open :1 for select * from scott.emp;
  open :2 for select * from scott.dept;
  open :3 for select * from scott.salgrade;
end;


Upon execution of this block, there will be three Ref Cursors opened. Therein lies the issue: A single integer property (RowSize) exposed by the OracleCommand object cannot properly represent the row size for the three opened Ref Cursors. Therefore, the property value is set to 0.

Typically you would create an OracleDataReader object for each of the Ref Cursors opened and read the data into the client. However, the OracleDataReader does not expose a RowSize property. Therefore, you can not set the FetchSize property on the OracleDataReader object to a specific number of rows using the normal technique of assigning the product of RowSize * NumberOfRowsToFetch to the FetchSize property. Keep in mind the RowSize property is 0 for the OracleCommand object and the OracleDataReader object does not expose a RowSize property.

However, if you have ever examined the members of the OracleDataReader in the debug window within Visual Studio, you may have noticed that the OracleDataReader does have a particularly enticing non-public member named "m_rowSize" which just happens to represent the row size. If only you could access that value you could then easily set the FetchSize property on the OracleDataReader to a specific (meaningful) value rather than be forced to accept the default of 64KB (65536). To see this, assume you have the following code:

OracleDataReader dr = cmd.ExecuteReader();

After executing this code while stepping through your application in the Visual Studio debugger, expand the "dr" node in the "Locals" window. Next navigate to the "Non-Public members" node, and expand this node. Scroll down the list and eventually you will find the "m_rowSize" member:

Image of m_rowSize in debugger

Now, of course, as this is a Non-Public member, you can’t just reference "m_rowSize" in your application code to retrieve this value. However, using the "reflection" capabilities of the .NET Framework, you can access this value. Once you have done that, you can set the value of the FetchSize property to whatever value is appropriate for your application.

Please note that this technique is not necessarily a supported technique, and, of course, if the "m_rowSize" member name should change, it would break any code using this technique - Caveat Emptor.

Here is a complete sample application using the SH sample schema. I used this schema simply because the SALES table is the largest table in the supplied sample schemas and, thus, is one that benefits from controlling the fetch size more than a small table (less than 64KB).



PL/SQL Code:

-- run as SH user

-- the SH user has the largest table in the demo schemas

create or replace procedure get_sales (p_refcur out sys_refcursor) is

begin

  -- open the cursor using the passed in ref cursor

  -- sys_refcursor is a built in type

  open p_refcur for

  select  *

  from    sales;

end;

/



C# Application Code:

using System;

using System.Data;

using System.Reflection;

using Oracle.DataAccess.Client;

using Oracle.DataAccess.Types;

 

namespace RefCursorFetchTest

{

  class Program

  {

    // connection string -- be sure to adjust for your environment

    public const string constr = "User Id=sh; Password=sh; Data Source=otndemo; Pooling=false; Enlist=false";

 

    static void Main(string[] args)

    {

      // create and open connection

      OracleConnection con = new OracleConnection(constr);

      con.Open();

 

      // enable extended sql tracing

      // this can be used to verify the number of rows fetched

      // uncomment to create trace file in user_dump_dest directory

      // EnableExtendedTrace(con);

 

      // do the fetch test

      // passing 0 for "rows" will use default fetch size of 64k

      FetchTest(con, 100);

      FetchTest(con, 1000);

      FetchTest(con, 10000);

      FetchTest(con, 0);

 

      // clean up

      con.Dispose();

 

      // prevent console window from automatically closing

      Console.Write("ENTER to continue...");

      Console.ReadLine();

    }

 

    static void EnableExtendedTrace(OracleConnection con)

    {

      // create, setup, and execute command to enable extended sql trace

      OracleCommand cmd = con.CreateCommand();

      cmd.CommandText = "alter session set events '10046 trace name context forever, level 4'";

      cmd.ExecuteNonQuery();

 

      // clean up

      cmd.Dispose();

    }

 

    static void FetchTest(OracleConnection con, int rows)

    {

      // used to track execution duration

      DateTime timeStart;

      DateTime timeEnd;

      double totalSeconds;

 

      // create and setup command to call stored procedure

      OracleCommand cmd = con.CreateCommand();

      cmd.CommandText = "get_sales";

      cmd.CommandType = CommandType.StoredProcedure;

 

      // create the ref cursor parameter and add to command parameter collection

      OracleParameter prm = new OracleParameter("p_refcur", OracleDbType.RefCursor, ParameterDirection.Output);

      cmd.Parameters.Add(prm);

 

      // get the data reader

      OracleDataReader dr = cmd.ExecuteReader();

 

      // There are 3 steps in getting the m_rowSize property value...

 

      // Step 1 - get the data reader type

      Type type = dr.GetType();

 

      // Step 2 - get the "m_rowSize" field info

      FieldInfo fi = type.GetField("m_rowSize", BindingFlags.Instance | BindingFlags.NonPublic);

 

      // Step 3 - get the value of m_rowSize for the dr object

      int rowSize = (int)fi.GetValue(dr);

 

      // if rows is greater than 0 use to set fetch size,

      // otherwise use default size (64k)

      if (rows > 0)

      {

        // set the fetch size on the dr object using the row size

        dr.FetchSize = rowSize * rows;

      }

 

      // capture test start time

      timeStart = DateTime.Now;

 

      // simply loop forcing the entire result set to be fetched

      while (dr.Read())

      {

      }

 

      // capture test end time

      timeEnd = DateTime.Now;

 

      // calculate total seconds for this test

      totalSeconds = timeEnd.Subtract(timeStart).TotalSeconds;

 

      // display time used for test

      Console.WriteLine("Number of rows = {0,5}: {1,8:###.0000} total seconds, fetch size = {2,9:###,###,###} bytes.", rows, totalSeconds, dr.FetchSize);

 

      // clean up

      dr.Dispose();

      prm.Dispose();

      cmd.Dispose();

    }

  }

}






Execution results on my laptop:
Number of rows = 100: 10.3125 total seconds, fetch size = 20,800 bytes.
Number of rows = 1000: 4.9219 total seconds, fetch size = 208,000 bytes.
Number of rows = 10000: 2.4219 total seconds, fetch size = 2,080,000 bytes.
Number of rows = 0: 4.0625 total seconds, fetch size = 65,536 bytes.
ENTER to continue...


Sunday, May 06, 2007

ODP.NET Tip: Retrieving dbms_output Text

If you frequently work with PL/SQL code from your ODP.NET application you may have encountered code that uses the dbms_output package. This is a common debugging technique employed in PL/SQL code. In fact, the Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) has this to say about the dbms_output package:

The DBMS_OUTPUT package enables you to send messages from stored procedures, packages, and triggers. The package is especially useful for displaying PL/SQL debugging information.

In practice, calls to dbms_output are embedded in the PL/SQL code and then the resulting text is often displayed in a character-based tool such as SQL*Plus. But what if you are using an ODP.NET application? How is it possible to retrieve values into your client application that have been output by the dbms_output package into a server-side buffer?

Fortunately the dbms_output package provides two procedures that can be used to accomplish this:

- get_line
- get_lines

As their names suggest, get_line is used to retrieve a single line from the buffer while get_lines can be used to retrieve multiple lines from the buffer.

In this tip I show you how to use both of these procedures in a single sample application.

In order to retrieve a single line of text, you use a parameter object that represents a Varchar2 data type and a parameter object that represents a Decimal data type. The Varchar2 parameter holds the actual line of text while the Decimal parameter holds the status code returned from the dbms_output package.

The code to retrieve multiple lines of text also uses two OracleParameter objects: one parameter object is a PL/SQL Associative Array to hold the lines of text and the other is a Decimal parameter that holds the number of lines to fetch before the call to dbms_output or the number of lines that were fetched after the call to dbms_output.

Be sure to review the documentation for dbms_output linked above.

Begin by creating the PL/SQL code as the database user that will connect to the database from the ODP.NET application. Of course, this is just sample code to illustrate this technique. Your real code would perform additional activities and almost certainly would be more complex.

The PL/SQL code for the sample package and package body:

create or replace package dbms_output_test as

  procedure emit_single_line;

  procedure emit_multiple_lines;

end;

/

 

create or replace package body dbms_output_test as

  procedure emit_single_line is

  begin

    /* enable dbms_output using defaults */

    dbms_output.enable;

 

    /* output a single line of text */

    dbms_output.put_line('This is a simple line of text emitted by dbms_output.');

  end;

 

  procedure emit_multiple_lines is

  begin

    /* enable dbms_output using defaults */

    dbms_output.enable;

 

    /* output multiple lines of text */

    dbms_output.put_line('Sgt. Pepper''s Lonely Hearts Club Band Track List');

    dbms_output.put_line('================================================');

    dbms_output.put_line('01 - Sgt. Pepper''s Lonely Hearts Club Band');

    dbms_output.put_line('02 - With a Little Help From My Friends');

    dbms_output.put_line('03 - Lucy in the Sky With Diamonds');

    dbms_output.put_line('04 - Getting Better');

    dbms_output.put_line('05 - Fixing a Hole');

    dbms_output.put_line('06 - She''s Leaving Home');

    dbms_output.put_line('07 - Being for the Benefit of Mr. Kite!');

    dbms_output.put_line('08 - Within You Without You');

    dbms_output.put_line('09 - When I''m Sixty-Four');

    dbms_output.put_line('10 - Lovely Rita');

    dbms_output.put_line('11 - Good Morning Good Morning');

    dbms_output.put_line('12 - Sgt. Pepper''s Lonely Hearts Club Band (Reprise)');

    dbms_output.put_line('13 - A Day in the Life');

  end;

end;

/



As you can see, this code simply outputs text using the dbms_output package and performs no other processing. The emit_single_line procedure outputs a single line of sample text while the emit_multiple_lines procedure outputs the track listing for Sgt. Pepper's Lonely Hearts Club Band by The Beatles.

The C# sample code is fairly well commented and should be easy to follow:

using System;

using System.Data;

using Oracle.DataAccess.Client;

using Oracle.DataAccess.Types;

 

namespace DbmsOutput

{

  class Program

  {

    // connect to default database using o/s authenticated account

    // be sure to adjust for your environment

    private const string constr = "user id=/; enlist=false; pooling=false";

 

    static void Main(string[] args)

    {

      // create and open connection

      OracleConnection con = new OracleConnection(constr);

      con.Open();

 

      // call method for single line output

      GetSingleLine(con);

 

      // call method for multiple line output

      GetMultipleLines(con);

 

      // clean up

      con.Dispose();

 

      // prevent console from closing automatically when run from within VS

      Console.Write("ENTER to continue...");

      Console.ReadLine();

    }

 

    static void GetSingleLine(OracleConnection con)

    {

      // call the procedure that emits a single line of text

      string stored_procedure = "dbms_output_test.emit_single_line";

 

      // anonymous pl/sql block to get the line of text

      string anonymous_block = "begin dbms_output.get_line(:1, :2); end;";

 

      // create command and execute the stored procedure

      OracleCommand cmd = con.CreateCommand();

      cmd.CommandText = stored_procedure;

      cmd.CommandType = CommandType.StoredProcedure;

      cmd.ExecuteNonQuery();

 

      // create parameters for the anonymous pl/sql block

      OracleParameter p_line = new OracleParameter("",

                                                  OracleDbType.Varchar2,

                                                  32000,

                                                  "",

                                                  ParameterDirection.Output);

 

      OracleParameter p_status = new OracleParameter("",

                                                    OracleDbType.Decimal,

                                                    ParameterDirection.Output);

 

      // set command text and parameters to get the text output

      // and execute the anonymous pl/sql block

      cmd.CommandText = anonymous_block;

      cmd.CommandType = CommandType.Text;

      cmd.Parameters.Add(p_line);

      cmd.Parameters.Add(p_status);

      cmd.ExecuteNonQuery();

 

      // write location, return status value, and the text to the console window

      Console.WriteLine("In method GetSingleLine...");

      Console.WriteLine("Return status: {0}", p_status.Value.ToString());

      Console.WriteLine("Return text: {0}", p_line.Value.ToString());

      Console.WriteLine();

      Console.WriteLine();

 

      // clean up

      p_line.Dispose();

      p_status.Dispose();

      cmd.Dispose();

    }

 

    static void GetMultipleLines(OracleConnection con)

    {

      // write location to console window

      Console.WriteLine("In method GetMultipleLines...");

      Console.WriteLine();

 

      // call the procedure that emits multiple lines of text

      string stored_procedure = "dbms_output_test.emit_multiple_lines";

 

      // anonymous pl/sql block to get multiples lines of text per fetch

      string anonymous_block = "begin dbms_output.get_lines(:1, :2); end;";

 

      // used to indicate number of lines to get during each fetch

      const int NUM_TO_FETCH = 8;

 

      // used to determine number of rows fetched in anonymous pl/sql block

      int numLinesFetched = 0;

 

      // simple loop counter used below

      int i = 0;

 

      // create command and execute the stored procedure

      OracleCommand cmd = con.CreateCommand();

      cmd.CommandText = stored_procedure;

      cmd.CommandType = CommandType.StoredProcedure;

      cmd.ExecuteNonQuery();

 

      // create parameter objects for the anonymous pl/sql block

      OracleParameter p_lines = new OracleParameter("",

                                                    OracleDbType.Varchar2,

                                                    NUM_TO_FETCH,

                                                    "",

                                                    ParameterDirection.Output);

 

      p_lines.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

      p_lines.ArrayBindSize = new int[NUM_TO_FETCH];

 

      // set the bind size value for each element

      for (i = 0; i < NUM_TO_FETCH; i++)

      {

        p_lines.ArrayBindSize[i] = 32000;

      }

 

      // this is an input output parameter...

      // on input it holds the number of lines requested to be fetched from the buffer

      // on output it holds the number of lines actually fetched from the buffer

      OracleParameter p_numlines = new OracleParameter("",

                                                      OracleDbType.Decimal,

                                                      "",

                                                      ParameterDirection.InputOutput);

 

      // set the number of lines to fetch

      p_numlines.Value = NUM_TO_FETCH;

 

      // set up command object and execute anonymous pl/sql block

      cmd.CommandText = anonymous_block;

      cmd.CommandType = CommandType.Text;

      cmd.Parameters.Add(p_lines);

      cmd.Parameters.Add(p_numlines);

      cmd.ExecuteNonQuery();

 

      // get the number of lines that were fetched (0 = no more lines in buffer)

      numLinesFetched = ((OracleDecimal)p_numlines.Value).ToInt32();

 

      // as long as lines were fetched from the buffer...

      while (numLinesFetched > 0)

      {

        // write the text returned for each element in the pl/sql

        // associative array to the console window

        for (i = 0; i < numLinesFetched; i++)

        {

          Console.WriteLine((p_lines.Value as OracleString[])[i]);

        }

 

        // re-execute the command to fetch more lines (if any remain)

        cmd.ExecuteNonQuery();

 

        // get the number of lines that were fetched (0 = no more lines in buffer)

        numLinesFetched = ((OracleDecimal)p_numlines.Value).ToInt32();

      }

 

      // just a couple of separator lines

      Console.WriteLine();

      Console.WriteLine();

 

      // clean up

      p_numlines.Dispose();

      p_lines.Dispose();

      cmd.Dispose();

    }

  }

}



Executing the sample produces the following output in the console window:

In method GetSingleLine...

Return status: 0

Return text: This is a simple line of text emitted by dbms_output.

 

 

In method GetMultipleLines...

 

Sgt. Pepper's Lonely Hearts Club Band Track List

================================================

01 - Sgt. Pepper's Lonely Hearts Club Band

02 - With a Little Help From My Friends

03 - Lucy in the Sky With Diamonds

04 - Getting Better

05 - Fixing a Hole

06 - She's Leaving Home

07 - Being for the Benefit of Mr. Kite!

08 - Within You Without You

09 - When I'm Sixty-Four

10 - Lovely Rita

11 - Good Morning Good Morning

12 - Sgt. Pepper's Lonely Hearts Club Band (Reprise)

13 - A Day in the Life

 

 

ENTER to continue...



Happy coding...