protected OClient.OracleConnection getOracleConnection() {
return new OClient.OracleConnection(MyConnection.ConnectionString);
}using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using OClient = Oracle.DataAccess.Client; // (1)
public partial class Class1 : System.Web.UI.Page {
protected void Page_Load(object sender, EventArgs e) {
using (OClient.OracleConnection oConn = getOracleConnection()) { // (2)
using (OClient.OracleCommand oCmd = new OClient.OracleCommand()) {
oCmd.Connection = oConn;
oCmd.CommandText = "mypackage.sayhello"; // (3)
oCmd.CommandType = System.Data.CommandType.StoredProcedure; // (4)
// Use name binding (instead of positional). Makes pl/sql parameter
// names part of the public api (can't change them without changing
// calling code), but moreclear and less error prone than positional.
oCmd.BindByName = true; // (5)
OClient.OracleParameter inSessionToken = new OClient.OracleParameter(
"in_session_token", OClient.OracleDbType.Long, ParameterDirection.Input);
OClient.OracleParameter outCursor_text = new OClient.OracleParameter(
"out_text", OClient.OracleDbType.RefCursor, ParameterDirection.Output);
// Should be first parameter added (see below)
oCmd.Parameters.Add(outCursor_text); // (6)
oCmd.Parameters.Add(inSessionToken);
inSessionToken.Value = 462; // just to show data getting passed in
oConn.Open();
Label1.Text = String.Format("MYPACKAGE.SAYHELLO: {0} at {1}",
oCmd.ExecuteScalar(), System.DateTime.Now); // (7)
}
}
}
}
- 1) There are various class name collisions between the Oracle.DataAccess.Client package and the System.Data.OracleClient package, and you need to reference both. Here the "using" alias helps make the code more readable while disambiguaging to the correct classes. Otherwise Oracle.DataAccess.Client would be sprinked throughout the code. It's really just syntactic sugar, but it helps.
- 2) Here the other (more important) application of "using" is managing the database resources. Note the nested calls for the connection and the command. This just saves boilerplate resource management code. Note that "using" calls IDispose on the underlying object. In the case of ADO, this should either return the connection to the pool (if pooling) or close the connection if not-\-assuming the provider was correctly written.
- 3) This is where the package.procedure is specified. Of course, you may just be using a procedure directly without a package (just use the procedure name by itself).
- 4) This is required to let the driver know you are calling a stored proc
- 5) I'm using named parameters, otherwise you could use positional parameters like :1, :2 , etc.
- 6) As I explain in the comments, it's safest to just add the ref cursor parameter first for the ExecuteScalar to work properly.
- 7) Note, that it doesn't make a lot of sense to spin up a ref cursor just to get a single "cell" of data out of it--you'd probably just want to return a varchar2 from a function instead. However, it's a quick way to show that it works. When a single value is all that's needed, ExecuteScalar can be used.
PACKAGE BODY "MYPACKAGE" IS
PROCEDURE "SAYHELLO" (
in_session_token IN PLS_INTEGER,
out_text OUT sys_refcursor) IS
BEGIN
open out_text for select 'Oracle says "Hello" for session token ' ||
in_session_token from dual;
END "SAYHELLO";
END "MYPACKAGE";You can also use the OracleRefCursor class in conjunction with the
OracleDataAdapter class to populate DataTables and DataSets. Also, note that
the ExecuteScalar method for a refcursor must be returned from the function or the must be the first out bind parameter.
Oracle article on using ref cursors with .Net
The ref cursor is then accessed as either an OracleDataReader object or a
DataSet, by use of the OracleDataAdapter class.
Oracle article on using the ways you can handle a ref cursor
If you only need read-only data, use the OracleDataReader to populate a DataSet. If you need offline or updateability, use OracleDataAdapater.
