Oracle stored proc fails without raising exception

I've got a very simple stored proc within a stored package in Oracle 10g. The stored proc takes its parameters and inserts them into a table -- no validation, no processing. My application executes the stored proc and retrieves the records affected. How is it possible to end up with 0 records affected for an insert statement, and yet no error be generated? The table is keyed on App_ID and Ch_Seq, and the values being passed for those parameters from the application are guaranteed/confirmed to be a unique combination. There are no other constraints on the table, and no triggers. Following is the stored proc in question:

Code:

---

PROCEDURE sp_Write_Cardholder (
  pApp_ID IN NUMBER,
  pCh_Seq IN NUMBER,
  pChPrefix IN VARCHAR2,
  pChFirst IN VARCHAR2,
  pChMI IN VARCHAR2,
  pChLast IN VARCHAR2,
  pChGen IN VARCHAR2,
  pChSoc IN VARCHAR2)
IS
BEGIN
  INSERT INTO Cardholders (
    App_ID,
    Ch_Seq,
    ChPrefix,
    ChFirst,
    ChMI,
    ChLast,
    ChGen,
    ChSoc
  ) VALUES (
    pApp_ID,
    pCh_Seq,
    pChPrefix,
    pChFirst,
    pChMI,
    pChLast,
    pChGen,
    pChSoc
  );
END sp_Write_Cardholder;

---

The problem appears to only happen with some clients, not all. Is there some configuration of the Oracle client that would affect this behavior? The stations having the problem have both the 9i and the 10g client installed. I am using ODP.NET, and even restricting the reference to only the 9i version of that (which is what I am using successfully on my development station) does not alleviate the problem.

Thanks for any insight.

becuase the client hasnt commited the data?

put some debug in there to work out what is going wrong

So the rows affected property would not be set until after the commit? This stored proc is being called as a part of a transaction, so the commit cannot proceed until the stored proc has been confirmed to succeed.

the rows would not be available to see to other sesisons until a commit

the stored proc doesnt need to confirm anything, it just runs and finished when it is done

Quote:

---

My application executes the stored proc and retrieves the records affected.

---

Are you sure that the same session is doing both?

Quote:

---

Originally Posted by davey23uk

the rows would not be available to see to other sesisons until a commit

---

Can you confirm that this is a change in behavior with the Oracle 10g client and/or the 10g version of ODP.NET? Because, as I mentioned, the rows affected returns the expected value on stations with only the 9i client installed.

Quote:

---

Originally Posted by DaPi

Are you sure that the same session is doing both?

---

Yes.