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,800 Comments
It would be nice to have the functionality of a proper text editor in a SQL environment. Have you considered licensing/using Sublime Text as your editor?
We’re never going to be sublime, the text editor, that is.
But you can setup sublime as an external editor, search here for my post on VI, and you can see how to do it.
Also, what’s keeping us from being a ‘proper’ text editor? We continue to make improvements with each release.
I do have adapter network could not establish the connection problem ?
I installed the Oracle SQL Developer in my Laptop windows 8 and JDK8, we have username , password , hostname ,and the service name .
I installed it in Mac pro too and has the same issue beside the other two computers .
Need help I am a student has two lap should submit them before November 14 2015.
Thank you
can you ping the db server?
If the db server the same hostname . My answer no I can’t ping it.
if the database server is unreachable on your network, there’s no way for us to connect to the database
you need to get on the network to do your homework is what it sounds like
Is it odd issue ? is there anything I can change or do from my side to make the connection . All my peer did that without any problem and they do not add or change further steps than mine .
go see what network your peer is on
Why forcing that the sql statement has to be valid, to get the DESCRIBE of an object.
select * from MYTAB2;
MYTAB2
ctrl+click works only for the first line. why. why… ?
because the parser is used to look at the grammar to tell if there’s a identifier there or not…otherwise we’d try to pull up the ‘select’ table, view, stored procedure, etc.
if you want to go to the object w/o typing valid sql, with your cursor on ‘MYTAB’, hit ALT+G instead
But in TOAD its working without forcing grammar
So use Toad then.
come on Jeff, I just pointed one and only one concern which I felt as a temptation to click TOAD icon.
that’s cool, but we don’t really do things just because Toad does something a certain way
Jeff:
A bit of a snark? Really? We’re going to get along great!
I teach database skills, theory, and practical advice for actuaries and actuarial students. As a profession, we are heavy users of Oracle, so I’d like to add that into the course as a capstone learning project. As a practicing actuary, I’ve been an Oracle user for almost 20 years. It is my platform of choice (when I have the choice).
It’s proven incredibly difficult to accumulate all of the information to instruct a course student to install Oracle Express, SQL Developer, and then set up all of the ODBC drivers and DSNs required to both teach using Oracle through SQL Developer and via ODBC (which is how most actuaries use it). This is especially true where many practitioners still use 32-bit MS Office products on a 64-bit MS Windows system. I was surprised at how complicated the process is and I have some newfound respect for my DBAs.
Would it be possible to post a comprehensive guide on the installation process for someone who has never used Oracle (or in my case has never had to deal with the client configuration issues)?
Thanks!
Arlie Proctor
you want to teach them how to install Oracle Database and how to configure windows to use a 32 bit ODBC driver to connect Access or Excel to said database?
or you want to teach them how to use Oracle Database via SQL and various client side tools?
I’d much rather teach the latter. However, for many of the students, still in college, I have to accomplish the former before I can teach the latter. I think I can walk someone through it for Win7 64 at this point, but I have not yet waded into the swamp of Win10 64.
I would have expected this to be a common enough question that there would be a step-by-step guide somewhere on the Oracle site. If it’s there, I missed it.
we put the VirtualBox VM out there for this very reason, get past the install/config bits and concentrate on the actual development/SQL stuff
I’m not sure we have a doc on configuring Access/Excel to work with Oracle via ODBC – and I hope we do not. It’s one of the worst ways to interact with Oracle Database. The SQL those tools generate is horrendous.
While I would agree that the SQL generated by any of the Microsoft tools is horrendous at its best, the unfortunate fact of the matter is that the combination of Access/Oracle is like coffee and cream out there in the wild. It’s not going away anytime soon.
So, the right answer is to teach users to write their own native PL/SQL within Access in order to connect local and server data together when Access is the only tool available to them.
I originally wrote that part of the course depending on the availability of the JDBC-ODBC bridge and intended to use only SQL Developer…Oracle sort of stepped on that idea.
you assume everyone will have access to Windows and Office, when SQL Dev, APEX, and SQL*Plus/Oracle Clients are freely available…just sayin’
Well, think about it this way. As a business-side user, my work machine comes constructed with Win7 and Office. It took me a year of begging to have SQL Developer loaded, even though it is free. It’s considered a developer’s tool and therefore not readily available to business users.
So, yes, I know for a fact that most of the students taking my course will frequently use Oracle via ODBC with an Office tool on the front end.
Now, do you want them sending MS BS SQL, or native SQL?
Yeah, but SQLDev is a zip that you just extract and run, nothing to install.
fact that most of the students taking my course will frequently use Oracle via ODBC with an Office tool on the front end.
That’s not good. You should fight to change that. I work with many, many schools and universities, and most of them use SQL Developer to teach Oracle and data management systems. Using Access or Excel puts them onto a bad path.
It’s not quite as simple as downloading and unzipping. The Java SDK has to be installed before it’s that simple. Even if it were that simple, corporate IT policies usually frown on downloading unapproved software.
I won’t argue that there are better tools available. However, it will be a long time before they replace replace Office as the tool of choice for many, if not most, business users. The plain fact of the matter is that most business users don’t know any better. Part of what I am doing puts SQL Developer and Access side by side. That’s how you start changing hearts and minds.
We make the zip download avail WITH the java bits, so nothing to install beforehand.
I understand your POV, I just can’t ever come around to thinking that Access/Excel is good way to go against the DB. I’ve seen way too many crazy cartesian products generated by crazy ODBC auto-generated queries…
It’s not just a point of view, it’s a reality out there in the wild. The fact that SQL Developer is a superior tool, in an of itself, does not make it the tool of choice. Business users will use what they are given, even if sub-optimal. You need to poke your marketing guys in the ribs and let them know that they need to go to work promoting it.
That said, there are myriad tools out there (R, SAS, Excel, etc.) used for analytical purposes that need to access Oracle databases. You can either teach proper PL/SQL or suffer what ODBC provides. My choice is to teach proper PL/SQL.
So I think we finally agree 🙂 ODBC and Office make horrible queries. I need to do more to help get SQL Developer out there.
So putting any effort into showing people how to hook up ODBC/Office would be counterproductive.
No, I’m afraid we do not agree at all. Business users will make use of ODBC to execute queries against Oracle databases. Company DBAs will set things up and allow it to happen, just because it has always been that way and it likely will always be that way.
ODBC is not, in and of itself, the problem. The SQL the user sends is the issue. If the user sends native PL/SQL, all is good with the world. When the user opens up Access, joins twelve tables (half of them local) in the graphical interface and lets ODBC interpret the query, you have a problem.
Local tables will always be an issue unless DBAs choose to allow all users to create whatever they want in Oracle (can we say chaos?).
Dear Jeff,
We use the SQLDeveloper HTML Data Dictionary, and it is fab. Thank you.
Is there a way to automate this, such that at the time of generating a build (which we do using Jenkins continuous build integration), we can generate the data dictionary HTML?
That would be Most Excellent.
Best regards,
Martin
not today, but that’s been on my list for awhile
I try to migrate from sqlserver to oracle. It generates a master.sql script and all tutorials say it should be run as sys. But then a lot of stuff is generated in the sys schema and I find this objectionable. Is there any way around this problems.
BTW the oficial oale tutorials look very dated http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/SQLServerMigration/SQLServerMigration.htm#t4
Is there somthing new and step by step ?
it’s dated, but it’s still accurate
you can see in there that it doesn’t say anything about SYS
have you read the migrations book in the SQL Developer help?
Hey Jeff!
Im using data modeler since Version 3.0. Also our data model is increasing over the time and so we have about 55 tables.
These tables are also shown in the menu tree on the left, but there only 50 tables in the relational model.
Might this be a bug or is there a secret property ?
The problem occurs at the printing of the model to pdf, because some tables are not shown.
Generating the ddl is okay, all tables have their create script.
Can you help me ?
Regards Anton
i would need more info, is there anything common re: your 5 missing tables?
what if you try to copy one to a subview, does it appear?
When a click on one table in the tree I can generate a subview with all tables around and than the tables are shown.
They are only gone in the main relational model where all tables should be shown.
Hey!
I solved the problem on an other way as expected.
Just drag the not shown table and drop it on the diagram.
I dont know why it disappears, but I can bring it back with this method.
Regards Anton
Hello,
I searched the forum and could not find my answer. I am using a mac running OS X 10.11.1 and I am unable to connect to my universities database. I have confirmed my account works with a windows computer but have not been able to resolve my issue on mac. There is no firewall enabled and i have tried connecting with my ethernet plugged in. I get this vendor code
An error was encountered performing the requested operation:
IO Error: The Network Adapter could not establish the connection
Vendor code 17002
Any help or direction would be much appreciated.
Thanks
Tim
Hey!
May check this site:
http://www.dba-oracle.com/t_sql_developer_vendor_error_17002.htm
I had the same problem on Fedora.
The solution was to correct the entries in listener config ( uppercase / lowercase ).
That could be hint for you.
Anton
Thanks but I am unable to use these commands in terminal.
what commands, ping? ping works on windows and os x terminal
you need to bring your mac to school if you want to connect, or you need to get on your school network from home
there’s no difference really between your mac and windows when it comes to sql developer jdbc connections
is your Mac on the same network as the PC? are the connection details 100% the same?
can you ping the server on your mac?
I am unable to ping the server on my home pc running windows 10 as well as my mac. The only time i have been successful in connecting to this database is through the university computers. Connection details are for sure the same.
Hi Jeff,
I need to go through a number of tables and delete some rows in each. Can this be done with the Excel report (and then model update)? I tried couple things and it didn’t work. I cleared the cell content (except the xml tag) and I also tried to delete the rows, but I still have columns in my test entity.
Nooooo, you’d need to manually update the databases. We don’t have a, take these changes in Excel and propagate them to Oracle feature.
Thanks!
Is it possible to get a running SQLDeveloper instance to open a new file from the command line?
I am a developer on an application that includes C++, Java and database components, using Eclipse as my IDE. After giving up on the TOAD plugin for Eclipse, I realized that what I really want is to setup SQLDeveloper as an external editor for all of the PL/SQL scripts in my project. But I don’t want to launch a new session for every file. It would be lovely if there were a command-line tool or option to request that my existing SQLDeveloper instance open a new file.
Thanks.
Roy
Hi Jeff,
I am trying to link a View to an entity in Logical datamodel and I get this below error. ‘No Source entity selected’. Is it not possible to link a view to entity?
Also is there a good document to under the link notations and its usage?
Thanks in advance,
Sushma
Hi Jeff,
We are using SDDM for modeling tables in a product where the column names can be 100 characters long. Is there a way to turn off the onslaught of warnings saying that the column name is too long ?
Thanks,
Bob
After running a database compare, is there a way to see a consolidated script of all the DDL that needs to be applied to sync up the two databases? I currently only see a way to get DDL on a per object basis.
yes, check the items you want to have included, then hit the save button in the toolbar – that will give you one file with all the object alter scripts in a single file
Hi,
I am working on a datamodel using SQL Developer. I have imported about 100 tables from multiple schemas of a db into relational datamodel. I am working on the logical datamodel. I have linked the tables in Logical model. I have 2 questions here.
1) how do I see the links in Relational datamodel that I have created in logical model?
2) I have imported few more tables to relational model. Now how do I engineer these few tables into the existing logical model?
Please respond asap.
Appreciate your response.
Regards,
Sushma
engineer it to logical, making sure it include foreign keys and mapping to relationships
if you add things, run the engineer to logical again, adding just the new objects
Thanks Jeff. Second one worked to add new tables to logical model.
For the first question, just to clarify, I have links created on Logical model. I want those links to be seen in Relational model. Does engineer to logical still works?
then you need to engineer the logical back down to the relational, making sure to check/bring over the relations as FKs
I am using SQL Developer 4.1.1.19 build 19.59 on Mac OS X 10.10.5.
Starting today, I am missing the connections view tab on the right-side.
Using View > Connections does not open anything.
Any suggestion where should I look for ? Or do I need to completely reset SQL Developer ?
Keyur
try Window > Reset to Factory
if that doesn’t work, you connections file probably got ‘borked’ – rename your connections.xml file with sql developer closed and start the app back up
That did the trick. Thank you!
Jeff,
We’re trying to replicate this particular TOAD functionality…
We open an object viewer for a table, and get the data tab open. When adding a new row of data, we would like any columns that have a foreign key to another table to be able to show that FK data in an LOV popup so we know the data we’re inserting is valid.
Can this be done somehow with extensions, or maybe a report?
Thanks.
You could set it up for 1 table probably with an extension. But dynamically for every table? That would be tricksy.
Thanks. Any ideas on how an extension could be configured to achieve this? We probably have 2 or 3 tables that this would be extremely useful for, so creating 2 or 3 extensions should be manageable.
Let me play with that today, give you an idea of where you could go.
Thanks! There seems to be limited documentation on what the different options are when writing/adding extensions.
Will try that. Thank you !! 🙂
Hi Jeff.
I have seen some posts on this topic, but none of those have helped me to resolve my issue. I have installed the latest SQL Developer 4.1.2 on my Windows 10 PC. I selected the one with the embedded Java.
When I start the application the splash screen only partially loads as has been described.
This is a new PC and I have not used SQL Deeveloper before on this machine.
I do have a local Oracle Database 11g here also.
Below is the stack trace I get and I do not have any further details available.
Java asserts are enabled!
java.lang.Exception: Stack trace
at java.lang.Thread.dumpStack(Thread.java:1329)
at oracle.ideimpl.MainWindowImpl.(MainWindowImpl.java:166)
at oracle.ide.osgi.Activator.setupWindow(Activator.java:281)
at oracle.ide.osgi.Activator.start(Activator.java:135)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl$1.run(BundleContextImpl.java:711)
at java.security.AccessController.doPrivileged(Native Method)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl.startActivator(BundleContextImpl.java:702)
at org.eclipse.osgi.framework.internal.core.BundleContextImpl.start(BundleContextImpl.java:683)
at org.eclipse.osgi.framework.internal.core.BundleHost.startWorker(BundleHost.java:381)
at org.eclipse.osgi.framework.internal.core.AbstractBundle.resume(AbstractBundle.java:390)
at org.eclipse.osgi.framework.internal.core.Framework.resumeBundle(Framework.java:1176)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.resumeBundles(StartLevelManager.java:559)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.resumeBundles(StartLevelManager.java:544)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.incFWSL(StartLevelManager.java:457)
at org.eclipse.osgi.framework.internal.core.StartLevelManager.doSetStartLevel(StartLevelManager.java:243)
at org.eclipse.osgi.framework.internal.core.EquinoxLauncher.internalStart(EquinoxLauncher.java:271)
at org.eclipse.osgi.framework.internal.core.EquinoxLauncher.start(EquinoxLauncher.java:241)
at org.eclipse.osgi.launch.Equinox.start(Equinox.java:258)
at org.netbeans.core.netigso.Netigso.start(Netigso.java:191)
at org.netbeans.NetigsoHandle.startFramework(NetigsoHandle.java:209)
at org.netbeans.ModuleManager.enable(ModuleManager.java:1352)
at org.netbeans.ModuleManager.enable(ModuleManager.java:1156)
at org.netbeans.core.startup.ModuleList.installNew(ModuleList.java:340)
at org.netbeans.core.startup.ModuleList.trigger(ModuleList.java:276)
at org.netbeans.core.startup.ModuleSystem.restore(ModuleSystem.java:301)
at org.netbeans.core.startup.Main.getModuleSystem(Main.java:181)
at org.netbeans.core.startup.Main.getModuleSystem(Main.java:150)
at org.netbeans.core.startup.Main.start(Main.java:307)
at org.netbeans.core.startup.TopThreadGroup.run(TopThreadGroup.java:123)
at java.lang.Thread.run(Thread.java:745)
To clarify, I have tried this with the 64bit dowbload.
I do not have an AppData folder for this application. So the cause is something else.
False alarm. The explorer settings were hiding the AppData and once I got them exposed and removed them, it is working again as expected.
Thanks the site helped.
Jeff, is there a way to ctrl-enter (single-step) through a large SQL script without having to manually click on the next statement each time?
Not today…you can ctrl+A and ctrl+enter to run all of the queries and get grids for each on though
Thanks Jeff…these are DDL (create table, etc.) statements rather than queries—so if one doesn’t work, I don’t want to run the others. Basically I’m single-stepping through a DDL script (I’m a DBA). I’ve used similar functionality in other tools and it’s very handy. Thanks again for your answer!
We support SQL*Plus commands, like SET PAUSE ON – which requires user intervention like hitting the ENTER key before going to the next statement. Give that a try…
Hey, that works–thanks so much!!!
Hi – I was looking for an option to copy text with formatting, so that it carries forward a monospace font when pasting into an email, say. Is that possible? A search of your site did not throw anything up.
But thanks for the AskTom link – nice to see AskTom back in action, last time I looked it wasn’t doing much.
Not today, but that’s on our list
Hi Jeff. I’ve just updated to 4.1.2.20 and notice the scriptrunner task progress bar shows the task is not completed, but it actually is!
It happens every time I commit an update statement, for example.
Researching, I’ve found this https://community.oracle.com/thread/3683236 thread about the issue, and this bug was seemingly fixed in Early Adopter builds, but now is occurring again.
Thank you.
Hi Jeff,
Is there any way to utilize GIT against an already created local repository (in my case I am using BitBucket Server and SourceTree) rather than having to clone from origin?
Regards
Paul
Hi Jeff,
i’am missing the table model tab(introduced with sqldeveloper 4.1) in the new version 4.1.2
Regards Günter
Hi Jeff,
‘Model’ tab in table editor(introduced in 4.1.1) is missing in 4.1.2 – intended or bug ?
Regards Günter
it was working for me at Open World last week, in 4.1.2
reinstalled from scratch – now it’s working – Thanks
any update ?
Hi! I have created a display editor that prints out trigger names for the selected table. Same thing like Triggers tab when you open a table. Next, I have created a context menu trying to disable the selected trigger in one go. I have tried to pass as parameter the trigger name, like #TRIGGER_NAME#, but the value could not be passed. Instead I got in return a SQL statement like ALTER TRIGGER {schema_name}.#TRIGGER_NAME# DISABLE.
If I take this logic, but instead of a display editor I go for building a report, then the report knows to translate #TRIGGER_NAME# into the name of the trigger that I have selected. Any idea why display editor does not know to interpret my #TRIGGER_NAME# parameter? Thank you!
Hi Jeff!
How well does SQL Developer 4.0.3 handle installations of both Oracle 11 Client 32 and 64-bit? To flesh out the other pieces, we are using Windows 7 64-bit and JDK 1.7.0_60 64-bit. I have developers who must use both bit levels of the Oracle Client. Thus far I have not had a great deal of luck getting SQL Developer to work without setting the ORACLE_HOME variable to the 64-bit client, which of course breaks the 32-bit side. I even tried using symbolic links and not setting ORACLE_HOME courtesy of an excellent post (http://realfiction.net/2009/11/26/Use-32-and-64bit-Oracle-Client-in-parallel-on-Windows-7-64-bit-for-eg-NET-Apps/). Alas, running a test on the connection yields: “Status : Failure -Test failed: no ocijdbc11 in java.library.path”.
I did search through the other answers but didn’t see anything related (if I missed one my apologies). Any thoughts on how/if I can get this working?
Thanks! Kern
you can’t used a 32 bit client with a 64 bit JDK and vice versa – the bit level of the clients have to match the bit level of the JDKs
Thank you, Jeff, for the quick response! So to make sure I understand you correctly, even though I have the 64-bit client and 64-bit JDK loaded, the fact that the 32-bit client is also installed will interfere? I had thought that I could go to Tools\Preferences\Database\Advanced and set the Oracle client in there to point to 64-bit (and indeed it gets me through a few of the checks when test is run).
So it sounds like the short answer is that they will not coexist and function correctly?