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,565 Comments
С какого хера он поганит тело пакета?
С как бы базы открывает старую версию?
Как обезьяна каждый раз рефреш давить чтоли?
Hi,
I have a few java source objects and I would like to export them. So I executed “select dbms_metadata.get_ddl(replace(object_type,’ ‘,’_’),object_name) from user_objects where object_type=’JAVA SOURCE'”, and then moved forward to copy or export the outcome.
The problem is I can’t get rid of the escaping of the quote character. Even if I choose the enclosing characters to be none, SQL Developer still escapes quotes (as double quotes) as if quotes were the enclosing characters.
Is there a way to get rid of these double quotes?
Probably – did you read the package spec docs for DBMS_METADATA? And have you tried SQL Developer’s Database Export and/or Cart to move the java stored procedures out that way?
Hi,
I am trying to run sqldeveloper 4.0.2. I am on Mac OSX 10.9.4. I am using java version 1.7.0_51. When I try to run sqldeveloper it just hangs and does not start up. I went into /Applications/SQLDeveloper.app/Contents/MacOS and ran the sqldeveloper.sh script manually and I get a stack dump:
java.lang.Exception: Stack trace
at java.lang.Thread.dumpStack(Thread.java:1364)
at oracle.ideimpl.MainWindowImpl.(MainWindowImpl.java:166)
at oracle.ide.osgi.Activator.setupWindow(Activator.java:269)
at oracle.ide.osgi.Activator.start(Activator.java:128)
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:1177)
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:190)
at org.netbeans.NetigsoHandle.startFramework(NetigsoHandle.java:198)
at org.netbeans.ModuleManager.enable(ModuleManager.java:1189)
at org.netbeans.ModuleManager.enable(ModuleManager.java:1011)
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:744)
I got it working. In the /Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin/sqldeveloper.conf file I changed:
SetJavaHome ../../jdk
to
SetJavaHome /Library/Java/JavaVirtualMachines/jdk1.7.0_51.jdk/Contents/Home
Is there any way to keep Oracle SQL Developer from closing my DB connections, or to increase the timeout? Sometimes during a long-running query, or no activity for 2 or 3 minutes, SQL Dev will just close the connection. I have tried a lot of tricks I found on internet such as set the SQLNET.EXPIRE_TIME parameter, but none of the tricks works for me. I am using SQL Developer Version 4.0.1.14 to connect Oracle database 11gR2. Thanks.
We will never close the connection – it’s the database or the network that’s doing that. The database shouldn’t be resource governing a connection with an active query running on it though, so I’m not sure exactly what you’re running into. Might need to do a session or network trace. And of course check your database alert.log.
When opening a file stored in an SVN remote repository in the Team->Versions window, I get the Editor Error popup, “An Error was encountered: null”.
Have you seen this before and/or know how to correct? I would like for everything to be seemless without having to go to Windows Explorer to get the file from the repository.
SQL Developer: version 4.0.2.15
JAVA: version 1.7.0_55
TortoiseSVN client: version 1.8
I have not seen it before. SQL Dev has an internal 1.7 SVN client – which should be OK with a 1.8 Subversion server, but that could be what’s causing you problems.
thank you jeff,
i did not know the bios had a settings for virtualization. yes,
it was disabled. i enabled that setting for my amd laptop.
i downloaded sql day developer again and imported into
virtual box and it worked without any problems.
do see all of hands-on-labs.
thanks, jeff
When SQL Developer locks up and I have unsaved files, I can recover the queries that I’ve already run through the SQL History … but … How can I recover the whole files that are unsaved or not-recently-saved? Are they stored in temp files? Is it possible to recover them before force-closing the application? One of the biggest issues with SQL Developer is its propensity to hang up when doing very mundane actions: like disconnecting a db session, or generating the SQL code from a View… Thank you for your help.
if you kill the app with unsaved work, you’re probably going to lose stuff
If you’re working on something important, I recommend always using a file instead of just what’s in the SQL Worksheet buffer, and save your work frequently – just like you would in any other application.
Our framework doesn’t offer the Office-auto-save-recover documents feature you’re looking for, today.
Hi Jeff,
It turns out that SQLDeveloper gives this error when it encounters enpty fields. Some records had no value in the cell corresponding to a field I wanted to import. (I found out by choosing “insert script” instead of “insert”)
I would expect SQLDeveloper verifies that beforehand.
Regards,
Geert
We don’t scan the entire document to make sure it’s good – imagine how expensive that would be on a million record file?
The records are committed every 50 or 100 records – that’s why you see the ‘partial’ rollback.
For large imports, I always recommend going with the INSERT SCRIPT (if Excel input) or SQL*Loader/External Table (if delimited input) – that way you have full control over what is going to happen.
Right you are. Thanks for taking the time to answer.
Hi Jeff,
I’m trying to import an Excel file (xlsx) containing 8400 records using SQLDeveloper Version 4.0.2.15 Build 15.21.
I get the message “Import Data into table NLSTEMPPT from file pt-BR_export nlsstrings_portuguese.xlsx . Task failed and import rolled back.”
When I run the query “select count(*) from nlstemppt” I get 2550. So, not rolled back really.
In the logging I find another line saying:
IdeConnections%23MDM_NLS+%5Bdoraps003%5D locked at completion of oracle.dbtools.raptor.navigator.db.impl.ObjectFolderTreeNode$LoadTask (statement = )
What could be going wrong?
Thanks in advance.
Hi,
I am facing the same problem. I feel it is because of the memory issue. Please share any solutions or hint to fix this problem. I am dealing with 3+ million records 🙁
3,000,000? You don’t want a batch of INSERTs piped through SQL Developer.
Save your XLXS file as a CSV, then use the same wizard to build a SQL Loader script and run that instead.
External Table would be nice too, but that would require server level access via an Oracle Directory.
Whenever I add a new table in SQL Developer Query Builder, it frequently ends at the bottom of the page and then I have to drag it slowwwwwly up. What is the fastest way to get the table to the top? Thanks
I think you stumped the chump. I’ll go play with this and see if I can’t find a better workflow for you.
Hi Jeff,
I was wondering if a solution was found relating to Tommy Connell’s post re: Query Builder and tables ending up at the bottom of the page and having to drag it up, which is really a slow process. Thank you so much.
hie jeff,
i downloaded the developer days hands-on-lab. I also downloaded
virtualbox. i started the developer days hands-on-lab in virtualbox.
I received the following message:
“this kernel requires an x86-64 cpu, but only detected an I686 CPU.
unable to boot. please use kernel appropriate for your CPU.”
I have a quad-core A6 AMD CPU with 4 GIG of RAM and 300 GIG
hard drive. I believe I need to look for hardware with a INTEL
CPU?
any comments on this.
thanks
vince r-m vachon
Your machine BiOS has virtualization disabled. Here’s some instructions on how to fix that.
I’m a novice with databases, and needed to load some Excel files. Your website helped me from installing SQL Developer to just completing my first Excel file load. Thank you so much!
You share a name with my mother in law, for a second I thought she was pranking me!
Anyways, thanks for the comment. It motivates me to keep writing!
Welcome to the world of databases, and good luck!
25 connections and no multi-select. I am lazy and always looking for a shortcut.
Cheers!
I have downloaded the stand-alone version of the Data Modeler. is there a particular directory I should place it in so it can use the connections already set up in SQL Dev?
Thank you.
You can put it anywhere you want – just make sure it’s a ‘fresh’ directory. Then use the export connections dialog in SQL Dev and use the Import button in SQL Dev DM.
Thank you.
I like that the export feature allows you to encrypt the password stored in the connection, but when importing I was not prompted for the key and the passwords were not imported.
I tried it twice. What did I miss?
So they’re super-duper double secure 🙂
Looks like we didn’t account for the new security scheme for connections in the Data Modeler. I’m adding it to our list for the next version.
OK. Thanks.
Now, what is the best way to remove all connections (because they don’t have passwords) from the Data Modeler?
Just select them and use the ‘Remove’ button in the Data Dictionary Import Wizard.
could you please tell me how to perform code review in oracle
Wow, that’s a really open ended question. Can you be more specific?
Hi Jeff,
I was wondering if there is any already-created free tables I can import in SQLDevel 4.
Regards,
Kayhan
You can get demo data scripts for SCOTT, HR, SH, and a few others from us. There’s lots of free data out there. I wrote a post on how to turn your iTunes library into a table as well.
since i’m all new to these stuffs, would tell me how to access those demo scripts?
Thanks.
never mind got it, thanks.
Hi Jeff,
Why does it take so long to “Load Content” of folders when using the Open File/Folder window? Sometimes the interface acts just like the standard Windows open file interface immediately opening folders. Other times it takes a few seconds “Loading content of “. And then other times it takes up to a minute it seems to load a folder.
Is there some tweak or something that can speed that up?
Thanks,
Shane
After you’ve been using SQL Developer for a while, the TableSettings.xml files tend to build up. For my version, they are found at:
%AppData%\Roaming\SQL Developer\system1.5.5.59.69\o.sqldeveloper.11.1.1.59.69\
Delete any file in this folder with a long number followed by “TableSettings.xml” (ex. “2144696926TableSettings.xml”), and SQL Developer will run a lot faster.
A more in-depth explanation of this is that whenever you run a query and view the results in the grid layout, it silently assigns a number to that query and creates a file with the column width, column order, etc settings that you’ve dragged and dropped. Deleting the file will not harm SQL Developer, but just return the results window for your queries to the default views (auto-sized columns, in the order of the query).
– Marc
Thanks Marc, but I don’t see how running queries and the tracking files created would make the Open File interface (as in exploring/navigating Windows directories and sub-directories in order to open a .sql file) take a long time to load the contents of those folders/directories. Especially when considering a fresh installation of SQL Developer (new computer, or after deleting all files and folders from old installations).
I’m not talking about running queries. Sorry if I wasn’t clear.
Unless you meant to reply to another post maybe?
Hi Jeff,
I want to generate scripts for all the tables available in schema.
I want to generate separate scripts for each table available in schema and each script must contains it’s dependent objects like indexes for that table or if triggers are available the this must include in the script.
I am using SQL developer 4.0.12 version. I tried with export option but separate scripts are generating for index,triggers,tables etc.
You can’t do this with SQL Developer. But, you CAN do it with Oracle SQL Developer Data Modeler – which runs inside of SQL Developer. You’ll need to import your data dictionary (your oracle database) to a new design/model – and then export/generate the DDL and use the ‘separate files’ option.
I’ll blog that today…
Hi! Is it possible to get SQL generated from a table in an easy way. In Toad you can click a table and choose “create select statement” or “create insert statement”.
Sure thing Peter!
Just drag and drop your table to the worksheet. You’re then prompted to get the name of the object, or one of several different types of SQL statements.
Hi Jeff,
I was searching for real active blog. I have landed in correct way. Thank you first of all.
I have recently attended an interview and he asked me a tricky question.
Q) consider an employee table with emp_id(Primary key) , Emp_name, Emp_sal
He asked me to use this query in SQL developer and do something which will interactively promt for EMP_ID and i should get the corresponding SAL for that EMP_ID.
NOTE:: after from table he said not to use anything.
select Emp_sal from Emp_table
How can we achieve this scenario , please guide me the approach.
Thanks,
ramesh
Sounds like a trick question. You need a where clause.
You could get trick and do this…
select emp_id fromtable, salary
from emp_table
where emp_id = :fromtable;
Maybe they wanted to know how you’d react to a question w/o a right answer?
Hi rp and Gary,
I make it clear.
1. I have created 5 Tests. The names of the Tests are: Test_1, Test_2, Test_3, Test_4 and Test_5. They are working fine individually.
2. I have created a Suite by combining the 5 Tests. The name of the Suite is Suite_1.
3. When I open the Suite, the Tests are listed in the same order. i.e., Test_1, Test_2, Test_3, Test_4 and Test_5.
4. But, when I run the Suite, the Tests are executed in the following order:
Test_3, Test_1, Test_4, Test_5 and Test_2
5. But I want the Tests to be executed in the following order:
Test_1, Test_2, Test_3, Test_4 and Test_5
6. I am unable to rename the Tests under the Suite node.
Even if I rename the Tests under the Tests node, and recreate the Suite, the order of execution is not proper. It is executing randomly.
Please help me to specify the order of execution. I use Version 4.0.0.13.
I have installed SQL Developer 4.0.2.15. I upgraded to Mavericks for my mac book pro. All of a sudden connection tab went missing. When I go to View -> Connection tab, nothing shows up, just blue screen. I am using tnsnames file for connections. All connections are correctly defined and SQL Developer is pointing to directory containing tnsnames file. I have 1.8.0_20-ea version of Java installed on my mac book.
Try, Window > Reset Windows to Factory Settings.
Using Java 8 shouldn’t be an issue, but we technically only support 7 at the moment. Disclaimer: I’m running v4 with Java 8 and it seems OK.
Jeff,
Thank you for your help. I got my connections window showing up now. But I am still not able to connect to any database.
I made sure that I have latest tnsnames file, and tried to create a new connection. After entering correct username, password, selecting ‘TNS’ in connection type, and selecting correct network alias, I hit test button, it ran successfully. But when I click ‘Save’ or ‘Connect’ button, nothing is happening (couple of times I saw a dialog box appearing and vanishing quickly before I have chance to read anything on it). The fact that I am seeing correct entries in network alias and test runs successfully proves that I have correct tnsnames file and SQL Developer is reading it.
Can you please help ?
Thanks and Regards
Chaitan Sukhadia
Try running SQL Developer in debug mode – take the sqldeveloper.conf file in the bin directory – edit the last line to include debug.conf vs nondebug.conf – open SQL Dev, and try to connect. Then check the log panel for any errors.
I can’t find bin directory you mentioned. When I opened up to show package content, I see two directories, MacOS and Resources, Resources have two directories, Emnglisj.Iproj and sqldeveloper. Now this sqldeveoper directory does not bin directory. It does have all other directories like lib, modules, rdbms, etc.
Nevermind, I found it
OK, Here is what I found in log.
In Messages
SEVERE: NULL Preferences during startup
In Logging Page, I find these two errors interesting, and they might be pointing to the problem
Sequence – Elapsed – Source – Message
728 – 3596 – o.ide.model.IdeSubject – Exception while updating observer[4]: oracle.jdevimp.runner.debug.JDebugger@55065ce9
721 – 1 – o.jdeveloper.db.DatabaseConnections – DatabaseConnections has no JNDI context so can not list connections
Hi Jeff,
Hows does one select or do a count check of result set (containing a nested array collection) returned from a function using unit test in the validation section?
Thanks.
I have been seeing 942 messages in the alert log. So I turned on trace to capture them. What I have found is sql developer doing queries that seem ‘useless’ eg: ‘select 1 from sys.external_tab$ where 1=2’ or ‘select 1 from dba_db_links where 1=2’. I suppose they are used to establish privilege, but they disturb my alert log monitor. So my questions are: what are they used for?; Can I safely grant privileges (select) on these table to prevent errors?; Is there a complete list of these queries?
We’re checking to see what views/tables we have access to, or to get column lists. You can safely ignore the 942’s, yes?
There’s no list.
Yes. But I have to filter through them and determine which are ignorable. I get 942 from other oracle system sources and I have been trying to track and cleanup any application 942s. It is one of those ‘false alarm shocks.
I would probably build a filter for SQLDev exceptions, this one would definitely go into that bucket.
Just downloaded 4.0.2 build 15.21
Prompted search replace is broken, it does not prompt you it just takes you to the first find and closes as if it were only searching.
I just tried it in a worksheet. Edit > Replace, scope set to prompt. First occurrence was found, and I got prompted with ‘Yes, No, All’