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,803 Comments
Hi Jeff,
Thanks for a great site! I always catch something new about SQL Developer every time I visit.
I have a question about the commit icon versus issuing an actual commit command. When you click the icon, it doesn’t show “committed.” in the Script Output window but it does when you issue the command in the Worksheet. Is there a setting to make it show when you click the icon so that you don’t end up clicking twice, just to be sure 🙂
Thanks,
Sameer
It’s there, you just need to know where to look – it’s not in the script output panel b/c you technically haven’t ran anything through the script engine. And btw, I always TRIPLE COMMIT — just to make sure.
WOW – that was a freaky fast reply 🙂 Very much appreciated.
Thanks.
Hi,
I just wanted to try out the new “sdcli utility import” feature to script an Excel import but I cannot find any information about the required XML config file.
Is there any documentation about it? Or examples?
Thanks and Regards
Peter
You walk the IMPORT wizard in the GUI – on the last page, there’s a button that says ‘Save State’ – that will be the XML file that defines how the IMPORT will run in the SDCLI command line interface.
this is from the burned in help…
┌─[09:37:22]─[wvu1999]─[MacBook-Air-Smith]:/Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin$
└─>./sdcli utility help
Oracle SQL Developer
Copyright (c) 1997, 2015, Oracle and/or its affiliates. All rights reserved.
Invalid UTILITY command: help [-data |d] -conn [-table|t ] [-log|logfile ] [-show|s] [-validate|v] -help
IMPORT Usage:
utility
utility
Supported commands:
import -config|c
Examples:
utility import -config /home/import/mydata.xml
Import the file using the options saved in mydata.xml
utility import -c /home/import/mydata.xml -data /home/data/importmydata.csv -log /home/import/mydata.log
Import the file identified by the -data argument using all other options saved mydata.xml. Write logging details to mydata.log
utility import -c /home/import/mydata.xml -table owner.mytable -cfg /home/import/mydata.xml
Import into table owner.mytable using all other options saved mydata.xml.
utility import -c /home/import/mydata.xml -conn myconnection
Import the file using connection myconnection and all other options saved in mydata.xml
utility import -config /home/import/mydata.xml -show
Show the options saved in mydata.xml without doing an import
utility import -config /home/import/mydata.xml -conn myconnection -table owner.mytable -validate
Validate the options saved in mydata.xml and the parameters specified on the import without doing an import.
Invalid UTILITY command: help
wow, this was a really fast reply, thanks!
I did as you told me – worked perfect in wizard mode from the UI. Then I saved the sdimp-file (the config XML) and tried again via sdcli.
Now I receive a NullPointerException at oracle.dbtools.raptor.data.readers.DataReaderRegistry.getReader(DataReaderRegistry.java:45).
Using the -show option worked fine and everything seems to be correct. (-validate produces the error above).
Better open an SR?
Thanks and Regards
Peter
well crap, i hate it when that happens
yeah, I’d open an SR
done!
Same problem here. Did you work it out?
Best,
Nils
is there a proper way to stop a query once you have started to run it? When I attempt to stop a running a query I invariably have to ctrl+alt+del to get out of sql developer. If i hit the close query button the program freezes
Try the stuff I recommend here.
thank you Jeff, that did help.
I’ve added a comment on a view. Is there a way to view the view comment in SQL Developer. I’ve found table comments in the Details pane. But not the view comments.
Thanks for your site–it’s helped me a lot.
I use the keyboard shortcut to comment out code with line commenting (–) all the time. But is there some way to select text and apply block commenting (/* */) around it instead?
When I’m debugging, it is useful to use this type of commenting to ensure I don’t lose track of the variables I need to put back in when I’m done:
Select *
From Scott.Emp
Where HireDate <= To_Date('06/30/1985'/*p_HireDate*/, 'MM/DD/YYYY');
you can highlight all of the code you want to comment out and press
“ctrl + /”
or you can use the block comment you referenced in your question at the beginning and end of the rows of text
Thanks, Bob. That’s actually what I was referring to when I said I used the shortcut for line commenting. But what I’m looking for is a similar shortcut that would wrap the selected text with the /* in front and the */ at the end so that I could block comment it in place. There are some situations where that is a better option for what I’m trying to do.
I thought I might be able to do it with a macro, but I can’t get it to work in the version I’m using (4.1.3.20).
try a REGEX search and replace
We can run a query using the F9 function key. Depending on the Preferences setup, when the query is run the Query Result tab will either by pinned or unpinned (ie new tab created). You can manually override this by pinning/unpinning the tab via the tab’s pin button. Now, to the question: is there a way to assign to function keys the ability to run a query so that tab is pinned or unpinned – overriding the Preference behaviour?
Not today.
Just starting to use SQL Developer Data Modeler and I have a quick question I couldn’t locate the answer to.
I have a database that does not have all the foreign keys enforced.
I’m trying to create a logical model and show relationships. When I create the 1-1 or 1-N relationship, I don’t have a choice to pick source field and target field – it only shows the primary key of both tables (or nothing in the two key drop downs if there are not keys defined).
Thanks for the help.
do you have uniques? you can’t just pick any attribute for a relationship
Getting the JDK installed is a huge problem for those of us on Managed networks. Please Help!
In a corp environment – better to play by the rules.
However, if you’re on a Win64 machine – as 90% of corp america is, just download the SQLDev package that includes the JDK. No install required, just unzip and run the exe.
Hi! I’m using SQL Developer 4.0.3.16/Build Main 16.84. When Exporting to an .xlsx file, I noted that within the .xlsx file, the headers have freeze pane activated. Is there a way to deactivate this feature or do so for each export? The reason is I use extracted data to import into ACL (Audit Command Language) and the excel file fails the import unless I manually remove the freeze panes within the excel file.
Thanks for your help!
No way, but you could alternatively write out CSV files.
I am using jdk1.8.0_66 on a Win 7 64 bit machine. I am able to connect to some remote Databases using sqldeveloper v3 and also through custom java applications. But after using sqldeveloper-4.1.3.20.78-no-jre once I am no longer able to connect to any remote databases – vendor code 17002. The same java application is also failing to connect – “Caused by: java.net.ConnectException: Connection refused: connect” . If I add “-Djava.net.preferIPv4Stack=true” , I am getting –
# A fatal error has been detected by the Java Runtime Environment:
#
# EXCEPTION_ACCESS_VIOLATION (0xc0000005) at pc=0x000007feff268d2e, pid=7084, tid=1196
#
# JRE version: Java(TM) SE Runtime Environment (8.0_66-b18) (build 1.8.0_66-b18)
# Java VM: Java HotSpot(TM) 64-Bit Server VM (25.66-b18 mixed mode windows-amd64 compressed oops)
# Problematic frame:
# C [WS2_32.dll+0x8d2e]
Before using sqldeveloper v4 , the same application was running smoothly with “-Djava.net.preferIPv4Stack=true”. I have tried removing the instalation folder aswell as %APPDATA% contents but I am unable to revert back to working condition.
Does sqldeveloper v4 change and Windows dll files ?
Please ignore the above issue. Even after re-formatting and without installing sqldeveloper v4 the issue persists. Thus sqldeveloper is not at fault. Probably a JVM vs Windows thing.
I’ve been able to import a good portion of my data model via the Data Modeler (v3.0.04). However im still lost on how to get grants/privileges imported. Even connecting as the schema owner I still see essentially none of the grants to other database users. Is there some special privilege that the *connecting* user needs in order to see the grants? I see a few of the other database users, but most of them have no grants to my current schema’s objects.
What im trying to do is import the entire picture of a given schema (all objects and grants to other users) so that I can use this going forward to generate alter scripts for each release.
Not sure about v3.0 – that’s really old now, but in version 4.1, the answer is ‘it works’
Dang! Somehow thought I was on the latest version. Yes v4 seems to get all the grants so far. Thanks!
Jeff,
If I have the following defined in my server side sqlnet.ora:
# Server Encryption Params
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256)
SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(MD5, SHA1)
What are my possible connection options when using sqldeveloper? Prefer to use the thin drivers, is this possible?
I am attempting to spool the results of a query into csv to be read in an excel file, but I either end up only exporting the sql code itself or at best the sql code followed by the text data.
my code is
SPOOL C:\DATA.xls
select /*csv*/
table.name,
table.address
from
table;
SPOOL OFF
I have looked through the forums and I cannot see what I am doing wrong.
I wold like just the results to export:
Name Address
Bob Alaska
John Canada
we can do that..i forgot to do the /*csv*/ bit, but you get the idea, I hope
SET ECHO OFF is only honored in scripts, so if you want JUST the data, do the spool inside a script as well
Thank you for the quick reply. I found some really useful things here.
Is this what you mean, because it is still not working for me.
SET ECHO OFF;
SET HEAD OFF;
SPOOL C:\DATA.xls
select /*csv*/
table.name,
table.address
from
table;
SPOOL OFF
set echo off won’t work that way – it only suppresses the commands executed in a script. so write your code in a file, and then execute that file
so i need to use sqlplus? i am not familiar with this. I will have to look around some more to find out how to incorporate.
thank you.
no..look at the picture i showed you
i need to execute the file through sqlplus. i am having an issue using it. i see what you did with the “@”
You can, but you can also execute it in SQLDev using F5. Your choice. Just honor the rules of ‘SET ECHO OFF’ – only honored in scripts
ok, so i wrote my code and saved it as follows:
SET ECHO OFF;
SET HEAD OFF;
SPOOL C:\DATA.xls
select /*csv*/
table.name,
table.address
from
table;
SPOOL OFF
then I ran the script
@C:\TEST.sql——THIS IS WHAT I SAVED THE SQL QUERY AS
it ran, but is still exporting the sql code along with the data at the bottom in an unusable format.
Is there something else i am missing?
only put the spool commands in the script file. the set commands should be outside the script in sqlplus itself.
SPOOL C:\DATA.xls
select /*csv*/
table.name,
table.address
from
table;
SPOOL OFF
then I ran the script
SET ECHO OFF
SET HEAD OFF
@C:\TEST.sql
this is not working; still pulling everything. i am running it in sqldeveloper with f5 as you said.
what is in test.sql?
{
SPOOL C:\DATA.xls
select /*csv*/
table.name,
table.address
from
table;
SPOOL OFF } this was saved as test.sql
then I ran the script
SET ECHO OFF
SET HEAD OFF
@C:\TEST.sql
I think that should work, what version of sqldev are you running, and what’s in your xls file after the script’s finished?
I am running version 4.1.2
it is returning the sql code and the results:
SPOOL C:\DATA.xls
select /*csv*/
table.name,
table.address
from
table;
NAME, ADDRESS
BOB, ALASKA
JOHN, CANADA
2 ROWS SELECTED
DM 4.1.3 does not draw the relationships when engineering a relational to logical model.
Is there a way of convincing it to do it? I did not find any preference or option related to it.
Under Solaris 11 (SPARC 64) sqldeveloper has a yellow outline on all of the text in the UI. Any way to turn this off? It started sometime last year, but I don’t remember the last version it looked normal.
Not all the text, just some of it, right?
I think it’s offering to auto-complete your * or use 12c feature to show you the actual SQL behind your statement. See this post either way
No. All of the text. The menus, the dialog boxes, everything. I can send you a pic if you’d like.
BTW I’m using version 4.1.3.20.
I’ll have to try to see which version is started with.
yeah, send a pic. doesn’t happen on Oracle Linux…
Turns out it is an issue with the gnome window manager under VNC. Not sure why it shows up the way it does because it does not affect jDeveloper. When I tried using twm it looked normal.
So I needed to change the way fonts are rendered under gnome. I had to turn off the subpixel smoothing (appearance/fonts/rendering) select something other than subpixel smoothing.
Data modeler 4.0 converts unique constraints (defined in the relational model) into indexes after saving the editor changes. The unique constraints disappear and the indexes appear. The DDL preview shows a “create index” statement instead of a “alter table … add constraint unique …”.
How can the conversion into indexes be avoided?
just tried in v4.1.3, and after saving my design/model, the unique constraint remains intact.
Thanks, Jeff.
I just tried with 4.1.3. But the same happens: the unique constraints are converted automagically into indexes after saving the table properties. The indexes are not even unique according to the DDL preview.
What I do is this: define unique constraints (for a table without indexes), save properties, reopen the properties editor and the unique constraints are no longer there, but indexes with the same names (but not unique).
do you have automatic index generation on unique constraints enabled for DDL preference?
Cannot say … I don’t see any such preference under Tools > Preferences > DDL . Where is it?
When the DDL Generation Options dialog appears, the table’s unique constraints are not listed, only the indexes.
Which means, the conversion happens before the DDL generation.
right click on your design in the tree, and open the design preferences
No automatic index generation for any kind of key was set.
I found a workaround: replaced the table with the unique constraints with an identical copy.
DM generates now unique keys instead of indexes.
Strange …
Hmmm, something was UP with that particular table then…the mystery deepens
we are having an issue with is the Document column which is a BLOB data type.
This field contains a variety of document types that were uploaded via the application to hold correspondence against customer complaints.
Now using tools such as SQL developer it is possible to view some of these files using the External Editor option, but not all of them – those that won’t open give the following type of view
view like
YYYYYYYYYYYYY
-> YYYYYYYYYY
have you seen this before, and is there a mechanism that we can use to resolve this; or alternatively is there any script or procedure we can run that could output all of the blobs, in their correct format into a file location as individual documents, but being referenced to the data held in other fields of the table.
are they all of the same file type?
more on blobs:
please, please, please use pl/sql developer for a short while, i guarantee you’ll be filled with inspiration, it is so much easier to work with in comparison to sql developer, sincere apologies for using this very helpful board in this way. please remove once you’ve read
I have. I’ve also used Toad for 10+ years. And I’ve used Golden, and SQL*Plus, and OEM, and Access/Excel as a front-end, and KeepTool, and Navicat, and more than a few others.
You have to give me direct, specific requests. Saying, make it be like tool x, y, z makes no sense. No actionable sense at least. And I would suggest you just keep using pl/sql developer if that’s what you prefer.
Hello,
I just downloaded Orcale 4.1.3.20 SQL developer on my MAC OS X 10.10.4 and i can’t create a connection. Whenever i do type a username and password a red message showup : “iO Error: The Network adapter Could not establish the connection ” I specifically downloaded Oracle for Mac OS X and i can’t seem to find a proper solution on other websites.
Where is your database?
Hi Jeff
I found I can easily share Reports with team members using Preferences / User Defined Extensions.
What if one wants to share preferences, snippets, templates and connections? One request is that a new developer (possibly on different OS) gets a standard initial setup. The other request is sharing objects like snippets (without copying files).
Do you see a better way than links for shared config-files and copying a standard set of config-files to proper location for initial setup?
Thanks!
Tino
Is there any option for “database export” to ensure the same sort order of database objects in ddl export files?
I’d like to create “snapshots” of schema-ddl during development cycle to have a chance to “diff” the changes based on those files. But currently this doesn’t work because the sort order (is there any?) of objects seems not to be fix causing the diff to be (nearly) worthless. Doing some changes e.g. on constrarnts or indexes results in a different sort order in databse export ddl.
Would be a very helpfull feature!
Thx,
Dirk
I am adding new connections to SQL Developer, and saving them. But when I shut it down and go back in it’s gone. The conx I added a long time ago are still there, but not the new ones I’m adding.
Thanks,
Jason
make sure your OS user has read AND write privs to the connections.xml file
I’m working with a database containing more than 400 tables, many of which are of no interest to me or my work. But, I want to see the relationship(s) between the tables I *am* interested in. When creating a Relational model by dragging tables from the database tree view (in SQL Developer), is it possible to remove unwanted tables without deleting them completely from the model? For example, if I’m multi-selecting tables and accidentally select an extra table I don’t care about, then drag them all to the model. I know I can use ‘Delete View’ if I’m working in a SubView, but is there something similar when working in the Relational? Or, should I always just do this sort of work in a SubView?
I’d definitely create one or more subviews to manage that many objects.
You can even close the main diagram, so that you only see the subview(s). Use the ‘Delete View’ to only delete it from the SubView vs ‘Delete’ which will completely remove it from the design.
Hi,
I’m using sqlcl 4.2.0.16.049.0842 RC and that does not put “insert” statements into the command history. Instead, a ‘ctrl-p’ or cursor-up produces the last non-insert statement.
Another issue is more an usability problem than a bug: i would prefer that hitting would always execute the current line instead of inserting a newline. This is nearly always what I want (e.g. after editing a typo in a stmt). You might use e.g. crtl-v as prefix for quoted insert (so ctrl-v ctrl-j would do what enter does today). This is how e.g. Zsh and Bash work.
Seems there is some HTML-escaping going on. The 2nd paragraph should read: … I would prefer that hitting “Enter” … (I originally used angle brackets around Enter)
Hi Jeff,
any chance on a feedback wrt. “insert stmts are not inserted into history”? Is that a bug in sqlcl or something I’m doing wrong?
Partial Solution: The Oracle database has over 100 schemas so the popups do not appear (correctly–default is set to 10). I am connecting to only one schema (mine). How do I get SQL developer Completion Insight to “work” only on the objects in my schema?
Write a more restrictive query..i.e. include the schema in your query/code.