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.CommandText="insert into foo values('foo','bar') returning id into :myOutputParameter";
cmd.Parameters.Add(newOracleParameter("myOutputParameter",OracleDbType.Decimal),ParameterDirection.ReturnValue);
cmd.ExecuteNonQuery();// an INSERT is always a Non Query returnConvert.ToDecimal(cmd.Parameters["myOutputParameter"].Value); }
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
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)
Warning: Code is often uncompiled and possibly started life written on the back of a napkin. Beware typos.
dweezil
Participant
1873 Points
395 Posts
Re: oracle parameter output
May 16, 2005 10:57 AM|LINK
<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>
SELECT * FROM users WHERE clue = 'yes';
Records found: 0
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.
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);
}