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

Discussion in 'ASP .Net' started by Steve Kershaw, Jan 18, 2007.

  1. 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, Jan 18, 2007
    #1
    1. Advertising

  2. Steve Kershaw

    David Browne Guest

    "Steve Kershaw" <> wrote in message
    news:...
    > 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, Jan 18, 2007
    #2
    1. Advertising

  3. 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:...
    > > 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, Jan 19, 2007
    #3
  4. Steve Kershaw

    David Browne Guest

    "Steve Kershaw" <> wrote in message
    news:...
    > 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
    :p_rv :=
    HDB.PRL_PAY_PERIOD_PAYROLL_PKG.get_rec_final_qtr_count:)p_year,: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
    David Browne, Jan 19, 2007
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. hongky
    Replies:
    5
    Views:
    785
    emerth
    Oct 9, 2003
  2. Allin Cottrell

    Re: anyone know about pthread_delay_np() function??

    Allin Cottrell, Oct 14, 2003, in forum: C Programming
    Replies:
    1
    Views:
    540
    Randy Howard
    Oct 20, 2003
  3. Simon Egginton

    LOOK! i just want to know does anyone know...

    Simon Egginton, Jul 26, 2004, in forum: Javascript
    Replies:
    3
    Views:
    169
    Dr John Stockton
    Jul 26, 2004
  4. Andries

    I know, I know, I don't know

    Andries, Apr 23, 2004, in forum: Perl Misc
    Replies:
    3
    Views:
    222
    Gregory Toomey
    Apr 23, 2004
  5. Feyruz
    Replies:
    4
    Views:
    2,131
    Sherm Pendley
    Oct 14, 2005
Loading...

Share This Page