«

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

"Steve Kershaw" <> wrote in message
news: oups.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_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,

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

Steve

David Browne wrote:
> "Steve Kershaw" <> wrote in message
> news: oups.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_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" <> wrote in message
news: ps.com...
> 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_c ount(_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