oracle parameter output RSS

• • Reply
  

  ccoe1

  Member

  743 Points

  167 Posts

  oracle parameter output

  May 16, 2005 03:48 AM|LINK

  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

   

  • Moved by SomeNewKid on May 16, 2005 09:43 AM
• • Reply
  

  JeffreyABeck...

  All-Star

  16423 Points

  3329 Posts

  Re: oracle parameter output

  May 16, 2005 10:55 AM|LINK

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

  If the answer I provided is useful or informative please check the "answer" button.
  
  Warning: Code is often uncompiled and possibly started life written on the back of a napkin. Beware typos.
• • Reply
  

  dweezil

  Participant

  1873 Points

  395 Posts

  Re: oracle parameter output

  May 16, 2005 10:57 AM|LINK

  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>
  

  ccoe1

  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

   

  --dweezil
  
  SELECT * FROM users WHERE clue = 'yes';
  
  Records found: 0
• • Reply
  

  DBT

  Member

  12 Points

  3 Posts

  Re: oracle parameter output

  May 24, 2005 06:30 AM|LINK

  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.

• • Reply
  

  azlams

  Member

  12 Points

  8 Posts

  Re: oracle parameter output

  Feb 04, 2012 09:10 AM|LINK

  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); 
  }