Get value from oracle stored procedure

Question

Hi all

I have a oracle procedure which should return a concatenated string of all the parameters

create or replace procedure tin_builder (type in varchar2, 
       tin_serial in number, rand_digit in varchar2, tin out varchar2 ) is 
    BEGIN
      tin := type || TO_CHAR(tin_serial) || rand_digit ;
    END

Now i want to call the procedure from visual studio 2008 (C# code)

public void TinBuilder(string type, long tin_serial, string rand_digit)
{
    OracleConnection connection = new OracleConnection("Data Source=xe;User ID=system;Password=******;");

    OracleCommand cmd = new OracleCommand();
    cmd.Connection = connection;
    cmd.CommandText = "tin_builder";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("type", OracleDbType.Varchar2).Value = type;
    cmd.Parameters.Add("tin_serial", OracleDbType.Decimal).Value = tin_serial;
    cmd.Parameters.Add("rand_digit", OracleDbType.Varchar2).Value = rand_digit;
    cmd.Parameters.Add("tin", OracleDbType.Varchar2).Direction = ParameterDirection.ReturnValue;

    try
    {
        connection.Open();

        cmd.ExecuteNonQuery();
        TextBox1.Text = cmd.Parameters["tin"].Value.ToString();
    }
    catch (Exception ex)
    {

    }
    finally
    {
        connection.Close();
    }
}

Then called it with :

TinBuilder("1", 10000001, "37");

But it does not show any value in the text box :( . Please someone help me out.

Answer 1

"Type" may be a reserved word. Are you sure the procedure compiled and is valid?

Also are you catching any error messages there, and hiding them with the catch clause? That seems like bad practice.

Answer 2

"Type" is a reserved word in Oracle. Here's the link: http://www.cs.umbc.edu/help/oracle8/server.815/a42525/apb.htm

And as said by OMG Ponies change & try: SELECT type || TO_CHAR(tin_serial) || rand_digit INTO tin FROM DUAL;

And also make sure you always 'initiate' exception to catch these kind of errors