SQL Developer: I’m Looking at a Record, How Do I Edit It?

thatjeffsmith SQL Developer 66 Comments

Tell Others About This Story:

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

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 66

  1. This solution works well if we have data from single table but if the data is coming from multiple table then this workaround is not working…

    Any workaround to that??

    1. thatjeffsmith Post
      Author

      That’s the last way I’d update a table. I suppose if you wanted to do this, you’d create a view using your SQL, and if it were updateable, work on it that way.

      But even then, I’d not advise folks to do that. If you’re going update a table, just update the table.

  2. NOT MAKING ME EDIT THE SQL RIGHT AWAY IS LIKE GIVING ME AN HAMBURGER WITHOUT THE BURGER INSIDE.

    FUCK YOU AND YOUR ASSWIPE PHILOSOPHY. EVERYTIME PEOPLE DOES TOOL WITHOUT LOOKING AT REAL WORLD USE AND THINKING THEY’RE USEFUL IN THEY FANTASY DREAM WORLD

    1. thatjeffsmith Post
      Author

      But, Flavio, you don’t want to edit the SQL, you want to edit the Data.

      I’d rather you hear it straight from us than get some wishy-washy response like ‘your request is on the list.’

      I hope you give us an another chance, but I understand if you don’t. There’s a few hundred tools out there to choose from. I hope you find one that meets your needs.

      1. I must say that Mr. FUCK YOU is right.
        Currently I am sitting in front of a burger without patty, you can excuse bad decisions with “Philosophy” but in the end it remains an unfinished product with this particular flaw which will turn away every sane user.

        Good job… not.

        1. thatjeffsmith Post
          Author

          We’re not selling food. I’d not sell or offer a compound mitre saw with the safety mechanism removed from the cutting blade.

          Sorry we don’t see things the same way. There are several hundred tools to choose from out there. I hope you find one that tastes good.

  3. I can see the value in the grid; I can see the rowid in the grid; you have everything you need to safely support updates and delete.
    So every day, we wish we could have TOAD. I’m suspicious your philosopher is an infiltrate… secretly conspiring to ensure we can never be happy with 2nd best, driving us ever closer to the fit of madness required to pay that kind of money.

    1. thatjeffsmith Post
      Author

      I can understand your perspective. Turning the worksheet data grids into two-way portals to your database are a huge undertaking. And before we even agree to do that amount of work, we already think it’s a bad idea. So, the gulf is quite large.

  4. Dear Jeff,

    What is the root of your philosophy? Namely why do you think it’s so damn bad to offer a feautre of editing query result right in the grid? I mean database can always determine if joined query (or view) is editable and what key columns will be,.. So…

    KR, Anton

    1. If you can prove that your philosophy is right for qureies with joined tables, you could implement the feauture just for single table queries.

    2. And Jeff, as a productivity freak, with the focus on figuring out how to attack tasks in the least amount of steps, clicks, or keystrokes. Will you show us how to save time, and hopefully have a little bit of fun in the process? πŸ™‚ I mean really, please help us!

    3. thatjeffsmith Post
      Author

      The grid represents the results of a query, or question youve asked the database. If you want to change the answer, you need to either update the source or change the question itself. It’s designed and meant to be a one way communication channel.

      Just bc some other tool let’s you do it, doesn’t mean it’s something we need to do too.

      Ctrl click to the table, paste in your predicate, and update the actual TABLE.

      1. It makes some sense. Thank you for the explanation.

        It will be perfect then if we had some ‘Go to Data with current filter’ button in ‘Query Result’ window if there’s just one table or view in a query. So there will be no need for select, copy, ctrl-click, paste? It really takes a lot of time.

        How do you think, can such a feauture request be accepted at Sql Dev Exchange?

    1. thatjeffsmith Post
      Author
  5. Rock’n’roll everybody! =)

    I have a little problem with editing data in table in database.
    I want to delete first blank in every record. How can I do it automatically?
    I’m looking for REPLACE command, but it works only in SELECT statement and it makes temporary table. How about table in database?

    1. thatjeffsmith Post
      Author
      1. As I know UPDATE command use “set” for a new value of record. But I have about two hundred different records, so I need two hundred UPDATE command. It’s very uncomfortable.
        Can I edit multiple records using: 1.column which I need to edit, 2.template of a record, that I want to edit and 3.template of a record, on which replaced?
        So it’s like REPLACE command, but without SELECT…

        1. thatjeffsmith Post
          Author

          an update can go against many or all records – it’s the WHERE clause that drives the ‘select’ to find the records to be updated. You’ll need to write a regexp() to find rows that start with a space and then set the value to a substr() or use another regexp() to remove the first blank space

  6. Hi Jeff,

    Just one easy question. When I trying to edit this data directly as you show, do this table is being locked for update? Or it still opened for everyone, so each team member can edit it at the same time?

    Thanks a lot!

    1. thatjeffsmith Post
      Author
      1. Do you mean that just modified rows will appear as locked?
        Then at the same time another mate could easily modify this table?

        1. thatjeffsmith Post
          Author
  7. Thanks for this post. I just started working with Oracle vs MS. This was an easy google search and perfect explination.

    1. thatjeffsmith Post
      Author
      1. yes, perfect title.

        however, blaming the lack of a perfectly reasonable time saving feature on ‘philosophy’ and saying copy + ctrl+click + paste is a reasonable workaround while ignoring the likelihood of having to edit the pasted filter borders on arrogance

        the where clause from the following simplistic select statement is not usable as a table editor filter without removing every table alias and replacing every substitution variable with an actual value…what a PITA workaround

        select * from customer.order_version t where t.order_id = &orderID and t.status_id = &statusID order by t.order_version;

        in case PITA isn’t clear it’s pain in the ###

        1. thatjeffsmith Post
          Author

          Thanks name-withheld

          I would say because you use the results grid in Toad to edit records, doesn’t mean you should in other tools.

          If you want to edit a table, use our table editor.

          At the end of the day, it’s our product and we have to own the decisions we make regarding its use and development. I’d rather be honest and say ‘no’ than give you lip service or pretend it’s coming in a future release.

  8. Ok, thank you for the extra info.
    Regarding the execution, I was not putting a delimiter (I never had to do it before for when trying out queries).
    Regarding the example and the ctrl + space

    yoryi [email protected]

    I just have a blank page, write down yoryi the option is given but ctrl+space does nothing. If I change that to something else, does not work either.

    1. thatjeffsmith Post
      Author
  9. Jeff…
    I will be blunt, and honest. This is not a good resolution.
    Please review your philosophy.
    As a dev, I tell you I need to do a lot of things, fast and easy…
    Having to do all these for getting a blob updated, is simply unnatural, the worksheet SHOULD allow the interactive edition. Set it as an optional on/off if you want, but as a dev I tell you, it is time consuming, and we devs do not have extra time, rather than that we work EXTRA HOURS.
    I’ve been “forced” to turn from TOAD to SQLDEV, and I notice the easy things I could do with Toad… that now I cannot in SQL Dev.
    I do not like that I actually have to paint the specific query I want to run, rather than just having my cursor on it, and running the query… since the full worksheet will run.
    The replace function is not very friendly with keyboards that are not set to US english. CTRL+Quote to change from upper/lower/etc does not work either on different keyboards… You should consider to change that, or allowing the user to setup how they want to trigger certain things. For the replacing text, I do prefer Toad option “space”, simple and clear.
    As you know, usually database allow your user to be a certain time… some servers are a lot more complicated than others… the ability to disconnect and reconnect is disturbing if it closes all your related windows… specially when for some odd reason the connection is “hang” and it won’t compile but won’t reconnect, and the disconnect will freeze/hang/kill/close your window.
    Yes, I am not very happy with the tool, but I believe can be improved, and make it easier for us the devs that work so many hours and need things to be smooth and fast, and not that for simple tasks you need workarounds.
    Regards.

    1. thatjeffsmith Post
      Author

      Blunt and honesty all the way, please!

      >>I do not like that I actually have to paint the specific query I want to run, rather than just having my cursor on it, and running the query
      That’s how it works actually. Put your cursor in the query, hit ctrl+enter.

      >>The replace function is not very friendly with keyboards that are not set to US english
      I’ve not heard this before, can you go into some detail?

      >>CTRL+Quote to change from upper/lower/etc does not work either on different keyboards…
      You can change the keyboard shortcut for this to anything you want, or is it something else that is off here?

      1. I have 4.0.3.16
        If I do not paint the piece of query I want to run, even if my cursor is sitting on that query, the whole worksheet runs.
        On the “SQL Editor Code Templates” added some text to be replaced… let’s say … yor to be replaced by [email protected] … so I write down yor, if I wait I am given the option for replacement properly… if I click on it, will replace… if I press ctrl + space, just does not work.
        Where do you get the option to change the shortcut for the upper/lower/init ?

        1. thatjeffsmith Post
          Author

          Are you using statement delimiters?

          If I have in a worksheet:

          select * from hr.employees;

          select * from scott.emp;

          select * from v$session;

          and put my cursor on the 2nd ‘from’, I only get a grid back of scott’s employees when I hit ‘ctrl+enter’. What exactly are you doing?

        2. thatjeffsmith Post
          Author

          ctrl+space works find for me on sql editor code templates, might be a problem with the code before or after the text to be auto-replaced…give me a complete before/after code sample to test

          all keyboard shortcuts are defined in the preferences

  10. I am using SQL Developer with DB2 tables and this hack does not work. I assume it’s because of the DB2 connection. For example, on the DB2 connection, I don’t see the plus sign next to the connection name that provides a drop down of folder features like I do with Oracle tables. Is there any way to use SQL Developer to do a direct update on DB2 tables or will I need to use another program for that? Thank you!

  11. Thanks for the solution. Using CTL-click works great (although not as nice as directly edit worksheet grid). It would be even better to have the Table editor option listed in the Right-Click menu. Then the edit option would be more visible and not limited to secret Control-Mouse click and my memory it is there.

  12. I’m still using PL/SQL Developer ’cause I don’t have a Mac and I only work on Oracle, and it works the way I think. But since I convinced my company to put most new folks on SQL Developer, I have to answer for it. After I sent out a mass email today, explaining how to join multiple tables and make the results editable (which works in TOAD and PL/SQL Dev), I received some lashing from developers who could not make it work. Just found this page explaining how it was your #1 request, yet your group can’t or won’t implement. Really doesn’t seem like it would be that hard if the result set included the rowid of the row being modified.

    And unless I’m missing something, this workaround only works when viewing a single table. The query results I want to be editable span multiple tables.

    So I had to eat some crow and apologize, and re-write the query as a SELECT to see the original and then an UPDATE statement with duplicate predicates to change it.

    1. thatjeffsmith Post
      Author
      1. Yes. Just assumed that since it worked in the previous five PL/SQL IDEs I’d used since 1995, it would certainly work in one produced by Oracle. Silly me and my blind faith. Was hoping for a helpful reply that would give all of us hope like “Oh, now we see why that might be useful. Yes, we’ve decided to bump this in our list of enhancements for version 4.2”

        1. thatjeffsmith Post
          Author

          #1, we don’t like the feature conceptually, and #2 it would be a ton of work to turn the worksheet grid into a two-way ‘street’ to the data, but that doesn’t matter because of #1.

          The great thing about database tools is that you have hundreds to choose from. Use what works for you.

          There are ways to do this in SQL Dev as well. If you’re going to be doing a lot of data clean-up, then turn your query into a view, and edit the view. That gets hairy as you start joining tables though – another reason we don’t like allowing for this automatically in the worksheet for query results.

  13. I created a table from a view as follows:

    create table schema.newtable
    as select *
    from schema.view;

    I then went into the table object’s data tab to edit some values on a per row basis and found that I could not edit data in some columns, but in most I could. Do you know of any reasons for this to happen. From the above statement the columns in question where created as varchar2(4000 byte) although the actual data in the table is relatively tiny (1-10 characters). All my other varchar2 fields can be edited with successful commits within the grid as well as numbers, dates, etc.

    1. thatjeffsmith Post
      Author

      Found that you could not…were the edit controls disabled, or did you get an error message when you tried to post/commit the changes?

      Also, you created a table from a view – not all views are editable…but the table in general, should be.

      What’s the DDL for schema.newtable? And if you query for ROWID on that table, do you see them?

      1. Ah, yes…what I meant by “found that I could not” is that I literally could not activate a text input box to even type the new value into. What would come up after a double-click is a little gray box with a pencil, but it won’t receive keyboard input and clicking the pencil opens a dialog with essentially the same information and no edit capability. I just found out that if do not double-click and simply type, I can edit, but can only put in 1 character (only for these columns, most other columns give me the white textbox and allow normal unlimited text input).

        This view might have some editable fields, but as you say, once the data is in a table, they should all be editable. When I pull up the view’s object viewer data tab, all fields are editable in this way, which is more or less useless and I regard as not editable. There is probably something I am missing, for one thing I really never use editable views anyway.

        FYI, I can query and see ROWIDs on the table.

        field3, field4, and field5 are some fields in question.

        CREATE
        TABLE “CUSTWORK”.”tableofview”
        (
        “field1” NUMBER NOT NULL ENABLE,
        “field2” NUMBER,
        “field3” VARCHAR2(4000 BYTE),
        “field4” VARCHAR2(4000 BYTE),
        “field5” VARCHAR2(4000 BYTE),
        “field6” NUMBER,
        “field7” NUMBER NOT NULL ENABLE,
        “field8” VARCHAR2(200 BYTE),
        “field9” NUMBER,
        “field10” NUMBER,
        “field11” VARCHAR2(200 BYTE),
        “field12” NUMBER,
        “field13” VARCHAR2(500 BYTE) NOT NULL ENABLE,
        “field14” NUMBER,
        “field15” NUMBER,
        “field16” VARCHAR2(200 BYTE),
        “field17” NUMBER,
        “field18” VARCHAR2(1 BYTE),
        “field19” VARCHAR2(1 BYTE),
        “field20” VARCHAR2(1 BYTE),
        “field21” NUMBER,
        “field22” NUMBER,
        “field23” VARCHAR2(200 BYTE),
        “field24” NUMBER,
        “field25” NUMBER,
        “field26” NUMBER,
        “field27” NUMBER,
        “field28” VARCHAR2(200 BYTE),
        “field29” VARCHAR2(1000 BYTE),
        “field30” VARCHAR2(20 BYTE),
        “field31” NUMBER,
        “field32” NUMBER,
        “field33” NUMBER,
        “field34” DATE,
        “field35” VARCHAR2(20 BYTE),
        “field36” DATE,
        “field37” VARCHAR2(20 BYTE),
        “field38” VARCHAR2(1 BYTE),
        “field39” VARCHAR2(1 BYTE),
        “field40” VARCHAR2(1 BYTE),
        “field41” VARCHAR2(4000 BYTE),
        “field42” NUMBER,
        “field43” NUMBER,
        “field44” NUMBER,
        “field45” NUMBER
        )
        SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
        NOCOMPRESS LOGGING STORAGE
        (
        INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
        FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
        CELL_FLASH_CACHE DEFAULT
        )
        TABLESPACE “DATA” ;

        1. thatjeffsmith Post
          Author
          1. I can’t edit them in single record view either. They are grayed out like in the grid. Field41does the same thing. What these fields have in common is that they have are the same datatype and size and that they are all essentially list aggregations (LISTAGG) results in the original view. I don’t know if something is happening in the table spec from using the CREATE TABLE AS… that is not showing up in the DDL export…? Not the end of the world. In fact, I don’t really need to do it at all, but I was curious enough to look it up, and mentioning it to you upon finding this page.

          2. I also see this “some columns uneditable” behavior, but on regular tables! Did anyone figure out what is causing this?

          3. thatjeffsmith Post
            Author
          4. Hmmm, I don’t know what’s causing it. A given table will be fine (all columns editable), and then some of the columns will exhibit this behavior. I can’t see any pattern, or see any difference between the columns that are editable and those that aren’t. I’m on version 4.0.2.15, build 15.21, by the way (on Windows 7). When I remake the table (using create new_table as select * from broken_table), the new one is fine. I’ll keep track of what I do and see if I can correlate what I do with when it happens. I saw someone mentioning a ROWSCAN bug, would that have anything to do with this?

          5. thatjeffsmith Post
            Author

            you can disable the ROWSCN for an update, tools – preferences – database – object viewer – use ORA_ROWSCN…try turning that off I guess

  14. Editing query results is #1 request?
    It takes a pretty arrogant person to believe he is right and that many very experienced developers are wrong.
    We have that functionality in the “other” product… which I’ve been using for 15 years. Your “alternative workflow” is a pretty poor hack, and does nothing but continue to make me yearn for the “alternative product”. For now I’m stuck with yours for the duration of this account assignment.

    1. thatjeffsmith Post
      Author

      It’s not that you’re wrong. And I used that other product for 12 years, and did like that feature. But SQL Developer is not that ‘other’ product. I appreciate your feedback. Who knows what the future will bring?

  15. Thanks for opening my eyes that an SQL worksheet tab can indeed be viewed as a full-fledged file editor with “Open” and “Save As…” commands. I only perceived it as kind of a powerful command line interface before. πŸ™‚
    I would probably have caught that earlier if the “Save As…” option of a non-saved SQL worksheet weren’t greyed out. (Instead, the “Save” option is active – but without elipsis “…” which made me reluctant to initiate anything unknown – I see, there are certain look-and-feel differences between MS Windows and SQL Developer.)
    My apologies for these digressions off topic.

    1. thatjeffsmith Post
      Author

      Don’t apologize – if something doesn’t look right, ALWAYS blame the software πŸ™‚ And every time someone asks me a question, I learn something too. So keep the feedback coming!

  16. Hi Jeff, your work-around does only the half of the job (filtering rows). What is your advice how to “filter” columns of the table grid in order to disburden us from scrolling horizontally across tens of columns again and again?

    1. thatjeffsmith Post
      Author

      I would reorder the columns such that the ones you edit frequently are displayed first for minimal scrolling. The column order positions are persistent across sessions, so you should only have to do this once.

      1. Thanks, Jeff.
        But I’m afraid I don’t have permission to do so as an end-user…
        What about creating a view with fewer columns? (None has been defined at all so far in the database I’m working with.) Are view grid cells editable?

        1. thatjeffsmith Post
          Author

          Ha! No, I mean you reorder the columns in SQL Developer only – NOT the database. Yeah, don’t rebuild the table. Just order the columns as you want in SQL Developer using the right-click Columns dialog. This will only change how the data is displayed for you.

          1. It works! Many thanks for helping me save time and reduce my risk of mouse-induced epicondylitis πŸ˜‰ .
            But now I’m curious to know on which layer such preference is being stored:
            (1) Somewhere on the client’s workstation? (I couldn’t find any trace left under “%AppData%\SQL Developer”.)
            (2) At some some place on the server specific to the database user account? (What then if this account is logged in twice?)
            (3) On the database layer? (I don’t think so.)
            BTW this uncertainty is typical to SQL Developer. The user is frequently facing the vacuous “Save files?” dialog box.
            – Which files?
            – Saving where?
            – What or who will be affected?
            Official documentation and system messages seem to be addressed to ora-freaks or ora-nerds only…
            Once again many thanks to you (and other helpful persons out there) for making up for such defects
            πŸ™‚ .

          2. thatjeffsmith Post
            Author

            1) Yes – generally always under %AppData%, although make sure you’re looking under the Roaming subdirectory.
            2) No, and typically never
            3) See answer 2

            It’s a client tool, anything you save application specific, is going to go on the machine where SQL Developer is running.

            Tell me more about the ambiguous file save dialogs, can you give me an example?

          3. Apologies for my superficial investigation :$
            Now I found the *GridSettings.xml files under “%AppData%\SQL Developer\system3.2.20.09.87\o.sqldeveloper.11.2.0.9.87”.
            (Yes, the last part of the %AppData% variable is “Roaming”.)

            Usually, I have a worksheet open with the same name as the connection, XYZ say. When I close the worksheet I’m asked: “XYZ has been modified. Save changes?” As I’m scared of modifying anything on the database *unintentionally* I click on “No” whenever I didn’t want to modify anything on the server. (You see I’m not a freak…) Novices would find dialog boxes and helptexts helpful which are slightly less ambiguous – given the default naming ambiguity. Two additional words would be sufficient: “Worksheet ‘XYZ’ has been modified. Save changes locally?”
            Did I understand you correctly: In SQL Developer, “Save” *always* refers to storage under immediate client control – as opposed to “Commit”?

          4. thatjeffsmith Post
            Author

            I just noticed that pending table changes come back with ‘Save file’ message dialog title, although the message itself says you have a change to a table pending. I’ll log that as a bug.

            Closing a file editor and being asked if you want to save your file, I don’t see how that’s ambiguous though.

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *