Problems with return parameter from Oracle function

4 Jan 2006 7:58 PM

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 AuthenticateUser(:username, :password, :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?

5 Jan 2006 1:59 PM

Paul Clement

On Wed, 04 Jan 2006 14:58:37 -0500, Chris DiPierro <cdipi***@mi-corporation.com> wrote:

¤ 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 AuthenticateUser(:username, :password, :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)