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,926 Comments

  1. Hi Jeff,

    In SQL Data modeller, I am trying to add RDBMS comments to columns within an editioning view definition. Within the view properties, the option exists to do just this, but this only works at the view level . I have entered the information for view columns, but when saving, the information entered is lost. It is also not generated in DDL. Is there another way to enter this information or is this a bug?

    Many thanks,
    Mark.

    • thatjeffsmith

      I create a view, marked it as editioned, added a comment. Saved the design. Then previewed the DDL –

      CREATE OR REPLACE EDITIONING VIEW ebr_emp (
      column_1, column_2
      ) AS
      SELECT *
      FROM employees;

      COMMENT ON TABLE ebr_emp IS
      'this is a comment.';

    • Hi Jeff,

      Thanks for the quick reply.

      Can you try adding comments to each of the view columns? I can’t get this bit to work.

      Many thanks,
      Mark.

  2. Hi Jeff,

    Sorry – yet another SQL formatting question.

    I really want to use the tool built into SQL Developer, but I just cannot get the formatting result I want from it. I have tried the “auto detect” feature using my formatting code but that doesn’t seem to work too well for me.

    I do know that the underlying formatter uses “arbori” rules and these can be modified to suit but this looks very complex. I know that a number of people have blogged on this but I still don’t feel comfortable modifying the out-of-the-box code.

    I did some looking on the internet and found an on-line utility (http://dpriver.com/pp/sqlformat.htm) that, whilst not perfect, does produce output that is similar to what I want. To be clear I don’t want to use this tool as I use SQL developer every day and am more than happy with it. It would just be awesome if SQL developer provided more customisable options in the formatting department.

    Any help/links/tutorials you can provide me would be greatly appreciated. I have a lot of PL/SQL legacy code that was clearly created with multiple coding standards and some that was generated by a code generator. Both would really benefit from source code formatting.

    Can you help?

    Mark.

  3. Getting following error :
    Could not load connection type:Oracle
    URI has an authority component
    when trying to create new connection in the Sql Developer.
    Tried looking for 18.4 folder in path
    C:\Users\Manikanta\AppData
    What are the other possible issues?

    • thatjeffsmith

      Make sure you’ve installed SQLDev to a ‘normal’ directory and not to a UNC path.

      Assuming that, try renaming your system18.4 AppData sql developer folder and starting the program again.

      Finally, 18.4 is very old, we’re up to version 20.2 now!

  4. A Modeler question on using Search.

    I search for a field name and get back several results including results under Diagrams. When I clean on a diagram under Diagrams, Modeler takes me to the entity I am looking for. However, some of my tables are very big and I would like to jump directly to the field I see in search results. Is there a way to do this, versus scrolling through the entire table?

    Thanks!

    • Meant to write …. When I click on a diagram under Diagrams …

    • thatjeffsmith

      no, it’ll just take you to the table, but you can configure the diagram to only show the ‘important’ attributes/columns if that helps

  5. I think it would be very handy to have a way to save/bookmark individual queries with SQL Developer. I don’t see every query I run in the history (not sure why) and there are times I’d like to keep something handy. I wish there was a way to “bookmark” individual queries within the worksheet and then bring these up similar to the way browers do with a name and folder structure. And if behind the scenes these went into a file which could be copied between computers would be great. I know I can do this myself by saving each query into an individual file but it’s slow to have to browse each time instead of just using a hotkey and selecting a query.

    • Jim Morgan

      I guess snippets could be used for this. That doesn’t really seem to be their purpose and adding user defined snippets though. Even having a button on the worksheet toolbar that would let me select code in the worksheet and then open the User Defined Snippet dialog box with the code selected in the Snippet box. And in Snippets have a button to switch behind built in and user defined snippets to keep the user defined ones in a separate area.

    • thatjeffsmith

      I think what you’d really looking for are the Code Templates. promote the most important queries to templates, then you can invoke them by name using Code Insight (ctrl+enter)

  6. 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 :-))

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  20. Hi,

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

    Numan

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

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

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

  24. 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,

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