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.
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,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.
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.
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".
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
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$
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.
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.
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;
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.
using System;using System.Data;using Oracle.DataAccess.Client;using Oracle.DataAccess.Types;namespace PipelineTest{class Program
{ // connection string - change as appropriateprivate const string constr = "User Id=markwill;" +
"Password=oracle;" + "Data Source=otndemo;" + "Pooling=false;" + "Enlist=false";static void Main(string[] args)
{ // create and open connectionOracleConnection 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 updr.Dispose();
cmd.Dispose();
con.Dispose();
// prevent console window from automatically closingConsole.WriteLine("ENTER to continue...");
Console.ReadLine();}
}
}
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;
/
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;
}
}
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;
}
}
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;}
}
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".
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:
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;
/
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...
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;
/
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();
}
}
}
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...
