Failed to execute bulk manipulation request

Question

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

I get this error "I can’t execute the request for mass manipulation" when I try to run this code, if I start the insert or update the personality, it works 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

It seems you are missing ;afterSELECT

This link may also interest you.

For insert / update, see the MERGE instruction. It works as follows:

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 section.

Answer 2

, , PL/SQL:

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

Answer 3

Go to the Oracle client, for example Toad or SQL Editor, and try to execute the procedure with the same parameters that you send through Hibernate.

In my case, Oracle threw an error, for example:

11:50:57 ORA-01403: no data found

The reason was the choice within the procedure / function. Then improve the procedure / function to catch these exceptions.