Anyone know how to call an Oracle function from C#?

[Steve Kershaw]

I'm not even sure this can be done. I have a requirement to call an
Oracle function (that returns a value) from C# code. The Oracle
function is as follows:

FUNCTION get_rec_final_qtr_count
(
"P_YEAR" IN NUMBER,
"P_QUARTER" IN NUMBER
)
RETURN Int
IS
p_count Int;
BEGIN
SELECT COUNT(prl_pay_period_payroll_id) INTO p_count FROM
prl_pay_period_payroll
WHERE year = p_year AND quarter = p_quarter AND quarter_end_report =
'T';

RETURN p_count;
END get_rec_final_qtr_count;

The C# code I'm trying to use is as follows:

objConnect.Connect();
OracleCommand DBCmd =
new
OracleCommand("HDB.PRL_PAY_PERIOD_PAYROLL_PKG.get_rec_final_qtr_count",
objConnect.Connection);
DBCmd.CommandType = System.Data.CommandType.StoredProcedure;
DBCmd.Parameters.

DBCmd.Parameters.Add("P_YEAR", OracleDbType.Int32);
DBCmd.Parameters["P_YEAR"].Direction =
System.Data.ParameterDirection.Input;
DBCmd.Parameters["P_YEAR"].Value = Year;

DBCmd.Parameters.Add("P_QUARTER", OracleDbType.Int32);
DBCmd.Parameters["P_QUARTER"].Direction =
System.Data.ParameterDirection.Input;
DBCmd.Parameters["P_QUARTER"].Value = Quarter;

Count = (Int32)DBCmd.ExecuteScalar();

Am I doing something wrong?

Thanks for your help.
Steve

 

[David Browne]

I'm not even sure this can be done. I have a requirement to call an
Oracle function (that returns a value) from C# code. The Oracle
function is as follows:

FUNCTION get_rec_final_qtr_count
(
"P_YEAR" IN NUMBER,
"P_QUARTER" IN NUMBER
)
RETURN Int
IS
p_count Int;
BEGIN
SELECT COUNT(prl_pay_period_payroll_id) INTO p_count FROM
prl_pay_period_payroll
WHERE year = p_year AND quarter = p_quarter AND quarter_end_report =
'T';

RETURN p_count;
END get_rec_final_qtr_count;

The C# code I'm trying to use is as follows:

objConnect.Connect();
OracleCommand DBCmd =
new
OracleCommand("HDB.PRL_PAY_PERIOD_PAYROLL_PKG.get_rec_final_qtr_count",
objConnect.Connection);

....

You need to add a parameter for the return value, use ExecuteNonQuery and
then extract the parameter value.

Like this.

DBCmd.CommandType = System.Data.CommandType.StoredProcedure;

DBCmd.Parameters.Add("P_YEAR", OracleType.Int32);
DBCmd.Parameters["P_YEAR"].Direction = ParameterDirection.Input;
DBCmd.Parameters["P_YEAR"].Value = 2007;

DBCmd.Parameters.Add("P_QUARTER", OracleType.Int32);
DBCmd.Parameters["P_QUARTER"].Direction = ParameterDirection.Input;
DBCmd.Parameters["P_QUARTER"].Value = 2;

DBCmd.Parameters.Add("P_RV", OracleType.Int32);
DBCmd.Parameters["P_RV"].Direction = ParameterDirection.ReturnValue;

DBCmd.ExecuteNonQuery();
int Count = (int)DBCmd.Parameters["P_RV"].Value;


Alternatively, you can use CommandType.Text, and use a complete PL/SQL block
with parameter markers, then bind parameters into that and execute it.
CommandType.StoredProcedure just tells the Oracle library to build the
PL/SQL block for you.

David

 

[Steve Kershaw]

David,

Thanks that worked! However, it only works with
System.Data.OracleClient NOT Oracle.DataAccess.Client. When will Oracle
get with the program!?

Steve

I'm not even sure this can be done. I have a requirement to call an
Oracle function (that returns a value) from C# code. The Oracle
function is as follows:

FUNCTION get_rec_final_qtr_count
(
"P_YEAR" IN NUMBER,
"P_QUARTER" IN NUMBER
)
RETURN Int
IS
p_count Int;
BEGIN
SELECT COUNT(prl_pay_period_payroll_id) INTO p_count FROM
prl_pay_period_payroll
WHERE year = p_year AND quarter = p_quarter AND quarter_end_report =
'T';

RETURN p_count;
END get_rec_final_qtr_count;

The C# code I'm trying to use is as follows:

objConnect.Connect();
OracleCommand DBCmd =
new
OracleCommand("HDB.PRL_PAY_PERIOD_PAYROLL_PKG.get_rec_final_qtr_count",
objConnect.Connection);

...

You need to add a parameter for the return value, use ExecuteNonQuery and
then extract the parameter value.

Like this.

DBCmd.CommandType = System.Data.CommandType.StoredProcedure;

DBCmd.Parameters.Add("P_YEAR", OracleType.Int32);
DBCmd.Parameters["P_YEAR"].Direction = ParameterDirection.Input;
DBCmd.Parameters["P_YEAR"].Value = 2007;

DBCmd.Parameters.Add("P_QUARTER", OracleType.Int32);
DBCmd.Parameters["P_QUARTER"].Direction = ParameterDirection.Input;
DBCmd.Parameters["P_QUARTER"].Value = 2;

DBCmd.Parameters.Add("P_RV", OracleType.Int32);
DBCmd.Parameters["P_RV"].Direction = ParameterDirection.ReturnValue;

DBCmd.ExecuteNonQuery();
int Count = (int)DBCmd.Parameters["P_RV"].Value;


Alternatively, you can use CommandType.Text, and use a complete PL/SQL block
with parameter markers, then bind parameters into that and execute it.
CommandType.StoredProcedure just tells the Oracle library to build the
PL/SQL block for you.

David

 

[David Browne]

David,

Thanks that worked! However, it only works with
System.Data.OracleClient NOT Oracle.DataAccess.Client. When will Oracle
get with the program!?

Well, you can always just use CommandType.Text. And set command text to

string sql = @"
begin
_rv :=
HDB.PRL_PAY_PERIOD_PAYROLL_PKG.get_rec_final_qtr_countp_year,_quarter);
end;
";

This is the exact same block you can use in SqlPlus.


Then bind an output parameter and two input parameters. Be careful, ODP.NET
used positional parameter binding by default.


David