Problems with return parameter from Oracle function

[Chris DiPierro]

I have an oracle function that looks like this:

AuthenticateUser(UserName IN VARCHAR2, pPassword IN VARCHAR2, AppName IN
VARCHAR2) RETURN VARCHAR2

Using the Oracle client provider, I'm setting up my command text as:
:results = call AuthenticateUserusername, assword, :appname)

I then add parameters as follows:

OracleParameter p = new OracleParameter("results", OracleType.VarChar);
p.Direction = System.Data.ParameterDirection.ReturnValue;
p.Size = 255
xcmd.Parameters.Add(p);
xcmd.CommandText = _authenticateCall;
xcmd.Parameters.Add(new OracleParameter("username",
OracleType.VarChar)).Value = "abc";
xcmd.Parameters.Add(new OracleParameter("password",
OracleType.VarChar)).Value = "123";
xcmd.Parameters.Add(new OracleParameter("application",
OracleType.VarChar)).Value = "Test";

However, when I execute the command, I get an ORA-01036: illegal
variable name/number exception.

If I leave out the :results paramater from both the command text and
don't add it to the parameters of the command, then I can call the
function w/o the exception. So it seems like .Net can't figure out how
to bind my parameters correctly (this is more or less confirmed by the
stacktrace of the exception).

In every example I've seen, this is the recommended way of calling a
function. What am I missing that would make it bind the return parameter
correctly?

 

[Paul Clement]

¤ I have an oracle function that looks like this:
¤
¤ AuthenticateUser(UserName IN VARCHAR2, pPassword IN VARCHAR2, AppName IN
¤ VARCHAR2) RETURN VARCHAR2
¤
¤ Using the Oracle client provider, I'm setting up my command text as:
¤ :results = call AuthenticateUserusername, assword, :appname)
¤
¤ I then add parameters as follows:
¤
¤ OracleParameter p = new OracleParameter("results", OracleType.VarChar);
¤ p.Direction = System.Data.ParameterDirection.ReturnValue;
¤ p.Size = 255
¤ xcmd.Parameters.Add(p);
¤ xcmd.CommandText = _authenticateCall;
¤ xcmd.Parameters.Add(new OracleParameter("username",
¤ OracleType.VarChar)).Value = "abc";
¤ xcmd.Parameters.Add(new OracleParameter("password",
¤ OracleType.VarChar)).Value = "123";
¤ xcmd.Parameters.Add(new OracleParameter("application",
¤ OracleType.VarChar)).Value = "Test";
¤
¤ However, when I execute the command, I get an ORA-01036: illegal
¤ variable name/number exception.
¤
¤ If I leave out the :results paramater from both the command text and
¤ don't add it to the parameters of the command, then I can call the
¤ function w/o the exception. So it seems like .Net can't figure out how
¤ to bind my parameters correctly (this is more or less confirmed by the
¤ stacktrace of the exception).
¤
¤ In every example I've seen, this is the recommended way of calling a
¤ function. What am I missing that would make it bind the return parameter
¤ correctly?

Just a WAG, what if you change the name of your output parameter?

Also, I don't think I've ever seen a working example that uses an Oracle function to return a value.
Can you use a stored proc instead?


Paul
~~~~
Microsoft MVP (Visual Basic)