Oracle stored proc fails without raising exception
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Oracle stored proc fails without raising exception

  1. #1
    Join Date
    Aug 2002
    Posts
    10

    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
    England
    Posts
    7,333
    becuase the client hasnt commited the data?

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

  3. #3
    Join Date
    Aug 2002
    Posts
    10
    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.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    My application executes the stored proc and retrieves the records affected.
    Are you sure that the same session is doing both?
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  6. #6
    Join Date
    Aug 2002
    Posts
    10
    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.

Posting Permissions

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


Click Here to Expand Forum to Full Width