2 Replies Latest reply: Mar 28, 2013 4:48 PM by Gary Graham-Oracle RSS

    In SQL Dev, View behaves one way in 10g, another in 11g, same code ... huh?

    SM_Work
      I originally posted this in the SQL / PL/SQL forum where I thot I had gotten an answer.

      Wrong assumption on my part. Here's the thread link ...

      ISO Trigger behavior (behaviour?) different in 11g (10g works, 11g not)

      V2 based on V1 (based on other tables / views).
      V2 has an ISO trigger to handle inserts/updates/deletes from base table.

      At SQL Plus level, the delete statement succeeds in both 10g and 11g ...
      An example ...

      delete from VWRTOGO_SP_XREF_0300_SUPPORT
      where cGOID = '20139028' and cSPID = '2013IND9919';

      [cGOID and cSPID combo defines unique record in underlying GO_SP_XReference table]

      In SQL Developer ... when I look at the record related to that example, via the view ...

      In the 10g database ... I see the insert / delete icons activated in the top bar of my SQL Dev frame.
      (as I expect ... a view with an ISO trigger, yes?)

      In the 11g database ... same record ... I do not see any icons activated.
      Indeed, it will not let me delete this record.

      Again, both cases, SQL Plus succeeds.

      =============================
      Further ... PL/SQL Developer ... that other tool ... has same issue ... will not delete the record.
      Further still ... Forms 6i (yes I know, don't go there) ... against 10g view succeeds, 11g view not.

      11g DB is set to 10g compatibility.
      =============================

      So ... what is going on here? Is this indeed a run up against Bug 14274321?
      Is this a proble with the SQL Developer tool?

      If SQL Plus deletion had failed, I would have said, it's the view / iso trigger code.

      BUT ... it succeeds.

      SO ... it may still be the code ... but ... I think somehow 11g is misperceiving the view.

      Can anyone please enlighten me? Offer thots?

      Thanks.

      SM

      (I can send / post screen shots if there's a way to do that)
        • 1. Re: In SQL Dev, View behaves one way in 10g, another in 11g, same code ... huh?
          SM_Work
          Turns out, the underlying view, V1 ... is the culprit ...

          key preserved table

          ... when we use the OLD syntax (a "plus") instead of the NEW syntax ("outer join") ... everything works as we expect.

          So problem is in Database, not in SQL Developer.

          http://eusebium.blogspot.com/2012/02/oracle-odbc-issue-accessviolationexcept.html#!/2012/02/oracle-odbc-issue-accessviolationexcept.html
          • 2. Re: In SQL Dev, View behaves one way in 10g, another in 11g, same code ... huh?
            Gary Graham-Oracle
            Hi,

            In addition to the database ANSI syntax bug noted in the thread you posted over in the other forum, SQL Developer's object viewer will attempt to detect whether a view is updateable and disable the edit icons in the toolbar if it is not. I recall a bug where the view was updateable, but the edit icons got disabled anyway. I believe that specific issue was fixed, but there may be other cases to be found. The behavior you experience may depend on the particular release of SQL Developer you use.

            Anyway, if interested, the following bug contains simple test cases for both updateable and non-updateable views:

            Bug 8460737 - ORA-22816 INSERTING ROW INTO VIEW WITH INSTEAD OF TRIGGER USING GRID

            By the way, that bug pertains specifically to an updateable view, the issue is entirely different from yours, and it is marked as not feasible to fix.

            Regards,
            Gary
            SQL Developer Team

            Edited by: Gary Graham on Mar 28, 2013 2:46 PM