Ask A Question

5,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,030 Comments

  1. Avatar

    Hi Jeff,

    I am attempting to get SQL data modeler to generate all the DDL for my application. Is there a way for the tool to generate the following command when creating a new user?

    ALTER USER ENABLE EDITIONS

    I have looked in the Users Properties dialog, and I can’t see to find it.

    If this is not supported yet? Is there any way of customizing the DDL generation in order to get this working prior to a future release?

    Many thanks,
    Mark.

    • Avatar

      Question: is there a kick-back for NOT allowing your customers to have access to the database in an Saas/HCM Cloud type of Oracle-hosted environment? Or is it a matter of security? Or both? I’m trying to use SQL Developer tool and it says Cloud Connections, but I’m told that it’s not supported. I just can’t make sense of this, so I figure it’s of financial benefit to someone. I would think that would enhance and bring home the bacon for Oracle, especially sales and product managers like yourself! (Apologies in advance if one of the 7K-some comments addressed this already or multiple times!)

    • thatjeffsmith

      It’s up to each individual cloud service team whether or not they expose core pieces of infrastructure like the database.

      Being on the database team, if it were up to me, you would have access to your data at the SQL layer. But it’s not up to me.

  2. Avatar
    Michael Stong Reply

    Our organization uses Azure DevOps and Git for version control. I have a personal access token I can use for the password when using SQL Developer to connect to the git repository over https. Is there any way to tell SQL Developer to use my PAT or even to store the password for Azure DevOps git so I don’t have to reenter it every time I want to do something? I’m on SQL Developer 22.2.1

    • thatjeffsmith

      I don’t know, that code comes from JDeveloper and hasn’t been touched in awhile. If the UI doesn’t show it, I’m guessing answer is no.

  3. Avatar
    Wade Stockton Reply

    I can’t find the jobs feature in Sql Developer. Using version 22.2. in DBA view and I see Scheduler but Jobs is no longer there, just Job Classes Thanks.

    • thatjeffsmith

      You’re looking in the wrong place, jobs are listed in your schema, alongside tables.

    • Avatar
      Wade Stockton

      Oh I had to go into preferences in Database/Navigation Filter and check Scheduler. Thanks

  4. Avatar
    Amin Adatia Reply

    I asked my DBA to install ORDS and give access to SQL Developer Web. The response was interesting because I had asked for the Date formats to be set to the ISO way instead of the default AMERICAN format. Has anyone tried to sort dates when it is the default format?

    >> All this is available in sqldeveloper and doesn’t need the ORDS web server and config. <<

    So is SQL Developer outside of ORDS going to be available and have the same features as the Web version?

    • thatjeffsmith

      Well I have no idea what DATE formats have to do with your DBA wanting or not wanting to deploy ORDS and SQL Developer WEb.

      SQL Developer Web is attractive for those that want to use a web interface, vs managing installs of desktop software.

      It’d be more likely to get ORDS deployed in an org for it’s ability to serve REST APIs for your database. And then as a bonus, you get SQL Developer Web too.

      “So is SQL Developer outside of ORDS going to be available and have the same features as the Web version?”
      Sorry, you lost me there. How do you mean?

    • thatjeffsmith

      Also I just realized you’ve taken the time to leave more than 100 comments/questions here. Thanks! I appreciate the time and consideration.

  5. Avatar

    Hi Jeff,
    When I open a table detail tab and I click the data tab it remembers the last filter/order by. It is the order by which is killing me. I previous would clicked a column to order by which was on a limited result set. But when I open it again it will retrieve the data immediately with the last order by. Ok for a table with a few hundred rows. Not ok for a table with 10 million rows.
    Anyway to to configure it that it does not remember the last order by?

  6. Avatar

    I am trying to set up ORDS and SQL Developer Web for Multitenant following your guide. I am doing this on Windows 11 and my database is on Linux Server.

    I started ORDS and am at the step ORDS is running, now what?

    REST enable some schemas
    Connect as SYS to a PDB

    Error starting at line : 3 in command –
    DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;

    BEGIN
    ORDS.ENABLE_SCHEMA
    ( p_enabled => TRUE
    ,p_schema => ‘EFMSI’
    ,p_url_mapping_type => ‘BASE_PATH’
    ,p_url_mapping_pattern => ‘efmsi’
    ,p_auto_rest_auth => FALSE
    );

    COMMIT;

    END;
    Error report –
    ORA-06550: line 5, column 2:
    PLS-00201: identifier ‘ORDS.ENABLE_SCHEMA’ must be declared
    ORA-06550: line 5, column 2:
    PL/SQL: Statement ignored
    06550. 00000 – “line %s, column %s:\n%s”
    *Cause: Usually a PL/SQL compilation error.
    *Action:

    Obviously, I am missing something

    • thatjeffsmith

      You need to login as EFMSI to run that, or use ORDS_ADMIN package instead if you want to enable someone else’s account.

  7. Avatar

    Hi Jeff,

    I have installed ORDS in the CDB so that I can set up PDB Lifecycle Management for use with Jenkins automation. The install seems to have gone smoothly and the ORDS service starts but when I try to access the PDB status URL (for example) I get a 503 error (using Postman).

    I found the following error in the ords log:

    TNS-12541 TNS listener does not currently know of service request ..etc and lists the jdbc connection that it is trying to connect to. The service name in the string is wrong in that it is “_.domain.name” rather than the CDB service name.

    I have set db.servicenameSuffix to the db_domain value.

    Is there something misconfigured? I have followed doco verbatim .

    BTW I have installed at the PDB level and it worked straight away!

    Thanks
    Tracey

  8. Avatar

    Hi Jeff,
    I am working with sensors connected to an Adafruit Feather nRF52840 Express. To read and write data from and to the Autonomous Database I use ORDS. To get data I use:
    https://redacted.com/ords/scr_andrea/scrApi/locations/
    This works perfect with postmen, but with Adafruit I get an error. Other get requests work nicely with Adafruit.

    Is this a known problem?

    Here the code in python on Adafruit, that executes a general get and then the get to my Autonomous Database:
    # adafruit_requests usage with an esp32spi_socket
    import board
    import busio
    from digitalio import DigitalInOut
    import adafruit_esp32spi.adafruit_esp32spi_socket as socket
    from adafruit_esp32spi import adafruit_esp32spi
    import adafruit_requests as requests

    # Add a secrets.py to your filesystem that has a dictionary called secrets with “ssid” and
    # “password” keys with your WiFi credentials. DO NOT share that file or commit it into Git or other
    # source control.
    # pylint: disable=no-name-in-module,wrong-import-order
    try:
    from secrets import secrets
    except ImportError:
    print(“WiFi secrets are kept in secrets.py, please add them there!”)
    raise

    # If you have an AirLift Featherwing or ItsyBitsy Airlift:
    esp32_cs = DigitalInOut(board.D13)
    esp32_ready = DigitalInOut(board.D11)
    esp32_reset = DigitalInOut(board.D12)

    spi = busio.SPI(board.SCK, board.MOSI, board.MISO)
    esp = adafruit_esp32spi.ESP_SPIcontrol(spi, esp32_cs, esp32_ready, esp32_reset)

    print(“Connecting to AP…”)
    while not esp.is_connected:
    try:
    esp.connect_AP(secrets[“ssid”], secrets[“password”])
    except RuntimeError as e:
    print(“could not connect to AP, retrying: “, e)
    continue
    print(“Connected to”, str(esp.ssid, “utf-8”), “\tRSSI:”, esp.rssi)

    # Initialize a requests object with a socket and esp32spi interface
    socket.set_interface(esp)
    requests.set_socket(socket, esp)

    JSON_GET_URL = “https://httpbin.org/get”
    MY_GET_URL = “https://redacted.com/ords/scr_andrea/scrApi/locations/”

    print(“Fetching JSON data from %s” % JSON_GET_URL)
    response = requests.get(JSON_GET_URL)
    print(“-” * 40)

    print(“JSON Response: “, response.json())
    print(“-” * 40)
    response.close()

    print(“Fetching JSON data from %s” % MY_GET_URL)
    response = requests.get(MY_GET_URL)
    print(“-” * 40)

    print(“JSON Response: “, response.json())
    print(“-” * 40)
    response.close()

    —————
    The result I get:

    Fetching JSON data from https://httpbin.org/get
    —————————————-
    JSON Response: {‘url’: ‘https://httpbin.org/get’, ‘headers’: {‘User-Agent’: ‘Adafruit CircuitPython’, ‘Host’: ‘httpbin.org’, ‘X-Amzn-Trace-Id’: ‘Root=1-63cab0c8-30c0b29916e69de96ca140cc’}, ‘args’: {}, ‘origin’: ‘194.230.148.73’}
    —————————————-
    Fetching JSON data from https://redacted.com/ords/scr_andrea/scrApi/locations/
    Traceback (most recent call last):
    File “code.py”, line 56, in
    File “adafruit_requests.py”, line 815, in get
    File “adafruit_requests.py”, line 661, in request
    File “adafruit_requests.py”, line 512, in _get_socket
    RuntimeError: Sending request failed

    Code done running.

    • thatjeffsmith

      Something’s up with your Python code, not ORDS.

      Also, I redacted your URLs. I’m a bit worried your REST APIs are unsecured, anyone on the Internet can get to your data. I hope this is a a ‘play’ only space for you, but you should secure your endpoints ASAP.

    • Avatar

      Hi Jeff,
      Thanks for your answer.
      It can not be the python code as it was working with the exactly same code and versions of code last summer.
      In addition: http-requests to other sources work well.
      About security: I am aware of that. It is a play ground and not secure to make a first step easier. The second step then is to secure it.

    • Avatar

      Good point.
      So I will check the details. The python used to work and there was no change in python or any of the libraries. So I thought something changed on the oracle side.

  9. Avatar
    De Groote Mike Reply

    We are avid users of your sql developer tool.
    All the vunrability exploits in the last years has made our security team rather twitchy.
    Recently they requested to investigate if sql developer was vunrable to CVE-2022-42889 commons-text.jar exploit.

    Sql developer carries this file.

    According to the Apache security team this exploit is fixed in their 1.10.0 version of the file. Release of this version was dated to 24/Sept/2022
    now the latest version of sql developer is dated 12-sept-2022

    Logic dictates that this version of sql developper is not using the latest version of the commons-text.jar file.

    Are the plans to remove this issue or is there a fix possible to remove this vunrability. We know you are rebuilding sql developer from the ground up. and this version will probably resolve this .But we also know this is not something that can be rushed. Might there be a fix possbile before the new version releases?

    Thanks for al the awsone info over the years.

    Greetings from Belgium

    • thatjeffsmith

      SQL Developer’s main application doesn’t use it, it’s part of an extension delivered by the Graph team. If you’re not using SQL Developer to execute SQL on a Graph database, then you can totally remove this file with zero impact.

      Version 23.1 will have an updated extension that addresses this issue.

      SQL Developer Next won’t have this Graph extension, so it’s not an issue for that platform.

  10. Avatar

    “Save File” slow when running SQL Dev on Java 11 and newer

    This is an issue that came to us when we went from v21.4.3 to v22.2 (Java 8 to Java 11).

    SQL Dev is running on a Windows image. On v22.2/22.2.1 when saving a new worksheet (Ctrl-S), it takes at least 10 seconds before the “Save” dialog shows up. With v21.4.3 on Java 8, this is instant.

    I have removed all older versions, cleaned up files and history. No preferences from older versions. Still problem.
    So I have tested v21.4.3 with different Java versions by setting the Java home in …AppData\Roaming\sqldeveloper\21.4.3\product.conf like:
    SetJavaHome C:\Program Files\Java\jdk1.8.0_202 or SetJavaHome C:\Program Files\Java\jdk-11.0.16.1

    When running v21.4.3 with Java 11 – “Save” (new worksheet) is slow (as with v22.2.1).

    Saving an existing file is fine.

    Have also toggled with: AddVMOption -DIndexedPreferencesCommand=false
    This has no impact on the “Save file” problem.

    Any help or suggestion would be appreciated.

    Thanks
    Jo

    • thatjeffsmith

      It’s a bug with Java 11. The Java team is going to fix that for us, and we’re going to ship it as a SQL Developer 23.1 update.

    • Avatar

      Thanks Jeff – that information actually helps us as we now can stop looking for issues in our environment.
      We will just continue on v21.4.3 until v23.1 is ready.

      Best regards
      Jo

  11. Avatar

    Hi Jeff, first of all thanks for the utterly helpful tipps regarding ORDS! We already found a lot of insights, helping us to shape our ORDS-Services.
    One question though that I maybe have just not found yet…:
    ORDS has a query-Option (“q={…”) that we also would like to use in webapps that use a paging mechanism. In order to implement that we would need to know the overall row count of entries, that this query will ultimately return.
    Is there a parameter that could be added to the query in order to return not the resulting rows but the row count?
    Defining a fixed modul for each of the variety of queries does not make sense.
    Thanks for your help,
    Thomas

  12. Avatar

    Hi Jeff,

    Is it possible to expose a Stored Procedure with 2 parameters – a varchar2 input, and codelist input like (‘1′,’2′,’3’) and has an out of varchar2? If it’s possible could you please share us the definition?

    • Avatar

      Sorry for missing out – To expose the above stored procedure as ORDS

    • thatjeffsmith

      Yes, of course.

      Click above on where it says ‘REST APIs’ – then under examples see my posts for PL/SQL and ORDS.

  13. Avatar

    Hi Jeff,
    We are upgrading our ORDS installation from 18.4 to 21. We run it from an application server to several database servers, and each database has a number of ORDS enabled schemas and associated services. Just a couple of questions:
    1) Is there a practical way of upgrading a subset of databases (the ORDS_METADATA schema) and performing a gradual upgrade or do we pretty much have to upgrade all registered databases at once?
    2) It would be nice if we could export all of our databases’ services at once. You had mentioned in an older post that ORDS may be able to export all the services in your database at once. Is that an option? if not is there an efficient way of exporting all of the services? (ie without having to log into each service schema in each database and performing an export).
    Thank you,
    Ryan

    • thatjeffsmith

      Just export the metadata schema, then you have logical backup of your APIs, privileges, and clients.

      You can choose to not upgrade a database and simply run a newer ORDS against it.

      That should be a short term solution, also you should consider ORDS 22.4, as 21.anything is already more than a year old.

    • Avatar

      I tried an export but it does not export any actual data. According to Metalink it isn’t supported (docid 2562468.1). It looks like I will need to export them schema by schema with the API or SQLDeveloper

    • Avatar

      I exported the services using ‘sqlci rest export’ as a result of another one of your tips. Very slick!
      One more question (?).
      Our test ords environment includes 120 training databases, each configured with their own ords installation (we take a weekly snapshot of a master used to populate all 120). if we were to remove those 120 training database pu.xml files from the config folder, perform the upgrade on the handful that were left, and then put them back into the config folder, would ords then automatically try to upgrade the restored databases if it found that the schema was still at the older version 18? is there a switch to start up ords that will avoid that? The idea is to allow Ords 21 to upgrade just the master snapshot database and let the next snapshot to refresh the 120 training databases (instead of running the upgrade against all 120 training databases, whose schemas will just be replaced with the master on next refresh). Thanks again.

  14. Avatar

    Hi,

    I am trying to load the VIEW into SQL Developer by meand of export / open / edit, various ways. But it does not load the entire VIEW. It truncates the VIEW code. Its a pretty long VIEW. Are there any options to increase the size of the code so it can load the entire VIEW.

    Thanks.

    • thatjeffsmith

      what do you mean by ‘load a view’

      Do you mean create the view?

      What’s a ‘long view’ – a very long SQL create statement?

    • Avatar

      Thank you Jeff,
      I had read somewhere that export/importing the ORDS_METADATA schema was not a reliable method of backing up ORDS in the database since it was Oracle managed, but I will certainly test that out. Cheers,

    • thatjeffsmith

      That’s where all our data is…you could also write a loop and cycle schemas via the ORDS APIs to export your schemas.

    • Avatar

      Sorry, that reply was to the wrong thread 🙁

    • Avatar

      Thank you Jeff.

      I am trying to get the source code of a existing VIEW from database so I can make changes. So I am trying to export the VIEW code out of database. When I export SQL Developer does not export the entire VIEW it exports only partial VIEW. My VIEW has about 2500 lines of code.

      But if the same VIEW in my own schema I am able to export the entire VIEW code. Is this something to do with the privileges I have on the VIEW or is it something related to some settings in SQL Developer that only loads so many lines/characters of code.

      Any help would be great.

      Thanks again.

    • thatjeffsmith

      Two possible problems.

      1. Truncated text (sounds like your issue)

      The view source is I believe stored as a LONG, so you need to add
      SET LONG 10000000

      That will allow the client (SQLDev/SQLcl) to allocate the space to print those potentially very ‘long’ bits of code stored as VIEWs.

      2. Permissions
      If this where the case, you’d get nothing back on the data vs a truncated bit of code. If it were a privilege issue you’d need an account with fairly high level access to the data dictionary, maybe even as high as the DBA role.

    • Avatar

      Thank you Jeff for your time and input. I will give those a try,

  15. Avatar
    Mauricio Fernández Reply

    Hi, very simple question…what tool, another than curl, you use to test webservices?? I can’t tell from the images…Postman??…thank you very much

    • Avatar
      Mauricio Fernández

      Thank you, Jeff Imsomnia works very well….I’m trying to test ORDS services with php…Oracle recomend any framework? besides pecl_http (I have errors trying to install it in oracle linux 9) ?

    • thatjeffsmith

      Sorry, I haven’t touched perl since like 2007. I imagine someone in the WordPress community would have good ideas/thoughts on this though.

  16. Avatar

    Sorry for the probably simple question, but how can I tell sqlcl to submit the sql on the current line if my cursor is in the middle of the line? If I hit ‘enter’, just enters a line break instead of running the query.

    Eg:, I just hit up arrow to recall a statement, move cursor to make a change, and want to run the changed sql without having to move the cursor back to the end of the line.

    The ‘end’ key just inserts ‘4~’ so that doesn’t work. ‘shift-enter’ doesn’t work either and also just adds a line break, thought I’d have probably expected ‘shift-enter’ for the line break, and just ‘enter’ to run it.

  17. Avatar
    Amin Adatia Reply

    SQL Dev v22.2.0.173
    Trying to run Real Time SQL Monitor — everything is grayed out and eventually get a message the I am running out of memory — set to 8GB

  18. Avatar
    Fazzi Shariff Reply

    I understand that the default LAF is Oracle. And yes, I agree that each windows user gets their own set of preferences…
    But how do I change the default to windows for ALL users? I mean, there should be a kind of global setting, right? (that applies this preference to all users?)

    • thatjeffsmith

      It’s in an XML file under each Roaming Profiles AppData directory, for each USER on a machine

  19. Avatar
    Fazzi Shariff Reply

    Hi Jeff,

    I read your blog on the “Look and Feel” part for Oracle SQL Developer and it’s really informative!
    I tried to set the default LookAndFeel to Windows via a “swing.properties” file and placed it in the folder “\sqldeveloper\sqldeveloper\bin” and it seems to have worked and changed the default from Oracle to Windows (taken from – https://docs.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html )

    However, this is not applied universally to ALL users, rather just to my user, i.e. and everytime I connect via a different user using an RDP session for example, the default LookAndFeel of Oracle loads up (which consumes a lot of resources).
    Can you please tell me how I can set the default LAF to Windows for ALL users at once? (hopefully this will save a lot of resources on my machine)

    Thanks and regards,
    Fazzi

    • thatjeffsmith

      Each windows user gets their own set of preferences. Default look and feel is Oracle. That’s probably why you’re not seeing it applied.

Write A Comment