Oracle, Stored Procedures with ref cursor from .Net code and BID tracing.
Ok, many concepts today.
First we’ll create some tables, a package and a stored procedure using ref cursors in a Oracle database.
Then we’ll create a .Net application that uses this stored procedure.
Finally we’ll create a BID trace for this that traces both System.Data.OracleClient and OCI activity (System.Data.OracleClient just calls into the OCI layer).
So, let’s go:
. Create two tables in Oracle and add some rows:
create table MyTable_A (cid int, cname varchar2(20), primary key(cid));
insert into MyTable_A values (1, 'T_A, Row 1');
insert into MyTable_A values (2, 'T_A, Row 2');
insert into MyTable_A values (3, 'T_A, Row 3');
create table MyTable_B (cid int, cname varchar2(20), primary key(cid));
insert into MyTable_B values (1, 'T_B, Row 1');
insert into MyTable_B values (2, 'T_B, Row 2');
insert into MyTable_B values (3, 'T_B, Row 3');
. Create package that will be used for stored procedure that returns ref cursor:
create or replace package MyPackage as type MyCursor is ref cursor;
procedure MyStoredProcedure (pin in int, OutCursor in out MyCursor);
end MyPackage;
/
. Create the stored procedure, this will return the result from a UNION ALL where cid > than argument:
create or replace package body MyPackage as procedure MyStoredProcedure (pin in int, OutCursor in out MyCursor)
is
v_cur MyCursor;
begin
open v_cur for
select cid, cname from MyTable_A where cid > pin
union all
select cid, cname from MyTable_B where cid > pin;
OutCursor := v_cur;
end MyStoredProcedure;
end MyPackage;
/
. Done, now create a new .Net console application that uses System.Data.OracleClient. Replace the Main method with this:
static void Main(string[] args)
{
string cs = @"Data Source=<your server>;User Id=<userid>;Password=<password>";
using (OracleConnection ocon = new OracleConnection(cs))
{
try
{
ocon.Open();
OracleCommand cmd = new OracleCommand("MyPackage.MyStoredProcedure", ocon);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("pin", OracleType.Int32)).Value = 1;
cmd.Parameters.Add(new OracleParameter("OutCursor", OracleType.Cursor)).Direction = ParameterDirection.Output;
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
// Just print out rows to show it works:
foreach (DataRow r in ds.Tables[0].Rows)
{
Console.WriteLine("{0} - {1}", r[0].ToString(), r[1].ToString());
}
ocon.Close();
}
catch (OracleException ex)
{
Console.WriteLine(ex.ToString());
}
}
}
. And run it, this should print out:
2 - T_A, Row 2
3 - T_A, Row 3
2 - T_B, Row 2
3 - T_B, Row 3
. Ok, time to start the BID tracing.
. Create a new directory; C:\OracleBidTrace
. Copy the all.mof file to this directory. This file attached at the end of this post
. Start a command prompt and navigate to C:\OracleBidTrace
. Run the following (this registers the trace file and starts the trace):
reg add HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BidInterface\Loader /v ":Path" /t REG_SZ /d msdadiag.dll
logman start MyTrace -p {DCD90923-4953-20C2-8708-01976FB15287} 0x00000086 -ct perf -o Out.etl -ets
. Run the application that holds the code you wish to trace (the code above in this example). You need to start it so that the OCI layer gets loaded.
. Run the following from the same command prompt (stops the trace and removes the key in the registry).
logman stop MyTrace -ets
reg delete HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BidInterface\Loader /v ":Path" /f
. You should now have a file in C:\OracleBidTrace called out.etl.
. From the command prompt, now run:
mofcomp all.mof
tracerpt /y Out.etl
. You should now have two more files in C:\OracleBidTrace called summary.txt and dumpfile.csv.
. And there you go :-)
. Notice that if you are running with 0x00000086 it will give the Advanced output, including calls to OCI.
If you just want to do BID tracing on System.Oracle.Client, replace this with 0x00000000
The complete list of GUID’s for tracing is found (along with further info on BID tracing) here:
“Data Access Tracing in SQL Server 2008”
https://msdn.microsoft.com/en-us/library/cc765421.aspx
This article shows you how to trace on all sorts of providers and their GUIDs.
It is referred to as the ctrl.guid.all file, this is also attached to this post.