Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Anyone know how to call an Oracle function from C#?

Reply
Thread Tools

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

 
 
Steve Kershaw
Guest
Posts: n/a
 
      01-18-2007
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

 
Reply With Quote
 
 
 
 
David Browne
Guest
Posts: n/a
 
      01-18-2007


"Steve Kershaw" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) 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


 
Reply With Quote
 
 
 
 
Steve Kershaw
Guest
Posts: n/a
 
      01-19-2007
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed) 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


 
Reply With Quote
 
David Browne
Guest
Posts: n/a
 
      01-19-2007


"Steve Kershaw" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) 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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: How include a large array? Edward A. Falk C Programming 1 04-04-2013 08:07 PM
install_driver(Oracle) failed: Can't load 'C:/Perl/site/lib/auto/DBD/Oracle/Oracle.dll' for module DBD::Oracle: load_file:The specified procedure could not be found at C:/Perl/lib/DynaLoader.pm line 230. Feyruz Perl Misc 4 10-14-2005 06:47 PM
write a function such that when ever i call this function in some other function .it should give me tha data type and value of calling function parameter komal C++ 6 01-25-2005 11:13 AM
LOOK! i just want to know does anyone know... Simon Egginton Javascript 3 07-26-2004 08:11 PM
I know, I know, I don't know Andries Perl Misc 3 04-23-2004 02:17 AM



Advertisments