locked
oracle parameter output RRS feed

  • Question

  • User1231629628 posted

    Hi there,
    I try to write out an oracle parameter from a stored procedure, but only receive the name of the parameter. Here is what I do:

    cmd.Parameters.Add(new OracleParameter("confidential", OracleType.Number)).Direction = ParameterDirection.Output;

    then i just try this:

    string strPageAccess = cmd.Parameters["page_access"].Value.ToString();

    which gives the error: Object reference not set to an instance of an object

    Trying:
    string strPageAccess = cmd.Parameters["page_access"].ToString();

    Just gives out the parameter name.

    What am I doing wrong? Thanks

    Chris

     

    Sunday, May 15, 2005 11:48 PM

All replies

  • User-1630302068 posted

    'Value' is a property of the OracleParameter.  In this case, Value is null (Nothing in VB)

    Monday, May 16, 2005 6:55 AM
  • User-144800190 posted
    If you trying to display the value of an output parameterl you will have to execute it first..
    <code>
    cmd.ExecuteNonQuery;
    Response.Write(cmd.Parameters["param_out"].Value.ToString();
    </code>
    To display the the value of an input parameter this should do the trick.
    <code>
    cmd.Parameters.Add("param1",OracleType.Varchar2);  
    cmd.Parameters["param1"].Value = "foobar" ;                      
    cmd.Parameters["param1"].Direction = ParameterDirection.Input ;
    Response.Write(cmd.Parameters["param1"].Value.ToString();

    </code>

    Hi there,
    I try to write out an oracle parameter from a stored procedure, but only receive the name of the parameter. Here is what I do:

    cmd.Parameters.Add(new OracleParameter("confidential", OracleType.Number)).Direction = ParameterDirection.Output;

    then i just try this:

    string strPageAccess = cmd.Parameters["page_access"].Value.ToString();

    which gives the error: Object reference not set to an instance of an object

    Trying:
    string strPageAccess = cmd.Parameters["page_access"].ToString();

    Just gives out the parameter name.

    What am I doing wrong? Thanks

    Chris

     

    Monday, May 16, 2005 6:57 AM
  • User1830759377 posted

    Try to set the output variable size !

    command.Parameters.Add("@UserID", SqlDbType.Int);

    command.Parameters["@UserID"].Direction = ParameterDirection.InputOutput;

    command.Parameters["@UserID"].Value = _userID;

    command.Parameters.Add("@UserName", SqlDbType.NVarChar, 4000);

    command.Parameters["@UserName"].Direction = ParameterDirection.InputOutput;

    command.Parameters["@UserName"].Value = _userName;

    command.ExecuteNonQuery();

    _userID = (int) command.Parameters["@UserID"].Value;

    _userName = (string) command.Parameters["@UserName"].Value;

     

    If the Parameter type is varchar the size is necessary to set, elsewhere the return value is nul or empty string.

    I hope this helps you.

    Tuesday, May 24, 2005 2:30 AM
  • User-1903939959 posted

    Here is the solution:-

     

    using (OracleCommand cmd = con.CreateCommand())

     { 
        cmd
    .CommandText = "insert into foo values('foo','bar') returning id into :myOutputParameter"; 
        cmd
    .Parameters.Add(new OracleParameter("myOutputParameter", OracleDbType.Decimal), ParameterDirection.ReturnValue); 
        cmd
    .ExecuteNonQuery(); // an INSERT is always a Non Query 
       
    return Convert.ToDecimal(cmd.Parameters["myOutputParameter"].Value); 
    }

    Saturday, February 4, 2012 5:10 AM