9 Replies Latest reply on Dec 4, 2014 10:34 PM by thatJeffSmith-Oracle

    Update a data in the result query

    user12162733

      In this moment we are moving from a database development tool to sql developer, in this tool we can update any data of the sql result in the grid, just clicking on the column, but in sql developer how i do that? Thanks

        • 1. Re: Update a data in the result query
          thatJeffSmith-Oracle

          We don't support that, and aren't likely to ever support it.

           

          You can copy the predicate from your query, ctrl-click into the table of question, and paste the predicate into a filter. This will give you the rows you want and the ability to edit them.

          • 2. Re: Update a data in the result query
            In this moment we are moving from a database development tool to sql developer, in this tool we can update any data of the sql result in the grid, just clicking on the column, but in sql developer how i do that?

             

            What do you mean by 'update any data of the sql result'?

             

            There is NO tool that can perform updates on just ANY data in a result grid that is the result of a generic query. That data could have come from multiple tables, views or even functions. There would be no way that a 'tool' could update the result produced by a function since there would be no way to know just where the function even got the data.

             

            You can certainly update the data in the grid for a table that you have selected in the navigation tree. Select a table, and then the data tab to show the data. You can then edit values in that grid and save the changes back to the database. That works even if you have applied filters to the data.

             

            When you press the 'commit' button you will see something like this in the log pane:

            UPDATE "SCOTT"."DEPT" SET DNAME = 'RESEARCH' WHERE ROWID = 'AAAR3qAAEAAAACHAAB' AND ORA_ROWSCN = '50990031'

            Commit Successful

            That is possible because the data from a table also includes the ROWID that uniquely identifies each row. So an update statement can be created, as the above shows, that will locate and update that same row.

             

            Unfortunately that inclusion of ORA_ROWSCN is a bug and will prevent you from changing that row if another session has modified any other row from the same block since ORA_ROWSCN, by default, is stored at the block level. It isn't stored at the row level unless the table is created with ROWDEPENDENCIES.

             

            So after updating a different row in the same table (but on the same block' sql dev will now show this if I try to change that same row again:

            UPDATE "SCOTT"."DEPT" SET DNAME = 'RESEARCH*' WHERE ROWID = 'AAAR3qAAEAAAACHAAB' AND ORA_ROWSCN = '51193796'

            One error saving changes to table "SCOTT"."DEPT":
            Row 1: Data got committed in another/same session, cannot update row.

            So whether you can do what you want depends on just what it is you want to do.

            • 4. Re: Update a data in the result query
              Gary Graham-Oracle

              One additional note to rp0428's explanation...  The use of ORA_ROWSCN can be disabled by unchecking

              Tools > Preferences > Database > ObjectViewer > Use ORA_ROWSCN ...

              • 5. Re: Update a data in the result query
                thatJeffSmith-Oracle

                there's a Toad feature that lets you do this - they grab rowIDs on queries...and some views are updateable

                 

                While possible we could implement said feature, we're not going to.

                 

                Query results are query results.

                • 6. Re: Update a data in the result query
                  Galo Balda

                  PL/SQL Developer does something similar.

                  • 7. Re: Update a data in the result query
                    there's a Toad feature that lets you do this - they grab rowIDs on queries...and some views are updateable

                     

                    While possible we could implement said feature, we're not going to.

                     

                    Query results are query results.

                    Ok - but didn't sql developer ALSO grab the rowid on the query? That log shows rowid was used in the update predicate to identify the row.

                     

                    IMHO there is too much risk for too little reward. There are far more important issues to deal with than something like that.

                     

                    It's one thing to allow a user to pick a table and update it in place. That adds a lot of value over forcing them to write a query. I personally could care less that my update got rejected because someone else change a column in the same row when I was changing a different column. Only one user should be changing the row at a time.

                     

                    There are tools that try to let you do finer granularity.

                     

                    Even 20 years ago the early versions of PowerBuilder the data window gave you three options.

                    1. primary key

                    2. primary key and modified values

                    3. primary key and modifiable values.

                     

                    Option #1 only checked the primary key - if it matched it let you update the row

                     

                    Option #2 checked that the primary and the columns that you actually modified have the same values that they had when the data was first queried. If they still match it meant no other user changed any of the columns that you are changing so it lets you update the row.

                     

                    Option #3 checked the key and ALL columns that you COULD modify even if you didn't actually modify them. They all had to be unchanged from the original values. for the update to proceed.

                    • 8. Re: Update a data in the result query

                       

                      One additional note to rp0428's explanation...  The use of ORA_ROWSCN can be disabled by unchecking

                      Tools > Preferences > Database > ObjectViewer > Use ORA_ROWSCN ...

                      Yes - but as I noted using that pseudo-column is typically NOT accurate in terms of indicating a change to a particular row. In my opinion it is a 'bug' (although a harmless one) in that it prevents legitimate changes from occuring in some instances. That is harmless in that there isn't any real harm done - but a user would have a hard time trying to figure out what happened.

                       

                      If the ROWEPENDENCIES status of the table was determined at the time the grid was populated then it could be used reliably. But all of that is overkill in my opinioni.

                       

                      An optimistic locking approach doesn't lock the rows until the user wants to make a change. If the row is locked in the DB then no other user can change that row no matter what tool they try to use. That 'optimistic' approach is what PowerBuilder was doing in my reply to Jeff.

                       

                      Functionally the sql developer team gets it right almost all of the time. Give the most bang for the buck - only it is Oracle that is spending the bucks not the customer.

                      • 9. Re: Update a data in the result query
                        thatJeffSmith-Oracle

                        It's technically possible, we're just not going to do it.

                         

                        If you want to edit data, open a table editor.

                         

                        If you want to run a query, run a query.