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

  1. Terry Glenn Owen Reply

    I have sql developer version 19.2.1.247. I am experiencing having to constantly having to connect to database when I make any SQL changes on the worksheet. Any changes to SQL seem to trigger this event. Is there a way to keep this from happening?

    • thatjeffsmith Post Author

      That’s 2 years old, lots of improvements/fixes in both the code and jdbc driver we’re using in version 21.2 – go get and try that.

  2. Rob Reply

    I have used the UNLOAD command to save a CSV file for a database table and it works great. I like it a lot better than select field1||’,’||field2||’.’||field3 string from table_to_export;

    If the table is partitioned (in 4 or 8 partitions), would it be possible to have each partition save to a CSV file at the same time? I was not sure if this functionality already exists or if this could be an enhancement that would be helpful to others as well.

    I would like to use this functionality to create delimited files that can be used by other systems. Some of these are created daily, some weekly, etc. Some are complete dumps of large tables that happen daily, and I believe if there was a mechanism to export partitions simultaneously, it could perform well.

    • Rob

      One important detail I forgot to post is that I am using SQLcl

    • thatjeffsmith Post Author

      create delimited files that can be used by other systems.

      Like…? As you can imagine, this is a really expensive way to share data in a database with other systems.

    • Rob

      Yes, I understand that. The large tables are currently handled by Pro*C code. It works well and is able to get the large tables to flat files within 15 minutes (using four sessions/threads for each table). The small flat files are created with UTL_FILE pl/sql package. Just like math is the “universal language”, I feel that flat files are the universal language of IT. Every company and system can output and input flat-files. You do not need to give outside people access to your system. The reason for my question is that I am hoping that the future could provide us with a solution more elegant and maintainable. I believe that SQLcl could be part of that solution. The other possibility would be if external tables could be written to and turned into flat-files. It is my understanding that a binary dump file can be created, but not a flat-file with external tables. Either SQLcl or external tables seem to be the most elegant solutions in the future, but currently…. neither has the feature to support this.

    • thatjeffsmith Post Author

      No, I’m wondering what piece in your IT system needs to consume the data in said flat files. Because you could also look at REST APIs. Pretty much any library/language can handle HTTP+JSON…and no expensive IO writing out all that data, and worrying about how stale it is.

  3. Trev Reply

    20.4.1 Is it possible to disable ‘Query Builder’? Every time I accidently click on it (the tab next to “Worksheet” it locks up SQL Developer for 1min +. I never use it.

  4. SD Reply

    SQLDeveloepr Version 20.4.1.407

    we tried to migrate the SQLserver database and in the dbo.tables it has 44 tables.

    Migration returned SUCCESSFUL but when we log on to the Oracle 11g database we notice that under the Migrated Schema created, we have 30 tables instead of 44 tables.

    checking the MIGRATION logs we see in the target status 30 tables and 14 UNKNOWN

    please advise how to fix this issue

    • thatjeffsmith Post Author

      I can’t, not without a lot more information.

      I recommend opening a service request with My Oracle Support.

  5. scott Reply

    Can you connect using secure external password store with sqlcl?

    I’ve tried to noavail sql.exe /@someentryinstore

    Thank you…

  6. Amin Adatia Reply

    SQL Dev v21.2
    What do I have to do to open a Job? Double click does nothing

    Regards

    • thatjeffsmith Post Author

      Did you try right-click, open?

      I’m able to open a Scheduler Job in 21.2. If that still isn’t’ working, do you see any messages in the Log panel?

  7. Jim Reply

    (sorry for the double post, first time was within someone else’s thread)

    Hi Jeff, long time listener first time caller. We use Oracle SQL Developer daily as contractors within one of the military branches. We’re currently authorized to use a previous version and I have asked to get the latest version (20.x) reviewed and put on the “approved” list. InfoSec came back and said “Oracle SQL developer has these vulnerabilities and the vendor will need to provide mitigations”. They also said they will need an Open Software Support Letter from the vendor which is a generic “we promise to maintain our code well” thing.

    How to I engage with Oracle on something like this where Oracle SQL developer isn’t covered under a support contract? Is opening an SR the best path?

  8. Pawel Reply

    Hi Jeff,

    a question about autoident code when pasting code into the editor.

    When I have a piece of code like

    select *
    from foo ff
    ;

    and I want to replace foo with an ad-hoc view like

    select x, y, z
    from bar

    I would copy the code of the ad-hoc view, open parenthesis in the main query and would past in the clipboard content where the x is

    select *
    from (
    x — past clipboard here
    )
    ;

    But the result is:

    select *
    from (
    select x,y,z
    from bar
    )
    ;

    But it actually should be like this

    select *
    from (
    select x,y,z
    from bar
    )
    ;

    This is what I mean with autoident. There are editors out there (textmate and others) that prefix code with the necessary space characters, so the relative formating to the first line is preserved.

    Is there such a feature in sql developer? I couldn’t find it (neither in sql developer, not via Google search). In other editors it’s called something like `auto-ident on paste`.

    Now I have always to do paste, select the pasted content except the first line and hit tab, tab, tab (as many as are needed) and that kind of sucks 😀
    I was doing that for the last 7 years, but enough is enough 😀

    Thanks!

    PS:
    Yes, I could do Paste, then select everything and autoformat, but I couldn’t get the autoformatter to do exactly what I want it to do.

    • thatjeffsmith Post Author

      There’s a code editor preference that LOOKS like it should do what you’re describing…but enabled/disabled, I don’t see it fixing anything.

      Adjust Indentation When Pasting: Corrects the indentation of a pasted in item that includes indentation.

      I’d be happy to file a bug for you.

    • Pawel

      I found that option now. I have the German Translation, so didn’t see it earlier. But yes, it doesn’t do anything.
      I’d be happy if you would file a bug for me 😀
      Thank you very much!

    • Pawel

      For documentation purposes: Here’s the Option in the German translation

      Beim Einfügen automatisch Einzug anpassen

    • Jim

      Hi Jeff, long time listener first time caller. We use Oracle SQL Developer daily as contractors within one of the military branches. We’re currently authorized to use a previous version and I have asked to get the latest version (20.x) reviewed and put on the “approved” list. InfoSec came back and said “Oracle SQL developer has these vulnerabilities and the vendor will need to provide mitigations”. They also said they will need an Open Software Support Letter from the vendor which is a generic “we promise to maintain our code well” thing.

      How to I engage with Oracle on something like this where Oracle SQL developer isn’t covered under a support contract? Is opening an SR the best path?

  9. Peter2 Reply

    Totally OT “dabase”, but ..

    on this website the “share-icons” (RSS, Twitter, ..) are always moving to the left side, middle height of the screen – and disturbes the reading of the text. Tested with Win 10, Edge and Firefox, on two PCs, both on 19″ monitor.
    Only my problem?

    • Peter2

      BTW, another 3rd-prio cosmetic issue:
      The website says I wrote my posting “5 hours ago”. That’s wrong – it’s approx. 1 hour. maybe is has problem with the time-zones (I’m writing from Central Europe Summer Time, now it is 11:30 a.m.)

  10. Daniel Looby Reply

    I’m in SQL Developer (version 20.4.0.379) and have a worksheet open to a development database. I execute a query of ‘select * from sys.dba_users where username = ‘DL23′ (time is 11:20 AM) and get a row back in Query Results. Note: I am logged in as user DL23.

    Then I go to Tools -> Real Time SQL Monitor… and the Real Time SQL Monitor worksheet opens. Under User I do not see reference to DL23. I scroll to the right to Start Time. The top two entries are ’08-JUL-2021 11:25:00′ and ’08-JUL-2021 11:00:02’.

    Neither are my query.

    Also Auto Refresh is set to ‘5.0 s’, but nothing appears to be refreshing. When I click the button to the right of the Auto Refresh value nothing happens.

    So as I was typing this it appears that it did refresh and there are two new queries at the top of the worksheet. Both where by user GTSIS… which isn’t DL23.

    Shouldn’t I be able to see my own query in Real Time SQL Monitor?

    • Daniel Looby

      That works… for the query I entered in the worksheet. Which was an ‘example’.

      Have a third-party application development instance that is logging an ‘ORA-00942: table or view does not exist’ for a user. What is isn’t logging is either the table name or SQL. So I thought I could use Real Time SQL Monitor to catch the statement. Not happening. And I can’t get into the third-party app set set the statements to include hints.

      Is there a solution?

    • thatjeffsmith Post Author

      Yes, enable session trace to grab ALL OF THE SQL/PLSQL issued on the connection from your 3rd party app. Then browse the trc file(s).

  11. lorenzo Reply

    Hello Jeff, thanks in advance for any help.

    I don’t want to appear a bit arrogant, but this feature I expected to be implemented rather soon:

    https://apex.oracle.com/pls/apex/f?p=43135:7::::RP,7:P7_ID:361

    and nobody developed this in so many years ? Maybe are the few to request such things but…

    Thank you.

    • thatjeffsmith Post Author

      I’m not sure sure how it got marked as ‘Accepted’ – maybe by my predecessor? I don’t have good news for you, it’s not on our list. There are good reasons for this, but I doubt you’re interested in hearing that it’s not coming.

  12. John Holland Reply

    Hi Jeff,

    In Oracle SQL Developer Data Modeler v21.1.0.092, is there any way to display the alignment grid as default? At the moment, for each new sub-view that I create (over 400 and counting!), I have to right-click, select Show, and then select Grid.

    I would like to be able to:

    1) Enable grid display at the SDDM level for new designs.
    2) Enable grid display at the Design level to show the grid on all sub-views, even if the grid was previously hidden.
    3) Enable grid display for all new sub-views.

    Hope this makes sense.

    Thanks.

    John.

    • thatjeffsmith Post Author

      Not that I know of, sounds like we need an application level preference under the Diagram section.

  13. Kevin Melrose Reply

    Issue: User is running a query that takes a long time, ie 30+ minutes to return the results. This example is the same for any query we runs. If I click the X to cancel the query in some cases it cancels immediately, SQL Developer does not freeze. If there is no activity bar while the query is running there is no way to cancel the query. At this point SQL Developer is completely frozen and has to be killed in Task Manager. The query will come back with results if we wait. Problem is SQL Developer cannot be used for anything else as it is frozen. Additionally SQL Developer will freeze when clicking the X to cancel and must be terminated in task manager. Running 20.4 and 20.4.1.

  14. Joe Reply

    Hi Jeff –

    We sometimes use sql developer to connect to postgres databses with the third party odbc driver, as our developers are familiar with it from working with oracle, and prefer it to some postgres tools. However, we are unable to do things like run a script that inserts data with an ampersand.

    Sql developer prompts for the value because that’s the default for an oracle substitution variable.

    “set define off” only works for oracle.

    I searched in preferences for “define”, “substitution”, “escape” and did not find anything relevant.

    I think I understand why it always prompts, but should there not be a way to disable prompting for substitution variables on non-oracle connections?

    We are currently on version 20.2, thanks.

    • joe

      Thanks, but that doesn’t work either, I assume because the command “set scan off” is being sent to the postgres engine, instead of just telling the client to not prompt for a value when it sees a &. “set scan off” is not a valid command on postgresql command and results in:

      ERROR: syntax error at or near “off”

    • Joe

      That’s a little snide. We do work with Oracle as I mentioned, and have some developers that prefer sql developer to the natively available postgres tools and wanted to use it for everything they can. You’ve seemed pretty helpful and a champion for sql developer across multiple sources like here, stackoverflow, reddit, or even random other areas. That attitude is a little unexpected.

      It didn’t seem completely unreasonble to me for a client side(client being sql developer) change or setting to tell it to not prompt for a substitution variable but we’ll just continue to use other tools.

    • Joe

      I guess I think of it more like making sql developer the best product it can; you even have a blog post detailing how to use it to access other dbs with the drivers. Seemed like a little thing to keep people in the oracle ecosystem, not asking for full blown postgres support.

      Thanks for the link, but as mentioned we tried both set define off and set scan off and neither worked.

    • thatjeffsmith Post Author

      The best tool we can, for Oracle.

      Other connection types are provided for one use case only, for migrating those platforms TO Oracle.

  15. Stephen Runnalls Reply

    I want to run a number of queries that read from different tables but have the same output structure, delivering a single row for each query. I export all the outputs to a single csv file, but it leaves a space between each result. Is there any way of telling it not to leave a blank line between each result?

    • thatjeffsmith Post Author

      Maybe, but you could run a batch script against the output file removing any blank lines…

      You could try setting feedback off to see if that helps.

  16. Dax Reply

    Hi Jeff and all,
    I am using sqlcl to connect remote database via local tns. But it worked only via TCP port, unable to connect via TCPS port in SQLCL. Can you please help to connect via TCPS port ?

  17. Amin Adatia Reply

    SQL Dev 20.4
    Memory Settings 10G for both
    When I start CPU = 0% Memory = 2.2G
    Open Shared Connection CPU = 0% Memory = 2.8G
    Open Unshared Connection CPU = 0% Memory = 2.87G
    Open Report SQL Active Sessions CPU = 0% Memory = 3G
    Open Real Time SQL Monitor CPU = 8-10% Memory = 5.2G and effectively frozen i.e cannot sort on Status or even select a row
    Run a Query CPU = 8.5% Memory = 8.5G

    How to get things not to freeze?

  18. Satish Reply

    Hi Jeff,

    Couple of clarifications regarding AutoRest of a Table.
    1) Can we restrict the columns that are part of the response ?
    2) Do we have any parameter setting which would get the total rows count similar to SaaS API’s totalResults=true

    Thanks

    • thatjeffsmith Post Author

      No and no…

      You could make a view it build your own module.

      In Oracle we don’t know the rowcount.. Not without doing as count()

  19. Tommy Reply

    “Did you ever try the code templates? You can pull them up by name…”

    Yes I use that as well for frequent used script and nice feature for quickly insert the code if you remember the name. but it is for different purpose. By the way, code template doesn’t offer search feature as well.

    I have hundreds of snippets saved in sqldeveloper for my system support work.

    It would be so nice if I could search and find my snippet quickly and drag and drop to the sql editor.

    Any chance add this to next release 🙂

    • Peter2

      Support for Tommy’s request.
      I use partially external SQL files because of better search and find and edit, but I would prefer a better Snippet-tool

    • thatjeffsmith Post Author

      You can search your templates I’m pretty sure…

      I can’t offer enhancements on the snippets but I’ll look into what’s possible for maybe 21.4 (December).

  20. Pawan Gupta Reply

    Hello Jeff,
    We are facing an interesting issue and not able to find the root cause, it will be great if you could give any hint.

    We have a table in ATP we are doing REST GET on that table based on a parameter say project_number it has 15k records for that we are using limit and offset to get 2000 at a time we are seeing that REST is returning duplicate data.. in some case some time. In the table we have confirmed we have 15k data.

    • Pawan Gupta

      Should we add a sort by in the GET call to avoid this?

    • thatjeffsmith Post Author

      It’s very possible rows move around the ‘window’ of records by page. Each request, we run the query, again – just for a different requested window.

      So yes, if ordering is important, always include an ORDER BY.

    • Pawan Gupta

      records in database are unchanged. is there a guranteed way to pull records with paging, without duplicate?

  21. Tommy Reply

    hi Jeff,

    Is there a chance we can have snippet search feature in sqldeveloper?

    I have been asking for this feature a few times over the years now either in your blog or Oracle forum.

    I really love sqldeveloper and save a lots of snippets in sqldeveloper over the years.

    Now the trouble is finding them and edit them. It took quite some time to find the one I want in the list and click edit, then it brings up the list again and I have to go through the list again.

  22. Patricia Freeman Reply

    Good morning Jeff,

    I’m a novice and have SYSTEM ADMINISTRATOR access. I would like to know how to get to the source SQL that’s behind either an Oracle canned report or one a former colleague (who is no longer with the organization)?

    What I would eventually like to do is two-fold: 1) copy an Oracle canned report and then customized it, and 2) when I’ve satisfactorily created either a customized SQL report or customized a canned report, then be able to make it available as a CONCURRENT REQUEST for end-users.

    I appreciate your time and guidance.

  23. Neetesh Reply

    Hi Jeff,

    I have a question regarding setting User Defined Properties in SDDM using scripting.

    I tried below method but it sets Dynamic property-

    table.setProperty(p_name,p_value)

    Could you please help me with the method which sets User Defined Property?

    Also, what is the difference between the two?

    Thanks,
    Neetesh Lodhi

  24. Michael Reply

    how can i get total count for a select with ORDS?

    select col1 ,col2 , count(*) over() total_rows from table1

    do i need to use a stored proc? can’t seem to do it with a view.

    thank you

  25. Ingimundur K. Guðmundsson Reply

    Hi Jeff,

    I am setting up APEX on one of our databases. Did this on a test database some time ago, which was version 11 base and it worked fine. Now we have upgraded the databases to 18.7 and this time I receive an insufficient privileges error when it tries to run “execute immediate ‘alter session set “_ORACLE_SCRIPT”=true’;”. I am using my superuser that is logged in as SYSDBA (not using SYS for security reasons). Wondering if there is an extra privilege that I need to give to my superuser so that this will work.

    Thanks in advance.

    • thatjeffsmith Post Author

      APEX installs, better to ask the APEX folks/community.

      Also, 18c is officially dropped from Support in like 2 weeks.

    • thatjeffsmith Post Author

      Yes, it’s fixed in version 21.2 – available for download now.

      SQLcl: Release 21.2 Production on Wed Jul 07 15:10:16 2021

      Copyright (c) 1982, 2021, Oracle. All rights reserved.

      Last Successful login time: Wed Jul 07 2021 15:10:17 -04:00

      Connected to:
      Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

      SQL> help format
      FORMATHR ¦ orcl ¦ viins ¦ None ¦ No time
      ---------

      FORMAT BUFFER - formats the script in the SQLcl Buffer
      FORMAT RULES - Loads formatter preferences file from SQL Developer export.
      FORMAT FILE
      SQL> format file C:\Users\JDSMITH\Documents\bad.sql C:\Users\JDSMITH\Documents\good.sql
      SQL> !type C:\Users\JDSMITH\Documents\good.sql
      SELECT
      cust_id,
      AVG(amount_sold)
      FROM
      sh.sales
      GROUP BY
      cust_id
      HAVING
      AVG(amount_sold) > 1250

      SQL>

Write A Comment