c# return out parameters from oracle function

Question

I have textbox and on the leave event I am using oracle function to retrieve data for other textboxs so I made below code but i am getting no data found

    private void TB_PRODUCT_DESC_Leave(object sender, EventArgs e)
    {
        string connstr = "Data Source=JDT; User Id=admin; password=admin;";

        string cmdtxt = @"F_GET_PRODUCT_INFO";   //~ Returning CUSTOMER_ID from trigger in database ~//

        using (OracleConnection conn = new OracleConnection(connstr))
        using (OracleCommand cmd = new OracleCommand(cmdtxt, conn))
        {
            try
            {
                cmd.CommandText = cmdtxt;
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new OracleParameter("TB_PRODUCT_DESC", TB_PRODUCT_DESC.Text));
                cmd.Parameters.Add(":V_PRODUCT_DESC", OracleDbType.Varchar2, ParameterDirection.ReturnValue);

                conn.Open();

                cmd.ExecuteReader();

                TB_NOTES.Text = (cmd.Parameters[":V_PRODUCT_DESC"].Value).ToString();

                MessageBox.Show(TB_NOTES.Text);
            }
            catch (Exception EX)
            { MessageBox.Show(EX.Message, "error msg", MessageBoxButtons.OK, MessageBoxIcon.Error); }
        }

function I am using below that return one varchar2 value

CREATE OR REPLACE FUNCTION F_GET_PRODUCT_INFO (P_PRODUCT_ID NUMBER)
  RETURN VARCHAR2
IS
   V_PRODUCT_DESC           VARCHAR2 (200);
   V_UNIT_PRICE                 NUMBER;
   V_MEASUREMENT_UNIT     VARCHAR2(32);
BEGIN
   SELECT PRODUCT_DESC,
                UNIT_PRICE,
                MEASUREMENT_UNIT
     INTO V_PRODUCT_DESC,
               V_UNIT_PRICE,
               V_MEASUREMENT_UNIT
     FROM WAREHOUSE
    WHERE PRODUCT_ID = P_PRODUCT_ID;

   RETURN V_PRODUCT_DESC;
END F_GET_PRODUCT_INFO;

Possible duplicate SO Question: stackoverflow.com/questions/23199713/… — ron tornambe
– ron tornambe, Commented Apr 29, 2016 at 0:59
@rontornambe i have correct two mistake 'ParameterDirection' should be return value and the 'cmd.Parameters.Add' should be before the execute but now I am getting no data found error — samer
– samer, Commented Apr 29, 2016 at 8:13

ron tornambe · Accepted Answer · 2016-04-29 14:56:31Z

1

You need to specify a size for the varchar2:

OracelParameter prm = new OracleParameter(":V_PRODUCT_DESC");
            prm.Direction = ParameterDirection.ReturnValue;
            prm.DbType = OracleDbType.Varchar2;
            prm.Size = 200;
            cmd.Parameters.Add(prm);

answered Apr 29, 2016 at 14:56

ron tornambe

10.8k8 gold badges38 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

c# return out parameters from oracle function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related