This discussion is archived
4 Replies Latest reply: Oct 1, 2012 12:52 PM by 965494 RSS

ODP.Net OracleDataAdapter RETURNING INTO returning null in Output Parameter

741302 Newbie
Currently Being Moderated
I've been searching all over the net for an answer to my question, but can't seem to find an answer. I'm trying to use an OracleDataAdapter to insert a row from a DataTable and return the primary key value generated by a sequence. Any suggestions on getting this to work would be greatly appreciated.

Here's my Table Definition (DESC STMT_MSG)

Name Null? Type
----------------------------------------- ---------- -----------------
STMT_MSG_ID NOT NULL NUMBER(38)
CREATE_DT NOT NULL DATE
CREATE_PROG_NM NOT NULL VARCHAR2(30)
BLOCK_ID NUMBER(38)
APPL_ID_TYPE_CD VARCHAR2(1)
SVC_ID VARCHAR2(2)
LOGICAL_TERMNL_ADDR VARCHAR2(12)
SESSION_NBR VARCHAR2(4)
HEADER_SEQ_NBR VARCHAR2(6)
APP_BLOCK_ID VARCHAR2(2)
APPL_INPUT_OUTPUT_IND VARCHAR2(1)
MSG_TYPE_CD VARCHAR2(3)
INPUT_TM NUMBER(38)
MSG_INPUT_REF VARCHAR2(28)
OUTPUT_DT DATE
OUTPUT_TM NUMBER(38)
MSG_PRIORITY_TYPE_CD VARCHAR2(1)
USER_BLOCK_ID VARCHAR2(2)
BANK_PRIORITY_TYPE_CD VARCHAR2(8)
MSG_USER_REF VARCHAR2(20)
BODY_BLOCK_ID VARCHAR2(2)
TXN_REF_NBR VARCHAR2(16)
TXN_REF_RELATED_NBR VARCHAR2(16)
ACCT_ID VARCHAR2(35)
STMT_NBR NUMBER(38)
SEQ_NBR VARCHAR2(5)
OPEN_FINAL_INTRMT_IND VARCHAR2(1)
OPEN_BAL_AMT NUMBER(15,2)
OPEN_BAL_DEBT_CREDT_IND VARCHAR2(1)
OPEN_BAL_DT DATE
OPEN_BAL_CURNCY_TYPE_CD VARCHAR2(3)
CLOSE_FINAL_INTRMT_IND VARCHAR2(1)
CLOSE_BAL_DEBT_CREDT_IND VARCHAR2(1)
CLOSE_BAL_DT DATE
CLOSE_BAL_CURNCY_TYPE_CD VARCHAR2(3)
CLOSE_BAL_AMT NUMBER(15,2)
AVAIL_BAL_DEBT_CREDT_IND VARCHAR2(1)
AVAIL_BAL_DT DATE
AVAIL_BAL_CURNCY_TYPE_CD VARCHAR2(3)
AVAIL_BAL_AMT NUMBER(15,2)
FORWD_AVAIL_DEBT_CREDT_IND VARCHAR2(1)
FORWD_AVAIL_BAL_DT DATE
FORWD_AVAIL_BAL_CURNCY_TYPE_CD VARCHAR2(3)
FORWD_AVAIL_BAL_AMT NUMBER(15,2)
SUMRY_INFO_TXT VARCHAR2(400)
SWIFT_TRAILER_BLOCK VARCHAR2(100)
MSG_FILE_NM VARCHAR2(128)

Here's my code (This is a proof of concept, so I'm filling a datatable with an existing record, the zeroing out the primary key so that I can test out the code. Once I have this working I'll work it into my application code).

class Program
{
static void Main(string[] args)
{

//Get a data row that we can use to test our inserts with
DataTable dt = OracleUpdater2.load_it();

//Set the primary key to 0 so we can insert as if it were a new row (I've also tried DBNull.Value here)
dt.Rows[0]["stmt_msg_id"] = 0;
dt = OracleUpdater2.update_datasets_return(dt);
}
}



public class OracleUpdater2
{
private static OracleConnection cn;
private static OracleDataAdapter da = new OracleDataAdapter();
private static OracleConnection conn = new OracleConnection();
private static OracleTransaction transaction = null;

public static DataTable load_it()
{

string connectionString = "Data Source=dwdx;User Id=CAMS;Password=camsdev$10";
string selectSql = "select * from swift_stmt_msg where stmt_msg_id = 5425";
string tableName = "swift_stmt_msg";

conn = new OracleConnection(connectionString);
conn.Open();
da = new OracleDataAdapter(selectSql, conn);
OracleCommandBuilder bldr = new OracleCommandBuilder(da);
DataTable dt = new DataTable(tableName);
da.Fill(dt);
conn.Dispose();
return dt;
}

public static DataTable update_datasets_return(DataTable dt)
{
string selectSql = null;
cn = new OracleConnection("Data Source=dwdx;User Id=CAMS;Password=camsdev$10");
try
{
cn.Open();
}
catch (Exception ex)
{
throw new ArgumentException(" Error: connection lost." + ex.Message);
}

selectSql = "select * from stmt_msg where stmt_msg_id = 0";
da = new OracleDataAdapter(selectSql, cn);
OracleCommandBuilder bldr = new OracleCommandBuilder(da);
da.RowUpdated += new Oracle.DataAccess.Client.OracleRowUpdatedEventHandler(da_RowUpdated);

OracleCommand insertCommand = null;
try
{
insertCommand = (OracleCommand)(bldr.GetInsertCommand());
}
catch (Exception ex)
{
throw new Exception("" + ex.Message);
}

//Get rid of the primary key's parameter since we're going to be using an output parameter to get that back from the sequence.
string keyColumn = insertCommand.Parameters[0].ParameterName;
string sourceColumn = insertCommand.Parameters[0].SourceColumn;
insertCommand.CommandText = insertCommand.CommandText.Replace(keyColumn, "stmt_msg_s01.nextval");
insertCommand.Parameters.Remove(insertCommand.Parameters[0]);

//Add a RETURNING clause to get the sequence generated for our primary key (stmt_msg_id)
insertCommand.CommandText += " RETURNING " + sourceColumn + " INTO :seqno";

//Add the parameter to get the sequence generated for us
OracleParameter p = new OracleParameter();
p.ParameterName = ":seqno";
p.SourceColumn = "STMT_MSG_ID";
p.OracleDbType = OracleDbType.Int32;
p.Direction = ParameterDirection.Output;
insertCommand.Parameters.Add(p);

da.InsertCommand = insertCommand;

//If the primary key initial value is 0 I get ORA-02292: integrity constraint. If it's null, I get a ORA-01407 cannot update to NULL error.
try
{
transaction = cn.BeginTransaction();
da.Update(dt);
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
cn.Close();
cn.Dispose();
throw new ArgumentException(" Error: update_datasets_return " + ex.Message);
}

cn.Close();
cn.Dispose();
return dt;
}

public static void da_RowUpdated(object sender, OracleRowUpdatedEventArgs e)
{
for (int i = 0; i < e.Row.Table.Columns.Count; i++)
{
if(e.Row[i] != DBNull.Value)
System.Diagnostics.Debug.WriteLine(string.Format("{0} - {1}", e.Row.Table.Columns.ColumnName, e.Row[i].ToString()));
else
System.Diagnostics.Debug.WriteLine(string.Format("{0} - DBNull", e.Row.Table.Columns[i].ColumnName));
}
}
}


If I remove the primary key column from the data table altogether the insert appears to work, but nothing comes back in the :seqno parameter, and my row doesn't exist in the database table.

Any suggestions? BTW, code very similar to this works just fine with System.Data.OracleClient. In that scenario I'm not using the command builder, as I understand it won't work on an empty table.
  • 1. Re: ODP.Net OracleDataAdapter RETURNING INTO returning null in Output Parameter
    319958 Explorer
    Currently Being Moderated
    I am assuming that the sequence is applied via a trigger on the insert. I tend not to use the datatable inserts and such, so I won't be any help there.

    but looking at this:
    //Add a RETURNING clause to get the sequence generated for our primary key (stmt_msg_id)
    insertCommand.CommandText += " RETURNING " + sourceColumn + " INTO :seqno";
    
    //Add the parameter to get the sequence generated for us
    OracleParameter p = new OracleParameter();
    p.ParameterName = ":seqno";
    p.SourceColumn = "STMT_MSG_ID";
    p.OracleDbType = OracleDbType.Int32;
    p.Direction = ParameterDirection.Output;
    insertCommand.Parameters.Add(p);
    I would think that you would need:
    insertCommand.CommandText += "BEGIN SELECT SequenceName.CURRVAL  INTO :seqno FROM DUAL; END;";
  • 2. Re: ODP.Net OracleDataAdapter RETURNING INTO returning null in Output Parameter
    729737 Newbie
    Currently Being Moderated
    Had the same problem. After some rework of your code i managed to get it working (for me). On my table i have a before-inster-triggger thats inserts the new value into the table from a sequence.

    ...
    SetDataSet(myDataSet, "select * from myTable ORDER BY 1 ASC ", myPrimaryKey);
    ...


    public void SetDataSet(DataSet dataset, string sql, string primaryKey)
    {
    OracleCommand cmd = new OracleCommand(sql, myConnection);
    OracleDataAdapter da = new OracleDataAdapter(cmd);
    OracleCommandBuilder cmdBldr = new OracleCommandBuilder(da);

    da.InsertCommand = cmdBldr.GetInsertCommand();
    da.InsertCommand.CommandText += " returning " + primaryKey + " into :newid";
    da.InsertCommand.Parameters.Add("newid", OracleDbType.Int32, DBNull.Value, ParameterDirection.Output);
    da.InsertCommand.Parameters["newid"].SourceColumn = primaryKey; //i only need this to know which column to update in the event          
    da.RowUpdated += new OracleRowUpdatedEventHandler(da_RowUpdated);

    da.AcceptChangesDuringUpdate = true;

    da.Update(dataset);
    }

    void da_RowUpdated(object sender, OracleRowUpdatedEventArgs e)
    {
    OracleDataAdapter da = (OracleDataAdapter)sender;
    e.Row\[da.InsertCommand.Parameters\["newid"\].SourceColumn\] = da.InsertCommand.Parameters\["newid"\].Value;
    }

    Edited by: user12104037 on 2010-feb-03 23:25
  • 3. Re: ODP.Net OracleDataAdapter RETURNING INTO returning null in Output Parameter
    965494 Newbie
    Currently Being Moderated
    Hi,

    I need the returning into to return multiple values because we have multiple sequences executed while inserting. Is this possible at all with ODP, I tried many things, but no luck.

    From the example: BEGIN insert into table(col1, col2) values (:col1, :col2) returning SequenceName.CURRVAL INTO :seqno; END; returning one column, I want to return an additionnal column

    I would like to do something like BEGIN insert into table(col1, col2) values (:col1, :col2) returning SequenceName.CURRVAL, SequenceName2.CURRVAL INTO :seqno; END;, but it won't work. I also, tried:

    BEGIN insert into table(col1, col2) values (:col1, :col2) returning SequenceName.CURRVAL, SequenceName2.CURRVAL INTO :seqno, :seqno2; END;
    and
    BEGIN insert into table(col1, col2) values (:col1, :col2) returning SequenceName.CURRVAL INTO :seqno returning SequenceName2.CURRVAL INTO :seqno2; END;

    but no luck. Ay ideas?

    Furthermore, is it possible to do this using multiple columns with different types, like returning a number and varchar2?

    Thanks for your help.

    Edited by: user7070254 on Oct 1, 2012 10:34 AM
  • 4. Re: ODP.Net OracleDataAdapter RETURNING INTO returning null in Output Parameter
    965494 Newbie
    Currently Being Moderated
    I found how to do it. In my test application, my problem was not with the sql statement but it was related to how I was setting my varchar in my parameter definition.

    This sql: BEGIN insert into table(col1, col2) values (:col1, :col2) returning table.col3, table.col4 INTO :numVal, :stringVal; END;
    works!

    My oracleparameters for output are:

    .ParameterName = ":numVal";
    .DbType = DbType.Int32;
    .Direction = ParameterDirection.Output;

    .ParameterName = ":stringVal";
    .DbType = DbType.String;
    .Direction = ParameterDirection.Output;
    .ArrayBindSize = new int[]{255,255]; // 2 values because my command.ArrayBindCount is 2

    and it also works with update statements.

    So, when you use arrays in your parameters, for the returned values, you do not need to specify the arraybindsize, EXCEPT for strings...which was my problem.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points