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,941 Comments
Hi Jeff!
I have a problem with SqlDev 4.0.1.14 when I launch a SQL statement that has errors, the script output view hangs and I can’t see anything. I have to select all the text (but I cannot see anything i am copying) and copy-paste to the notepad++ to see anything.
Sorry Alberto, I have no idea what might be happening. Are you able to see script output when there are no errors? What sorts of errors are ‘invisible’ when you are actually able to see them after the copy/paste?
Hi Jeff,
Please let me know how can I represent relation between view and base table in ER diagram created using sql datamodeler version4.
Thanks,
Sabitha S
hi Jeff,
we have copied sqldeveloper files to unix env.
our aim is to run unit tests automatically via cron job.
our run script is
bash sdcli unittest -run -test -name testname -repo repos -db connection_name
we can not find connection.xml in the unix env whereas this file exists
C:\Users\myname\AppData\Roaming\SQL Developer\system3.1.07.42\o.jdeveloper.db.connection.11.1.1.4.37.59.48
directory in the windows env
could you please tell us how can we run unit test in the unix env.
All of the application setting files on UNIX will be in he $HOME directory of the user running it. There will be a hidden ‘.’ directory for sqldeveloper.
So for me, it’s at /usr/oracle/.sqldeveloper
Hi Jeff,
SQLDeveloper for the Connection.xml file ‘.’ We have created root.
Unit tests can do to be successful.
But connection.xml the state tests we want to save passwords password again to run.
How can this be solved?
Hi Jeff,
I’d like to implement Oracle Connection Manager to control user connections to the Oracle database. I could not find any information on whether SQL Developer would work with Connection Manager.
Could you please shed some light on this?
Thanks,
Bosco
TNS Connections
The TNS connection type is an appropriate option in any of the following circumstances:
You have an Oracle client installed on your machine. You have access to many Oracle Database instances. You do not know the machine details of the system hosting the Oracle Database instance you want to connect to.
A TNS connection uses an alias entry from a tnsnames.ora file. Oracle SQL Developer uses only one tnsnames.ora file. You may have more than one on your local machine or want to use the tnsnames.ora file on a remote machine, so note that Oracle SQL Developer looks sequentially for the tnsnames.ora file in the following locations:
1. $HOME/.tnsnames.ora
2. $TNS_ADMIN/tnsnames.ora
3. /etc/tnsnames.ora (non-Windows systems)
4. $ORACLE_HOME/network/admin/tnsnames.ora
5. Registry key
On Windows systems, if a tnsnames .ora file exists but Oracle SQL Developer isn’t using it, create a TNS_ADMIN environment variable via Control Panel -> System -> Advanced -> Environment Variables , specifying the file’s location as the variable’s value.
Follow these steps to create a TNS connection in Oracle SQL Developer:
1. In the New / Select Database Connection dialog box, enter the same connection name, username, and password you used for the basic connection.
2. Select TNS from the Connection Type list. The GUI changes slightly to provide a list of all network alias entries available to you. Select an alias.
3. Click Test and Connect as before.
http://www.oracle.com/technetwork/issue-archive/2008/08-may/o38sql-102034.html
it’s not something I’m familiar with, did the Connection Manager team have any insights on this? Like, how would it work with SQL*Plus?
Hi Jeff,
From my 2nd post it appears that like SQL*Plus, SQL Developer would have to be setup to use TNS. So every user who’s using SQL Developer would need to have an Instant client installed and an appropriately configured local tnsnames.ora file setup. Currently the user setup doesn’t need the Instant client setup or the tnsnames.
Does this assessment sound correct to you?
Thanks for your help.
Bosco
Setup to use TNS? All we need is a TNSNames.ora file. And we don’t need a client.
I hope this post helps clear up any confusion.
Could u please elaborate as to How to get files in sql developer from the svn repository and make changes, and check in the file?
‘check out’ the project/dir level to create a local directory and get all the files. then edit local. save. commit.
Is it possible to checkout a file from sub version through sql developer 4.0.3 instead of checking out the whole directory? If so please could u explain?
you don’t really do checkouts in SVN do you? you just want to do a get on a file you don’t have locally?
Hi Jeff , I’m trying to use the Object Copy feature (database copy) available SQL Developer but the menu option is disabled. Is so much in my windows installation as on my Mac installation. Can you give me any tips on how to enable this feature ? Thank U!
Do you have 1 or more connections open?
No. Just one connection registered and opened.
I create a nested table, then populated this nested table.
which code is following :
DECLARE
CURSOR CRSR_A IS SELECT * FROM EMP;
TYPE EMP_NTAB_TY IS TABLE OF EMP%ROWTYPE;
EMP_NTAB EMP_NTAB_TY := EMP_NTAB_TY ();
I NUMBER := 0;
BEGIN
OPEN CRSR_A;
LOOP
I := I + 1;
EMP_NTAB.EXTEND;
FETCH CRSR_A INTO EMP_NTAB(I);
EXIT WHEN CRSR_A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(EMP_NTAB(I).EMPNO);
END LOOP;
CLOSE CRSR_A;
END;
I have some question on above table which are following :
1. now i want to get index value of a particular element of nested table
like what is the index value of JONES who is in ENAME Column of EMP_NTAB Nested table.
2. I need to update the Job of JONES Only.
I have a lot of SQL Developer questions to answer. Sorry, I don’t have time to help with general Oracle SQL or PL/SQL topics. Check out the OTN Community spaces or Stackoverflow…
I am new to SQL Developer, so I apologize if this question has been asked before.
I have a select statement stored in an .sql file. I would like to export the results of the select statement to an Excel spreadsheet.
If I copy the select statement directly into the SQL Worksheet, I get a Query Result tab from which i can export using the Export… feature.
However, if I run the select statement indirectly, by calling out to the .sql file in which it’s contained (e.g., @”C:\Users\XXXX\TestCase.sql”;), I don’t get a Query Result tab. I have to save the output to a file and then Export manually.
Is there a way to gain access to the Export… functionality when running a statement that’s contained in an .sql file?
Thank you very much for your assistance.
— Steve
The @ command is a SQL*Plus/scripting commmand. So when you run it, you get the script output back. If you want the ability to export the query output using the grid/wizard, you’ll need to open the file, then execute it.
OR
Build the formatting comments into your queries…like in TestCase.sql have your query look like
select /*csv*/ * from …
then the script output will come back directly as CSV.
Build a SPOOL around that, and you have your file.
Thank you very much for the prompt reply.
Will there be a version of the early adopter that has the jdk bundled with it (like 4.0.3 has)? This is our only easy install option. Thanks!
No, but when 4.1 goes production there will be a 64bit JDK included for Windows users.
I have been trying the beta version of SqlDeveloper 4.1.0.17.29 and when I try to generate a script output, it fails – says task completed 0.06 sec but no script output is displayed. This is using the most recent jdk1.8.0_31 or jdk1.8.0_25.
Previous version sqldev 4.0.3.16 generates the script output properly and takes about 4 sec using jdk1.7 or 1.8.
Am I missing some tweak somewhere?
I am running a windows 7 64 bit machine. I can install and work with sql developer version 3 with no issues. When I install version 4, it starts up fine and I am able to look at tables, data, etc but before long it shuts down unexpectedly (right after I click on a table for example). This has been happening with any version 4.x for me. I’ve tried running 32 bit and 64 bit with the same results.
Others in the office with same configuration do not seem to have this problem with version 4.
Any help would be appreciated!
Jeff
Sounds like java is crashing on your machine. What version of the JDK are you running? I’ve seen similar issues where an out of date video driver of all things caused problems like this.
currently pointing to C:\Program Files\Java\jdk1.8.0_31 with version 4.1.0. Also tried running sqldeveloper.exe and sqldeveloper64.exe
Oops, I spoke too soon – SQL Developer still crashes when cancelling a Unit Test …
Edit to the above – I found this Preferences setting – Database – Advanced – Use OCI/Thick driver (http://www.thatjeffsmith.com/archive/2013/04/die-or-how-to-cancel-queries-in-oracle-sql-developer/), and that seems to allow cancelling a unit test.
Two questions in one here I’m afraid …
Whenever I cancel a unit test (using the test dialog box Cancel button), SQL Developer crashes and I have to shut it down using task manager (we’re using SQL Developer 4.0.2.15). Any idea what causes it, and is this a known bug?
Also when testing the output of REF cursors, if the test fails then the ‘Expected / Received’ dialog is truncated, so I usually can’t see which column has failed.
Any help gratefully received!
Thanks
I am trying to connect using SQL Developer on my Macbook Air to an Oracle 12c database (that I just installed) on another laptop running on Windows Vista. I provided the IP address (ipconfig) of my Windows machine on the new connection details of SQL Developer (on the Macbook Air). But it is not able to connect. Is there something that I am missing? Please help.
can you ping the Vista box?
What happens when you try to connect?
Hi Jeff,
I have installed Oracle Sql Developer version 3.0.04.34. I got success to install it properly and I am also able to establish a connection with Oracle 10g express edition. But when I am trying to expand the database it shows no table under it. I also tried to check the schema, but it always says loading. I tried to run a query just to check if some thing works, it shows this massive error which is listed below,
—-
java.util.UnknownFormatConversionException: Conversion = ‘0’
at java.util.Formatter.checkText(Formatter.java:2503)
at java.util.Formatter.parse(Formatter.java:2485)
at java.util.Formatter.format(Formatter.java:2414)
at java.util.Formatter.format(Formatter.java:2367)
at java.lang.String.format(String.java:2769)
at oracle.dbtools.raptor.backgroundTask.internal.SimpleRaptorTaskUI.getFormattedTime(SimpleRaptorTaskUI.java:219)
at oracle.dbtools.raptor.backgroundTask.internal.RaptorTaskUI.setState(RaptorTaskUI.java:43)
at oracle.dbtools.raptor.backgroundTask.internal.SimpleRaptorTaskUI.(SimpleRaptorTaskUI.java:59)
at oracle.dbtools.raptor.backgroundTask.internal.RaptorTaskUI.(RaptorTaskUI.java:36)
at oracle.dbtools.raptor.backgroundTask.ui.TaskProgressViewer$4.(TaskProgressViewer.java:297)
at oracle.dbtools.raptor.backgroundTask.ui.TaskProgressViewer.createTaskUI(TaskProgressViewer.java:297)
at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager.initViewers(RaptorTaskManager.java:323)
at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager.addTask(RaptorTaskManager.java:276)
at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager.addTask(RaptorTaskManager.java:181)
at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager.addTask(RaptorTaskManager.java:142)
at oracle.dbtools.worksheet.editor.OpenWorksheetWizard.invoke(OpenWorksheetWizard.java:339)
at oracle.ide.wizard.WizardManager.invokeWizard(WizardManager.java:372)
at oracle.dbtools.worksheet.editor.WorksheetOpenController$1.run(WorksheetOpenController.java:67)
at oracle.dbtools.worksheet.editor.WorksheetOpenController.openWorksheetWizard(WorksheetOpenController.java:73)
at oracle.dbtools.worksheet.editor.WorksheetOpenController.handleEvent(WorksheetOpenController.java:40)
at oracle.ide.controller.IdeAction.performAction(IdeAction.java:529)
at oracle.ide.controller.IdeAction.actionPerformedImpl(IdeAction.java:884)
at oracle.ide.controller.IdeAction.actionPerformed(IdeAction.java:501)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
at javax.swing.AbstractButton.doClick(AbstractButton.java:357)
at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:809)
at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.java:850)
at java.awt.Component.processMouseEvent(Component.java:6290)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3267)
at java.awt.Component.processEvent(Component.java:6055)
at java.awt.Container.processEvent(Container.java:2039)
at java.awt.Component.dispatchEventImpl(Component.java:4653)
at java.awt.Container.dispatchEventImpl(Container.java:2097)
at java.awt.Component.dispatchEvent(Component.java:4481)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4575)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4236)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4166)
at java.awt.Container.dispatchEventImpl(Container.java:2083)
at java.awt.Window.dispatchEventImpl(Window.java:2482)
at java.awt.Component.dispatchEvent(Component.java:4481)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:648)
at java.awt.EventQueue.access$000(EventQueue.java:84)
at java.awt.EventQueue$1.run(EventQueue.java:607)
at java.awt.EventQueue$1.run(EventQueue.java:605)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:98)
at java.awt.EventQueue$2.run(EventQueue.java:621)
at java.awt.EventQueue$2.run(EventQueue.java:619)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:618)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
——
It would be a great help if you can tell me what i can do to resolve this.
thanks in advance
Two big problems, version 3 of SQLDev and 10g of Oracle Database are both really old.
The easy thing to try first would be to upgrade SQL Developer to version 4.0.3 and try again.
The next easiest thing would be to get 11gR2 XE of your database up – much better support there.
Thanks for replying Jeff.
I tried to connect with SQLDeveloper 4.1.0.17 but the same problem is still there. As my database is also not expanding, I tried to find something on web and there I found an article suggesting that there may be some kind of low level TCP bug which could be removed by adding, “AddVMOption -Doracle.net.disableOob=true” to SqlDevelper config file in bin. But this also doesn’t seem to resolve the issue. The same kind of error still exists.
any suggestion??
Thanks in advance. 🙂
There is a SQL Worksheet SAVE dialog box pop-up that’s driving me crazy and I can’t seem to stop it.
Scenario:
1) Open a connection – a new SQL Worksheet opens
2) Do any of the following:
– Minimize(maximize) your SQL Developer window
– open or launch ANY other application
– try to switch to another application from SQL Developer
Any event such as this prompts you to save any unsaved SQL Worksheet. If you have multiple open SQL worksheets, one SAVE dialog opens for EACH.
I don’t want to have to save every single open SQL worksheet I have just to stop this pop-up.
Is there anything I can do to stop that pop-up?
OS: Windows 7 enterprise
SQL Developer: Version 3.2.20.09 (Unfortunately I’m a contractor and this is the default and ONLY version supplied as well as being the default tool used)
Thanks for your help.
Brett
Hi Jeff,
for tables there is a way to generate table API by clicking right mouse button on a table name.
is there any similar way to create an getter/setter api for object types?
this would save loads of typing.
did you get my question or do i have to explain it more detailed?
greetings from abroad
peter
yes there is – but you get to code it
What I wrote here for synonyms you could apply to TYPEs. Or you could write a master/detail report, with a list of types, click on it, and a plsql child report that generates the APIs you want
Hello Jeff,
Do you know how we can monitor a standby DB from SQL Developer 4 (4.0.3.16) ?
The DBA > Performance > ASH Report Viewer is showing data from the primary instance.
Many Thanks,
Asparuh
Workaround ( ASH report on Active Data Guard ):
1. Using SQL Developer: View > Reports > All Reports > ASH and AWR > ASH Report for the past 30 minutes
2. Using SQL*Plus > Go to the DG server > sqlplus / as sysdba > @$ORACLE_HOME/rdbms/admin/ashrpt.sql
Hi Jeff,
please edit my previous post with the correct path:
View > Reports > All Reports > Data dictionary reports > ASH and AWR > ASH Report for the past 30 minutes.
I apologize for the typo.
BR,
Pooh
Hi Jeff,
I am adding tables to an existing database for a new feature.
I need to keep the first DDL script unchanged (Reason I am using a tool named flywayDB this tool checks that I am running with the expected DB version).
I have used SQLDevelopper to build my model.
I want to be able to generate the new V2_DDL.sql
What is the best practice ?
1. Keep using mydatamodel and filter the generated DDL ( I will name my tables using the new feature name)
I have to be careful not to change any V1 tables (otherwise I have to use “Compare Merge models”
2. Create a new schema. I will have to deal with foreign keys between the new schema and the old one
3. Another better way to do it ?
Regards
Christophe
Unfortunately, the output says only,
SQL Error: ORA-32035: unreferenced query name defined in WITH clause
32035. 00000 – “unreferenced query name defined in WITH clause”
*Cause: There is at least one WITH clause query name that is not
referenced in any place.
*Action: remove the unreferenced query name and retry
It would be super if it would tell me which with clause query I did not reference.
Thanks.
the database engine returns the error messages and line numbers – we just display them. it appears on your your example that it does include those
Got it. Thank you for your time.
Pasting into this box changed the single quotes and minus signs into characters that trip up the SQL. The script output I see is below. It would be super helpful if SQLD would tell me exactly which with clause query I did not reference. Thanks.
Error starting at line : 1 in command –
with
cte2013 as
(
select to_date(‘2013-01-01’, ‘YYYY-MM-DD’) + Rownum – 1 “Date”
from dual d
connect by Rownum <= to_date('2013-12-31', 'YYYY-MM-DD') – to_date('2013-01-01', 'YYYY-MM-DD') + 1
),
cte2014 as
(
select to_date('2014-01-01', 'YYYY-MM-DD') + Rownum – 1 "Date"
from dual d
connect by Rownum <= to_date('2014-12-31', 'YYYY-MM-DD') – to_date('2014-01-01', 'YYYY-MM-DD') + 1
),
cte2015 as
(
select to_date('2015-01-01', 'YYYY-MM-DD') + Rownum – 1 "Date"
from dual d
connect by Rownum <= to_date('2015-12-31', 'YYYY-MM-DD') – to_date('2015-01-01', 'YYYY-MM-DD') + 1
)
select *
from cte2014
Error at Command Line : 21 Column : 6
Error report –
SQL Error: ORA-32035: unreferenced query name defined in WITH clause
32035. 00000 – "unreferenced query name defined in WITH clause"
*Cause: There is at least one WITH clause query name that is not
referenced in any place.
*Action: remove the unreferenced query name and retry
Doesn’t it say right there in the output?
Error at Command Line : 21 Column : 6
Hi Jeff,
Nice work on SQL Developer. It would be very helpful instead of simply telling me, “ORA-32035: unreferenced query name defined in WITH clause”, it would tell me which query, in my case a common table expression, is unused. Keep up the good work.
Thanks,
L
I need more info Larry. What exactly are you running, where when you see that?
Here is a very simple example. It would super helpful if SQLD would tell which CTE I did not reference.
with
cte2013 as
(
select to_date(‘2013-01-01’, ‘YYYY-MM-DD’) + Rownum – 1 “Date”
from dual d
connect by Rownum <= to_date('2013-12-31', 'YYYY-MM-DD') – to_date('2013-01-01', 'YYYY-MM-DD') + 1
),
cte2014 as
(
select to_date('2014-01-01', 'YYYY-MM-DD') + Rownum – 1 "Date"
from dual d
connect by Rownum <= to_date('2014-12-31', 'YYYY-MM-DD') – to_date('2014-01-01', 'YYYY-MM-DD') + 1
),
cte2015 as
(
select to_date('2015-01-01', 'YYYY-MM-DD') + Rownum – 1 "Date"
from dual d
connect by Rownum <= to_date('2015-12-31', 'YYYY-MM-DD') – to_date('2015-01-01', 'YYYY-MM-DD') + 1
)
select *
from cte2013
You should see the line number and curpos of the error…and if you execute as script, we can do even better
SQL Developer seems to take a long time > 3 minutes to start up and be usable. With version 3.x it was only about 30 seconds. I think the problem is that with our roaming profiles, a lot of the config files, etc are stored on a network share and have to be loaded every time I start SQL Developer.
Is there a way to redirect the start up files to the local directory or limit how many need to be loaded on each start up?
Thank you,
Parley Kennelly
Yes, you can tell SQL Developer to store those files anywhere you want. See this post from Kris.