ODP.NET +Oracle9 +functions

[Phil Lindsay]

Hello,

can anyone confirm or deny that functions still work in ODP.NET
(running against an Oracle 9 db)? I am having trouble calling a
function, but I have no trouble calling a stored procedure. I've done
some digging around on the internet, and a lot of people seem to talk
about stored procedures, but no one talks about calling a function
using ODP.NET.

There are only 3 options for command type in the ODP.NET library...
CommandType.StoredProcedure, CommandType.TableDirect, CommandType.Text

I've tried using CommandType.Text and calling the Oracle function the
same way I've called it in VB6. The function I am trying to call
returns a varchar2 variable which represents the next available label
(the function searches for "gaps" in between numbers caused by
previously deleted entities) and returns the lowest available number
in VARCHAR format. My function appears to work (no exception is
thrown) but I get NULL returned to me when I call this function...

===== start of code sample ====

myCmd = new OracleCommand();

myCmd.CommandType = CommandType.Text;

myCmd.Parameters.Add("LocationLabel",
Oracle.DataAccess.Client.OracleDbType.Varchar2);
myCmd.Parameters["LocationLabel"].Value = myLoc.Label;

OracleParameter p2 = myCmd.Parameters.Add("StructureLabel",
Oracle.DataAccess.Client.OracleDbType.Varchar2);
p2.Direction = System.Data.ParameterDirection.ReturnValue;

myCmd.CommandText = "MyPackage.Func_GetNextStructureLabel";
myCmd.Connection = myCon;

myCmd.ExecuteNonQuery();

//this returns NULL??
NextLabel = p2.Value.ToString();

===== end of code sample ====


One of our web developers was also having trouble calling the function
in another case, and they solved this problem by converting the
function to a stored procedure... I am just wondering has the line
between function and stored procedure blurred to the point that
there's no difference now? (i.e. stored procedures have replaced
functions??)

Thanks for your input,
PL

 

[David Browne]

Hello,

can anyone confirm or deny that functions still work in ODP.NET
(running against an Oracle 9 db)?
Confirmed.

I am having trouble calling a
function, but I have no trouble calling a stored procedure. I've done
some digging around on the internet, and a lot of people seem to talk
about stored procedures, but no one talks about calling a function
using ODP.NET.

There are only 3 options for command type in the ODP.NET library...
CommandType.StoredProcedure, CommandType.TableDirect, CommandType.Text

CommandType.StoredProcedure means "the commandText is the name of a stored
procedure or function. Please build a PL/SQL batch for me based on the name
and my parameters."

CommandType.Text means "I'll build my own SQL or PL/SQL and place the
parameter markers by hand."


You can call a function with either one.

My advice is that whenever you have problems, use CommandType.Text and build
it all yourself.
The PL/SQL you build can be used in SQLPlus, and you have 100% control over
the process.

Once you get this working you can tinker with CommandType.StoredProcedure,
and ParameterDirection.ReturnValue.

NB. With CommandType.Text you never use ParameterDirection.ReturnValue
because in the PL/SQL block the function's return value becomes just an
output parameter.


myCmd.Connection = myCon;
myCmd.CommandType = CommandType.Text;
myCmd.CommandText = @"
begin
select MyPackage.Func_GetNextStructureLabelLocationLabel)
into :StructureLabel
from dual;
end;";

myCmd.Parameters.Add("LocationLabel",
Oracle.DataAccess.Client.OracleDbType.Varchar2);
myCmd.Parameters["LocationLabel"].Value = myLoc.Label;

OracleParameter p2 = myCmd.Parameters.Add("StructureLabel",
Oracle.DataAccess.Client.OracleDbType.Varchar2);
p2.Direction = System.Data.ParameterDirection.Output;


MyCmd.ExecuteNonQuery();

David

 

[Phil Lindsay]

Hello (and thanks for your response!)

I tried your code, and need to make a slight modification to get it to
work (needed to put in line returns and added a dbms_output.put_line
instead of SELECT.. INTO)...

The call to the function "returns success", but I still end up getting
NULL returned in the p2 parameter (the Structure label is coming back as
Null). Any thoughts??

here's what I wrote:

==== code start ===

myCon = this.DBControl.GetDatabaseConnection(out ErrorMsg);
myCmd = new OracleCommand();

myCmd.CommandType = CommandType.Text;

sb = new StringBuilder();

sb.Append("DECLARE StructureLabel Varchar2(20);");
sb.Append("\n");
sb.Append("BEGIN");
sb.Append("\n");
sb.Append("structurelabel := myPackage.Func_GetNextStructureLabel('");
sb.Append(myLoc.Label);
sb.Append("');\n");
sb.Append("dbms_output.put_line(structurelabel);");
sb.Append("\n");
sb.Append("END;");

myCmd.CommandText = sb.ToString();

myCmd.Parameters.Add("LocationLabel",
Oracle.DataAccess.Client.OracleDbType.Varchar2);

myCmd.Parameters["LocationLabel"].Value = myLoc.Label;

OracleParameter p2 = myCmd.Parameters.Add("StructureLabel",
Oracle.DataAccess.Client.OracleDbType.Varchar2);

//I've tried ReturnValue, Output, InputOutput... they all seem to
succeed but return NULL
p2.Direction = System.Data.ParameterDirection.InputOutput;

myCmd.Connection = myCon;
myCmd.ExecuteNonQuery();

NextLabel = p2.Value.ToString();

=== code ends here ===

 

[David Browne]

Hello (and thanks for your response!)

I tried your code, and need to make a slight modification to get it to
work (needed to put in line returns and added a dbms_output.put_line
instead of SELECT.. INTO)...

The call to the function "returns success", but I still end up getting
NULL returned in the p2 parameter (the Structure label is coming back as
Null). Any thoughts??

here's what I wrote:

==== code start ===

myCon = this.DBControl.GetDatabaseConnection(out ErrorMsg);
myCmd = new OracleCommand();

myCmd.CommandType = CommandType.Text;

sb = new StringBuilder();

sb.Append("DECLARE StructureLabel Varchar2(20);");
sb.Append("\n");
sb.Append("BEGIN");
sb.Append("\n");
sb.Append("structurelabel := myPackage.Func_GetNextStructureLabel('");
sb.Append(myLoc.Label);
sb.Append("');\n");
sb.Append("dbms_output.put_line(structurelabel);");
sb.Append("\n");
sb.Append("END;");

I think you never want to build SQL strings like this.

It's hard to read, hard to fix, impossible to cut and paste out into SQLPlus
or Toad, and (as here) easy to mess up.

You wrote a self contained program with no bind variables.

DECLARE
StructureLable Varchar2(20);
BEGIN
structurelabel := myPackage.Func_GetNextStructureLabel('abc');
dbms_output.put_line(structurelabel);
END;

Which works fine, but doesn't have any inputs or outputs.

You needed

BEGIN
:structurelabel := myPackage.Func_GetNextStructureLabellocation);
END;

:structurelabel and :location are bind variables, or sites for you to bind
OracleParameters.


Anyway here's a working sample, both with CommandType.Text and
CommandType.StoredProcedure

David



OracleCommand myCmd = new OracleCommand();
OracleParameter p2;

bool useText = true;
myCmd.Connection = con;
myCmd.BindByName = true;

if (useText)
{
myCmd.CommandType = CommandType.Text;
myCmd.CommandText = "begin :StructureLabel :=
MyPackage.Func_GetNextStructureLabelLocationLabel); end;";
myCmd.Parameters.Add("LocationLabel",
Oracle.DataAccess.Client.OracleDbType.Varchar2);
myCmd.Parameters["LocationLabel"].Value = "ddd";

p2 = myCmd.Parameters.Add("StructureLabel",
Oracle.DataAccess.Client.OracleDbType.Varchar2);
p2.Size = 2000;
p2.Direction = System.Data.ParameterDirection.Output;
}
else
{
myCmd.CommandType = CommandType.StoredProcedure;
myCmd.CommandText = "MyPackage.Func_GetNextStructureLabel";

myCmd.Parameters.Add("in_location",
Oracle.DataAccess.Client.OracleDbType.Varchar2);
myCmd.Parameters["in_location"].Value = "ddd";

OracleParameter p2 = myCmd.Parameters.Add("StructureLabel",
Oracle.DataAccess.Client.OracleDbType.Varchar2);
p2.Size = 2000;
p2.Direction = System.Data.ParameterDirection.ReturnValue ;

}
try
{
myCmd.ExecuteNonQuery();
Console.WriteLine(p2.Value);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}