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_q tr_count
(
"P_YEAR" IN NUMBER,
"P_QUARTER" IN NUMBER
)
RETURN Int
IS
p_count Int;
BEGIN
SELECT COUNT(prl_pay_p eriod_payroll_i d) INTO p_count FROM
prl_pay_period_ payroll
WHERE year = p_year AND quarter = p_quarter AND quarter_end_rep ort =
'T';

RETURN p_count;
END get_rec_final_q tr_count;

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

objConnect.Conn ect();
OracleCommand DBCmd =
new
OracleCommand(" HDB.PRL_PAY_PER IOD_PAYROLL_PKG .get_rec_final_ qtr_count",
objConnect.Conn ection);
DBCmd.CommandTy pe = System.Data.Com mandType.Stored Procedure;
DBCmd.Parameter s.

DBCmd.Parameter s.Add("P_YEAR", OracleDbType.In t32);
DBCmd.Parameter s["P_YEAR"].Direction =
System.Data.Par ameterDirection .Input;
DBCmd.Parameter s["P_YEAR"].Value = Year;

DBCmd.Parameter s.Add("P_QUARTE R", OracleDbType.In t32);
DBCmd.Parameter s["P_QUARTER"].Direction =
System.Data.Par ameterDirection .Input;
DBCmd.Parameter s["P_QUARTER"].Value = Quarter;

Count = (Int32)DBCmd.Ex ecuteScalar();

Am I doing something wrong?

Thanks for your help.
Steve

[David Browne]

"Steve Kershaw" <st***********@ yahoo.comwrote in message
news:11******** **************@ s34g2000cwa.goo glegroups.com.. .

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_q tr_count
(
"P_YEAR" IN NUMBER,
"P_QUARTER" IN NUMBER
)
RETURN Int
IS
p_count Int;
BEGIN
SELECT COUNT(prl_pay_p eriod_payroll_i d) INTO p_count FROM
prl_pay_period_ payroll
WHERE year = p_year AND quarter = p_quarter AND quarter_end_rep ort =
'T';

RETURN p_count;
END get_rec_final_q tr_count;

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

objConnect.Conn ect();
OracleCommand DBCmd =
new
OracleCommand(" HDB.PRL_PAY_PER IOD_PAYROLL_PKG .get_rec_final_ qtr_count",
objConnect.Conn ection);

....

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

Like this.

DBCmd.CommandTy pe = System.Data.Com mandType.Stored Procedure;

DBCmd.Parameter s.Add("P_YEAR", OracleType.Int3 2);
DBCmd.Parameter s["P_YEAR"].Direction = ParameterDirect ion.Input;
DBCmd.Parameter s["P_YEAR"].Value = 2007;

DBCmd.Parameter s.Add("P_QUARTE R", OracleType.Int3 2);
DBCmd.Parameter s["P_QUARTER"].Direction = ParameterDirect ion.Input;
DBCmd.Parameter s["P_QUARTER"].Value = 2;

DBCmd.Parameter s.Add("P_RV", OracleType.Int3 2);
DBCmd.Parameter s["P_RV"].Direction = ParameterDirect ion.ReturnValue ;

DBCmd.ExecuteNo nQuery();
int Count = (int)DBCmd.Para meters["P_RV"].Value;
Alternatively, you can use CommandType.Tex t, and use a complete PL/SQL block
with parameter markers, then bind parameters into that and execute it.
CommandType.Sto redProcedure 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.Ora cleClient NOT Oracle.DataAcce ss.Client. When will Oracle
get with the program!?

Steve

David Browne wrote:

"Steve Kershaw" <st***********@ yahoo.comwrote in message
news:11******** **************@ s34g2000cwa.goo glegroups.com.. .

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_q tr_count
(
"P_YEAR" IN NUMBER,
"P_QUARTER" IN NUMBER
)
RETURN Int
IS
p_count Int;
BEGIN
SELECT COUNT(prl_pay_p eriod_payroll_i d) INTO p_count FROM
prl_pay_period_ payroll
WHERE year = p_year AND quarter = p_quarter AND quarter_end_rep ort =
'T';

RETURN p_count;
END get_rec_final_q tr_count;

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

objConnect.Conn ect();
OracleCommand DBCmd =
new
OracleCommand(" HDB.PRL_PAY_PER IOD_PAYROLL_PKG .get_rec_final_ qtr_count",
objConnect.Conn ection);

...

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

Like this.

DBCmd.CommandTy pe = System.Data.Com mandType.Stored Procedure;

DBCmd.Parameter s.Add("P_YEAR", OracleType.Int3 2);
DBCmd.Parameter s["P_YEAR"].Direction = ParameterDirect ion.Input;
DBCmd.Parameter s["P_YEAR"].Value = 2007;

DBCmd.Parameter s.Add("P_QUARTE R", OracleType.Int3 2);
DBCmd.Parameter s["P_QUARTER"].Direction = ParameterDirect ion.Input;
DBCmd.Parameter s["P_QUARTER"].Value = 2;

DBCmd.Parameter s.Add("P_RV", OracleType.Int3 2);
DBCmd.Parameter s["P_RV"].Direction = ParameterDirect ion.ReturnValue ;

DBCmd.ExecuteNo nQuery();
int Count = (int)DBCmd.Para meters["P_RV"].Value;
Alternatively, you can use CommandType.Tex t, and use a complete PL/SQL block
with parameter markers, then bind parameters into that and execute it.
CommandType.Sto redProcedure just tells the Oracle library to build the
PL/SQL block for you.

David

[David Browne]

"Steve Kershaw" <st***********@ yahoo.comwrote in message
news:11******** *************@3 8g2000cwa.googl egroups.com...

David,

Thanks that worked! However, it only works with
System.Data.Ora cleClient NOT Oracle.DataAcce ss.Client. When will Oracle
get with the program!?

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

string sql = @"
begin
:p_rv :=
HDB.PRL_PAY_PER IOD_PAYROLL_PKG .get_rec_final_ qtr_count(:p_ye ar,:p_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