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

thatjeffsmith
Author

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

86 Comments

  1. Marc Welling Reply

    I subscribe that without the possibility to modify the results of a query directly in the data grip this tool is NOT used by professionals. The alternative operation is too slow and it is often not possible to use it.
    Really a unfortunate because the rest worked great.

  2. Hi Jeff,

    I too am struggling with the lack of this feature. I really want to buy into your stance and I am open minded but can you advise how your philosophy translates to when the initial selection criteria is based on more than one table? It seems my only road in from here is to then use an identifier, where the maximum number in the expression is 1000, to do the table updates. What If I need to update thousands? Can you advise your stance on this? Am I missing something obvious here?

    Thanks,

    • thatjeffsmith

      I’m not clear on what you’re trying to do – are you trying to hand-update 1,000 records?

    • Hi,

      I am ideally trying to select data based on a query that pulls from two or more tables and then update data based on this. Your example above shows how to adapt a single table query into an editable view in a table. But if the initial query is based on criteria from multiple tables then how do I get to an editable view of this selection only?

      The only workaround I see so far is to use the data from the query and then use an identifier (NAME in your example) to select these rows in the single table I am looking to update (hence the reference to 1000 records).

    • thatjeffsmith

      If you’re doing this a LOT, i would look at creating a view and edit the data from there

  3. It’s ok Jeff πŸ™‚ I’m an adult and have a 14yo child. I’ve heard plenty of cursing over the last few years. Although personally, I’ve never found the need.

    Scoot, just because YOU aren’t a professional doesn’t mean that other people aren’t. Why do you feel like this GUI is only used by students? For that matter why do you feel like students should be given a tool that lets them do things that no professional would ever do? Does this help them get hired later? Don’t you feel like it’s better for students to learn to act like people who know what they are doing so that when they have a job, they not only know what to expect but don’t make potentially tragic mistakes?

    I mean I get it. My kids WANT to do all kinds of things. Now I don’t always let them because what they want while they are learning isn’t always what is smart. But in case you were curious the word “student” sounds a lot like “study” for a reason. It means you don’t know everything, not even when what you WANT isn’t all that good an idea. That’s why students have teachers. That’s why professionals do their best to help students learn. And that includes following best practices.

  4. Thanks for the info Jeff – very helpful.

    I thought I’d remember doing this before, but couldn’t figure out for the life of me how (since as most other replies here mention that similar tools allow one to just edit the results of the query).

    I was just about to ask if there was a place we could go to request features, since I think this would be near the top, if not the #1 feature.
    I see this request has been rejected though.
    Maybe if enough users complain Oracle will reconsider.
    Here is the link for the rejected request if any of your blog readers wish to add their voice: https://apex.oracle.com/pls/apex/f?p=43135:7:10582227384542::NO:::

    I currently resort to using Powerbuilder 7.0 (circa 2001) just to edit query results (since it’s so much simpler).
    While I sympathize with Oracle’s philosophy to keep this functionality separate, I think the vast majority of SQL Developer users are more aligned with the Python philosophy: “practicality beats purity”.

    • Are you referring to entering the where clause in the 1 line “filter” line?

      …well, maybe if it had many more than 1 line so I could more easily see larger queries…and if it allowed entering the entire select, so I could choose the columns I cared about (instead of having to manually find them in the table, and drag each one across several pages)…and if it allowed me to just type (or copy/paste) sort criteria instead of having to use a GUI sort builder…

      Oh, wait – I’ve just described the SQL Worksheet (sans the update part that many are begging for).

      I certainly can’t speak for other users, but I do a fair number of ad-hoc queries during the day. The worksheet is really convenient for that. Less often (but still fairly frequently), I’ll need to do some updates to a table. If there are a lot of records, and the update is the same for every row – I’ll just write the update in SQL. If the update is just for a few records, or different between rows, it is super convenient to be able to just execute the SQL, edit the data I’ve selected, and save it. While the functionality to update a table like this is technically there, the interface vs. the ability to just edit ad hoc query results directly is just so much more awkward.

      I really like SQL Developer – it is an awesome tool with a ton of useful features. This is the one killer feature missing though.

    • thatjeffsmith

      You can hide columns. And you can paste your where clause that you wrote in a worksheet, into the filter area.

  5. Antonis Glytsos Reply

    Although editing in place would be desirable, I am fine with the available functionality.
    Just a question. Is there any way to force the DATA tab to not automatically query the table. On tables with LOB columns, querying the table can get quite painfull.

    • thatjeffsmith

      Isn’t clicking the data tab, the same as querying it though? If you’re in a code editor, hit SHIFT+F4 on a table, you’ll get a popup dialog with the same tabs – but the data one is not there.

  6. 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??

    • thatjeffsmith

      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.

  7. It’s sad that SQL Developer doesn’t support this feature like Toad or etc.
    But thanks, your idea helped me alot.

  8. 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

    • thatjeffsmith

      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.

    • HeShouldReallyGoFuckHimself

      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.

    • thatjeffsmith

      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.

    • Although I don’t agree with the tone and the language of Mr. F*#k You, I do more or less agree with his idea. The comparison to the hamburger works. As long as you’re served a proper burger, you may always elect to remove the meat, if you so please, with no ill effect.

      The mitre box analogy does not work. Backed up data and insert scripts can remedy errors that may arise from improper use of direct editing. It would be foolish to remove the safety mechanism from a Mitre blade because we don’t have “backup fingers,” nor do we have “regrow tools” built into our DNA.

      Simply put, the lack of direct edit is a result of what looks like a bit of arrogance and a falling in love with the process rather than the product. It’s never a great sign when the business tries to tell the consumer what they want.

    • Peter Nosko

      If you worked for me, you’d be looking for a new job. With a negative recommendation.

  9. Mike.of.NZ Reply

    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.

    • thatjeffsmith

      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.

  10. 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

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

    • 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!

    • thatjeffsmith

      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.

    • 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?

  11. 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?

    • 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…

    • thatjeffsmith

      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

  12. 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!

  13. Thanks for this post. I just started working with Oracle vs MS. This was an easy google search and perfect explination.

    • name withheld

      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 ###

    • thatjeffsmith

      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.

  14. 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.

  15. 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.

    • thatjeffsmith

      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?

    • 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 ?

    • thatjeffsmith

      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?

    • thatjeffsmith

      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

  16. 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!

  17. John Bailey Reply

    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.

  18. Bill Coulam Reply

    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.

    • thatjeffsmith

      So you sent out a mass email on how to do something in a product you never tried for yourself?

    • Bill Coulam

      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”

    • thatjeffsmith

      #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.

  19. 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.

    • thatjeffsmith

      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?

    • 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” ;

    • thatjeffsmith

      right click in the row and try the Single Record View?

      I’m at a loss as to what would cause that behavior though.

    • 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.

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

    • 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?

    • thatjeffsmith

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

  20. 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.

    • thatjeffsmith

      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?

  21. 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.

    • thatjeffsmith

      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!

  22. 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?

    • thatjeffsmith

      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.

    • 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?

    • thatjeffsmith

      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.

    • 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
      πŸ™‚ .

    • thatjeffsmith

      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?

    • 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”?

    • thatjeffsmith

      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.

  23. Quoting the Rolling Stones in an SQL Developer blog. You are sooooo cool πŸ˜‰

Write A Comment