Database Reference
In-Depth Information
String sql = "SELECT pad FROM t WHERE val =
:val
";
OracleCommand command = new OracleCommand(sql, connection);
OracleParameter parameter = new OracleParameter("val", OracleDbType.Int32);
command.Parameters.Add(parameter);
OracleDataReader reader;
for (int i = 0; i < 10000; i++)
{
parameter.Value = Convert.ToInt32(i);
reader = command.ExecuteReader();
if (reader.Read())
{
pad = reader[0].ToString();
}
reader.Close();
}
With ODP.NET, it's not possible to implement test case 3. However, to achieve the same result, you can use
client-side statement caching. There are two methods for enabling it and setting the size of the cache. The first, which
controls statement caching for all applications using a specific Oracle home, is by setting the following value in the
registry. If it's set to 0, statement caching is disabled. Otherwise, statement caching is enabled, and the value specifies
the size of the cache (
<Assembly_Version>
is the full version number of
Oracle.DataAccess.dll
).
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\<Assembly_Version>\StatementCacheSize
The second method controls statement caching directly in the code through the
Statement Cache Size
attribute provided by the
OracleConnection
class. Basically, it plays the same role as the registry value but for a single
connection. The following code snippet shows how to enable statement caching and to set its size to 10:
String connectString = "User Id=" + user +
";Password=" + password +
";Data Source=" + dataSource +
";
Statement Cache Size
=10";
OracleConnection connection = new OracleConnection(connectString);
Note that the setting at the connection level overrides the setting in the registry. In addition, when statement caching
is enabled, it's possible to disable it at the command level by setting the
AddToStatementCache
property to
false
.
The C# code used for the examples in this section is an excerpt from the
ParsingTest1.cs
and
ParsingTest2.cs
files that implement test case 1 and 2, respectively.
PHP
In PHP, the PECL OCI8 extension provides full control over the life cycle of cursors. For example, in the following code
snippet, which implements test case 2, notice how every step is explicitly coded:
$sql = "SELECT pad FROM t WHERE val = :val";
for ($i = 1; $i <= 10000; $i++)
{
$statement =
oci_parse
($connection, $sql);
oci_bind_by_name
($statement, ":val", $i, -1, SQLT_INT);
oci_execute
($statement, OCI_NO_AUTO_COMMIT);
