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.
- 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,793 Comments
Hi Jeff,
Running 18.3 . Context menus, right-click, on Scheduler->Jobs->my_job_name is not working. Worked 18.2. Also Also opening an edit table dialog for external table opens the table as a normal, so cannot edit external table properties. Service Request 3-18530146921 – ‘ reappearance of Bug 27344008
Any chance for patch before 18.4?
We don’t do patches…so i would go back down to 18.2
One of my team member recently found a bug in SQL Developer. Did anyone come across this and has any fix?
– User connects to the DB from sqldeveloper
– Change the schema password outside the SQLDeveloper
– Kill the DB session or user leaves his desktop in sleep mode
– When user comes back, he/she tries to run the query from worksheet assuming he is already connected to the database. SQL Developer never shows ORA-01017 and keeps trying to re-connect in the background leading to too many unsuccessful logins.
In a typical controlled environment, where we don’t allow more than ‘X’ number of unsuccessful logins, this bug is causing user accounts locking.
That’s not a bug, I don’t think. We do try to bring the session back. When you run the query, and we see the connection is gone, we attempt a silent reconnect.
Now, this part:
>>too many
How many do you see SQL Developer trying?
There seems to be a bug on your side too – how is your user supposed to know their password has been changed?
Thanks Jeff for the quick response. Every time user tries to run the query from worksheet, it makes two login attempts. Since it doesn’t throw any error message (ORA-01017), user keep retrying and ultimately end up too many failures.
Yeah, that’s a bug.
Thanks for the report.
Oracle support filed Bug 28801256 : SILENT RECONNECT TO RUN SQL ON WORKSHEET WITH LOST CONNECTION DOESN’T SHOW for the issue.
No, I FILED THAT BUG 🙂
Hi Jeff,
I use SQLcl command line tool a lot (on Linux) .
I use the csv format a lot because I find it handy to extract data for small tables into csv files and then use that in excel.
I wanted to know if it’s possible to have a similar feature while printing the refcursor outputs (ie. output the refcursor records in CSV format ?
Regards,
Mohit
Sorry, no…but you could of course always program your plsql to spit out the refcursor that way.
Jeff,
I have installed the latest version of oracle sql developer.18.3.XXX.
When I format the SQL it adds spaces around operators:
a. select a. from … becomes select a . from… –> there are space around . etc.
b. select a.1,a.2 from .. becomes select a . 1 , a . 2 from .. –> there are spaces around comma too.
There is no way to control this when the previous installation preferences was imported.
Is there any way to re-set these preferences so that they go away while using the formatter?
Thanks,
Ravi
We found a bug with the custom formatting preferences. Reset your formatter preferences, and you should be good to go.
Is it possible to retrieve data for a report from a package?
I’ve got a fairly simple procedure in a package that’s used in other programs and it works fine there but I’ve been asked to get just that part so I was hoping I could use a report. In the sql for the report I tried a simple call to package like I’d use when calling it from another part of the package and I get an error saying it ‘Cannot perform a fetch on PLSQL statement: next Vendor Code 17166’.
Thanks,
Jeff
Plsql reports are looking for DBMS_OUTPUT.
Can you use SQL to call your pkg and get the data?
Hello, Jeff. You are always great to help and fast in your reply. Here’s a question:
I have 6 relational models that I pulled in from various data dictionaries. These 6 models all reside under the same design. I have been doing compares between them using the Compare/Merge Models utility. Two of these models I’ll call Alpha and Beta, respectively. When I select Alpha as the SOURCE model and Beta as the TARGET model for a compare, I get some tables that show up in Alpha but not in Beta which I expect. However, when I flip the order in the tool (i. e., Beta as the SOURCE and Alpha the TARGET), those tables do NOT show up in either Alpha or Beta). So I wonder why these delta tables only show up in Alpha when I do the first compare, but do NOT show up in Alpha when I do the second compare. It’s the same relational models I am comparing – I just expect to see the ‘delta’ tables to always show up in Alpha regardless whether I pick Alpha as the SOURCE or TARGET. What am I missing/doing wrong? Thank you. = Matt
New version 18.3 does a ‘special’ format!?
A blank before + behind the dot (.) and before the comma (,)
SELECT
cy . comp_sk ,
cy . comp_sn ,
cy . reg_sk ,
cy . comp_sk ,
cy . comp_sn ,
In 18.2 this was not done!
Is there a way to chnage this behaviour in 18.3?
That’s no good.
I’ll take a look in a few hours.
Can you Export your formatter preferences?
Then reset them to their defaults, and readjust to your liking – that should fix it.
Then send me your export.
Hey Jeff!
In versions 18.2 and below, connection folders were shown at the top of the connections list, and then all the connections were ordered alphabetically below folders.
In 18.3, folders are ordered together with connections, so, they are no longer at the top of the list, but spread over the connections list.
Is there a way to change that?
Easiest way would be to number your folders in the name, 1 comes before A in the alpha sort scheme.
Yes, that is why I did, but feels like a workaround.
Is that intended behavior, or a bug?
It doesn’t make much sense to me, TBH.
I mean, the change from one scheme to the other one is what doesn’t make much sense to me, because it had been the other way for as long as I can remember.
But anyway, will have to adapt.
Best,
I’d have to ask the developer if it was intentional or not. I would guess it was unintentionally intentional 🙂 But we will be making tweaks in 18.4 based on feedback of 18.3 – this will definitely be taken into account.
Haha, great to know.
Thanks!
Hi Jeff,
I cannot connect to the database with SqlCl.
It returns the following error:
Username? (”?) nwe
Password? (**********?) ******
USER = nwe
URL = jdbc:oracle:oci8:@
Error Message = no ocijdbc18 in java.library.path
USER = nwe
URL = jdbc:oracle:thin:@localhost:1521/orcl
Error Message = IO Error: The Network Adapter could not establish the connecti
on
USER = nwe
URL = jdbc:oracle:thin:@localhost:1521/xe
Error Message = IO Error: The Network Adapter could not establish the connecti
on
Username? (RETRYING) (‘nwe/*********’?)
Oracle is installed on a server where there is also the tnsnames.ora.
I’m trying to connect from a windows virtual machine.
With SQL Plus I have no problem connecting just specifying the username, password and the database name or alias.
But SqlCl is using other Oracle Home, I guess.
How can I change the Oracle Home for SqlCl? Or how can I connect like I do it with SQL Plus?
Thank you!
George
what’s your connect string?
if you open sql with /nolog and run show tns – what comes back?
In SQL Plus I have the following result:
SQL*Plus: Release 10.1.0.5.0 – Production on Fri Oct 12 14:16:52 2018
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> SHOW TNS
SP2-0158: unknown SHOW option “TNS”
SQL>
In SqlCl I have only the option to insert the username and password. I don’t know which Oracle Home it uses by default or to which database it tries to connect.
From where can I change the defalt database in SqlCl?
This is all I can enter at SqlCl connection window:
SQLcl: Release 18.3 Production on Fri Oct 12 14:20:55 2018
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Username? (”?) nwe
Password? (**********?) ******
What em I doing wrong?
Thank you!
George
Wow, your client is like 15 years old!
Show tns is a SQLcl command.
SQL /nolog
That will get you into SQLcl without logging in. Then you can try show tns.
I tried this in SQLcl:
SQLcl: Release 18.3 Production on Fri Oct 12 14:47:10 2018
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Username? (”?) SQL /nolog
USER = SQL
URL = jdbc:oracle:oci8:@
Error Message = no ocijdbc18 in java.library.path
USER = SQL
URL = jdbc:oracle:thin:@localhost:1521/orcl
Error Message = IO Error: The Network Adapter could not establish the connecti
on
USER = SQL
URL = jdbc:oracle:thin:@localhost:1521/xe
Error Message = IO Error: The Network Adapter could not establish the connecti
on
Username? (RETRYING) (‘”SQL “/*********’?)
Also not working.
Something is not good…
c:\SQLDev\18.3\sqldeveloper\sqldeveloper\bin>sql /nolog SQLcl: Release 18.3 Production on Fri Oct 12 08:22:30 2018 Copyright (c) 1982, 2018, Oracle. All rights reserved. SQL> show tns TNS Lookup locations -------------------- 1. USER Home dir C:\Users\jdsmith Location used: ------------- C:\Users\jdsmith Available TNS Entries --------------------- LISTENER_ORCL12C ORCL ORCL12C SQL>I’ve asked, and have never seen, what’s your connect string?
Does this work?
SQL user/password@server:port/SERVICE ?
Jeff
Hi Jeff,
After using SQL Developer Data Modeler v4.2, many DMRS Tables, Views etc are created. How can we drop these DMRS Tables, Views etc,.
Thanks & Regards,
Riaz
So you setup the Data Modeling Reporting Schema?
Go back into Data Modeler, File, Export, Reporting Schema. Then look down at the Maintenance tab.
Jeff,
Thanks for the quick reply. But I need to drop the Tables, Views etc., starting with DMRS.
Thanks & regards,
There’s a button to click that does that for you.
Hi Jeff,
I had to install SQLDeveloper fresh and after that, I can’t find the settings for “autosave” worksheets any more. What I mean is, that in my last installation, when I worked in a worksheet, after a while, there poped up a window to save the worksheet. This feature was very comfortable and secured my work.
Regards.
‘Save All when Exiting’ in preferences – but I can’t remember a time we had an auto-save feature for files.
How do I get SQL Developer to save window layout changes? When I open SQL Navigator by default the Connections window is a sidebar on the left side. I like to have it on the right side so I drag it over there. However, the next time I open SQL Developer the Connections window is back in its default position on the left side. There are other changes I like to make to the layout but they don’t save and whenever I open SQL Developer the default layout appears. It’s rather annoying.
I’m running Version 18.2.0.183 on Windows 10.
Thanks in advance.
That’s how it should be working today. If it’s not, i wonder if we’re not able to save changes to the XML file controlling this – which we store in an AppData folder under roaming profiles for your OS user.
I found the sql developer folder under app data and checked the properties. Read Only was gray so some files may have been read only. I cleared the Read Only box and clicked OK. Now it seems to work okay. So maybe the file was read only for some reason.
EXCELLENT
Hi Jeff
Subject: Oracle SQL Developer Data Pump Export Wizard.
I want to know how to specify the version parameter in the Oracle SQL Developer Data Pump Export Wizard.
For example, I want to export a schema from a 12.2 database and import the data dump into a 11.2 database.
Thanks in advance.
Vadi, Bengaluru, India
I don’t believe you can.
What you can do is walk the wizard, at the end. click on the PL/SQL page. Copy that to a worksheet. And add the missing bits you require…datapump_set_parameter(something);
Hi Jeff, I’ve a question about Oracle SDDM, specifically related to “folding” FK attributes. I frequently model with identifying relationships in the LDM. If developer supports the approach then the model enforces some important business rules. If they don’t support this pattern then they have the business rules encoded there within the LDM.
However, the keys on great-great-grandchildren entities gets horrendous. Its hard to manage and loses meaning as a communication tool. Engineering to a relational model is yielding some Is there a way that I can ‘fold’ the attributes within the LDM? I’d like to present the folded attributes and name them meaningfully for the reader while retaining the association between the attributes and the relationships they implement. (I accept the consequences of overloading attributes with >1 FK constraint)
Thanks
I deferred your question to my development manager for SDDM, and his reply…
I don’t understand the problem. FK attributes can be named “meaningfully for the reader”. Overlapping attributes can be marked to be folded (or not) during engineering to relational model.
Here Division_id is propagated to Class_Enrollment through two different paths producing FK attributes Student_Division_ID and Course_Division_ID. They can be marked to be folded when engineering to relational model. FK attributes are not shown when Barker notation is used.
Hi,
I, as an independent consultant, use SQL Developer extensively and exclusively. I have been using latest version of 18.2 as of this writing.
What I love most about SQL Developer, again as an independent consultant who does not get a licensed copy of TOAD, is that it is FREE to download and use.
Besides using Cloud Control, if and when made available/accessible to me by a client, I have written several ( more than 500) scripts, in User Defined Reports, to get information about client databases (size, configuration, and a whole lot more) quickly and in a format that I like. These scripts also help me generate SQL statements commonly used to update/lock/unlock/delete/export/import optimizer stats, copy SQL profiles from one database to another, rebuild indexes etc. without having to remember syntax with all the possible options.
Having said that, I would love SQL Developer more than I already do if,
1. User Defined Reports: Have a fully function editor with below functionality when writing User Defined Reports
a. copy/paste
b. redo/undo
c. find/replace
d. save/save as
2. User Defined Reports: When I write Child Reports, I would like to have an ability to move them up and down so I can reorganize them. Currently, it does not allow me to do that.
3. User Defined Reports: PDF option for Master or Child Reports – Ability to use macros or variables to populate PDF description section such as Title, Subject dynamically using information from Main Report and/or Child Report.
4. User Defined Reports: Allow me to save individual Master Report along with its Child Reports as an HTML document.
Hopefully, I will get to see some or all of these features in future releases of SQL Developer.
Thanks
Anand Mahajani
Does the SQL Developer team have a feature in the pipeline to take advantage of MacOS’s new Dark Mode feature?
Set your look and feel to OS X, and we’ll adopt whatever the rest of your OS is doing – theoretically Dark Mode as well.
Tested this claim, and SQL Developer did not switch to a “Dark Mode” on 10.14.
Then my hunch/hopes have been dashed.
To answer your original question, there are no plans for custom work to support this OS X feature. To give you a bit of background, less than 5% of our users have Macs.
Will the upcoming plan from Oracle to charge a license fee for Java SE have any impact on the embedded JDK that SQL Developer uses?
What we embed will continue to be free.
Hi Jeff,
We recently migrated from version 4 to version 18.1 and in a script I run daily, I have the command CLEAR prior to any SQL. When I run this script in version 18, the output displays all of the parameters for the CLEAR command:
CLEAR
—–
CL[EAR] option
Where option represents one of the following clauses:
BRE[AKS]
BUFF[ER]
COL[UMNS]
COMP[UTES]
CONTEXT
SCR[EEN]
SQL
TIMI[NG]
In version 4, this did not happen (no parameters for CLEAR were displayed). Is there a setting in version 18 that turns off this parameter display? I appreciate the feedback.
4.1/2 gave us the entire sqlplus script engine aka SQLcl.
So now you need to clear screen
Hi Jeff,
I tried to generate swagger docs from ORDS modules, but the security (authorization section), is not generated automatically if the module is protected. Is there anything to do.
Regards,
Omar
Hello Jeff,
I’m using the data modeler feature of SQL*Developer 18.2.0 to reverse engineer the relationships for a given table. The reverse engineering process works fine so far, but I’ve noticed a few problems:
1. There appears to be no way to rearrange the entities and their relationships after the E-R diagram is generated. Even the steps you mentioned in this link did not work:
https://www.thatjeffsmith.com/archive/2013/01/configuring-display-of-model-relationships-in-oracle-sql-developer-data-modeler/
2. How do you zoom-in and / or zoom-out after the reverse engineering process is done? It seems like the diagram is frozen.
Any ideas?
Thanks.
-John
Something is wrong with your design. You should be able to select anything on the diagram, and move it with either your mouse or the keyboard. Do you see any error messages in the Log panel?
What OS and version of Java are you running?
Hello Jeff,
I figured out the problem. After I click on the “Model” tab, there are 3 buttons and 1 drop-down menu at the top: “Freeze Content”, “Refresh Diagram”, “Copy to Data Modeler” and “Available Actions”. I didn’t see the “Copy to Data Modeler” button. Once I clicked on it, I was able to rearrange the objects / links without issue.
Thanks for your time!
-John
Hi Jeff
we are using ords 17.4 , without (Apex and PLSQL gateway) .
is there any way to get client IP in GET handler ?
Thanks
I’m traveling today, but have a look at
https://community.oracle.com/thread/889311
I am connecting to Oracle through SSH tunnel. It worked fine till today.
Now I am getting
An error was encountered performing the requested operation:
IO Error: Connection reset
Vendor code 17002
We don’t have Oracle client on our machine. We connect directly from SQL developer to DB through SSH tunnel
Please help
Check your server logs – but also, are you using putty or are you using our SSH connection type?
Hi Jeff. In our company we have a bunch of developers who use PL/SQL and others (like me) who prefer Oracle SQL Developer.
Regarding variable substitution, when I use “&” in PL/SQL, it have the same behavior that “:” in OSD.
Sometimes we have to share scripts that uses variable substitution, and when I run a statement in OSD with “&”, it prompts for substitution, however, different from “:”, it does not store the last value used, and it prompts for the same variable as much times as it appears in the query statement.
Is there a way to configure OSD to behave with “&” the same way it does with “:” ?
Thank you.
You need && if you want that behavior – check the SQL*Plus docs, that’s how we treat & vs && for substitution values.
Hi Jeff,
I am running a Delivery web app on APEX, ORDS and Tomcat latest releases, Oauth2. I am moving from MySql, nodejs and codeignitor PHP framework, JWT. So
1. How can I get the user from the access_token, I need to identify the user posting the order so I can record his ID with his own orders.
2. Can I change the the ORDS 401 page returned from the https://…./oauth/token, in order to get the user a JSON object rather than html page?
Thanks in advance.
Omar
I need to check the docs but I think you can use :current_user
Tomcat is responsible for the 401 page, right?
Hi Jeff,
Please kindly do, I am stuck here, is :current_user will return the client_id?
I am getting back the ORDS page, I didn’t change Tomcat.
Regards,
Omar
ORDS sends back a 404 status, but it’s tomcat’s page.
Try :user or :current_use, I know it’s one of those.
Example here.
Hi Jeff,
:current_user worked fine, regarding the 40X pages, I couldn’t send an image but here is a 404 page, is this from Tomcat or ORDS?
Oracle REST Data Services
404 Not Found
The request could not be mapped to any database. Check the request URL is correct, and that URL to database mappings have been correctly configured
Hi – is it possible to get SQLDeveloper on a Mac to load UserSnippets.xml from a file on a dropbox folder? I’ve seen posts that suggest adding something like
AddVMOption -Draptor.user.snippets=../../mysnippets/snippets.xml
to the sqldeveloper.conf file, but the examples are always for Windows. I can’t find sqldeveloper.conf on the Mac installation.
Thanks
On a Mac, there is a . sqldeveloper folder in your $home directory
Thanks – I found the directory, but there’s no sqldeveloper.conf file there.
Do I just need to create one and add a line like AddVMOption ?
Show me.