You’re doing some data clean-up, or maybe you’re just trying to answer a business question for a user, and you notice the data doesn’t look quite right.

Name that tune...
Name that tune…

You want to click in that ‘(null)’ cell and tell the world that Tyler and Perry composed that song! But, we’re in a query result set, and not a table editor. I’m the ultimate lazy-dev, so what’s the easiest way to get what I want?

Throw In a Little Copy & Paste with some Mouse Magic

Copy the WHERE and ctrl+hover into the table
Copy the WHERE and ctrl+hover into the table
  1. Copy your WHERE clause, minus the actual word ‘WHERE’
  2. Ctrl+Mouse hover over the table or view name in the FROM
Every Table and View Editor Has a Filter Control Every Table and View Editor Has a Filter Control[/caption]

Now paste in your text from where you copied it in the worksheet and hit

Edit away!
Edit away!

But Why Can’t I Just Edit the Data in The Worksheet?

Suffice to say there are philosophical and technical challenges at play here. We’ve decided to leave the worksheet as a place where you go query and run scripts. Interactive (read+write) grids in the worksheet are not going to happen.

On the philosophy side of things – the concept of doing a SELECT, getting results back, and then treating those as ‘live’ records for you to update: that doesn’t jive with us. If you want to edit records, then you should go into the table editors. Or write an UPDATE. Or a DELETE.

And if you want to do this for joined records, make a view.

But instead of just ‘being stuck’ I hope you find this alternative workflow better than just writing your UPDATEs and DELETEs on your own.

No, you can’t always get what you want
No, you can’t always get what you want
No, you can’t always get what you want
But if you try sometime, you just might find
You get what you need
— Mick Jagger/Keith Richards

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

93 Comments

    • Marc S.

      Come on, get real. This is a business decision by Oracle and has little to do with philosophy. Yes, it would take some programming to get it fixed, but it’s not rocket science. And there are other programs on the market that support data editing directly from query results, and do it very well..
      Yes Jeff, your work around does work but it’s clumsy, slow, and is impractical for more complicated queries.
      SQL Developer is a tad rough around the edges, but on the whole it’s a pretty good product, and I’m getting my work done. The lack of direct editing is one of the few serious irritations.
      Thanks for the support.

  1. Kurt Jegge Reply

    I can do it in toad, plsql-developer almost in any other tool except in oracle sql developer. This userunfriendly change your mind!

  2. that simply sucks, but oracle an user friendly isnt a good combination, i guess the main purpose is to sell specialists

  3. Lucio Menci Reply

    Hi,

    Do you know why my SQL developer don’t work as you wrote? I use it with a db2 connection with a java driver developed by IBM (db2jcc4.jar), but cells seems to be uneditable. I opened a table like your sudgestion, I see the pencil when doubleclicking in a cell, and it opens the edit window, but the OK button is disabled. The buttons to add and delete rows neither the filter fields are not visible on the main panel. Can you sudgest a way to perform it in my case?

    Thank you,
    Lucio Menci

    • Because this feature, as are about 95% of the features, in the application, are written and supported for an Oracle Database only.

      DB2 connectivity is provided for exactly one use case – migrating that to an Oracle Database.

  4. Ok Jeff I understand what you are saying but when I am writing a query on the editor and I right click on a table to ‘Open Declaration’ to change the data, some times works and some times does not (the vast majority). ‘Pop up Describe’ always works with right click but you cannot see data on that.

    • I need you to share those scenarios where the table won’t open for you. Then we can fix that for you.

  5. In “toad for oracle” there is possibility to edit row in worksheet if you select extra column: rowid. Is it possible in SQL Developer? or in future versions?

Write A Comment