locked
Oracle stored procedure doesn`t return any value RRS feed

  • Question

  • User-1418894206 posted

    Hello!

    For some time I was using OLEDB provider to return Oracle stored procedure values. I was adding procedures in DataSet.xsd with execution mode NonQuery and all was working fine.

    Now I decided to move to ODP.NET provider. I added stored procedure in DataSet.xsd. When I try to execute it with mode NonQuery I always get integer not returning type. When I try to execute it with mode Scalar I get object but it always is null.

    Maybe someone now how to solve this problem. I will be glad to any solution.

    Thursday, August 4, 2011 9:15 AM

Answers

  • User-1418894206 posted

    Ok, I found solution by myself for getting values from code. But if anyone know something about .xsd files post informathion please.

    1. Return parameter must be added to oracle command first.
    2. Return variable must be set as value for this parameter (note that this doesn`t return value)
      oraCmd.Parameters.Add("RETURN_VALUE", OracleDbType.Varchar2, 4000, retval, ParameterDirection.ReturnValue);
    3. Value is returned this way: 
              oraCmd.ExecuteNonQuery();
              retval = oraCmd.Parameters["RETURN_VALUE"].Value.ToString();
        public static string GetClientname(string clientID)
        {
            string conStr = ConfigurationManager.ConnectionStrings["P3L_ODP"].ToString();
            OracleConnection conn = new OracleConnection(conStr);
            OracleCommand oraCmd = new OracleCommand("P3L.GET_CLIENTNAME", conn);
            oraCmd.CommandType = CommandType.StoredProcedure;
            string retval = string.Empty;
            oraCmd.Parameters.Add("RETURN_VALUE", OracleDbType.Varchar2, 4000, retval, ParameterDirection.ReturnValue);
            oraCmd.Parameters.Add("IN_CLIENTID", OracleDbType.Varchar2, clientID, ParameterDirection.Input);
            oraCmd.Parameters.Add("IN_FORMAT", OracleDbType.Varchar2, "VU", ParameterDirection.Input);
            conn.Open();
            oraCmd.ExecuteNonQuery();
            retval = oraCmd.Parameters["RETURN_VALUE"].Value.ToString();
            oraCmd.Dispose();
            conn.Close();
            return retval;
        }
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 5, 2011 7:55 AM

All replies

  • User269602965 posted

    show the code so users can help

    Thursday, August 4, 2011 9:44 AM
  • User-1418894206 posted

    Note that I have no code for adding procedures. I only have to drop them to .xsd file. Wasn`t anyone working with that kind of files in VS?

    But I was trying to execute that procedures through code and they still do not return any value using both providers: ODP.NET and OLEDB.

        public static string GetClientname(string clientID)
        {
            string conStr = ConfigurationManager.ConnectionStrings["P3L_ODP"].ToString();
            OracleConnection conn = new OracleConnection(conStr);
            OracleCommand oraCmd = new OracleCommand("P3L.GET_CLIENTNAME", conn);
            oraCmd.CommandType = CommandType.StoredProcedure;
            oraCmd.Parameters.Add("IN_CLIENTID", OracleDbType.Varchar2, 32768, clientID, ParameterDirection.Input);
            oraCmd.Parameters.Add("IN_FORMAT", OracleDbType.Varchar2, 32768, "VU", ParameterDirection.Input);
            oraCmd.Parameters.Add("RETURN_VALUE", OracleDbType.Varchar2, 32768, ParameterDirection.ReturnValue);
            conn.Open();
            oraCmd.ExecuteScalar();
            //oraCmd.ExecuteNonQuery();
            string retval = ((OracleString)oraCmd.Parameters["RETURN_VALUE"].Value).ToString();
            //string retval = string.Empty;
            //OracleDataReader rdr = oraCmd.ExecuteReader();
            //if (rdr.Read()) retval = rdr.GetString(0);
            //rdr.Close();
            oraCmd.Dispose();
            conn.Close();
            return retval;
        }
    Thursday, August 4, 2011 10:33 AM
  • User-1418894206 posted

    Ok, I found solution by myself for getting values from code. But if anyone know something about .xsd files post informathion please.

    1. Return parameter must be added to oracle command first.
    2. Return variable must be set as value for this parameter (note that this doesn`t return value)
      oraCmd.Parameters.Add("RETURN_VALUE", OracleDbType.Varchar2, 4000, retval, ParameterDirection.ReturnValue);
    3. Value is returned this way: 
              oraCmd.ExecuteNonQuery();
              retval = oraCmd.Parameters["RETURN_VALUE"].Value.ToString();
        public static string GetClientname(string clientID)
        {
            string conStr = ConfigurationManager.ConnectionStrings["P3L_ODP"].ToString();
            OracleConnection conn = new OracleConnection(conStr);
            OracleCommand oraCmd = new OracleCommand("P3L.GET_CLIENTNAME", conn);
            oraCmd.CommandType = CommandType.StoredProcedure;
            string retval = string.Empty;
            oraCmd.Parameters.Add("RETURN_VALUE", OracleDbType.Varchar2, 4000, retval, ParameterDirection.ReturnValue);
            oraCmd.Parameters.Add("IN_CLIENTID", OracleDbType.Varchar2, clientID, ParameterDirection.Input);
            oraCmd.Parameters.Add("IN_FORMAT", OracleDbType.Varchar2, "VU", ParameterDirection.Input);
            conn.Open();
            oraCmd.ExecuteNonQuery();
            retval = oraCmd.Parameters["RETURN_VALUE"].Value.ToString();
            oraCmd.Dispose();
            conn.Close();
            return retval;
        }
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 5, 2011 7:55 AM