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.
- 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.
- 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.
- 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
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.
There is a festure to generate custom/replacement DDL – I would give that a look.
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!)
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.
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
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.
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.
You’re looking in the wrong place, jobs are listed in your schema, alongside tables.
Oh I had to go into preferences in Database/Navigation Filter and check Scheduler. Thanks
Oh great, happy to hear you found it!
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?
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?
Also I just realized you’ve taken the time to leave more than 100 comments/questions here. Thanks! I appreciate the time and consideration.
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?
Right click on the column headers and select ‘Delete Persisted Settings…’
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
You need to login as EFMSI to run that, or use ORDS_ADMIN package instead if you want to enable someone else’s account.
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
PDB installs are much simpler, so not surprised there.
Maybe the config “didn’t take?”
I have step by steps here
Update:
Installed ORDS 22.4 and PDB Lifecycle Maagement now works!
Great!
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.
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.
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.
If curl works and your python program doesn’t…
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.
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
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.
“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
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.
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
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
So you built your own paging for PLSQL endpoints?
About row counts…
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?
Sorry for missing out – To expose the above stored procedure as ORDS
Yes, of course.
Click above on where it says ‘REST APIs’ – then under examples see my posts for PL/SQL and ORDS.
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
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.
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
not data pump, just save the contents of the tables as INSERT statements
or or or…
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.
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.
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?
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,
That’s where all our data is…you could also write a loop and cycle schemas via the ORDS APIs to export your schemas.
Sorry, that reply was to the wrong thread 🙁
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.
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.
Thank you Jeff for your time and input. I will give those a try,
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
I like httpie on the Mac for CLI, but use Insomnia mostly.
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) ?
Sorry, I haven’t touched perl since like 2007. I imagine someone in the WordPress community would have good ideas/thoughts on this though.
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.
Ctrl+R
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
22.2.1 is latest.
What version database are you connected to?
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?)
It’s in an XML file under each Roaming Profiles AppData directory, for each USER on a machine
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
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.