how to define Output parameter as character string for Oracle procedure in c#?

Question

Here is the simple stored procedure that returns a character string literal

PROCEDURE getUserValidateProfileValue (      
  outParam1 OUT VARCHAR2)
AS
BEGIN  
    SELECT 'testABC' into outParam1 from dual;
END getUserValidateProfileValue;

this runs OK in Toad. And following is how I call it from my C# app (note: I have several other procedures that get called using same connection, etc, which all work fine)

...
using (OracleCommand command = DaoHelper.GetOracleBoundByNameStoredProcedureCommand(conn, schema, pkg, "getUserValidateProfileValue"))
{
   command.Parameters.Add("outParam1", OracleDbType.Varchar2, 2000, ParameterDirection.Output);
   command.ExecuteNonQuery(); <<<---bombs out here
   s1 = command.Parameters["outParam1"].Value.ToString();
}

this bombs out at "command.ExecuteNonQuery();"

how to retrieve this output?

Rich P · Accepted Answer · 2016-03-17 20:31:33Z

2

OK -- I found the fix -- you have to include the OracleCollectionType which in my case I set to None:

command.Parameters.Add("outParam1", OracleDbType.Varchar2, 2000, OracleCollectionType.None, ParameterDirection.Output);

Now my procedure works.

answered Mar 17, 2016 at 20:31

Rich P

2024 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Christopher Lake · Accepted Answer · 2016-03-17 19:26:49Z

0

Give this a try in place of the current parameter add and see if it helps at all?

command.Parameters.Add(new SqlParameter("outParam1", OracleDbType.Varchar2));

answered Mar 17, 2016 at 19:26

Christopher Lake

3784 silver badges10 bronze badges

3 Comments

Rich P Over a year ago

--it doesn't like "SqlParameter" -- getting red line.

Christopher Lake Over a year ago

Admittedly I haven't used this in a few months, do this rather:

SqlDataReader reader = command.ExecuteReader();         while (reader.Read())         {             s1 += reader[0].ToString();         }

Rich P Over a year ago

The proc returns only one value, so a reader would be a little overkill. But I found a fix anyway -- have to include OracleCollectionType .None to the parameter arg list.

Collectives™ on Stack Overflow

how to define Output parameter as character string for Oracle procedure in c#?

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related