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
Note a question , just a note.
I work with Oracle Developer Version 4.0.2.15 in Win7
I need to work switching between two languages English/Russian on keyboard. Few times I had situation when one language sticks and I couldn’t switch to another keyboard. In other application I can switch without any problem, but for Developer I have to restart it .It is rather annoying
Hey anyone know of a legend that explains the various icons and decorations used in SQLDeveloper?
Hi!
Thanks for all the great tips on tuning SQL Developer.
Unfortunately, I can’t find any solution to the single really annoying thing in SQL Developer (at least for me).
I am using version 4.1.1.19 on Windows 7 SP1 64-bit, JDK version 1.8.0_60 64-bit.
The issue:
When I open the details of some database table and switch to Data view, the whole SQL Developer window gets dimmed for some time (and some slanted dash appears in the top toolbar). This repeats when more data is loaded while scrolling down.
It happens regardless of the selected “Look and Feel”.
What I’ve tried:
I included the option “AddVMOption -Dsun.java2d.d3d=false” at the end of sqldeveloper.conf file in sqldeveloper/bin folder, but it doesn’t seem to have any effect. I tried also setting different combinations of settings for “sun.java2d.d3d”, “sun.java2d.translaccel” and “sun.java2d.opengl”, but to no avail.
Please, tell me this is not a “feature”…
Thanks!
is the annoying part the brief dimming until the grid paints or the amount of time it actually takes to display the records?
The problem is the brief dimming. Is there any way to avoid it?
(The time it takes is perfectly tolerable, considering the specs of my PC.)
Thank you for your response.
Yeah, it’s a known bug, and we’ll have a fix for it in 4.2. I can’t tell you when that will be though.
Thanks a lot! It’s comforting to know that this is not some twisted “feature”.
I’ll eagerly wait for the release of version 4.2 then…
Hi Jeff,
SQL Developer 4.1 and 4.1.1; APEX 4.0 and 5.0
I set up remote debugging between APEX and SQL Developer. Works like a charm, launch PLSQL from APEX and SQL Developer stops at the break point. Very sweet.
Problem: I want to go back to local debugging but now the local debugger displays the following message: “The debugger is not able to listen for JPDA using the specified parameters.” Remote debugging still works.
I tried this at work and at home (thus the different versions).
What is the trick to switch back and forth between local and remote debugging?
Cheers,
Patrick
Hi Jeff,
My question is about SQL Developer Data Modeler
After I import a DDL file into an existing model the date type VARCHAR or VARCHAR2 does not show its size. For example, in the file I have col1 VARCHAR(10) but the model shows col1 VARCHAR.
Any idea why this would happen?
weird, can you share with me your DDL file? [email protected]
Jeff, thank you for your response.
I was running 4.0.2-840, after upgrading to 4.1.1-888 this problem is gone.
Is there a way to order the columns alphabetically in the Single Record View screen? This is helpful on tables with many columns.
not today, you’d have to write your query such to reflect the column order you want
I have Oracle PL SQL developer exe (1.5.5) installed on Windows 7. When I query data, sometimes, I get a message “display driver stopped responding” and screen goes blank and gets restored again. I am facing this issue only when I am using this tool. Other applications I don’t face this issue. Any settings to be changes in SQL Developer tool?
Thanks
Ramky
1.5.5 is almost 10 years old.
Upgrade.
Brilliant response. 🙂
Jeff, thank you very much for keeping SQLDeveloper current!
One issue I see over and over is change tracking. Is it possible to add a preference capability – where you specify the DB/User/password which will be used as a repository of *ALL* changes done to *ANY* databases connected by SQLDeveloper.
For example:
(1) I connected to connection: db_1
update scott.emp set sal=sal+1000; — being holiday and all 🙂
commit;
(2) db_2
update scott.dept set ….
commit;
What I would like is a change log of *ALL* information logged to this database. Information such as :
username,service_name(if),oracle_sid,port,start_time,end_time,sql-statement, rows modified/selected etc, errorinfo,errorcode,dbuniuename,instancename,instancehostname,clob_log(actual log file outout @script level if @script is used)
— If using @ or @@sql_script – then log the full path of sql script as well – and any of the SQL’s inside the script as they run
— this will give me capacity to run some SQL against repository and tell what all I have touched …
We produce the log files etc but loading in table (using autonomous tx) will be really cool
Is this possible?
Thanks Jeff –
/S/
do you want auditing of user activity or do you want development change tracking for building deployment scripts?
Hi Jeff,
This is purely for tracking so I can apply the same change to other databases. Or run a report aginst the repository and find which DB does not have a particular @script run against it.
This is NOT an audit monster witch hunt 🙂
Thanks again
/S/
i would check out the Lifecycle Management Pack first, it’s already built, and there’s support for it in SQL Developer
Hi Jeff,
It would be nice if the script output window displayed a “Commit/Rollback Successful” or something similar after hitting the Commit or Rollback buttons.
Thanks,
Ron
We show it, just not where you’re looking for it.
Thanks Jeff,
I think that it would be helpful to show Commit or Rollback messages in the script output window as well (when clicking the commit button). I often run scripts manually and switch between multiple tabs and it would nice to see at a glance if I had committed my DML in a particular tab when I go back it.
I Posted a question on the SQL Developer forum a while back but have not had any response from anybody. I asked about locking columns in a data grid so that when one scrolls to right the locked column is always visible. Is this a better place ask such questions.
Thanks,
Ron
Hi Jeff
I have installed oracle 12c on Oracle Linux 7.1 provided on the Oracle Website. I am able to connect the database from the same system using sqlplus but from the SQL developer I am getting some issues for Input Output while creating connection.
An error was encountered performing the requested operation:
IO Error: The Network Adapter could not establish the connection
Vendor code 17002
rpm file for SQL Developer is also downloaded from Oracle Website.
Thanks
Sayam
SQLDev can’t get to the server you’re telling it to go to. I need to see your connection details for both SQL*Plus and SQLDev to guess what the problem is.
I have a Oracle Database of the client that I am accessing through Citrix using Putty provided inside the Citrix window.
I want to use the database using SQL Developer on my machine using SSH tunnelling.As per your post http://www.thatjeffsmith.com/archive/2014/09/30-sql-developer-tips-in-30-days-day-17-using-ssh-tunnels/
I tried the steps but I am not able to connect to Oracle Database.
Also when I ping the IP address of the Putty Client it does not give any response.
So I would like to know how to access the database on the client server which is accessible using Citrix Putty.
To simplify, I enter a URL for Citrix and logon to client’s Citrix Domain.
Then there are few links available as icons for Test region, Development region and Putty Software.
I click on Putty software and logon to their Oracle database using Putty terminal window.I want to access the same database through SQL developer installed on my machine.How to proceed ahead with it?
Thanks in advance
When I search for a specific text inside a package body with several long procedures it shows and highlights the found text. However – I need it to highlight on the package tree on the left tin which procedure this text was found. Is this possible?
When the procedures are long and I need to see in which procedure (in the package) it found the text I need to scroll up in the body .
Just open the Outline, right-click in your editor, Quick Outline
When you click into the code, it will highlight in the outline where you’re at…
Thanks!
I recently upgraded to SQL Dev 4.1.1.19 – after upgrading, spooling out data to a file from a script is limited to 5000 rows. I overcame this obstacle with the former version of SQL Dev. It appears that I now have to find a way to overcome it again.
So I set Tools > Preferences > Database > Worksheet > Max Rows to print in a script to 500000 (that’s 500K).
I also set Max lines in script output to the same.
I added login.sql to my startup script. It contains this:
set linesize 2500
set pagesize 50000
set timing on
The result? No change. It still outputs only 5000 rows. I have searched high and low for other options, but there doesn’t seem to be one published anywhere on the web that will solve this.
Why have the option to set Max rows to print in a script if it doesn’t work? This appears to be an application bug to me.
Thanks for the help,
Jack
I should add that SQL Dev has been restarted several times since making these changes…
Hello,
Whereas SqlDeveloper actually let the user peak an Oracle Client for the use of the OCI Stack :
Tools > Preferences > Advanced > Use Oracle Client and Use OCI/Thick driver checkbox (and Tns directory)
…SDDM (SqlDeveloper Data Modeler 4.1) doesn’t seem to offer any path selection ; only :
Tools > Preferences > Data Modeler > Use OCI/Thick driver check box (and Tns directory)
II’ve tried playing around with %PATH% and %ORACLE_HOME% environment variables amongst my many ora clients but they don’t seem to be taken into account.
How do you set for sure a specific OCI stack image ?
Thanks
you can’t do that for the modeler, it just tries the first one it can find I think
as a workaround have you tried to run the modeler inside of sql developer?
Hi Jeff,
Your workaround is effective indeed.
…but less efficient in term of performance compared to the standalone product ; I guess I’ll manage to import metadata through the plugin and will actually work on the model afterwards with the standalone sddm.
Thanks
Xavier
Hi Jeff – I’ve made an infographic about Standard Edition 2 here:
http://www.xynomix.com/oracle-standard-edition-2-what-you-need-know
Is it accurate? Let me know what you think.
Thanks muchly
Richard
I think there might be a couple of inaccuracies around the hardware bits, but I’m not an official spokesman for Oracle.
Thanks Jeff! There’s currently a socket-related discussion going on on our site, so I’m not sure about its accuracy at the moment!
Hi Jeff. Does SQL developer has possibility to read messages from pipe in real time (when i use dbms_pipe) like PL/SQL Developer feature Event Monitor.
Thanks
Hello
i want to clear unit test repository for each build, as i am planning to integrate this tool in build system. is there any command by which i can clear the repository ? (we can do it graphically but it is not feasible to do it graphically for each build) i want to automate it.
http://reldesgen.blogspot.com/2015/05/how-to-delete-sqldeveloper-unit-test.html
Hi Jeff,
Pretty nice informations on your site here! Have been multiple times around here but not yet found the solution on my “Problem”:
I am looking for the setting so that the “open”-Dialog always goes to my Default SQL-Folder where we store our scripts. It always starts at the first drive-entry in the open Dialog (which is in our case the A:\ for the old emulated floppy on our Server…).
Do you have an idea how we can set the Default path for the open Dialog?
Thanks and best regards,
Pius
sorry I forgot to provide some essential informations:
we use Version 4.1.1.19 on a Windows 2012r2 server
After you open a file on that editor type, to a different directory than A:…and then go back to open another file, or save the existing, is it still on A:? It should be in that same directory.
Also, in newer versions of the tool, you should see on the left, a list of at least 5 of the MRU directories you can auto-navigate too.
Additionally, many of the ‘file pickers’ in dialogs should have a dropdown control now that lets you re-use previous selections.
Hi Jeff,
Thank you for your answer, thats right. When I open a file after already navigate to a folder where I opened something before it starts there. Unfortunately that also lasts until I close the tool and reopen it. But the “last used Folders” on the left side are more persistent.
I would love an option where I can define a “default folder for opening” or something similar but haven’t found an Option like that.
Best regards from switzerland,
Pius
I’m using SQLdeveloper v 4.1.0.19. Why is the Refresh button not working. The Delete file button works fine.
Thank you.
The refresh button, where exactly? We have more than 1 of those spread out amongst millions of lines of code 🙂
How can you reduce the size of alert.log data returned in the Reports section? I’m new to sqldeveloper and it looks like it is querying x$dbgalertext to get alert.log info…however, in PROD it only returns last 24hrs of alert.log, but in DEV it returns all alert.log info from day1? I know there is a filter, but why the difference between PROD and DEV results? I would like to restrict what is returned to last 7 days every time I click on alert.log. How do I enforce that?
Thanks!
Chris
create a custom report, add a where clause predicate to filter on days or number of rows
I’m using SQL Developer 4.1.1.19 and am having trouble launching the debugging process. After ensuring I’ve got all the needed privileges, compiled the package for debug and invoked the debug-script dialog for setting the parameters and calling the routine to debug, then click OK, the debugging log appears at the bottom of SQL Developer but has no output (no dbms_debug_jdwp.connect_tcp call, for example), and the shiny “launching” bar at the lower right shows “Launching / Starting target”, but no process actually starts, so I cannot debug and no buttons appear anywhere allowing me to step into the procedure I want to debug. I wonder if you can give a hint where I might look to fix this problem? Many thanks!
try enabling this in the preferences, Prompt for Debugger Host for Database Debugging
OracleSqlDev Version : 4.1.1.19
to see time portion in DATE fields in DataGrids, i edited NLS option in preferences menu to “dd/mm/yyyy HH24:MI:SS” as described in your posts, and that worked in both Objects Viewer and SQL Worksheets QueryResult TAB, and that seems to change the session parameter.
BUT, when i run scripts containing any DATE handling, inserts, updates, like executing “fnd_global.apps_initialize” it gives :
ORA-06502: PL/SQL: numeric or value error: character string buffer too small has been detected in fnd_global.set_nls
which caused by trying to accept DATETIME format while it expects DATE format.
SO, is there any way to change date displayed format in both ObjectViewer and , without changing NLS option and leaving session parameters as it is ??
any help appreciated,
ayman
yes, you can set your session parameters manually
or define them in your fnd_global.apps_initialize script or stored procedure
thank you for reply, but i think the NLS option in SqlDev is already changing session parameter to achieve it’s functionality, so if i change session parameter manually, i’ll be forced to change it back again to see time in ObjectViewer and QueryResults TAB, and looping forever to modify that, which is inapplicable approach.
I’m looking for a “Display” date format, leaving session parameters to be handled by database
thanks for help,
ayman
Tools > Preferences > Database > NLS > Skip NLS – check that box.
The best way to handle dates is to include the date format you want in your query, so if you always want to see the time, have your query be explicit in how you want it returned, i.e.:
select to_char(sysdate, ‘MON’) from dual — will ALWAYS ONLY show the MONTH.
Hi Jeff,
I’m using SQL Developer 4.0.3.16. I’m editing large packages that have been developed and edited by several people over the years. There’s a mix of upper and lower case object names in this code. When I search for a table name in the editor, it does a case sensitive search and only finds matches for the object name in the code with the same case. Can I change this feature so I can find all upper/lower/mixed case versions of a table name in the PL/SQL editor?
Thanks,
Jeff
In 4.1 there’s a case sensitive toggle in the editor…PRETTY sure there’s one in 4.0 as well. Did you look?
Sorry I’ve been through all of the code editor preferences and I don’t see any one that indicates case sensitivity.
A co-worker found the toggle for me in the search drop down menu. It’s working as desired now. Sorry to bother you with this.
Cool, thank your co-worker for me please. Some cool things in 4.1 too if you have the time to upgrade.
Hi Smith,
Please help me to get out this issue.
Thanks for the mail smith..
Now am getting the error “Unable to launch the java virtual machine located at path: c:\sqldeveloper\jdk\jre\bin\server\jvm.dll”.
But I copied this file also in that location mentioned above.
try Google
there’s at least 2 or 3 threads on forums that talk about what’s going on here
why are you launching from the bin directory instead of from the application directory root? use the exe in sqldeveloper main directory