I was recently discussing (and by that I mean trading emails) with a friend about an interesting behavior observed when using PL/SQL "out" VARCHAR2 variables with ODP.NET - in this case it looks like ODP.NET is sending data from the client and to the server for a parameter that is defined as "out" in a PL/SQL procedure. This seems like strange behavior and I don't have a good answer as to why data would be sent from the client to the server for a parameter declared as an "out" parameter.
In any case here's a quick and dirty sample that illustrates this (on my test system anyway):
PL/SQL Procedure
create or replace procedure ptest (p_vc out varchar2) as
begin
p_vc := rpad ('a', 31999, 'a');
end;
/
C# Sample Console
using System;
using System.Data;
using System.Data.OracleClient;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace Miscellaneous {
class Program {
static void Main(string[] args) {
string constr = "User Id=markwill;" +
"Password=oracle;" +
"Data Source=orademo;" +
"Pooling=false;" +
"Enlist=false";
TestODP(constr);
TestMS(constr);
Console.WriteLine();
Console.Write("ENTER to continue...");
Console.ReadLine();
}
static void TestODP(string constr) {
Oracle.DataAccess.Client.OracleConnection con = new Oracle.DataAccess.Client.OracleConnection(constr);
con.Open();
Oracle.DataAccess.Client.OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "alter session set events '10046 trace name context forever, level 12'";
cmd.ExecuteNonQuery();
cmd.CommandText = "ptest";
cmd.CommandType = CommandType.StoredProcedure;
cmd.BindByName = true;
Oracle.DataAccess.Client.OracleParameter p_vc = new Oracle.DataAccess.Client.OracleParameter();
p_vc.ParameterName = "p_vc";
p_vc.OracleDbType = OracleDbType.Varchar2;
p_vc.Size = 32000;
p_vc.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p_vc);
cmd.ExecuteNonQuery();
Console.WriteLine("p_vc = {0}", p_vc.Value);
p_vc.Dispose();
cmd.Dispose();
con.Dispose();
}
static void TestMS(string constr) {
System.Data.OracleClient.OracleConnection con = new System.Data.OracleClient.OracleConnection(constr);
con.Open();
System.Data.OracleClient.OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "alter session set events '10046 trace name context forever, level 12'";
cmd.ExecuteNonQuery();
cmd.CommandText = "ptest";
cmd.CommandType = CommandType.StoredProcedure;
System.Data.OracleClient.OracleParameter p_vc = new System.Data.OracleClient.OracleParameter();
p_vc.ParameterName = "p_vc";
p_vc.OracleType = OracleType.LongVarChar;
p_vc.Size = 32000;
p_vc.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p_vc);
cmd.ExecuteNonQuery();
Console.WriteLine("p_vc = {0}", p_vc.Value);
cmd.Dispose();
con.Dispose();
}
}
}
This is pretty straight-forward and simply opens a connection, enables Extended SQL Tracing via setting event 10046, creates a parameter for the output value, and calls the stored procedure.
Looking at the respective trace files I find this for the ODP.NET test:
PARSING IN CURSOR #4 len=28 dep=0 uid=88 oct=47 lid=88 tim=9306513611 hv=1721325005 ad='7ff2eca8c38' sqlid='dk08s2dm9kpfd'
Begin ptest(p_vc=>:v0); End;
END OF STMT
PARSE #4:c=0,e=798,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=9306513607
WAIT #4: nam='SQL*Net more data from client' ela= 48 driver id=1413697536 #bytes=44 p3=0 obj#=-1 tim=9306513786
WAIT #4: nam='SQL*Net more data from client' ela= 7 driver id=1413697536 #bytes=24 p3=0 obj#=-1 tim=9306513827
WAIT #4: nam='SQL*Net more data from client' ela= 171 driver id=1413697536 #bytes=4 p3=0 obj#=-1 tim=9306514027
WAIT #4: nam='SQL*Net more data from client' ela= 28 driver id=1413697536 #bytes=49 p3=0 obj#=-1 tim=9306514085
BINDS #4:
Bind#0
oacdty=01 mxl=32767(32000) mxlc=32000 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=178 siz=32767 off=0
kxsbbbfp=1c3d8000 bln=32767 avl=32000 flg=05
value="Ä6¿¿"...
WAIT #4: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9306514373
WAIT #4: nam='SQL*Net more data to client' ela= 49 driver id=1413697536 #bytes=8216 p3=0 obj#=-1 tim=9306514456
WAIT #4: nam='SQL*Net more data to client' ela= 23 driver id=1413697536 #bytes=8192 p3=0 obj#=-1 tim=9306514507
WAIT #4: nam='SQL*Net more data to client' ela= 271 driver id=1413697536 #bytes=8192 p3=0 obj#=-1 tim=9306514805
EXEC #4:c=0,e=1139,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=9306514839
XCTEND rlbk=0, rd_only=1
WAIT #4: nam='SQL*Net message from client' ela= 69517 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9306584449
XCTEND rlbk=0, rd_only=1
As you can see there are several "SQL*Net more data from client" messages. Of course, these are followed by the sending of data to the client.
Now here is the relevant portion of the trace file for the MS provider test:
PARSING IN CURSOR #3 len=30 dep=0 uid=88 oct=47 lid=88 tim=9306615423 hv=1003497284 ad='7ff27721950' sqlid='42zzxzhxx09u4'
begin ptest(p_vc=>:p_vc); end;
END OF STMT
PARSE #3:c=0,e=1507,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=9306615420
BINDS #3:
Bind#0
oacdty=01 mxl=32767(32000) mxlc=32000 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=178 siz=32767 off=0
kxsbbbfp=1c3d8000 bln=32767 avl=00 flg=05
WAIT #3: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9306615726
WAIT #3: nam='SQL*Net more data to client' ela= 69 driver id=1413697536 #bytes=8216 p3=0 obj#=-1 tim=9306615822
WAIT #3: nam='SQL*Net more data to client' ela= 18 driver id=1413697536 #bytes=8192 p3=0 obj#=-1 tim=9306615861
WAIT #3: nam='SQL*Net more data to client' ela= 200 driver id=1413697536 #bytes=8192 p3=0 obj#=-1 tim=9306616081
EXEC #3:c=0,e=612,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=9306616112
XCTEND rlbk=0, rd_only=1
WAIT #3: nam='SQL*Net message from client' ela= 60351 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9306676628
XCTEND rlbk=0, rd_only=1
In this case there are no messages from the client and in the bind section there is no "value=" element.
An interesting wrinkle is that if I set the parameter direction to "ParameterDirection.InputOutput" using ODP.NET I do not observe the sending of data from the client.
Why, I wonder, does ODP.NET send data to the server for an output parameter? Ideas? Similar behavior observed?
