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,829 Comments
There are a couple of posts on your site about SQL Developer slow load times. So I tried disabling extensions and changing the look and feel to Windows and it made a difference. My problem is the response times if I have just left SQL Dev open for some hours and then return to do some work
1) It is likely the remote database will have ended my session, I can wait up to a minute for the ‘Disconnect’ menu option to actually work
2) If I close SQL Dev down, again I can wait up to a minute for it to wake up and begin the shutdown process i.e. ask if I want to save files
All advice greatfully received
I’m running 64bit on Windows 8.1
Thanks
Steve
Hi Jeff,
Once again thank you for sharing you knowledge with us!
Maybe I found another bug while saving (exporting) sql results.
I tried to overwrite an existing xlsx File.
Export ==> Chose Folder ==> Order results in Fold by Date (newest first) ==> choose one of the existing files ==> press store (speichern I am using the German Version) ==> in the next window a completely other file is chosen and the other file will be overwritten.
I stored a snagIt video capture on my computer. If you need it just write me a mail and I will send it to you (~5MB).
Look below for my sql Developer Version.
Thank you for your help!
Kind regards,
Markus
—-
Oracle SQL Developer 4.0.3.16
Version 4.0.3.16
Build MAIN-16.84
IDE Version: 12.1.3.2.41.140908.1359
Product ID: oracle.sqldeveloper
Product Version: 12.2.0.16.84
Version
——-
Komponente Version
========== =======
Oracle-IDE 4.0.3.16.84
Java(TM)-Plattform 1.7.0_55
yeah, post that somewhere online and send me the link or email the video to me at [email protected]
The file picker we use has been updated in v4.1 – so you could try the Early Adopter and see if you can repeat it there first…
I have to change windows user due to a domain change at work. I have saved my connections, user defined extensions and reports, but can I save my snippets? Can I also save the other preferences I have set up?
I am using both 4.0 and 4.1 of SQL developer
just grab the entire sqldeveloper directory under appData, roaming profiles – that has everything sqldev stores for your user, then copy it to your new account once you have it setup
Jeff,
Can you help me on these two items, that i need for an application data modelling.
#1 – I can see the option of defining B*Tree and Hash clusters in (physical) data model? but i dont see the option of defining Sorted Hash clusters.
#2 – In the physical model , i have seen how to define column level encryption, but i dont see the same(encryption) at Tablespace level, is that not possible to do in SQL Developer Data modeller
Hi Jeff,
If I have a number of programs that might/might not use a particular table, is their a way like audit trial that I can set up on that specific table and log the details like, which program used that table for what purpose (like select, insert, update etc. ) ?
In one line, Can I set up something on Oracle SQL Developer, to know dependency of various programs on a specific table ?
Using auditing to build a dependency tree assumes that your users will 100% exercise the program and thus find ALL of the dependencies.
In one easy answer, I will say ‘no.’ But there are various things you can try to solve this puzzle. If you have access to the source code, I’d grep it for references to the table, and then be aware of dynamic SQL where that won’t work…
Thank you Jeff..
Hi Jeff,
In SQL Developer 4.1 I am no longer able to find Application Express node under my tree. Am I missing something ?
Thanks
Alex
Weird, it’s there for me. Did you turn it off? Preferences > Database > Navigation Filter
After some investigation, I found that Application Express node is there for an 11g(11.2.0.3.0) database, but not for 10g (10.2.0.4.0).
Can You confirm?
Thanks
Alex
Hi Jeff,
Is there a way to remove duplicates from tables using sql developer? Any command/short-cuts or feature?
If not, could you please help me delete duplicates records from a table that has 19 columns and around million records?
Thanks!
Hetal
Yes, using SQL.
Find duplicates..
[sql]
Select HIRE_DATE , count(*) as Occurrences
from HR.EMPLOYEES
HAVING count(*) > 1
group by HIRE_DATE;
[/sql]
So this assumes that HIRE_DATE should be unique – but maybe you have duplicates on hire_date. Silly example, EMP_ID probably makes more sense, but you get the idea.
Then for those records, get the ROWIDs, and delete the ones that are the offenders.
Thank you Jeff
I’ve done a little searching and haven’t quite found what I’m looking for. In SQL Developer, I am exporting a data set from a query that is quite long running and returns lots of rows of data. When the query first returns, I click on export and put the spreadsheet information in. When I click “Finish” and it starts running, there’s a dialog box the shows it’s running and how long it’s running. That dialog box has 2 buttons, “Run in background” and “Cancel”. The “Run in background” is grayed out. This particular query/dataset takes about 30 minutes to export to file. In the interim, all of SQL Developer is locked up. Is there a way to get around this?
I think this will give you what you want
Thanks much! I knew Ctrl-end would load all the data into the output area. What I didn’t know was that it re-ran the query if you hadn’t loaded all the data. That solved my problem.
I really appreciate your responsiveness to these questions/comments. I’m very impressed that as product manager you take the time to do this. You should tell your boss you deserve a raise. 🙂
I have a question that is
I have a table named Emp which has million of rows.
I need to partition the table by Range Or Hash Or List but problem is this that table is exist with data.
Is it possible when table is exist with data, if Yes then how plz explain ?
You’ve got two options basically. You could use a Create Table as SELECT and build a NEW table with the partitioning scheme, and then drop the original table and rename the new one to be the same name as the original, or you could use DBMS_REDEFINITION package to rebuild the table as a partitioned table. The latter option is more involved, but your table remains online throughout the process.
Hi Jeff I have downloaded 4.1 Early Adopter But on Launching the sqldeveloper.exe the following error message occures & the application closes.
”
Unable to find main class.
Class specified is: oracle/ide/osgi/boot/OracleldeLauncher
”
for your Information I am using Win 8.1 x64 and Oracle 12c.
Sounds like a bad download. Delete what you put down, download the zip again, and try again.
Thanks Jeff finally I was able to run the application I just Deleted Old folders and that worked for me.
how to develop sql query in worksheet based on sqlbuilder like logic to build query with joins
Try the Query Builder.
Hi Jeff I am using SQL Developer v4.0.3 build 16.84 with my Oracle 12c
declare rc sys_refcursor;
Begin
Open Rc For Select * From Mcity;
sys.dbms_sql.return_result(rc,true);
end;
it alway gives me Error.
Error report –
ORA-29481: Implicit results cannot be returned to client.
*Cause: An attempt was made to return implicit results to an older version
client that is not capable of receiving these implicit results.
*Action: Upgrade the Oracle Database Client software.
Which version should I use for implicit results ??
You need a version of SQL Developer that uses a 12 or higher JDBC driver. Version 4.1, which is now available as an Early Adopter on OTN, ships with a 12.1.0.2 driver, so this should work just fine.
Hi Jef,
You have been doing great job.
I need your help regarding Unit Testing Import from XML files. I save (export) Unit test for each version of a same function in my repository (SVN) in form of XML files. Later when I import from the saved XML files, the startup, teardown and validation steps keeps on getting accumulated from each import version.
Is there a way to have the startup, teardown and validation steps for the latest verion only i.e. a way to overwrite the startup, teardown and validation of previous versions of the same function. Thanks,
Munib
Hi Jeff,
hope u had a wonderfull xmas (wich means lots of beer, food and presents).
to my opinion an auto-update would be a great improvement for long time using of sql-dev.
in my case i support lots of customer installations. on every system an seperate instance of sql-dev is installed. it’s an anoing work to update all installations one-by one. again i’m a kind of a lazy guy. so very often i use an older version, just because i do not want to log in, download and unpack the most recent version.
happy new year from good-old-germany
peter
You could build an msi pack to push to your windows machines…I’ve not done it, but we’re pretty simple.
The problem with auto-updates is we require the user to go through the EULA via Oracle Single Sign On. But I agree, it would be the ideal scenario.
hi,
why not correct Display tables on non-english from a database link in SQLDeveloper?
I don’t know. There’s not enough information from your question for me to give a good answer. I can say that we can’t display characters correctly if you don’t have a good font selected in the Preferences under Code Editor…
Jeff,
I can see how to define ENABLE NOVALIDATE on primary key / foreign key column in the physical Model.
Can you please help me to how to define a RELY ENABLE NOVALIDATE constraint in the data model ?
You have to create and open a physical model
Hi Jeff, do you know if SQL Developer is capable of running more than one query at the same time? It would be really helpful to allow separate query threads, so that you don’t need to wait for one query to finish before starting another. Is there a setting I’m missing? Thanks!
Yes, you can. You just need to open an unshared worksheet to get the ‘separate thread’ support you’re looking for. I have a post here on how to do that. Let me know if you can’t find it.
Thanks! that works
Hi Jeff,
I love SQL Developer and have been using it since the early Raptor days.
A few month ago I stumbled accross a strange oddity for which I couldn’t find a plausible explanation – here’s a summary:
http://spotonoracle.wordpress.com/2014/07/13/sql-developer-oddity-on-windows/
I’ve also raised an SR with Oracle Support which resulted in Bug 19247691. Unfortunately, the bug is now in status “33 – Suspended, Req’d Info not Avail”.
Please excuse me approaching you here directly on your website regarding this issue, but I thought maybe you can shed some light on this.
Thanks a lot!
Beat
I got nothing on Mac, and I got the ‘box’ on Windows 7.
What font do you have set in preferences under, Code Editor > Display?
Hi Jeff,
In all tests I’ve used the standard setting in SQLDev – font is “DialogInput”.
This problem reproduces any time in any version of SQLDev and Oracle DB version. In the meantime I have run this test at about 8 different environments with the same result.
Thanks,
Beat
And…?
Hi Jeff,
On all the Windows – SQLDev setups I have tested I always get right single quotation mark instead of the ‘box’. I even asked some friends to run this query in their environment, all have reported the behaviour I described. Do you know which parameters might influence this behaviour, Windows settings? JVM settings?
Thanks a lot.
Beat
Somewhere i read installing sql developer 4.1 requires jdk 8.
I have jdk 7,8 on this pc. I also read that there is a configuration file
pointing to the correct jdk, 8 in this case. WHERE IS THE POINTER??
4.1 does require Java 8
Are you on Windows or Mac?
Hi Jeff
Apologies if this has already been asked but I have not been able to find an answer. I run a data export from SQL developer version 4.0.2.15 to excel every week. Can this be scheduled ? If not I have done the following extract to a CSV file which works almost as well, I just have to remove some spurious Line Feed characters.
spool “C:\MARS CATS Extract\ccj.csv”
SELECT /*csv*/* FROM MARS_CATS_OUPUT ;
spool off;
How do I put the above in the scheduler to run ?
Thanks Chris
if it’s just a select * from table, then I would use the cart.
Create a cart.
Then use the command line interface (SDCLI) to export the cart to the file type of your choice – including Excel.
In version 4.1, we’ll have a new command line interface, currently called ‘sdsql’ – where you can connect and run queries and do spools. That could easily be scheduled using native OS stuff.
If you want a database job today, you’d have to create a dbms_scheduler job to run a program, sdscli, to export the cart to the Excel file(s).
This can also be done with stored procedures, but you’d need to write the code to pipe the data out in the format you want.
I am using SQL Developer 4.0.2.15 . I am running a script that works in TOAD but not in SQL developer.
In SQL Developer – I get this error: ORA-01858: a non-numeric character was found where a numeric was expected
01858. 00000 – “a non-numeric character was found where a numeric was expected”
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation
The code I run has a to_date(CC.ADMIT_DATE,’dd-mon-yyyy’) <= '30-NOV-2014'
SQL*Plus is the gold standard, not Toad.
9x out of 10, these issues come down to different NLS settings. What are your NLS_DATE_FORMATs set to in SQL Developer vs the other tools?
Thank you !…you identify the issue. I modify my NSL setting in SQL Developer.
The code now runs in both TOAD and SQL Developer.
Hi,
I was just wondering why was feature request for “EDITABLE Result Sets” rejected? It’s one of the biggest complains from my co-workers when I advertise using SQL Developer (instead of SQL Navigator for example)…
It’s a little annoying to me also, having to switch to table => data to change something I found needed changing using SQL from worksheet.
Other than that – great tool, great job!
Regards,
Bruno
Did you read this post?
I did – it’s a cool workaround I use daily, but I still miss being able to edit result set directly. And I know “i can’t always get what I want” 🙂
I was just curious why this request was moved from “Under consideration” (or something like that) to “Rejected”, and whether it’s rejected for good or there’s a chance you’ll implement it somewhere down the road? And what is (technically) biggest issue that makes is hard to implement?
Thanks,
Bruno
I moved it b/c I’d rather be honest with users than string them along. There’s zero chance we’ll build this.
It’s not a technical problem – of which there are many including knowing what could be edited or not via ROWIDs, it’s a philosophical one.
It doesn’t GROK for us that one would edit the results of a query. You edit the contents of a database. Once the data is in the worksheet, it’s no longer part of the database. And it invites BIG oopsie-daises from end users that might not realize what they’re doing.
You’re only a ctrl-click and a copy/paste away…we know you like this feature, but we’re not going to turn SQLDev into a ‘Toad-clone.’ Or a SQLNav-clone 🙂
Fair enough Jeff, thanks for explaining.
Although, one might argue that what we see in table => data is no more part of the database than the result in worksheet, except that we have single table in FROM clause.
But I respect your argument and decision, so just thanks again for your time.
Regards,
Bruno
I see what you did there 🙂 Although I COULD counter that those grids include the SCN, so they represent the tables as of a certain state.
Nothing is forever. This could happen, but for the moment, it’s not likely.
Jeff,
Can you help me here? I am unable to connect to a 12c database using SDSQL but the same is possible using SQL*PLUS. please see below for more details and kindly help me if I missed out any. Thanks.
D:\>tnsping pdb1
TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 14-DEC-2014 10:59:52
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
D:\app\179818\product\12.1.0\dbhome_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = lt010782.cts.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME=pdb1)))
OK (30 msec)
D:\>cd sdsql/bin
D:\sdsql\bin>sdsql rajesh/oracle@pdb1
sdsql: Release 4.1.0 Beta on Sun Dec 14 11:00:32 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Error starting at line : 0 in command –
connect rajesh/oracle@pdb1
Error report –
Connection Failed
D:\sdsql\bin>sqlplus rajesh/oracle@pdb1
SQL*Plus: Release 12.1.0.2.0 Production on Sun Dec 14 11:01:35 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Fri Dec 12 2014 20:23:26 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
rajesh@PDB1>
Hi Jeff,
i try the E/A Version of SQLDev 4.1.0.17.29.
Think a found a small bug:
In the Package Body some time ago i defined a procedure which is published in the package header for external use.
Now i want to overload that procedure with a version that accepts a second parameter. ’cause i’m a lazy guy i right click the procedure and try to synchronize the packagae specifikation.
what i expected was that a second declaration would be added in package header.
what happend was that the original declaration was overridden.
hope i could make myselve clear!? shall i provide an example?
regards
peter
No, perfectly clear. Sounds like lazy code on our side. If I were you, I’d log a bug with My Oracle Support.
Hi Jeff,
I am trying to install SQL developer on my mahcine.
On opening the Application instance it logs the following errors and doesnot open an SQL procedure on clicking of the instance.
SEVERE 134 149 oracle.ide.IdeCore Exception firing mainWindowOpened to oracle.dbtools.crest.fcp.DataModelerAddin$1@146817d
SEVERE 132 364 oracle.ide.IdeCore Exception firing mainWindowOpened to oracle.ideimpl.jsr198.OracleActionRegistry$1@79ad9c
SEVERE 130 135 oracle.ideimpl.controller.IdeActionHook$DeclarativeResolver Failed to load icon ${OracleIcons.CANCEL_BUILD}. Base class was class oracle.jdevimpl.resource.JdevBuildExtensionManifest_en
SEVERE 128 811 oracle.ideimpl.controller.IdeActionHook$DeclarativeResolver Failed to load icon ${OracleIcons.CANCEL_BUILD}. Base class was class oracle.jdevimpl.resource.JdevBuildExtensionManifest_en
SEVERE 124 12 oracle.ide.IdeCore Exception firing addinsLoaded to oracle.jdevimpl.vcs.VCSManagerImpl$6@13892b3
SEVERE 123 1 oracle.ide.IdeCore Failed to load icon ${OracleIcons.EXTRAS_UNVERIFIEDBREAKPOINT_SUSPENDED}. Base class was class oracle.jdevimpl.runner.ExtensionResources_en
SEVERE 122 384 oracle.ide.IdeCore Exception firing addinsLoaded to oracle.ideimpl.editor.EditorManagerImpl@1e17cff
SEVERE 120 0 Failed to create addin oracle.dbtools.migration.workbench.plugin.PostgreSQL9Plugin
Solutions Worked upon:
I have tried deleting SQL developers Application data.
I have tried several combinations till now.
JDK 1.7 + and SQL Devloper 4.0
JDK 1.6 + and SQL Devloper 3.2.2
JDK 1.6 + and SQL Devloper 3.2.2
JDK 1.6+ and SQL Devloper 3.0.04
JDK 1.7+ and SQL Devloper 3.0.04
Did you unzip the archive to a FRESH directory, that you have write privs to?
Yes.
The Funny thing is earlier it was giving error and not opening Worksheet.
For JDK 1.6+ and SQL Devloper 3.0.04, it is adding worksheet only when I try to create a new SQL query. Clicking on a Stored Procedures or right clicking and selecting Edit does nothing.