C#.NET stored procedure call

[kpfunf]

Trying to follow examples I've seen online to call a simple stored procedure. Converting from (working) data readers to stored procs to get SQL code out of .NET project. Error is on execute ("ORA-00900: invalid SQL statement"). I've used other SQL examples and get the same problem. Not sure if it's in my parameter adding.

        public string DGetEmailAddress(string emplid)
        {
            string returnValue = string.Empty;
            string sql = "SP_GET_EMAIL_ADDRESS";
            OracleConnection sqlConnection = new OracleConnection(GetConnectionString(connectionADP));
            OracleCommand sqlCommand = new OracleCommand(sql, sqlConnection);
            sqlCommand.Parameters.Add("MyEmplid", OracleDbType.Varchar2).Value = emplid;
            sqlCommand.Parameters.Add("Email", OracleDbType.Varchar2).Direction = ParameterDirection.Output;

            try
            {
                sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                returnValue = (string)sqlCommand.Parameters["Email"].Value;
            }
            catch (Exception ex)
            {
                returnValue = string.Empty;
            }
            finally
            {
                if ((sqlCommand != null))
                {
                    sqlCommand.Dispose();
                }
                if ((sqlConnection != null))
                {
                    sqlConnection.Close();
                }
            }
            return returnValue;
        }

CREATE OR REPLACE PROCEDURE SP_GET_EMAIL_ADDRESS 
(MYEMPLID IN VARCHAR2, EMAIL OUT VARCHAR2)
IS
BEGIN
  SELECT EMAIL_ADDRESS INTO EMAIL
  FROM PS_PERSONAL_DATA WHERE EMPLID = MYEMPLID;
END SP_GET_EMAIL_ADDRESS;

[phvfl]

CommandType

Have you tried setting the CommandType of the command object to StoredProcedure ? I tend to have a ms-sql database but a similar error is thrown if the command type is not set.

[kpfunf]

Thanks pfvhl. I did add that, but also had to change the type in .NET to Char, and set a length greater than what was needed. Not sure why Varchar wouldn't work.

[phvfl]

Thanks pfvhl. I did add that, but also had to change the type in .NET to Char, and set a length greater than what was needed. Not sure why Varchar wouldn't work.

Glad you've got it working - it seems strange that you need to use the char type. What happens if you set the length on the parameter when the type is varchar? Example below setting the length to 100:

 sqlCommand.Parameters.Add("MyEmplid", OracleDbType.Varchar2, 100).Value = emplid;

[kpfunf]

That actually works as well.

Now my issue is why do I need to set the size? Isn't variable character supposed to be just that, variable? (OK, read some more and see that length needs to be declared to to let Oracle know how much space to allocate to hold the return value.)

I can just put 100 or 1000, but I'm assuming that's poor programming and may eat resources. I'm trying to write generic methods to call, so I can't hard code a length. I have used parameter1.leng th for the input parameter length, but return length could be anything (in one case I'm pulling back 2-3k characters of HTML). Do you know if there's a way to not declare length? Or a better way to handle the larger amounts?

[phvfl]

Not sure how Oracle handles varchar data. With MS SQL the declared size of the VARCHAR is an upper limit - if you had a VARCHAR with length of 1000 and used it to store 1 byte then the size in memory would be 1 byte (strictly speaking slightly more as the actual length of the value is stored).

The client class in .NET uses the type enumeration to aid conversion between database types and .NET types - the native .net type will be a string which doesn't have a predefined width so will only be as large as required.

In summary I do not think that specifying a large width will have an adverse affect, assuming that the width is compatible with your data schema. All this is based on my own knowledge so I wouldn't treat it as absolute - run some benchmarks to find out would be the best solution.