How to return sequence value from trigger, after executing Oracle insert statement?

Question

In c#, after executing Oracle insert statement, I want to return sequence value that has been assigned to inserted record in the table, which has sequence-based id increment trigger on it. And then I want to set textbox value to that new Id.

I've tried that way after searching about, but the idea it not very clear to me:

• can id be returned into textbox directly?

• Is the word id after the returning keyword, a column name?

• what should I do next?
  

  INSERT INTO teams (name) VALUES ('West Ham United') RETURNING id INTO :textbox

I have also tired this

cmd.Parameters.Add(new OracleParameter(parameterName = ":OUT_CUST_ID", OracleDbType.Int64, direction = ParameterDirection.Output));

Answer 1

You seem doing right things. Use RETURNING INTO and ExecuteNonQuery with output parameter. Considering that you're using ODP.NET:

string sql = @"INSERT INTO teams (name) 
               VALUES ('West Ham United') 
               RETURNING id INTO :1";

using (var cmd = new OracleCommand(sql, conn))
{
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.Add(":1", OracleDbType.Decimal, ParameterDirection.Output);
    // !!! If your output parameter is of String Type, 
    // it is necessary to specify "size"
    if (cmd.ExecuteNonQuery() > 0)
    {
        txtData.Text = ((OracleDecimal)cmd.Parameters[0].Value).ToInt32().ToString();
    }
}

Oracle output numeric parameter value is actually Oracle type, which you need to convert.

Answer 2

Use a procedure (and as an added bonus you won't need the trigger as you can encapsulate the trigger's logic into the procedure as well):

CREATE OR REPLACE PROCEDURE add_team(
  i_name IN  TEAMS.NAME%TYPE,
  o_id   OUT TEAMS.ID%TYPE
)
AS
BEGIN
  INSERT INTO teams (
    id,
    name
  ) VALUES (
    TEAMS_SEQ.NEXTVAL,
    i_name
  )
  RETURNING id INTO o_id;
END;
/

Then just call the procedure from your code.

Answer 3

according to @T.S. suggestion I made some changes

removing (OracleDecimal) removing .ToInt32() replacing cmd.Parameters[0].Value with cmd.Parameters[0].Value

which containing the the parameter name :1

that let the code works below the working code

    string sql = @"INSERT INTO teams (name) 
               VALUES ('West Ham United') 
               RETURNING id INTO :1";

using (var cmd = new OracleCommand(sql, conn))
{
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.Add(":1", OracleDbType.Int32, ParameterDirection.Output);
    // !!! If your output parameter is of String Type, 
    // it is necessary to specify "size"
    if (cmd.ExecuteNonQuery() > 0)
    {
        txtData.Text = (cmd.Parameters[":1"].Value).ToString();
    }
}

Answer 4

Thank you all fro your help below an example for returning sequence value from trigger after executing Oracle insert statement...

 public void data_insert()
    {
        string connstr = "User Id=user;Password=pwd;";
        string cmdtxt = @"insert into customers 
                          (CUST_ID,F_NAME,CUST_PHONE1,CUST_PHONE2,EMAIL)
                          values (null,:TB_NAME,:TB_PHONE1,:TB_PHONE2,:TB_EMAIL)
                          RETURNING CUST_ID into :OUT_ID";

        using (OracleConnection conn = new OracleConnection(connstr))
        using (OracleCommand cmd =new OracleCommand(cmdtxt,conn))
        {
            cmd.Parameters.Add(new OracleParameter("TB_NAME", TB_NAME.Text));
            cmd.Parameters.Add(new OracleParameter("TB_PHONE1", TB_PHONE1.Text));
            cmd.Parameters.Add(new OracleParameter("TB_PHONE2", TB_PHONE2.Text));
            cmd.Parameters.Add(new OracleParameter("TB_EMAIL", TB_EMAIL.Text));

            cmd.Parameters.Add(":OUT_ID", OracleDbType.Decimal, ParameterDirection.Output);

            cmd.CommandText = cmdtxt;
            conn.Open();
            cmd.ExecuteNonQuery();

            TB_CUST_ID.Text = (cmd.Parameters[":OUT_ID"].Value).ToString();
        }
    }