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,827 Comments
Hi Jeff,
I am using Oracle SQL Developer 1.1.3 and I’m trying to figure out how to copy query results with headers into Excel. Ctrl + A then Ctrl + Shift + C or Shift + Ctrl + C does not work and there’s no option when I right click to copy with headers. I’d like an option instead of always having to export into a file. Any suggestions?
TIA
Alana
You need to upgrade – that version is like from 2006. It’s about 8 years old.
The code completion insight feature doesn’t work consistently. For instance I’ve saved my worksheets as .sql files. When I open one of these files in a worksheet and select the database connection the code completion insight doesn’t work. It only seems to when I create a new worksheet.
Is this a limitation?
Is there a way of associating an SQL file with a particular database connection.
I would also appreciate advice on how to look back and find particular SQL statements or script that I have written amongst the many .sql files I have.
I’m using 3.1.0.7 which is the version my company makes available.
Can you download v4.0.2? It’s just a zip file – extract and go. You’ll want the version that includes the Java 7 JDK. I’m guessing the problem you’re seeing is a bug with that version, which is old and no longer supported.
No way of associating .SQL files with a particular connection.
For finding particular SQL statements/scripts – have you seen the SQL History (F8)?
Thanks!
1. Do I need to uninstall 3.1.07 before I install 4.0.2?
2. I downloaded the zip file for 32 bit Windows. The release notes are titled Release 3.0 (4.0.2.15.21) and say 3.0 ships with JDK 1.6.0 . I’m confused. Is this the version you recommended?
3. On my computer I have Java 6. I don’t do any Java coding. Do I need to install a later version and if so where should I get it?
1. No. There’s no ‘install’ or ‘uninstall’ process for SQL Developer – you just extract the Zip and run, and delete when you’re done.
2. I think you got on an old download page. I recommend version 4.0.2.
3. You need Java 7 to run the latest version of SQL Developer. If you’re on 32 bit Windows, you’ll need to install the 32bit JDK. You want ‘Windows x86 127.98 MB jdk-7u67-windows-i586.exe‘
I have a strange issue with MySql 5.6 Master-Master replication. I’m not sure if its entirely a replication issue or a client issue. We have two MySql databases on two different RHEL servers. We have setup a common schema for them and enabled schema level replication. The replication works fine when issuing any DML using the command line utilities or NaviCat client on either Master. But it does not seem to replicate the data when using SQL Developer or MySql WorkBench clients.
Here is the server information:
Linux “Hostname” 2.6.32-431.3.1.el6.x86_64 #1 SMP Fri Dec 13 06:58:20 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
Here is the my.cnf file
[client]
#password = your_password
port=3306
socket=./mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port=3306
socket=./mysql.sock
#skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
default-storage-engine=InnoDB
lower_case_table_names=1
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
log-bin = mysql-bin.log
log-bin-index = /mysql-bin.index
log-slave-updates
replicate-do-db=schema_replicate
binlog-do-db=schema_replicate
replicate-do-table=schema_replicate.ref_tbl
relay-log = /relay-bin
relay-log-index = /relay-bin.index
relay-log-info-file = /relay-bin.info
slave-skip-errors = 1062
# binary logging format – mixed recommended
#binlog_format=mixed
# required unique id between 1 and 2^32 – 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
# The number of seconds the server waits for activity on a noninteractive
# connection before closing it. Range: 1-2147483. 2073600 is 24 days, close
# to maximum
wait_timeout=2073600
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_size = 48M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 16M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
Any help is appreciated.
If it doesn’t work with the MySQL Workbench Client, I’d say that’s a showstopper. I know close to nothing about replication for MySQL – I’d go to their forum and post your question there.
Turns out it we were using the default schema while running the DML statements, just specifying the SCHEMA1.TABLE_NAME isn’t sufficient enough. We had to give ‘use ‘ and then run the statements on .
Example, when you are logged into SCHEMA1
update SCHEMA2. set FIELD1=’VALUE’;
The above statement does not replicate the SCHEMA2., rather the below works
use SCHEMA2;
update SCHEMA2. set FIELD1=’VALUE’;
This might be a common issue, but I couldn’t find any similar solutions, so posted this one.
I am just learing the product and fumbling around trying to understand it so I can explain it to the rest of the group.
I found out what happened. If you have several worksheets opened in a document tab group and you move them around, you can lose track of which was the original worksheet you took the option to “Create Document Tab Group” on. Context-menu-wise, this is the only worksheet that has the context option to “Collapse document tab group”. You can’t have 2 document tab groups so the option appeared to not be available.
Because I already had a tab group and had forgotten about it no worksheet would offer to allow me to create a document tab group. When I took the option from the main Window -> configure window -> Collapse Document Tab Group, I was able to create a tab group again.
… and slowly the concepts sink in …
Thanks again,
Barry
Nah, I am way too new to the product to just “blame the software”. And I do appreciate your help.
Thank-you!
No, not confusing with splitting a window. The option is gone from my context menu when right-clicking a worksheet tab, but regardless I can grab the tab and dock the tab anywhere I like. I also found the “float” option which is pretty cool. Everything is working great. I also found that I can get to “New document tab group” through the Window -> Configure Window -> New Document Tab Group, it is just gone from the context menu is the only remaining mystery. But now it is not a problem, so thanks again!
Weird…when in doubt, blame the software 🙂
Hi Jeff,
I am trying to figure out what I did to get rid of the “New Document Tab Group” option in the context menu in worksheet editing. I suspect I have set a preference somewhere that has made it go away but I can’t locate it or find it via Google. It doesn’t seem to make much difference since I can click and drag a tabbed worksheet and move it where I want to so I can have split editing on two different scripts which is the same effect. Is using “New Document Tab Group” the equivalent to just dragging a worksheet tab to a new location?
I was able to reset my settings to factory default by renaming my users/barry/appdata/SQL Developer directory to a different value. The context option reappeared after that. But I have personalized my preferences to the point where I don’t really want to do a reset if it is not necessary.
Thanks for all your help!
Not sure, there’s no setting to make this ‘go away.’ There no drag UI artifact to do a new doc tab group – are you confusing this with the ‘split document’ feature?
Hi Jeff,
I am running into a problem with Sql Developer. It is Oracle SQL Developer 4.0.2 and I am using Windows 7 64 bit. Everything was working fine until I added a couple of connections and tried to compile a file. The compilation went through but since then I am unable to open SQL developer. Everytime I open the developer it will load all the way until it says “restoring windows” and then a “connection information” window pops up and everything freezes. I have tried rebooting and even reinstalling SQL Developer but it does not solve the issue. Please let me know what else I can do, thanks!
James
Find your connections.xml file – and try renaming it to something else. Then try starting SQL Developer. If this works, you’ll have to redefine your connections.
My connections file was here
C:\Users\jdsmith\AppData\Roaming\SQL Developer\system4.0.2.15.24\o.jdeveloper.db.connection.12.1.3.2.41.140418.1111
How did you get the alternating row colors that appear on the screenshots of your query results?
It’s a preference.
Thanks, Jeff. Believe it or not, I read that and never made the connection.
Meaning, I read check box field name on the Worksheet form – I don’t think I read past “Grid in checker board” and figured it wasn’t what I was looking for.
My team uses SQLDeveloper for among other things unit testing. To allow the whole thing to be integrated into the corporate CI/CD, I developed a SQLDeveloper-maven-plugin that loads the PL/SQL, loads the test, runs them and generates the results in JUnit format for Jenkins and other tools consumpton, and finally packages the PL/SQL code for auto-deploy.
I am looking for some place to contribute this tool for other who may need it to use. Might the SQLDeveloper team at Oracle be interested in this?
I am! Email me at [email protected]
Omer, I hope Jeff is able to get something going with your maven-plugin. Very nice. In the interim, I have published 2 SQL*Developer reports that will create JUnit output from the Unit Test Repository: http://reldesgen.blogspot.com/2015/03/integrating-jenkins-and-sqldevelopers.html
Hi Jeff,
Is there a way to trace back an object to its location in the navigation tree, from a search? For example, I’ll do a search under the Find Database Object window and when I get the result, I would like to pull it up in the tree in the Connections window so that I can drag it to a SQL worksheet. Right now I’m just doing the search and then manually navigating through the tree to the approriate schema in the Other Users folder where I find the object (usually a table), but I’m thinking there’s a quicker way. Is there?
Thanks,
John
Nope.
Eclipse has this feature. And I want it for our users too. This is definitely on the list.
Jeff, I guess its Silly but we need to run a SELECT statement 100 times to check how much it took in two different systems. Using SQL Developer How can I run a SELECT statement 100 times wiht out copying the statement 100 times in the SQL workshhet.
I appreciate your reply.
Thanks.
Put it in a script and call the script a 100x?
Hi Jeff,
I am working in SQL Developer Data Modeler with my design and I need to add a new column to some table. I use “Compare/Merge Models” feature to generate a delta DDL.
The DDL generated is built to recreate and reload the table with the newly added column rather than to use “ALTER TABLE ADD…” for existing table. When I need to add a new column to some large (even huge) table the recreate-reload method (with subsequent foreign key recreation) may take a long time, while adding a new column could be executed very quickly. And in our specific case more than 99% of schema changes could be implemented with “ALTER TABLE ADD…” or “ALTER TABLE MODIFY…” statements.
Is there a way to generate delta DDL in such a way?
Thanks in advance,
Ilia
I just added a new column to HR.EMPLOYEES and saved as a new design.
I then compared my original HR model to my new one with the extra column.
This is the ALTER SCRIPT that was generated:
— Generated by Oracle SQL Developer Data Modeler 4.0.2.840
— at: 2014-08-11 09:05:46 EDT
— site: Oracle Database 12c
— type: Oracle Database 12c
ALTER TABLE HR.EMPLOYEES ADD ( NEW_COLUMN DATE ) ;
We do try to generate ALTERs as much as possible. You don’t say exactly what type of column you added, which might be important to know here.
Hi Jeff,
The manually created DDL for the new column is:
ALTER TABLE QUERY
ADD (
SUB_APPL_TYPE NUMBER (6) DEFAULT 0 NOT NULL );
Also I remember that when I evaluated Data Modeler I got correct ALTER … statement(s). I don’t remember what data type it was.
Regards,
Ilia
Hi Jeff,
I think the explanation is that I added the new column in the middle of columns list and Data Modeler when generating delta DDL acts to preserve the same columns ordering as defined within model. I am accustomed to Oracle Designer’s behavior, which ignores columns ordering and generated ALTER TABLE ADD … statements independently on where new columns have been added.
Now I tested and proved this by reordering columns in the model.
Is there a way (some checkbox) to ignore columns ordering on delta DDL generation?
Thanks in advance,
Ilia
That would do it. Put the column at the end of the table definition if you want an alter – Oracle has no alter table move/reorder column like DB2 has for example – so for the table to reflect how it is shown in the model, the drop and re-create is necessary.
But if I don’t need to reflect columns ordering of the model to database table? Is there a way (checkbox) to say: “ignore columns ordering”?
I just detected the checkbox “Use “Columns Order” Property in Compare Functionality”. Should it solve my problem? Or its meaning is different?
Thanks in advance,
Ilia
When you added the column you have to explicitly move it up, so you are asking us to rebuild the table, yes?
You’re right, that compare option will give you the behavior you want.
Correct!
I tested and it works just as I need!
Thanks a lot.
And as for “asking to rebuild table”, no. I wish the future new schemas to be produced with “logical ordering” of columns, but for upgrading existing schema I don’t need such a feature. It’s run time cost is too high, but we never use “select *” which may require predefined columns ordering when for explicit select list it does not matter.
In any case thank you once more,
Ilia
>>but we never use “select *”
Perfect 🙂
new to the tool
log into a DB as SYSTEM
want to see all schemas, tables, indexes, etc
what are we not doing?
You’re probably looking at the SYSTEM objects. If you want to see other people’s stuff, go to the ‘Other Users’ node in the tree and explore around. Or right click in your connection and choose ‘Schema Browser.’ I have a post or two here on these topics.
We have updagrded Oracle to 11.2.4 and using SQL developer 2.1.1. The problem I have is I can do count on a table but when I use select * from PS_DEPT_TBL it never fetch the results. Do I need to do any config updates?
The Table has BLOB(CLOBS) as one of the columns
Version 2.1.1 of SQL Developer is 5 years old. Go get version 4.0.2 and let me know if that works any better.
Hi Jeff,
that might be a very silly question – I’m a total newbie to SQL server!
I was playing a little bit with a fairly complex query, and I kinda wanted to “visualize” it on the SQL worksheet. I pasted the query and successfully see all the tables, columns selected and relations. The only thing I would like to avoid is too see all the columns of each table, even the ones I’m not selecting.
This is only for visualization purposes, I was just wondering if there was a feature to hide the columns that are not selected, that’s all.
Thank you in advance for your insights!
Theo
What version of SQL Developer are you using? We deprecated support for any non-Oracle RDBMS in the Query Builder a version or two back.
Unfortunately there’s no way to do that, even if you’re on an older version.
It’s meant to allow one to easily add and remove columns from a query…
Hi Jeff,
thanks for your reply.
I’m running the 3.2.20 version. So that’s absolutely no way of doing that, right? That’s a pity 🙂
Thanks a lot!
I can’t think of a GOOD way to do it…but instead of a query builder diagram, you could use our Data Modeler, and build the diagram there – you have much more control over what’s shown
create tables in different schema & different tablespace using CTAS
Is that a question?
Hello Jeff
I’m using Oracle Data Modeler Version 4.0.2.840
How i can export my RuleSets and import their on another computer?
I am in the process of migrating from ms sql server 2005 to oracle 11g. However, I am little confused here. I am new to this area of work. Could you pls share any documentation (PDF/PPT) if you have that describes the high-level plan for this migration?
Appreciate your help.
There’s an entire help book in SQL Developer on the topic – check out ‘SQL Developer: Migrating Third Party Databases.’
We also have our own migrations page on OTN for more information.
I am using SQL Developer v 3.2.20.09 to migrate from Sybase to Oracle Pl/SQL 12c
While migrating the stored procedure the following block did not convert. I got NULL instead of object_id:
Sybase Block:
IF OBJECT_ID(‘dbo.CheckEst’) IS NOT NULL
BEGIN
DROP PROCEDURE dbo.CheckEst
IF OBJECT_ID(‘dbo.CheckEst’) IS NOT NULL
PRINT ‘<<>>’
ELSE
PRINT ‘<<>>’
END
Oracle Block after conversion:
BEGIN
IF NULL/*TODO:OBJECT_ID(‘dbo.CheckEst’)*/ IS NOT NULL THEN
BEGIN
DROP PROCEDURE CheckEst;
IF NULL/*TODO:OBJECT_ID(‘dbo.CheckEst’)*/ IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(‘<<>>’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘<<>>’);
END IF;
END;
END IF;
END;
Lines 1 & 4 got converted to NULL. I have many places where such code is written.
Is there any quick way to overcome such an issue? or what needs to be done in such case?
I think the TODO markers are for things the translator wasn’t able to handle…but this question is more suited for our Migrations OTN forum where the migration developers can help you directly.
I understood about the TODO markers but i wanted to know if there is any alternative to overcome this issue?
Nevertheless, I have posted this issue on the migration forum:
https://community.oracle.com/message/12577379#12577379
Hi Jeff,
I have used SQL Server for small data set analysis for a long time. The things I like are the database can be set on my own PC and the integration service wizard can load data from Excel files to the tables that I create quickly. I am wondering if I switch to SQL Developer, would I still have the things I like, which are, can the database be set on my PC, if can, what type of database will the SQL Developer create, Oracle or other types? Does SQL Developer has the component similar to SQL Server Integration Services that load data from flat files to the database in a timely way?
Thanks for your answers and help!
Yes, you can create and run an Oracle Database on your PC – we have a Windows installer.
SQL Developer doesn’t create databases, you’ll use the DBCA, Database Creation Assistant to do that.
SQL Developer can take a CSV, Excel, or delimited file and create tables/load that data for you. You can find step by step instructions for that amongst my most popular posts – see the panel of those to the right of this page.
Hi Jeff ,
I use Oracle SQL data modeler V 4.0 for data modelling and Oracle 11g is the underlying Database.
I am facing problem while trying to “sync data dictionary” with Model .
When I click the button “sync data dictionary with Model” it initially lists out all the tables which needs to be created in the DB.
Later , I executed a DDL script and created all the tables in Oracle 11g
after this process
When I again click the button “sync data dictionary with Model” it is not listing out the tables which I created previously .
Ideally I would expect to see all the tables which was created through that DDL script.
As a result I am unable to sync “the Data dictionary with Model” as each time it is prompting me to create new tables
Please advice
I’m confused – you synced your model – created the ‘missing’ tables in the database, and then did another synch. It didn’t find anything…because you had already added the missing tables previously?
Jeff, we are continuing to evaluate SQL Developer 4.0 and I requested that ports 4000-4999 be opened so that the debugger could be evaluated. They would like more of an explanation of what SQL Developer 4.0 is doing that it would need to go externally through only these ports?
Is there a minimum number of ports that could be chosen? Is just one enough or do you need 1000 open ports? Can it be any port number?
As always, thank-you for your time and your response.
Barry
So the debugger initiates a connection FROM the database server TO your machine. You can use any range/number of ports you’d like. They can read up on the specs for DBMS_DEBUG_JDWP.CONNECT_TCP() for more information.
But basically, we automatically start a listener on your machine to accept debug requests from the database. You need to make sure the database server can reach your desktop machine.
If I am understanding you correctly, I could ask our IT department to open just a single port of my choosing and use it as the parameter to use the debugger – is that correct?
Yes. You probably want to talk to your DBA about it as well as you’ll need a couple of database privileges granted too.
Jeff,
Is the debugger host my machine or the Oracle Database server? The default action of the program is to load in my IP number.
We use Oracle on Demand so our servers are not local. Is the port a port on my machine or on the server machine?
You start a “listener” on my machine to accept debug requests from the database. I need to be able to explain my need for an open port, where it is, and how it will be used. Aren’t there security concerns with just open ports on the internet? My management keeps coming back to me asking for reasons for a port and I am not even sure whether the port is my machine or the server :/
If you could elaborate on this I would appreciate it.
On your machine, so you’ll need the corp firewall setup to allow outside communication to your desktop.
BTW, opening up a production instance for debugging SHOULD be a big deal. I’m not surprised you’re getting some grief over this. Again, bring in the DBA for backup. Or have them debug it for you.
Hallo Jeff,
How and where can I create dynamic Propertiy for Domain?
Thanks
Hi Jeff,
I am trying the option “Synchronyze Data Dictionary with Model” (right arrow icon) and get the error “Unable to generate design. See the log file for details”.
In the log file I see:
2014-08-05 14:46:59,779 [Thread-128] ERROR DBMExtractionController – Error Generate Design: Engineering
java.lang.ClassCastException: javax.swing.tree.DefaultMutableTreeNode cannot be cast to oracle.dbtools.crest.model.ModelObject
at oracle.dbtools.crest.swingui.compare.CompareModelsDialog.initFilterObjects(CompareModelsDialog.java:1499)
at oracle.dbtools.crest.swingui.compare.CompareModelsDialog.initPropertiesFilter(CompareModelsDialog.java:509)
at oracle.dbtools.crest.swingui.compare.CompareModelsDialog.(CompareModelsDialog.java:481)
at oracle.dbtools.crest.swingui.compare.CompareModelsDialog.(CompareModelsDialog.java:303)
at oracle.dbtools.crest.imports.metadata.DBMExtractionController$Runner.run(DBMExtractionController.java:371)
at java.lang.Thread.run(Thread.java:744)
(not for sure this error is just relevant, but I don’t see any other).
I am working with the version 4.0.2.840 and the deltas script generation is technologically very important for me. Is there any way to normalize this situation.
Thank you in advance,
Ilia
I would advise you to open a ticket with My Oracle Support or post this as a new thread on the data modeler OTN forum.
OK, will do.
Thanks a lot
Hi Jeff,
I opened the ticket with My Oracle Support, but my investigations show that a design/model could be synchronized with DB schema only if it was imported just from this schema. For the design that was not imported from data dictionary “Source Connection” list is empty. But also “Redirect Connection” does not work: it causes to some Java error (and to “Unable to generate design” popup).
Maybe I do something wrong and there is a way to synchronize design with a schema?
Thanks in advance,
Ilia