For those of you working with Oracle and using the Oracle OleDb Provider for .Net. In other words, your connectionstring provider looks like Provider=OraOLEDB.Oracle;....... as opposed to Provider=MSDAORA;......
You probably have come across the issue of wanting to get a ref cursor back from a stored procedure. If you haven't already noticed, it doesn't work without some modifications to your connection string:
If your procedure has the following signature:
PROCEDURE
someProcedure(identifier in varchar,out_cursor out CURSOR_DEFINITION);
Then your connectionstring should look like this:
Provider=OraOLEDB.Oracle;OLEDB.NET=true;PLSQLRSet=true;Password=[password];User ID=[userid];Data Source=[DataSource]
Notice the following differences about this connectionstring
- PLSQLRSet=true This states that resultsets can be returned from stored procedures.
- OLEDBNet=True This means that OraOLEDB is compatible with the OLE DB .Net data provider. If you set this to true, it cannot be used with ADO.
Unfortunately, you cannot set these attributes at the object level like you could with ADO.
Your c# code, would look something like this:
using( OleDbConnection oracleConn = new OleDbConnection( _connectionString ) )
{
// open connection
try{oracleConn.Open();}
catch{throw new LogonException();}
// build command
OleDbCommand cmd = new OleDbCommand("{call someProcedure(?)}", oracleConn );
cmd.CommandType = CommandType.Text;
// add parameters
cmd.Parameters.Add( "identifier", OleDbType.VarChar ).Value = identifier;
OleDbDataAdapter da = new OleDbDataAdapter( cmd );
// fill dataset
DataSet ds = new DataSet();
da.Fill( ds);
// all done, return
return ds;
}
-Mathew Nolton
For those of you wanting a break from techno-babble for at least a couple of minutes.
This is funny and it hits a bit close to home. http://www.iondesign.net/drinkometer/
-Mathew Nolton
<diagnostics><trace enabled="false" input="inputtrace.xml" output="outputtrace.xml"/>diagnostics>
Normally, this is part of my own checklist, but I missed it and it started tacking on up to 3 seconds to each xml web call (albeit the trace file got up to 14 MB). Tsk. Tsk. Fortunately I found it very quickly and it was in a qa environment and not production, but do not make the same mistake.
-Mathew Nolton
<compilation debug=true/> Trying to figure out what this attribute really does if you do not compile it as a debug release so that pdb files are created for you. In other words if you compile it to release mode initially so that there are no pdb files, then what does setting this attribute to true really buy you.
This article gives a nice little description of why.
http://samples.gotdotnet.com/quickstart/aspplus/doc/debugcomsdk.aspx
-Mathew Nolton