could not execute native bulk manipulation query

Question

Hi,

I am trying to implement a "if exists, update, otherwise, insert" data access method in NHibernate. My database is Oracle 10g.

I am getting this "could not execute native bulk manipulation query" error when try to run this code, if I run the insert or update individualy, it works just fine.

Thanks!

string sql = @"DECLARE
                CntOfRow Number(10,0);
              BEGIN
                    SELECT count(*)
                    INTO CntOfRow
                    FROM Table1
                    WHERE 
                        QueID=:QueID

                    IF CntOfRow=0 THEN
                        INSERT INTO Table1 ...;
                    ELSE
                        UPDATE Table1 ... ;
                    END IF;
                END;";


            INHibernateSession session = NHibernateSessionManager.Instance.Session;

            try
            {
                session.BeginTransaction();
                ISQLQuery query = session.GetISession().CreateSQLQuery(sql.Replace(System.Environment.NewLine, " "));
                query.SetParameter("QueID", queID);
                query.ExecuteUpdate();
                session.CommitTransaction();
            }
            catch (Exception ex)
            {
                session.RollbackTransaction();
                throw;
            }

Answer 1

I don't know why you get that error, but you could try this simpler PL/SQL block instead:

BEGIN
    INSERT INTO Table1 ...;
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        UPDATE Table1 ... ;
END;";

Answer 2

You seem to be missing an ; after the SELECT

This link might also be of interest to you.

As about inserting/updating, see MERGE statement. It works like this:

MERGE INTO t1 dest
USING (SELECT 1 pk, 11 i FROM dual) src
   ON (dest.pk = src.pk)
 WHEN NOT MATCHED THEN INSERT (dest.pk, dest.i) VALUES (src.pk, src.i)
 WHEN MATCHED THEN UPDATE SET dest.i = src.i;

Also see this topic