70 responses

  1. chet
    December 27, 2011

    so how do you make a tab character actually be 2 spaces?

    • JeffS
      JeffS
      December 27, 2011

      Suck the gremlins out of your machine? That should be the default behavior.

  2. Andrea
    December 28, 2011

    I guess it could be this:

    Preferences->Sql Formatter->Oracle Formatting
    Choose the profile you are working on -> Edit
    Indentation->Spaces

  3. Dan McGhan
    March 7, 2012

    Hi Jeff,

    I know you can set the default location for scripts at:
    Tools > Preferences > Database > Worksheet

    But what about a default for File Open (say when you first open SQL Dev)? Is there a default for that? If not, how can we control the options on the left of the File Open dialog?

    Regards,
    Dan

    • JeffS
      JeffS
      March 7, 2012

      It’s on our to-do list Dan. I can’t tell you if and when it will happen, but I can say that I agree with you, it’s a good idea!

      There is request very similar to this on our Developer Exchange, please go vote for it! I know it says it’s targeted for v3.1, but it did not make it this time around.

  4. Neel
    March 9, 2012

    We would like to capture inofmation like who, when and why a user connected to a database in production using SQL developer, so i was wondering if is it possible to customize the “Connect” option to prompt the user to enter the “Why” information at the instance when he/she connects to a database? I know “When” and “Who” can be captured behins the scenes via monitoring but we are also intersted to see if there is a capability to put additional information such as “Why” within the connect option in the SD interface.

    Tx

    • JeffS
      JeffS
      March 14, 2012

      Thanks for such a great question! It’s inspired me to write today’s post!

    • Jim McCusker
      November 12, 2013

      Jeff,

      Has the ability to set the default directory for saving SQL Scripts been added to SQL Developer? If so, what version was this implemented in and where is the setting located?

      –Jim

      • thatjeffsmith
        thatjeffsmith
        November 13, 2013

        Not yet.

      • Jim McCusker
        November 13, 2013

        Darn… I know it’s a simple feature but it’s really annoying not having a default load/save folder.

  5. Mercedes
    July 23, 2012

    Jeff, I have a couple of issues. I need to take an SQL certification course so I decided to do the following:
    1. Download (to my personal PC) the free SQL Developer 3.1 (SD), the JTDS file 1.2, the free download of Oracle Express 11G.
    2. Initially all seemed fine, but I could not enter and save all the recommended default values for create a new (hr) database(DB) connection. So in my smart mind I thought maybe I need Oracle loaded to my machine. Although all documentation I read did not specifically say I did need it.
    3. Downloaded 11g Express and it had it’s own set of problems (I’ve captured the problem in a screen image.
    4. So now I am still no closer to running SQL queries so I can finish up my developer exam so I can get certified and I am running out of time.
    Oh That Jeff, can you help ? I have read many documentation areas and brought it up to the SD developer forum who are really great by the way. My real consternation is do I need Oracle in order to add new connection values for the DB called HR ? And the forum user are familiar but I have received basically the answers I’ve found from reading and the recommended default values are not giving me the correct results. Which is just be able to create a new hr db connection with the recommended default values. Plus the Oracle 11g appeared to have an issue ……Help plz ??????????????? M

    • thatjeffsmith
      thatjeffsmith
      July 23, 2012

      Oracle Express 11G will have everything you need to run queries. You say you have a screen image showing problems with your install? Where is that?

      Once you get it running on your machine you can connect via SQL*Plus or SQL Developer.

  6. Chris Gould
    August 13, 2012

    In the PL/SQL debugger, is it possible to list the member procedures/functions of a package in alphabetical order to make it easier to find the one you want to run? I can’t find an option for that anywhere.

    • thatjeffsmith
      thatjeffsmith
      August 13, 2012

      Not today Chris, but I just added it to ‘the list,’ so stay tuned!

      For now you can use the kb to navigate the Target list. If my package has 10 procedures, I can type through the list.

      • James
        February 12, 2014

        In version 4 you can right-click on the package name and there is an ‘Order Memebers By’ option.

  7. Yury
    January 19, 2013

    Great stuff. Thanks for educating us. Lovin the Preferences feature.
    Few comments from my side:
    -a- It wasn’t too intuitive for me personally that if you change some option and press “Esc” button the changes are not saved (especially if you do multiple changes). I wonder if the behavior is the same in chrome or other leading GUI. I would at least introduce a warning stating that if I would like to save changes I should press Enter instead of Escape at the time I press Esc button
    -b- Preferences auto search functionality is great (reminds Chrome preferences page :). However I would include individual preferences names in the search scope . e.g. tro change SQL Worksheet font you type “Font”. However to change “SQL History Limit” a user should type “Worksheet” or “SQL Array Size” => “Advanced”. IMHO: it a bit confusing and makes the preferences search a bit more difficult. If I would be able to change “SQL Array Size” typing “SQL Array” or “Array Size” it would be even more coooler :)

    Let me know if the enhancement requests already exists for the 2 suggestions above or should I file a new once. If those exists I am happy to add my vote there :)

    Keep up the good job you are doing man.

  8. Ragavendran
    March 27, 2013

    How to view the single row size in oracle using sql developer

    • thatjeffsmith
      thatjeffsmith
      March 27, 2013

      Do you mean, how can you determine how much data is stored in a given row?

  9. Carlos Pinho
    March 28, 2013

    Hi Jeff!
    Imagine that i have configured all my preferences in my SQL developer, and now i want to put the same preferences in my co-workers laptops. Is there any way to export my preferences and load it in their’s SQL developer? Or i really need to remake all the steps that i have made in my SQL Developer?

    Thanks in advance!

    • thatjeffsmith
      thatjeffsmith
      March 28, 2013

      I would copy the preferences.XML file over to your co-worker’s machines. Just make sure your copy of SQLDev is closed when you copy the file and their copies of SQLDev are closed when you paste.

  10. sathishkumar
    April 20, 2013

    Wow this post really helped to improve sql dev
    Thanks

  11. Avinash
    May 20, 2013

    Jeff,

    How do i control look and feel for the results window?

    Thanks
    Avinaash

    • thatjeffsmith
      thatjeffsmith
      May 21, 2013

      You have 3 things for results windows and grids in general that you can tweak:

      1. Font Face
      2. Font Size
      3. The Checkerboard/Zebra affect

      The checkerboard preference is on the Database > Worksheet page in Tools > Preferences. The font settings are in Code Editor > Fonts, also in preferences.

  12. Don
    June 5, 2013

    I am running sql Developer version 3.0.04. When I click on a Heading for a column in the data of a table the filter window pops up. The number of items in listed in the filter window is limited. I (think that I) would like to have all available entries shown so that I don’t have to “know” what I want to use as a filter.

    Is there a preference option that I can play with to change the number of items displayed in the filter window?

    • thatjeffsmith
      thatjeffsmith
      June 5, 2013

      We don’t know what all the distinct values are, because all of the data hasn’t been fetched yet. In the grid, do a ctrl-PgDn. Then do the column value filter dialog. Beware, if there’s a million unique values on that field, then don’t complain that your computer bogs down when you do this – you get what you ask for :)

      Also, any chance you can upgrade to version 3.2.20.09? Version 3.0 is way way way out of date.

      • Matthew
        August 5, 2013

        When I click on a column heading I am not given that option anymore (my old computer would show it). Any idea how to enable this feature? I have scoured the internet high and low :(

      • thatjeffsmith
        thatjeffsmith
        August 12, 2013

        Email me with your details, there’s too many comments on this post to keep my head on straight :)

        jeff.d.smith@oracle.com

  13. Kim Berg Hansen
    June 11, 2013

    A related question:

    How to make backspace only remove one single space at a time instead of deleting back to nearest “tab stop” (as per the indentation setting) ?

    If I have my indentation set for 3 and I type three spaces at the beginning of the line, one press on backspace removes all 3 spaces. How can I avoid that? I keep on pressing backspace too often and end up deleting a character or two at the end of the line above…

    I would like backspace to only delete ONE space at a time, but the only way I have found to make it so, is to set my indentation size at 1 – which I also dislike ;-)

    Thanks for any help ;-)

    • thatjeffsmith
      thatjeffsmith
      June 11, 2013

      I don’t think you can Kim. I’ve had set to ‘5’ for years, and I’ve never noticed this behavior before, but it is there. Apparently I don’t type enough :)

      The backspace does indeed only delete 1 character at a time IF the editor sees a ‘word’ vs just the tab stop.

      I guess your recourse is to slow down your keyboard or use the UNDO/ctrl+Z to get your characters back after a delete frenzy?

      • Kim Berg Hansen
        June 11, 2013

        It’s more a matter of typing habits. Often I get to right position I want to type in by pressing TAB-TAB-TAB-BACKSPACE rather than TAB-TAB-SPACE-SPACE-SPACE. That’s what my fingers are used to after 12 years of Toad, so it’s just an annoying (to me) little thing that keeps me from switching to SQL Developer ;-)

        Oh, well, worth a shot trying to ask you. Thanks for answering. See you in New Orleans :-D

      • thatjeffsmith
        thatjeffsmith
        June 11, 2013

        After 10 years of using another IDE, I had many quirks to unlearn. It is possible :)

        No need to switch tools, use what works for you!

        That being said, this doesn’t mean we can’t file an ER. So what ideally would the new behavior exhibit when using BACKSPACE? I imagine many users would be upset having to hit the key many times to undo whitespace that only required a single key to create in the first place?

      • Kim Berg Hansen
        June 11, 2013

        Sure – users who are used to the SQL Developer way would be upset with my way – I understand. There are probably about as many opinions about “the right way” than there are programmers (or more…)

        I think the best probably would be a preference setting allowing the user to choose between “Backspace to tab-stop” or “Backspace single space”.

        Not a critical ER – just when convenient ;-)

  14. Kim Berg Hansen
    June 11, 2013

    PS. I actually would have guessed that the Formatting option “Use Tabulator” would have controlled it.
    So that if it was set to “Use Tabulator”, backspace would of course delete the “TAB”, but if “Use Tabulator” was unchecked so indenting is done with spaces, then I would have expected backspace also to “use spaces.”

    Could that be an enhancement request? Pretty please? ;-)

  15. Dan Bunch
    August 9, 2013

    Is there a setting to show you how many characters your identifiers are? Oracle is set to 30 characters. It would be nice if there was a way to have it highlight the background of identifiers for the 30 characters so you could see where you are in the amount of characters you have left(or how many you have over 30).

    • thatjeffsmith
      thatjeffsmith
      August 12, 2013

      No…but that sounds kinda cool!

  16. Sudhir
    September 15, 2013

    Jeff,
    SQLDeveloper is really good at many things. One thing I love most is editors regular expression! Its really great and has saved me tons of time.
    One question: when I am pulling down 30k rows from sql and exporting it to say excel format almost every time (depending upon rowlen) it tanks somewhere in between and then proceeds at a crawl pace. Same download on toad/plsqldeveloper flies. What is going on? Are you buffering data in memory? or is it being written to file in array size (any way if just showing or downloading)?

    the latest sqldeveloper 4.0.0.12.84 has sqlcli.bat without invocation to sqlcli at the end!!

    one feature by default caused me grief is nls_sort and other settings – they overwrote some other than database had got in a whole lot of trouble

    all above from 3.x version – just downloaded 4.x version

    It is much slick look and I want to use sqlcli very much –

    Thanks for hard work, its great!!

    Sudhir

    • thatjeffsmith
      thatjeffsmith
      September 15, 2013

      Don’t use the XLS format, use the XLSX format. It will run fast and is robust to handle larger 1,000,000 row-type resultsets. The XLS format is an older format that the library reads into memory and doesn’t release…the XLSX format in v4 does not have this critical limitation.

      The NLS settings are for your client application, in this case SQL Developer. Check your registry, you’ll see for your Oracle client installs (to run the other applications you mentioned) will also have NLS settings. They don’t ‘overwrite’ the database settings, they dictate the settings for your client session.

      You want to run sdcli.exe – I’m not sure what you mean about sqlci.bat? Here’s a quick write-up on the command-line interface stuff.

  17. Uday
    October 24, 2013

    Is there a shortcut in the Worksheet to move from SQL to SQL … either to the beginning or to the end of the SQL? Will be very handy. Also, jump from beginning to ‘from’ clause to ‘where’ clause to ‘group by’ etc etc

    I tried setting shortcut on ‘Other->Next’, but it did not do anything. What is Next/Previous for anyways?

  18. Milind
    March 10, 2014

    Priority: Production issue
    while running query it is showing a result, but after updating table and rerun the query it showing the same result form sql developer.
    But at sql*plus in server showing the result which we want.
    But we want report using sql developer, which showing old result.
    How we can overcome this problem.

    • thatjeffsmith
      thatjeffsmith
      March 10, 2014

      My first guess is you are updating the table from SQL*Plus and not committing the change, so it’s not viewable outside that session.

  19. chartman
    March 11, 2014

    I am trying to set my preferences and I make the update to the preferences then press ok but when I open sql developer again the preferences didn’t save.

    What am I doing wrong?

    Thanks Cshartman

    • thatjeffsmith
      thatjeffsmith
      March 11, 2014

      It sounds like you don’t have write permissions to the Application Data directory on your machine – assuming you’re on windows.

  20. James B
    April 3, 2014

    I’m using Oracle SQL Developer 3.0.04 here at work. I connect to our database and I see a directory structure type list of all the usual things… Tables, Views, Indexes, etc. I expected to see a list of the tables under Tables when I clicked on it (well, on the plus in a square icon). Instead, the little plus-in-a-square icon goes away and no table names are forthcoming. It is as if there were no tables. There are definitely tables … I can do Selects on them to my heart’s content. I searched online and saw that others have had a similar problem. Is there a jre compatability issues? One website suggested adding this to the sqldeveloper.conf file: AddVMOption -Doracle.net.disableOob=true

    Or even adding this if running from the cmd line: -J-Doracle.net.disableOob=true

    Neither of these work. Any ideas?

    —miserable in dallas, texas

    • thatjeffsmith
      thatjeffsmith
      April 5, 2014

      It sounds like this is happening to you.

      Also, that version is way too old. Tell you work folks it’s time for a FREE and 5 MINUTE update :)

  21. royce
    May 28, 2014

    We have a 5000 lines of PLSQL codes. Don’t ask me why it’s that long. Whenever I do code format (Ctrl+F7), the IDE cursor runs to the bottom of the code making me lose my focus codes I am currently editing. Why is it like that?

  22. Brad Martin
    June 5, 2014

    Jeff, I am running Oracle SQL Developer 4.0.1.14. When I debug a package it opens a new tab of the package body. This tab seems to have a previous version of the package bodies code (It does not have my changes). I guess I have a few questions
    1) Can I stop this behavior? I really hate having tabs open, I just want to have the 1 tab that I am working in.
    2) If I must have a new tab why am I seeing previous versions of the package?
    Thanks in advance for any help.

    • thatjeffsmith
      thatjeffsmith
      June 6, 2014

      Yeah, I see that. Looking into it now.

      • Brad Martin
        June 6, 2014

        Thanks Jeff, I look forward to a reply.

      • thatjeffsmith
        thatjeffsmith
        June 6, 2014

        Surprise! It’s a bug :(

      • Brad Martin
        June 6, 2014

        WOW, I found a bug for Oracle. I usually just make bugs since I’m a developer! So I have rolled back to version 3.2.20.09. Any idea when / if there would be a new release. Any way I could be notified of a new release. My team at Hertz uses SQL Developer, we try to stay on a newish version. Thanks so much for looking into this issue. Is this a bug in all of version 4? I’ll advise my team to not use this version.

      • thatjeffsmith
        thatjeffsmith
        June 6, 2014

        Yeah, in all of 4 I think.

        Not to trivialize the bug, but this seems more on the minor side, definitely not hard to work around. In most dev shoppes I find less than 25% of the devs even use the debugger.

        And even if you do, simply close the package when you’re done debugging.

        To answer your question, we MAY have another patch for v4.0. Either way, we’ll be sure to get this fixed for the next drop.

      • flindeberg
        July 1, 2014

        Any updates on the bug? It is really irritating, using 4.x for daily work, but I must revert to 3.x for debugging. Breakpoints are being changed as well when the file is modified.

      • thatjeffsmith
        thatjeffsmith
        July 1, 2014

        It’s fixed in our internal builds – it will be included when we ship a new release/patch.

  23. Roger Rabbit
    June 5, 2014

    This is so funny…. I found the article and started to search all the options in my Toad for Oracle.
    I didn’t realize this article was for SQL Developer, since I had it saved in the bookmarks.
    (Not) Surprisingly most of this options are in Toad as well. :)

    • thatjeffsmith
      thatjeffsmith
      June 6, 2014

      The tools are different, but the users, and what they want/need are generally the same. How we deliver that is where we try to shine.

      Sorry I ‘tricked’ you ;)

  24. Brad Simmmons
    July 3, 2014

    Great ideas and tips here!

    Not sure where else to ask, but how do I get SD to display non-printable (‘whitespace’) characters in the data grids? (Just like the sql editor option for displaying whitespace.) I work with a lot of dirty file imports and being able to visually id non-printables would be really helpful. Even a would help!

  25. Jerome
    July 17, 2014

    Hi Jeff,
    I have some DEV users working on PL/SQL packages under SQLDevelopper.
    My problem is that we are installing packages under SQL*plus for automation of install, and that SQL Developper is more tolerant than SQL*plus, especially for “/” after en of Create Function/package/procedure.

    Is there a way that SQLDevelopper script launching can be compatible with SQL*Plus?

    Thanks in advance,

    • thatjeffsmith
      thatjeffsmith
      July 20, 2014

      Jerome, I’m not sure what you’re asking for exactly. So, can you give me a bit more detail on what you mean by ‘compatibility?’

      • Jerome
        July 21, 2014

        Yes, surely. Thanks for your answer.

        We have some procedures installed on different environmment using SQL*Plus tools.

        The dev team is developping those procedures under SQLDevelopper, but never testing under SQL*Plus.

        The problem is for PLSQL, if under SQL PLus you don’t put a ‘/’ the package/procedure/function/PLSQL piece of code is still launched and executed.

        This is not working under SQL*Plus.

        set serveroutput on
        BEGIN
        DBMS_OUTPUT.PUT_LINE(‘test’);
        END;

        bloc anonyme terminé –> in french sorry…
        test -> has worked.

        Where as under SQL*Plus
        SQL>
        set serveroutput on
        BEGIN
        DBMS_OUTPUT.PUT_LINE(‘test’);
        END;SQL> SQL> 2 3
        4
        5

        This is ignoring all the code until there is a ‘/’…

        My proposition would be that there could be some optional checkbox like ‘SQL*Plus buffer management’ allowing the SQLDevs tools to parse like SQL*Plus does, waiting for ‘/’ else nothing…

        Thanks for your reading,

      • thatjeffsmith
        thatjeffsmith
        July 21, 2014

        Anything that’s going to be ran through SQL*Plus into production, needs to be tested through SQL*Plus – that doesn’t mean you can’t develop it in SQL Developer though. And SQL Developer works just fine with the slash terminators.

  26. Rufat
    July 23, 2014

    how i customize my local oracle sql developer table columns when i click on column name it shows textbox?

    • thatjeffsmith
      thatjeffsmith
      July 23, 2014

      I’m sorry Rufat, I’m not sure what you mean by customizing table columns?

  27. Al G.
    July 24, 2014

    Jeff, is there a way to change the font in which the query results are displayed (i.e., NOT the font of the code editor)? I’m not seeing that option in Preferences, but then I’m very new to this product.

    • thatjeffsmith
      thatjeffsmith
      July 24, 2014

      The font preference controls both the editor and the grids. We’ve considered splitting that out to two preferences, but I’m wondering why someone would choose different ones…

      • Al G.
        July 24, 2014

        Well, there are always weirdos like me who could (and would) use this feature if it were made available :) Thanks for the response.

      • thatjeffsmith
        thatjeffsmith
        July 24, 2014

        Just to be sure – you know you can change the grid font now? It just also changes the code fonts…is there a font that you would like for data that would be drastically different than for the editor?

        Part of keeping it to one setting is keeping the number of preferences down, to keep things simple, a la KISS – but I don’t want to restrain folks unnecessarily either.

        Also, you’re not a weirdo :)

  28. kartvyasinh
    July 28, 2014

    when we are closing windows the last stored table values are not seen when we again log in in sql

    • thatjeffsmith
      thatjeffsmith
      July 28, 2014

      Are you committing your pending changes?

  29. Jose
    September 3, 2014

    Why didn’t sql developer don’t save records and running long time with out showing any errors. I was trying to add a new record from data view

    • thatjeffsmith
      thatjeffsmith
      September 3, 2014

      Save records and running long time w/o showing any errors? I’m not sure what this means. Can you provide an example of what you’re doing/seeing?

Leave a Reply

 

 

 

Back to top
mobile desktop