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,
I gather that there’s been a delay of some sort on 18.1. Will it possibly be out this week? Thanks,
Mark
Stay tuned.
Can we change the definition of a word in SQL Dev, so that Control+right arrow skips over underscores? It’s really a pain to stop at each underscore and makes navigation slower.
Preferences, Code Editor. Un-check ‘use change of case as word boundary.’
Awesome sauce.
Hi Jeff,
A SDDM question (v174):
Are there restrictions when importing a ddl file for updating comments (on RDBMS) on view columns from a ddl file into sddm?
Got a ddl file with two “comment on” statements – one for the view and one for a view column. Only the view comment is updated in the model. This is not expected – bug?
Importing a complete ddl file with the “create view” statement into a new model will also set the comment on the view column. So this works fine.
Thanks
Jo
Yeah, that sounds like a bug.
Hi Jeff,
I would like to export all DDL (Tables, Packages,Triggers etc) using SQL Developer (17.4.1) from Tools > Database Export menu.
I would like to export all DDL from a few schema (not my schema)
Is there the way that SQL Developer NOT also export my schema objects.
Thanks,
Binh Le – Greensboro NC
It by default will pick all objects of the said type you have checked on the wizard for the logon schema.
Easiest thing to do would be to logon as one of those 2 schemas vs using a 3rd schema.
If that’s not possible, make sure to pick at least one object of each type in schema 1 or 2 on the object picker.
If you don’t pick any views, but you have VIEWS enabled, it will export every view from the logon schema.
Jeff,
Was trying to navigate the Connections tree via keyboard per your ‘SQL Developer Trick: DropDown Vs Tree (Feb.27, 2012) and ran into an issue. The version of SQL Developer is 17.3.1.279 (Build 279.0537).
Cursor was on the database instance and pressed ‘O’. Cursor move to ‘Operators’ (yes, some of these could be hidden, but aren’t). Pressed ‘O’ again expecting the cursor to move to ‘OLAP Options’. Didn’t move anyplace. Pressed the ‘Right Arrow’ to expand ‘Operator’. Since there was nothing under it just the little ‘+’ sign disappeared. Pressed ‘O’ again and cursor moved to ‘OLAP Option’. Press ‘O’ again and cursor moved to ‘Other Users’. So why was the ‘Right Arrow’ required prior to the ‘O’ to go from ‘Operators’ to ‘OLAP Option’?
Jeff,
Were you able to determine if this is an error? I believe it should have gone from ‘Operators’ to ‘OLAP Options’ without requiring the ‘Right Arrow’ in between.
Hi Jeff,
Is there any way to import old SQL Developer settings into a new instance after the installation has been completed?
I installed a new version of SQL Developer over my old (‘saved’ in the trash) and during the installation process was asked by the installer if I would like to import my settings. Unfortunately other issues I was having with my desktop forced me to cancel that installation (strange problem unrelated problem with Windows updates where UI didn’t recognize my clicks). I deleted the installation and tried again after rebooting, hoping to see the import settings option again but was disappointed when I didn’t.
Justin
They’re all still there, you just have to copy them over.
Hello,
Is it possible to open from SQL Developer (Files view) a Windows Explorer directly on that folder? Like we have in IntelliJ menu Show in Explorer?
Thank you,
I don’t think so.
Hi,
Is it expected for SQLD 17.4.1 to run the “IndexPreferencesTask” in the lower right corner on every startup?
Thanks, Tim…
Yes.
HI
I’ve been using SQL dev. for many years but still i havn’t find out in what way the “connection selector” dropdown list in the upper right corner of the SQL Work is sorted, is it sorted at all?
Thanks
Gunnar
Open/Active connections first, then the rest alphabetically
Jepp so it does Now when you say so ;-))
Thanks!
We have a huge database with data of different categories of people and many procedures that manipulate the data. We need to provide an api for a mobile application- and other applications in the future- to retrieve data and execute procedures. We need to be able to validate that the request is allowed to be executed on the data it’s manipulating. We have our SSO system on which we’re going to rely for authentication.
>> We need to be able to validate that the request is allowed to be executed on the data it’s manipulating
The right place to do that would be in your PL/SQL code. Either in the POST handler code, or in the stored procedure it ultimately executes.
We thought it might be easier and more performant if it was to be called by ORDS automatically before each request.
One more question, what is the best way to return the response if the request passes the validation with minimal code?
No we’re not. Do we need to use it? and what implications it will have?
security.requestValidationFunction is there to make sure the RIGHT people are allowed to use the pl/sql gateway to execute stored procedures.
What are you trying to do with ORDS? Can you not just secure the resource? ORDS would then require authorization to be in place for every call to it.
Hi Jeff,
We’re using ORDS 17.4 without APEX. We need to do some validations for every request. How can we do that.
security.requestValidationFunction doesn’t seem to work. If it not available without APEX, are there any alternatives?
Your help would be much appreciated.
Are you using the PL/SQL Gateway?
Jeff,
I’ve never used Toad. But a couple users who have used Toad, but have lost that access, claim that they could create a ‘multi-database connection’ in Toad and write a query that selected data from multiple instances. Can SQL Developer do the same thing?
From one of the toads, Toad for Data Analysis or some thing, whatever they’re calling it now…
To do this in SQL Developer, one would use DB_LINKs.
We don’t offer client-side joining of data across jdbc connections.
Do you have any suggested processes, strategies or best practices for management of custom domains and types in SQL Developer Data Modeler that will be shared between several modelers?
Heli suggests keeping them in Source Control as well as your designs themselves.
She’s written an entire book on how best to use the Modeler, feel free to drop her a comment on further elaboration.
Hi Jeff,
I know SQL Developer 17.4.1 released on 3/7. Will there still be an 18.1 released later this month? Thanks,
Mark
yes
Thanks Jeff, but are there still issues with ‘Check for Updates’?
I’m on 17.3.1.279, I see that 17.4.1.054.0712 was released on 7/3/18, and yet when I do a check for updates it doesn’t show me any available updates?
I’m just checking the ‘Oracle SQL Developer’ Center at ‘http://apex.oracle.com/pls/apex/dbtools/usage/cfu’ that it is defaulted to.
I’ve tried with and without proxy, as std user and admin.
As an admin, it started up and told me there were updates, but now that icon (bottom right hand corner of screen) has gone away, and ‘check for updates’ is not returning anything.
Right, I’ve just found another post where you say the check for updates is for extensions, not the product – may be worth adding ‘for extensions’ to the menu option as it’s obviously confusing a few people?
Hi, Jeff,
This version 17.4.0.3555 has a bug.
When you right-click on an external table “edit”, the pop-up shows incorrect “table type”, it shows “normal”, all previous versions show “External”, the problem is: user cannot edit, because “external table properties” is not available.
Please take a look.
Thanks,
Dave
17.4.1.054 has the same issue.
I’ll take a look – but just a reminder for everyone – bug reports should go to My Oracle Support.
I filed a bug in January, fixed in 18.1
Bug 27344008 https://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=27344008 https://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=27344008 – SQL DEVELOPER 17.4 LATEST RELEASE NOT ALLOWING TO EDIT EXTERNAL TABLE IN GUI
When are we getting a book names “How to Jeff Smith with Oracle”
Mom, I already told you, no more comments on the blog!
And thanks, I think?
I’d like to share my snippets with a co-worker. Is it possible to export/import snippets?
No, but you can find the xml file they’re stored in and send it to them.
Hi Jeff,
Thank You for all your blogs they are really helpful. I have small question I would like to know how to check if my database is properly normalized currently at present we have lot of performance issues. I think it is because of poor database design how to verify the database design if it is tunned for optimal performance.
There’s no ‘is my model, right?’ test you can have the tool apply to your database. But there are rules, or best practices. But you need to look at if your db is OLTP, a DW, or mixed-use. Also, why do you think the perf is bad b/c of the design? If you have evidence showing that, the lack of a foreign key or a denormalized table would be easy to spot.
http://www.schemacrawler.com/lint.html
https://www.openhub.net/p/dblint
Thought you’d like this slightly amusing bug. Downloaded the latest sqlcl, as you can see. But…
C:\sqlcl\bin>sql
SQLcl: Release 17.4.0 Production on Thu Mar 08 09:15:34 2018
Copyright (c) 1982, 2018, Oracle. All rights reserved.
New version: 4.1.0 available to download
Username? (”?)
Hmmm, I’m not seeing that, but check for the note. I’ll keep my eye out.
I’ve created a logical model & engineered it to a relational model (using standalone Oracle Datamodeler, not as part of Sqldeveloper; version is 4.0.3.853). My destination db is Postgresql. How do I set up Datamodeler to generate Postgres-syntax scripts?
You don’t. Best you can hope for is generic ANSI SQL standard DDL.
How to enable TNS encryption and data Integrity in SQL-Developer connection. I did the required changes in sqlnet.ora on client and server but the sessions to database using SQL-Developer are not showing as encrypted one while SQL* Plus and TOAD one are shown in v$session_connect_info. Are there any special settings required?
SQL Dev is not using sqlnet.ora – we’re jdbc. So you can setup SSL for JDBC or you can tell SQL Developer to use a THICK connection to go via OCI, which will then pick up your sqlnet.ora settings.
Thanks it works using THICK connection to go via OCI. Do you have steps to setup SSL for JDBC?
Hi Jeff, does SQL Developer needs user admin rights to function properly under windows 10?
At some point in time program started reporting “…Could not load connection type:Oracle null ”
(connection preferences, export sql etc…).
This was 4.1.3 but tested with 17.2 and 17.4. versions with same results
All above versions work when running as user with admin rights.
thanks
You just need read/write access to the AppData folders for your Win OS user.
thanks for answering, the problem was solved by not importing the settings from the previous installation (running as admin was working because importing was not done as well, it looks like admin privs had nothing to do with the problem but something with the application user settings…
it works now 🙂
thank you again
Excellent.
Hi Jeff, I am still having the issue in my sql developer showing error while launching “http://apex.oracle.com/pls/apex/dbtools/usage/cfu”.
Since I have configured it for PosgreSql as well some how I am able to run queries but when I wanted to export output the Export Wizard is failing with error “java.lang.NullPointerException”
Would you please help me here to get this corrected.
Can you export from an Oracle database?
Here is my insert querry:
insert into nametable
(id, last_name, first_name)
values
(12345, ‘LeBlanc’, ‘Sarah’);
select * from nametable where id = 12345;
Here is the return:
12345, Leblanc, Sarah
What should I do so that the field displays what I inserted. I want to see
12345, LeBlanc, Sarah
Advice, please. Thanks.
Xiaoling
I’m confused, you’re showing the same values for both the actual and desired results.
I expect to see ” LeBlanc”, not ” Leblanc”.
Show me. Based on normal circumstances, that’s not possible.
Here are the last name from a source table:
LeBlanc
McFarland
de Kok
After inserting query, the last name from the designation table become:
Leblanc
Mcfarland
De Kok
I wish I could paste my picture here, but this form does accept my pictures. I think it must have something to do with the designation table (setting? trigger? etc. )
What do you think?
Something is getting in the way of the data going into the table. A Trigger would be one thought – does your table have any triggers?
There are several triggers attached to the designation table, but nothing looks like formatting name (such as first letter has to be an upper case and the rest lower case).
For instance, the following trigger is the only trigger which has “last_name” involved.
create or replace TRIGGER “SATURN”.”ST_SPRIDEN_NAME_COMPRESS”
BEFORE INSERT OR UPDATE OF SPRIDEN_LAST_NAME, SPRIDEN_FIRST_NAME, SPRIDEN_MI
ON SPRIDEN
FOR EACH ROW
BEGIN
:new.spriden_search_last_name :=
gukcmpr.f_compress_name(:new.spriden_last_name);
:new.spriden_search_first_name :=
gukcmpr.f_compress_name(:new.spriden_first_name);
:new.spriden_search_mi :=
gukcmpr.f_compress_name(:new.spriden_mi);
:new.spriden_soundex_last_name := soundex(:new.spriden_last_name);
:new.spriden_soundex_first_name := soundex(:new.spriden_first_name);
END;
Do you see anything I missed here?
Thanks.
gukcmpr.f_compress_name() -> that looks like a function call. Go see/test what it’s doing.
Thank you, Jeff, very much for your advice and time. I will find out what that function does.
Bye.
Hi,
We have downloaded the following versions of SQLDeveloper and SQLcl
– sqldeveloper-17.4.0.355.2349-x64.zip
– sqlcl-17.4.0.354.2224-no-jre.zip
We have a secured environment and need to supply a PGP Signature or SHA-1 hash or MD5 hash for any files to be imported into the environment. Do you know where I can find one of these signatures for the above files?
md5 hash for your sqldev install is (856c70371862f2f404aff1aed2400cbd) – this is on the downloads page