Mostly Making oracle easy, mostly:
Search for:

Ask A Question

Nearly 7,000,000 Oracle professionals use SQL Developer on a regular basis. Have a question about Oracle SQL Developer? Searched this blog and couldn’t find the answer? Ask away!

If your question is about Oracle Database, SQL, PL/SQL, etc – go Ask Tom!

Feel free to ask anything you want, but I’ll feel free to send you to Support or our Forums if it goes sideways.

Note: This page has turned out way more successful(?) than I would have ever imagined. Please keep these things in mind when asking questions.

  1. I am NOT support. Don’t expect me to log bugs for you, or give you official timelines on bug fixes, enhancements, or product releases.
  2. I AM NOT SUPPORT. Don’t open an SR with My Oracle Support AND leave a question here. Pick one and go with it, and when in doubt, go to My Oracle Support.
  3. I try to answer questions as quickly as possible. If you don’t get an answer, ask me for an update. I may have just forgotten or overlooked your request.

Go!

7,791 Comments

  1. Kevin Yin Reply

    Hi Jeff,
    Please bear with my newbie question,
    I just installed sqlcl for mobaXterm on my win7 _x64 and it appears to not like queries being pasted with line-breaks. What am I doing wrong?
    I don’t have this issue with sqlplus and thank you in advance.

    example:
    system@OFDEV1> select sysdate
    from dual;

    Error starting at line : 1 in command –
    select sysdate
    Error at Command Line : 1 Column : 15
    Error report –
    SQL Error: ORA-00923: FROM keyword not found where expected
    00923. 00000 – “FROM keyword not found where expected”
    *Cause:
    *Action:
    Elapsed: 00:00:00.228

    Error starting at line : 1 in command –
    from dual
    Error report –
    Unknown Command

  3. Mike R. Reply

    Hi, Jeff

    Is it possible to configure preferences so schema identifier is shown along with the name when i open procedure or package code.
    I know it is possible for tables. But for PL/SQL objects i did not find it.

    Thnks.

    Mike R.

  4. julesdba Reply

    Modeler & Roles – Modeler has numerous database properties that can be added & edited. I added some roles, but don’t see a way to generate a script from Modeler?

    Thanks!

    • julesdba

      Yes, database roles.

      The ones that can be setup under:

      -> Physical Models
      -> Oracle Database 12c
      -> Roles

  5. Bernhard Pietsch Reply

    I run into a somewhat unexpected behaviour with SQLcl when using “sqlformat delimited”. I’m trying to generate CSV output importable into Excel and I need to escape certain columns with =”xyz” instead of just plain “xzy” for that.

    set sqlformat delimited ; =” ”

    does the trick very well except for the first column of the result set.

    select to_char(1), to_char(2) from dual;

    returns

    =””1″;=”2″

    instead of

    =”1″;=”2″

    as I would have expected. Is there any reason why SQLcl does treat the first column differently here?

  6. Alex Michael Reply

    It was great seeing you at the last UTOUG Fall Symposium! Hope the rest of your Spartan races for the year went well! Aroo!

    I know the functionality exists to export a single query result to a MS Excel worksheet (with option to name the worksheet) as well. I also understand the functionality exists to export data from multiple objects (tables/views/materialized views) data to separate worksheets in a single workbook file.

    But is there a way to export multiple custom query results to a single MS Excel workbook with each query result in it’s own worksheet?

    This would be very useful (extra credit for providing some metadata in the query comments to override/name the worksheet tabs).

    Thanks so much!
    Alex

    • thatjeffsmith Post Author

      No way to do that outside of copying and pasting, sorry.

      I’m signed up for a super and sprint in WV for August. My foot is bothering me, hopefully I can get ready!

  7. TR Rudkin Reply

    Been exploring sqlcl as and alternative to Sql*Plus for some of the users. I am having two issues. First is returning a resultset of about 2000 rows about 2 minutes in sqlcl and 5 second in sqldeveloper , using F5 run script.

    To test if changing sqlformat would change the speed I discovered an issue that really concerns me. When I use set sqlformat default I properly receive an error I knew is in the view. When I use set sqlformat ansiconsole I retrieve a portion of the rows but not error.

    I was testing various sqlformats and json fails to show error but csv does.

    Is this a bug or a feature?

    • TR Rudkin

      It could be any error in the select but in this case it is an ORA-01427, I can be reproduced with :
      SELECT o.*,
      ( SELECT created FROM all_objects A WHERE A.object_name = o.object_name ) new_date
      FROM all_objects o

      run as both default and ansiconsole. The failure is seen in default mode, but just a partial resultset is seen in ansiconsole. This fails because the same object name exist in two schema and at least one buffers worth of data is returned before the errant row.

  8. Kiki Martin Reply

    Hello Jeff,
    Before I ask my question, I should disclose I am completely new to Oracle SQL developer, (& to databases in general).

    Question: How do I export a query output to SPSS (Statistical Package for Social Sciences)?
    (I saw your /*csv*/ post, and ran it successfully, but have been unsuccessful when importing it into SPSS). My apologies if this is a remedial question…. but thank you in advance for the assistance.
    Kiki Martin

  9. Saeed Hassanpour Reply

    Hi Jeff,

    In my table to insert Persian characters so when I want to retrieve data then got wrong characters. How to set character set or get correctly data? in other tools such as SQL Developer or Toad everything is right without any setting.

  10. melissa Reply

    Is it possible to run a query against two different connections at one time? If so, how? PS-I love SQL Developer. <3

    • thatjeffsmith Post Author

      with one click? No, but you can open a connection on each db and run the query in 2 worksheets concurrently.

  11. Magnus Reply

    Unit Testing:
    I’m wondering if there is reason for not allowing Variable Substitution in the teardown phase? I’d like to have that functionality. (I’m on 4.2 EA)

    • thatjeffsmith Post Author

      Maybe? It was built out in version 2.0 and has seen bug fixes and small tweaks since then. Describe your use case on the Exchange, and maybe we can work it in.

  12. Quamrul Polash Reply

    Hi Jeff,
    I am trying to use the latest SQL Developer 4.2.0.16, but I am unable to launch it. I am getting complains about msvcr100.dll file. I have followed the instruction (found on the net) to edit sqldeveloper.conf file and comment out SetJavaHome. I never get prompted for Java Home and for whatever reason it’s looking under ~sqldeveloper\jdk\bin instead of ~sqldeveloper\jdk\jre\bin.

    Do you have any trick to make it work?

    Thanks

    • thatjeffsmith Post Author

      yeah, it’s a bug with the EXE – you need to copy/create that directory manually and put the DLL in there

      it’s already been fixed for production release, sorry for the confusion/inconvenience

  13. Sergey Reply

    Hi, Jeff.
    One more question.
    I want to use the ‘SQLDeveloper Navigate ShortKey’ for moves cursor on the SQL-Worksheet
    (go to marks, go to the last edit), but it works only in EditorWindow
    I often edit large scripts and ‘bookmarks’ can really help me in my work.
    Why does it not work?
    Maybe i misunderstand the idea?

    • John Garmon

      I also would like to use bookmarks in SQL Worksheet to navigate within a long script. Googling and Oracle documentation do not help me enable bookmarks. I can toggle the view for bookmarks, but cannot determine (or find) how to create a bookmark in my code.

      Help?
      john3

  14. Mel Reply

    Hi Jeff, firstly thank you for an awesome website!
    I’ve decided to try the query builder, and have run into a problem –
    I drag a couple of tables in, but when I right click on a table in Query Builder, the click doesn’t do anything – I don’t get any context menu with an option to uncheck all.
    For comparison’s sake, I can double click the table title to change the alias, or left click other areas to get a context menu no problem.
    So unless I want to use all the columns (not likely) it’s not of much use to me!
    Is there a preferance or setting I’m missing?
    Running vs 4.1.3.20
    Thank You!!

  15. John Boyd Reply

    Jeff,

    In SQL Developer a pop up box appears where values can be entered and bound to parameters. I want to pass a parameter that binds to a CSV, i.e. value1,value2 … etc., so that I can write my where clause as:

    WHERE app.attribute1 IN (:P_CSV)

    so that the parameter becomes

    WHERE app.attribute1 IN (‘value1′,’value2’)

    Is this possible?

  16. Sergey Reply

    Hi, Jeff. Thank you for thatjeffsmith.com 🙂
    I am using Oracle SQL Developer in my daily work. Please tell me whether you can change the position of the “Query result area” between the right and the bottom of the SQL-worksheet or make a “float window”?

    • thatjeffsmith Post Author

      Sorry Sergey, the results panel is locked to the bottom portion of the worksheet window. You can minimize it, and click/drag it up and down, but you can’t move it to the ‘right’ or undock/float it from the Worksheet itself.

    • Sergey

      Oh, I suppose it..
      Hotkey to minimize/display ResultsPanel could help me, but i not find this in Preferences -> ShortKeys 🙁

    • Sergey

      Ok!
      Thank you very much, Jeff.
      I like this product and i wish it become better and better.

  17. SJ Reply

    Trying very hard to get all developers onto one tool viz SQL Developer however we also want to start managing our database as code. I cannot find an equivalent to Devart’s db projects?
    We have 4.15 and 4.2 installed. Does this functionality exist?

  18. Richard Reply

    I have a SQL which I use within SQL*Plus

    select * from &owner..emp;

    Using the same SQL in SQLDeveloper, I replaced the “&” with “:”
    select * from :owner..emp;
    I tried various combinations and I can get it to work.
    For eg: select * from :owner.emp;
    select * from “:owner”.emp;
    select * from ‘:owner’.emp;

    Is it even possible?

  19. Bernd M. Reply

    Hi Jeff,

    I have a question regards IOTs (Index Organized Tables) and the “Database Export Wizard”:

    My IOTs are not shown after lookup under “Specify Objects”. Just all “normal” heap organized tables.

    Exporting single SQL by selecting IOT in schema browser tree works fine. But I would like to do a FULL schema-ddl-export including IOTs with the export wizard.

    Anything I am missing?

    Version 4.2.0.16.356
    Build 16.356.1154

    All the best,
    Bernd

    • Bernd M.

      Oh now I now what I am “missing” -> They will be created with the creation of the index-type CTXSYS.CONTEXT on the Base-Tables. Correct?

  20. Barry Murphy Reply

    Eye see you’ve reformatted your web page – and from the looks of your picture you haven’t reached the age yet where grey on white is hard to see. Here’s a wish we can get grey replaced with black for older eyes. The contrast really helps. Love your site and your sense of humor!

    • thatjeffsmith Post Author

      Darker text on the post body? I think I can do that for sure. If it makes you feel any better, I officially turn ‘old’ this weekend.

    • thatjeffsmith Post Author

      Ok I darkened the text, it’s not ‘pure’ black, but it’s still darker and has a bit of contrast from the titles. Let me know if that helps.

  21. Larry Hopper Reply

    Hi Jeff. I love the way you do search and replace in SQL Developer and wish every program worked the same way, entering the sought text into the replace box and highlighting it. That saves time! 🙂
    Keep up the good work.

    • thatjeffsmith Post Author

      I love this too – so much that I about lost my mind when someone re-mapped Ctrl+R to ‘refresh’ instead of replace in 4.2. I logged a bug and that SHOULD be fixed for when we go prod.

  22. Adam Reply

    I use Code Blocks for C++ programming and there is great shortcut: “Copy line”.
    Without selecting a text just copy current line. In default shortcut is CTRL+Shift+T,
    but i changed it to CTRL+D, because I use it very often.

    I read your blog, looked into SQL Developer and googled a lot, but didn’t found that option.
    Could you tell me if there is a way to define such action and assign shortcut for it in SQL Developer?

    Note: using version 4.1.
    Maybe it is a good idea to introduce such feature in next release? 🙂

    • thatjeffsmith Post Author

      Best I can offer, you can set a kb shortcut for ‘select line’ – you’d still have to do the copy though, so 2 keystrokes instead of 1.

    • Adam

      Thank you for your answer – ‘select line’ shortcut is helpful.

      In fact that shortcut, which I described (and desire in SQL Developer) do a bit more: copy current line, create new line down there and paste this line.
      So 1 shortcut for 4 operations, nice deal.

      I will be happy to see that available, maybe in a future 🙂

    • thatjeffsmith Post Author

      Have you tried to record a macro for this? It’s not something I’ve spent any time with in SQLDev, but they are supported.

    • Adam

      Thank you for that idea.
      I tested that and it works, it’s clever way to save some effort. But downside is that I can have only 1 macro, while more of them will be useful (for example as User-Defined shortcuts).

      Thank you!
      Regards,
      Adam

  23. Ilmars Reply

    Is there a way to force SQL Developer to load package body and specification from server upon open (double click or Edit)? Or is there a way to check for definition change on the server before save? We have run into situations where package has been update by somebody else, but other developer overwrites it.

    Thank you,
    Ilmars

    • thatjeffsmith Post Author

      force both when you open just the one – no

      way to check if source has changed – it depends

      Are you using Source Control? Hopefully the answer is ‘Yes!’ – then say if you’re using Subversion, you should see incoming changes in your SVN client or in SQLDev if using it there, and then you can check-in/merge.

    • Ilmars

      Jeff, let me clarify the first question. Users A and B open up a connection, navigates to Packages. User A modifies package X – compiles. User B right away opens up package X by right clicking package Edit. User B does not see the changes made by user A. Most of the times, changes are reflected in a specification and not in a body. User B either needs to close the package and open it again with Edit or do a package refresh and then open with Edit.
      Does SQL developer caches any package definitions? Trying to understand if this is a database or SQL Developer issue. Using 11g XE, SQL Developer version 4.2.0.16.260.

      Thank you!

    • thatjeffsmith Post Author

      View > Log > Statements.

      Open your package.

      You’ll see the queries go across the connection to get the code.

      Close the object.

      Open it again. You’ll see the queries run again.

      I don’t think we’re caching pl/sql source in the code editors, although we do cache OTHER things, like the DDL we generate on a table – I THINK.

  24. Stasia Reply

    Good Day, Jeff
    A trivial question:
    I updated my SQL Developer from 4.1 to 4.2. I work with 2 separate connections and have the worksheets side-by-side vertically.
    With the previous version, i save my worksheets and close the Developer, then when i reopen it, the worksheets open in the vertical orientation.
    In 4.2 it is not the case. Am i missing a setting to always open in vertical view?
    Thank you in advance.

    • Stasia

      I’m sorry, Jeff but I do not see where there is an option to attach a file and paste in the text window does not seem to be an option for this site.

    • Stasia

      I think i may have found the resolution.
      I just tried this out:
      In the Preferences >> Database >> Worksheet
      Check the “Open a Worksheet on Connect”
      Now every time i open Developer, my connections tile vertically.

      Thank you for such quick replies, Jeff.
      You are amazing for having this discussion board that links the community to you!

    • thatjeffsmith Post Author

      Ah, thanks Stasia. I can’t do my job w/o talking to our users, and this makes it a bit easier – I hope!

  25. Emily Reply

    I have SQL Developer 4.1.3.20. When I run a script that I’ve stored under the User Defined Reports and spool the data to a csv file, the output has the query in the file. How do you omit the query from the file?

Write A Comment