7 Replies Latest reply: Apr 2, 2010 7:10 AM by 763539 RSS

    Insert statement fails in not LIVE workspaces: ORA-01403: no data found

    763539
      Hello!

      I'm encountering a strange error, when I'm trying to run a single insert statement on a version enabled table. In the LIVE workspace it works perfectly, but in any other workspaces it fails. A trigger genereated by the workspace manager throws ORA-01403 error. I have checked all related objects and all are valid. The not null columns of the table are all set in the insert statement too, and the table dont have any child tables. What could possibly cause this problem?

      Thanks in advance.
      Viktor
        • 1. Re: Insert statement fails in not LIVE workspaces: ORA-01403: no data found
          357282
          Hi Victor,

          Can you provide a bit information about the "other" workspace.

          Sounds like an OWM trigger queries referential data using (cursor or select), check for present of a parent key (if there is one).

          Serge
          • 2. Re: Insert statement fails in not LIVE workspaces: ORA-01403: no data found
            763539
            Thanks for your reply!

            What kind of information would you like to know about the "other" workspaces? On "other" workspace I mean any workspace that I've created, and all of them have the same settings. The parent of all workspaces is the 'LIVE' workspace. All of these are continually refreshed and locking has been set to 'S'. For the rest I use the default values.

            That was my first tought too, so I've already checked all referential data. All of them existed. To get further prove I created a new workspace, run the insert statement and it failed. After switching back to LIVE it worked flawlessly. I'm sure that no data modifications at the referential values had been made between the two insert.

            My second tought was that it could have something with the null values (which would be an abnormal behaviour as far as I know, but who nows? ). I have checked all null values in the statement that have referencial relationship with any column and switched them into an exist primary key. That didn't help unfortunately.

            If I can provide you any more usefull information, feel free to ask.

            Thanks in advance.
            Viktor
            • 3. Re: Insert statement fails in not LIVE workspaces: ORA-01403: no data found
              NoelKhan
              Viktor,

              This site suggests that there may be a SELECT INTO statement (inside of a trigger perhaps) which results in NO_DATA_FOUND.

              Good luck,
              Noel
              • 4. Re: Insert statement fails in not LIVE workspaces: ORA-01403: no data found
                763539
                Thanks Noel,

                Debugging the insert trigger would get me some clues, but due to modifiying WM generated triggers is forbidden I'm really lost. Any good tips how to debug it without modifying its source?
                The thing that bothers me the most is that its working in the LIVE workspace. Does it have any specialities? Yes, I know that this must be the base workspace of all other workspaces in the end, but does the WM handle it in a different way then user created workspaces?

                Viktor
                • 5. Re: Insert statement fails in not LIVE workspaces: ORA-01403: no data found
                  Ben Speckhard-Oracle
                  Hi,

                  A ORA-1403 error could be happening from a number of places, so I would suggest to set the following trace prior to performing the insert. It should give a better indication as to exactly where the error is occurring.

                  SQL> alter session set events '1403 trace name errorstack level 3, forever';

                  It's not going to be possible to post the trace here, so you want to look for the last pl/sql call stack listed in the generated trace file.

                  Also, what version of OWM are you using?

                  Regards,
                  Ben
                  • 6. Re: Insert statement fails in not LIVE workspaces: ORA-01403: no data found
                    NoelKhan
                    Viktor,

                    . . . .In another post, bspeckha pointed out that OWM 'internalizes' table-triggers upon version-enabling. Did you have any triggers defined on pertinent tables before versioning? If so, validate the logic in those triggers. If you find a bug and need to fix those internalized triggers (OVM_INSERT_##), then you could recreate the trigger within a DDL session as follows:
                    -- connect using the table owner
                    connect <SCHEMA>/****
                    
                    exec dbms_wm.BeginDDL('<TABLE>');
                    
                        -- create or replace trigger on <TABLE>_LTS...
                    
                    exec dbms_wm.CommitDDL('<TABLE>');
                    . . . .Also, does the trigger-owner have sufficient privileges to access all the database objects required by your trigger/procedures/etc? When you perform your update through the LIVE workspace, which database-user are you using and what are that user's credentials?

                    . . . .Another question that comes to mind is whether your LIVE/child workspaces are frozen and if so, in which modes? In our environment, users perform edits in non-LIVE and unfrozen leaf workspaces. If I don't want to flounder-about with workspaces, I schedule updates after-hours during which time I temporarily unfreeze the LIVE workspace and run my DML.

                    Regards,
                    Noel
                    • 7. Re: Insert statement fails in not LIVE workspaces: ORA-01403: no data found
                      763539
                      Hy all,

                      Thanks for the hints.
                      Fortunately we reconsidered the datastrucure and logics in our database at the table we had problems due to other reasons, and that fixed the issue... We did nothing, but resolved an N:N relationship between two tables. (One of them was the one I tried to insert a new row into.)
                      I still dont know what caused the problem with the insert, but i'm almost sure it was one (or more) of our generated triggers combined with the N:N relationship. I had no time to check them all one by one, and now I've lost the opportunity to do so. It's a little frustrating, it's really interesting me what caused the strange behaviour. Now nothing left for me, but to guess.

                      Regards,
                      Viktor