Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2002
    Location
    Tennessee, USA
    Posts
    8

    Unanswered: 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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Maybe the client didn't commit? Or maybe some exception is raised and the client ignores it?

  3. #3
    Join Date
    Aug 2002
    Location
    Tennessee, USA
    Posts
    8
    I failed to mention that this is being called within a larger transaction, so it is true that at the point I'm checking the return value, the transaction has not yet been committed. Would this explain the discrepancy in return values (records affected) on some stations? Would the 10g client, for example, not set this value until after the commit? If that's the case, then how would I check whether the operation succeeded in order to determine whether to continue and commit, or rollback?

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by lebb
    I failed to mention that this is being called within a larger transaction, so it is true that at the point I'm checking the return value, the transaction has not yet been committed. Would this explain the discrepancy in return values (records affected) on some stations? Would the 10g client, for example, not set this value until after the commit? If that's the case, then how would I check whether the operation succeeded in order to determine whether to continue and commit, or rollback?
    What "return value" are you retrieving, and how?

  5. #5
    Join Date
    Aug 2002
    Location
    Tennessee, USA
    Posts
    8
    Quote Originally Posted by WilliamR
    What "return value" are you retrieving, and how?
    The return value of cmd.ExecuteNonQuery(), where cmd is the ODP.NET OracleCommand object set up to execute the stored proc in question.

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    The return value of cmd.ExecuteNonQuery(), where cmd is the ODP.NET OracleCommand object set up to execute the stored proc in question.
    Hum, from the documentation :
    ExecuteNonQuery returns the number of rows affected, for the following:

    * If the command is UPDATE, INSERT, or DELETE and the XmlCommandType property is set to OracleXmlCommandType.None.
    * If the XmlCommandType property is set to OracleXmlCommandType.Insert, OracleXmlCommandType.Update, OracleXmlCommandType.Delete.

    For all other types of statements, the return value is -1.
    So the call to the stored procedure with cmd.ExecuteNonQuery() does not return the number of rows processed. Don't test this return value with stored procedures or functions : it is not significant. With your code, if you did not get any exception, then everything was OK, otherwise you would have gotten an exception raised to .NET.

    Commit and check the rows within the table : you will see the inserted row.

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  7. #7
    Join Date
    Aug 2002
    Location
    Tennessee, USA
    Posts
    8
    The stored proc contains an insert statement, so the rows affected property normally reflects this -- this has held true consistently with the 9i client. The documentation does not specify that stored procs containing UPDATE, INSERT, or DELETE statements will be handled differently than straight SQL statements (and in fact, as I said, in the 9i client they indeed do give the same result). I can run the same application on a PC with the 9i client and ODP.NET, and I get the expected result of 1. I would just like to see some documentation confirming that this behavior changed in 10g; otherwise my only assumption can be that it is a bug.
    Last edited by lebb; 05-20-06 at 19:48.

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    For all other types of statements, the return value is -1.
    Is a stored procedure either an INSERT, UPDATE or DELETE statement ? NO

    So it is considered as "other types of statement", simply.

    It is NOT a bug. IT IS DOCUMENTED, and BTW it works the same as in JAVA or OCCI.

    You think of a stored procedure as processing only ONE statement, whereas a stored procedure can execute dozens of statements : which one's number of rows affected would you want it to return to you ? This cannot work that way and certainly never did (though I cannot find the 9i .NET documentation, I think the 1 you are getting does not represent the number of row affected : try with an INSERT ... SELECT ... or with an UPDATE affecting several rows, maybe you'll also get 1).

    Now, if you want the number of rows affected by your insert statement without any doubt, you can do that :

    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,
      pNbOfRowsInserted OUT NUMBER)
    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
      );
    
      pNbOfRowsInserted := SQL%ROWCOUNT;
    
    END sp_Write_Cardholder;
    You can also use a function instead of a procedure :

    Code:
    FUNCTION 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) RETURN NUMBER
    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
      );
    
      RETURN SQL%ROWCOUNT;
    
    END sp_Write_Cardholder;
    Notice that even using a function, ExecuteNonQuery will still return -1. To get the result of the function you will have to call something like "BEGIN :1 := sp_Write_Cardholder(:2,...); END;" with ExecuteNonQuery.

    Next time, please don't be too quick in blaming others for bugs of yours .

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •