Ask A Question

4.5+ million 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!

5,842 Comments

  1. Hi Jeff.

    a while ago I posted a comment with sample code that make sql editor hang (in sql developer v 19.2.1). The latest version has not improved because today while editing a script with a lot of quote characters the editor literally trashed the while interface. I had to fix the script with an external editor and then copy the script into the Developer’s editor.
    This was a double problem because I was also in a hurry.
    It seems that the editor wants to have everything sintactycally OK to work. It there are missing quote char when there are a lot of them around, even a missing quote char can make it go crazy and garble everything.

    Thank you

    • It was about three months ago , you can find it here http://www.thatjeffsmith.com/ask-a-question/comment-page-84/#comment-172228

      I removed the protocol to try to make the comment go through

      The blog software changes the quote characters so I uploaded the plain text sample here textuploader.com/1fs63
      Please try to paste it into an editor window, you’ll notice that without adding a quote somewhere before the last row, that row becomes unreachable. You can’t click there, also you can’t move the cursor there with the arrow keys.

      On Friday I had somewhat the same problem but this time the editor started to show me multiple times the same rows and, by moving the cursor around , I got the interface completely garbled.
      I add that I am using the Developer on Linux (with oracle java 8). I noticed that the product works better on Windows but that this bug It there nevertheless. Here is an animated gif that can illustrate the point. bb.co/bHHmmnr

    • the last link is ibb.co/bHHmmnr (trimmed the initial I, sorry)

    • thatjeffsmith

      ugh, got it, and yeah, that’s bad

      i’ve logged a sev1 bug with the developer, will let you know if he proposes a workaround while we work on a fix in an upcoming version

      thanks for pestering me, and apologies for missing this!

    • No problem and thanks to you Jeff..
      I can pester you even more if you want, I have a long backlog of requests :-))

  2. Hi Jeff,

    Appreciate for your support. I have a requirement where want to return specific node values if a particular node exist. Example – for the below XML, my output will be 2 records because (Jobsdata/jobList/jobData/job/document) node exist 2 times and returning nodes are (job/document/fileID and job/directoryid ). O/p –> 100,D100 200,D200

    JOBNAME10

    100

    D100

    IMMEDIATE

    host

    node100.localdomain

    host

    node200.localdomain

    host

    JOBNAME20

    200

    D200

    REPEAT_BY_DAYS

    ACTIVE

    host

    node300.localdomain

    host

    JOBNAME300
    D300

    3

    5

    WEEKLY

    node400.localdomain

    host

  3. Jason Feng Reply

    Hi Jeff,

    I am using Oracle SQL Developer Version 19.2.0.206 and could you please help me with following questions?

    Question 1: It happened to me many times that when I close the SQL Developer the system asked me to choose from commit/roll back my statement but all I did was running some select sql statements, nothing should change the database table/view. It is really annoying that I am afraid I accidentally run any insert/update/merge statements, but I tested a few times to make sure I only did select statements. Could you please share some insights?

    Question 2: For certain reason in my work, we try to avoid writing “commit” in the sql statement, rather, we click the “commit” button in the sql developer. Is there anyway we can see the acknowledgement that the “commit” button has been clicked and the changes have been committed? That would be very helpful when running complicated statements.

    Thanks a lot,

    Jason

  4. Hi Jeff,

    When using Data Modeler, is there any way to mark an object as EDITIONABLE and NONEDITIONABLE within the physical model area. The target database for the physical model is “Oracle Database 12cR2” and I cannot find the option. I am asking because I have a collection type that is a table attribute and this must be marked as NONEDITIONABLE for it to work. I also couldn’t find a way to get a user generator with the ALTER USER ENABLE EDITIONS DDL.

    I am confident that editions are fully supported by DM but options must be tucked away somewhere. 🙂

    Many thanks,
    Mark.

    • Hi Jeff,

      Sorry to ask but do you have an update for me?

      Many thanks,
      Mark

    • Jeff,

      Thanks for the reply. Is this something you would consider adding for the next release?. EBR is a really cool feature unique to Oracle database.

      Many thanks,
      Mark.

  5. Amin Adatia Reply

    SQLDev v20.2

    When I startup SQLDev I get a task IndexPreferencesTask which runs for a few seconds — sometimes 10 seconds. What is it doing?

    Also the logging Page shows

    Level => SEVERE
    Sequence => 139
    Elapsed => 0
    Source => oracle.ide.extension.HashStructureHook
    Message => Unexpected runtime exception while delivering HashStructureHookEvent

    • thatjeffsmith

      indexing all the text/labels for preferences so when you do a search for something there, you can find them

      it runs on a background task and shouldn’t interfere with anything else

  6. Mohmood Zaboli Reply

    Hi Mr. Jeff Smith

    What is the reason for that we can (or must) not connect with sqldeveloper to oracle Fusion Cloud database (SAAS) ,Oracle Cloud ERP, Oracle HCM Cloud, but other tools can !!! Can you explain that please?
    AS EBS developers, we use tools like Toad, SQL Developer, etc. but are unable now for SQL queries and analysis with Oracle Cloud.
    For us tools like Oracle BI are not efficient ,user friendly and time consuming and we must go through multiple hoops to be able to execute a query etc. Can you explain ?

    Regards
    Mohmood

  7. Hi Jeff

    We are using ORDS for Oracle DB centric APIs. We have specific authentication use-case to validate bearer token (JWT) from third-party (Okta in my case). What would be your suggestion? Is their a provision within ORDS or should I frontend internal gateway like Spring Cloud on Apache Tomcat/WebLogic?

    Appreciate your passion and intent to support the Oracle Community.

  8. Hi Jeff,

    There is a situation at one of our Production database, there was this below query, SQL Developer auto generated, which has ran for almost 30 minutes. Want to understand what user action might have triggered this query such that we shall be cautious at least to do it Production.

    Version being used: 4.1.3.20 (Yeah.. Old version though)

    SELECT object_type type, owner, object_name, null column_name, null column_id, null data_type FROM all_objects WHERE object_type =’TABLE’ and object_name not like ‘BIN$%’ and rownum <=50 union all SELECT object_type type, owner, object_name, null column_name, null column_id, null data_type FROM all_objects WHERE object_type ='VIEW' and object_name not like 'BIN$%' and rownum <=50 union all SELECT object_type type, owner owner, object_name, null column_name, null column_id, null data_type FROM all_objects WHERE rownum <=50 and object_name not like 'BIN$%' and object_type IN ('PROCEDURE','FUNCTION','PACKAGE')

    • thatjeffsmith

      Looks like one of the queries we run in the object navigator… Imagine going to browse the TYPES using the connection tree.

  9. Hi

    How to adjust that ‘smart data’ and ‘data’ windows are shown during debugging? I have only ‘watches’ window there.

    Thank you in advance.

    • thatjeffsmith

      try resetting your desktop or try View > Debugger > Smart Data and Data

      Generally those open by default, but it’s possible you previously closed them?

  10. I’ve created an autonomous database (ATP) environment and logged on with admin. I want to create schemas to separate things.

    CREATE USER HI IDENTIFIED BY somevalidpassword;
    yields
    Substitution cancelled
    in the script output.
    HI is the user/schema.
    Why is it doing this and how can I create schemas in my ATP environment?

    • I was using & in the password.
      Second problem. After I eliminated that, I got an ORA-00922: missing or invalid option.
      That’s because I was starting the password with a number.
      So surrounding the password with double quotes fixed that.

  11. Well, using the syntax @http://svn.something.com/script.sql to execute a script in SVN, the script must be loaded into SQL Developer. Is this functionality not expected? Everything works well, except the constant login prompts.

    • thatjeffsmith

      yes, that’s expected…navigating authentication requests wasn’t something I’d tried before.

  12. Hi Jeff;

    I often execute SQL scripts connecting to SVN using syntax @http://svn.something.com/script.sql. Is there a way to save the “CollabNet Subversion Repository” user name and password in SQL Developer?

    Thanks for your help.

    • thatjeffsmith

      Not outside of using the svn feature…but you’d use that to bring the files down locally.

    • Jeff;

      Let me clarify. I can execute SVN scripts in SQL worksheet without download the files locally, either F5 or F9. SQL Developer will initially prompt for username/password and that stays in effect until I quite SQL Developer. That’s very convenient. However, in 19.4 (maybe lower version too), upon next restart, SQL Developer starts to prompt for SVN login. If I do not want to login SVN at that time, I have to click “cancel” many many times. So I thought maybe SVN login can be saved, similar to the “version” feature. If not, is there a way to stop the prompting?

      Thanks in advance for your help.

  13. Hi Jeff,

    Recently started using sql developer 20.2…now I am frequently getting “Your connection has been reset. Any pending transaction…” dialog.

    • thatjeffsmith

      The only change of note is we went from one 19c jdbc driver to a more recent version of the same, 19c jdbc driver.

      You could try an Instant Client/Oracle Home (OCI) Thick connection to see if that improves things.

    • Actually I am using oracle instant client connection (both on old and new version of sql developer)
      I’ve got oracle instant client v12.1.0.2.0 installed will this cause any issue

  14. Hi Jeff. I have a question for what I hope is a simple yes or no answer regarding default SQL Developer functionality (I’m using version 18.1.0.095 and Build 095.1630 currently). Is it possible to change the ordering of the Connection drop-down list in the upper right-hand corner of SQL Developer or even just completely prevent it from reordering connections? The reason I ask is because the default functionality of where it reorders the Connection list by open connections doesn’t seem to be for ALL open connections, only some, which means you have to look at the top for some open connections and then scroll down and search for others. I would rather it just left the order as-is rather than change it for some open connections and not others, which ends up scrambling them up.

    Thanks in advance!
    Bob

    • thatjeffsmith

      Upgrade

      It should be open connections first, then the rest alphabetically

      If you see differently in 20.2, please let me know

  15. Hi,

    20.2 materialized views sql tab does not display view ddl just show grants.

    Numan

  16. Hi Jeff,

    I tried to use sqlcl Version 20.2 with Liquibase and reusing our current Liquibase Changelogs and changeset, but unfortunatley I saw the following exception:
    SQL> lb update db.changelog-3.37.0.xml
    Exception in thread “main” java.lang.NoSuchMethodError: liquibase.ext.ora.create oraclejob.CreateJobChange.setObjectType(Ljava/lang/String;)V
    at liquibase.ext.ora.createoraclejob.CreateJobChange.(CreateJobCha nge.java:18)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstruct orAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingC onstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at java.lang.Class.newInstance(Class.java:442)
    at liquibase.change.ChangeFactory.register(ChangeFactory.java:71)
    at liquibase.change.ChangeFactory.init(ChangeFactory.java:42)
    at liquibase.change.ChangeFactory.getInstance(ChangeFactory.java:52)
    at liquibase.parser.core.xml.XMLChangeLogSAXHandler.(XMLChangeLogS AXHandler.java:52)
    at liquibase.parser.core.xml.XMLChangeLogSAXParser.parseToNode(XMLChange LogSAXParser.java:110)
    at liquibase.parser.core.xml.AbstractChangeLogParser.parse(AbstractChang eLogParser.java:15)
    at liquibase.Liquibase.getDatabaseChangeLog(Liquibase.java:217)
    at liquibase.Liquibase.update(Liquibase.java:190)
    at liquibase.Liquibase.update(Liquibase.java:179)
    at oracle.dbtools.raptor.scriptrunner.commands.liquibase.liquibaseComman d.handleEvent(liquibaseCommand.java:470)
    at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(C ommandRegistry.java:346)
    at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.j ava:226)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecut or.java:344)
    at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecut or.java:227)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java :410)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli. java:421)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli .java:1292)
    at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:50 2)
    $
    So I am not sure if we are using liquibase in a a way which does not support some liquibase features:

    The changelog I was using in this test:

    • thatjeffsmith

      You have to use the -changelog flag, yes? Check the cmd help to be sure (I’m at the beach), and if that’s good, then I might need your changeLog too.

  17. Hi,

    I was trying to extract the data through sqlcl and load it using sqlldr. One issue we came across is field like comments and remarks are having line separator (\n). While extracting the data using SQL format as loader or CSV we are seeing those records are getting into new line in sqlcl and when we import it its causing an issue using sqlloader like second enclosure string not present. Any thoughts to fix it ?

    Just a sample record.. You can see one record is gone to multiple line..

    125 “SI3″|||”61″||”0″||”SO”|”MUDLO1″|12551|”1″|”1″||||||”2020-07-29 10:45:34″||”2020-06-20 17:56:56″|”28/06 ripini per il 12/07 mata . n otia al clt. spgar
    126 12/7 sac 1266 fallito per cle assente lta!”||

    • thatjeffsmith

      Mutli-line strings basically kill CSV…for sqlldr there’s this.

      If that’s not going to work, using the INSERT method or building an external table might be the way to go.

  18. Hi Jeff,
    removing deep (very deep) directory structure in windows was quite challenging. But I managed it.
    The problem is reproducible.
    The Migration environment from the previous Version 19.4 fails; the loading screen shows “IDE Platform-Core”. At this point, the process runs forever. The same directory structure is generated.

  19. Hi,

    The challenge is to output using ORDS “total no of rows” and recordset (sys_refcursor) so that:
    (1) sql query is optimal (performed ONCE).
    (2) totals is not repeated for every row returned (network efficient – totals data is returned just ONCE)

    I managed to make it optimal (1), but “totals” is present in every row – (2) failed.
    I could compute totals in one SQL query/cursor, store the value in an OUT parameter and perform a 2nd query with the rows returned in a sys_refcursor OUT parameter, but that would fulfill (2) and fail (1).

    Any ideas ?

    Thanks,

  20. Hi Jeff,
    i was nosey and already installed sqldev 20.2 on my windows 10 system.
    During the first start the progress bar hung, the process sqldev was consuming >900MB ram and growing. so i killed that process.
    after that i looked into %APPDATA% directoy and found an almost infinite number of directories which i have trouble to delete:
    \\?\C:\Users\\AppData\Roaming\SQL Developer\system20.2.0.175.1842\o.ide.12.2.1.5.42.200515.1304\o.ide.13.0.0.1.42.190403.1502\o.ide.13.0.0.1.42.170225.201\o.ide.13.0.0.1.42.170225.201\o.ide.13.0.0.1.42.170225.201\o.ide.13.0.0.1.42.170225.201\o.ide.13.0.0.1.42.170225.201\o.ide.13.0.0.1.42.170225.201\o.ide.13.0.0.1.42.170225.201\o.ide.13.0.0.1.42.170225.201

    Did i run into a bug?

    Have a nice weekend
    Peter

    • Maybe I found the reason:
      The same directory looks weird in the previous versions’ profiles as well. I guess during migration the directory structure is copied into the new version. Including this strange structure.
      What du you think? Is it safe to remove the structure of my profile data? Or do I loose required informations?

      Regards Peter

Write A Comment