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,561 Comments
Hi Jeff
I sometimes run a bad query that runs for ages (I’m sure we all do).
Using the Cancel Query button does not seem to kill the query in most cases.
Is there a good way to get your session back without sys access?
you can try running with the thick client – it has better query cancellation support in the db than over jdbc. Details here.
Hi Jeff,
Congratulations for the mature stage of SQL Developer.
It’s my choice to demonstrate some snippets to my colleagues.
https://youtu.be/8fEDcedIr3g
when you select the word and right-click to open the object, you can alternatively ctrl-mouse hover-click…might be faster. thanks for sharing!
Yes Sir,
I knew that but I thought the audience wouldn’t notice that short.
Thank you so much.
Hi Jeff,
have upgraded to sqldeveloper 4.1.1.19 a couple of months ago and have one issue. My previous login.sql script doesn’t appear to work as it did with the previous version. Basically i dont’t see any output from dbms_output. I have tried issuing selects in the login.sql script, but don’t see the results of these either. Here’s a snippet of my login.sql
— start
set serveroutput on
set termout on
declare
v_mode varchar2(100) := sys_context(‘userenv’,’database_role’);
begin
dbms_output.put_line(‘–‘||v_mode||’ ‘||v_mode||’ ‘||v_mode||’ ‘||v_mode||’ ‘||v_mode||’ ‘||v_mode||’ ‘||v_mode||’ –‘);
end;
— end
I know the login.sql script is running as i have put inserts in there and then viewed the data in the relevant tables after i have connected.
Any ideas ??
Are you checking in the right place?
Jeff,
As part of minimizing risk, I am looking for a way to password protect the SQL Developer application. The scenario we are envisioning is that by some means, an intruder has gained access to one of our DBA’s stations. By simply opening the SQL Developer app, that intruder could use the application and perform privileged actions by opening a connection through SQL developer.
Does SQL developer have a method to password protect the application as a whole? As a means to add a roadblock in the way of any intruder? If not, are there any plug-ins that could help?
you need to secure the machine
the passwords are encrypted and can’t be read outside the machine and UI
you can disable the ability to save passwords, but that won’t help you if the user is already connected and the walk away
ideally the machines lock themselves down after a few minutes of inactivity
what do you do if a hacker gets access to a terminal and can login to a database as SYS using a simple, sqlplus / as sysdba?
Hi, I’m a big fan of the Find/Highlight feature in SQLDeveloper. I find that persisted highlights do not persist very long though and disappear sporadically when changing search paremeters in a report. Is there a way to lock them in?
I don’t think it’s sporadic, I think it’s performing the search immediately on you entering/changing text in the ‘find’ toolbar. I can’t find a way to ‘lock in’ the highlights.
Hi Jeff, I use SQL Dev 4.1.2 and I frequently browse large tables. SQL Dev appears to hang, & I get a ‘waiting to initialize’ message and then nothing. I have to kill SQL Dev and try again. Is this a memory thing? I have increased my memory available to SQL Dev, by adding Add64VMOption -Xmx1024m – which is up from the default 800m as you can see. This seems to have fixed it. Would this be the correct thing to do? Sorry I forgot to mention I’m using Windows 7 64bit
Thanks,
Vic
You could also try lowering the fetch array size in the preferences to retrieve fewer rows at a time, but adding memory is nice if you have it available.
Hi Jeff,
We are using SQL Developer 4.1.0.19 – Build MAIN-19.07. We are using Oracle 11g Enterprise.
Operating System is Windows 7.
We are having an issue when compiling Packages.
There are times when the compilation process causes SQL Developer to hang.
It seems that the only way to get back to a state where we can compile the Package is to shut down the SQL Developer instance and restart it. We normally do this by going to the Task Manager and ending the task.
Is this possibly an issue with SQL Developer? If so, do you have any suggestions for a workaround?
Thanks.
did you check for wait events/locks for your session in the database?
what’s happening in the database when you do the compile?
I talked with our DBA. He has asked if there have been any reported issues between SQL Developer and the Oracle Database Firewall.
Would you have any information on this?
Thanks again.
That’s a pretty vague question. I don’t know of any, but I haven’t searched the bug DB to know for sure. Tell your DBA he can open an SR with MOS for SQL Developer if he needs to.
Sounds good. I will relay the information.
Thanks again.
Hi Jeff,
in terms of customer support and development i use quite a number of SQSDev installations on various machines (all windows).
on every installation i have to make the same changes in the configuration for having SQLDev look and work the same. no matter on which machine i’m logged in.
is there a possibility to (auto) synch the configuration over different systems?
as an example the tortoise client offers such a feature: you can define a synch directory in which the config is stored; outside the regular path. if you place this synch on lets say your drop box directory you have an easy way to transfer changes in the setting over a great number of installations.
if not existing, does this sound like a cool feature?
yup, check out this post from Kris
Hi Jeff.
We are planning for migrating our data models from CA ERwin to SDDM. One requirement is collaborative development and I have a great hope that we can use the Team functionality. With SD 4.0 or SD 4.1, what version of Subversion are supported/required (… we have an existing Subversion 1.6.11 installation)?
Thanks!
Jo
i believe 1.6, 1.7, and 1.8 should all be OK
1.7 would probably be best
I’m using SD 4.1.1 and 4.1.2 – coloring the connections is great. But even if I have read (and seen in some of your pictures) that the folders could be colored too I don’t find how it works? Any hint would be great … THX
that’s new to me..colored connection folders?
Hi, my requirement is to inject details like Connection Name, Username, Password, Hostname, Port and SID from the command line (or using any API if exist) into Oracle SQL Developer for Oracle database, so that all these details are prefilled for the user and user just need to click on the connect button to establish a database connection. I am doing this to broker the database connection.
Any guidance on this please?
we don’t support this (sending connect string to SQLDev from CLI) today..but you could create the connections.xml file for them that has their connections already defined and ready to go
Is it possible to create/update it programmatically. And upon modification, will the Oracle SQL developer pre-filled with the fields like Hostname, username, password, etc?
Where I can find this file? Any tutorial you can suggest me on this?
it’s an xml file, search for connections.xml
you could create it programmatically as long as you follow to the xml structure expected
Hello jeff, thanks for your reply. I am sorry but I searched and found no file with the name of connections.xml. I searched in my all drives.
I installed Oracle SQL developer and it is installed under “\Program Files (x86)\Microsoft Application Virtualization Client” folder. I do have file like sfterr.xml under it but no able to trace connections.xml anywhere in my system? Is something I missed during installation? I have installed SQL Developer 4.0.3 version and I am able to connect it with Oracle database system using this client.
It’s in the AppData folder for your OS user
hi,
im using SQL developer after i switched from toad,
the data grid/query result in toad used to show up as a table when we ran the query,
the query result is plain in sql developer by default,
i want my result to be in table format,
i tried changing a lot of things in colours in preferences but can’t find this one.
please let me know
use ctrl+enter of F9 to execute a query and get the results back in a table/grid
doesn’t work 🙁 i fire a query always by ctrl+enter,
and it is a table, i want the cells separated by a line.
that is not happening 🙁
can u suggest something?
can you send a screenshot so I can see what you’re seeing?
Hallo,
The sqlcl is amazing. But…
as i run the dbms_job procedure with SQLcl, then always failed. F. e.:
SQL> exec sys.dbms_job.broken(90,true);
Fehler beim Start in Zeile : 1 in Befehl –
exec sys.dbms_job.broken(90,true)
Fehlerbericht –
ORA-06550: Zeile 1, Spalte 7:
PLS-00306: Falsche Anzahl oder Typen von Argumenten in Aufruf von ‘BROKEN’
ORA-06550: Zeile 1, Spalte 7:
PL/SQL: Statement ignored
06550. 00000 – “line %s, column %s:\n%s”
*Cause: Usually a PL/SQL compilation error.
*Action:
Sorry im not knowing how to change the output language.
Could you please give me some advices?
thank you
Jipeng
Hi,
When I go here:
View > Show Toolbar > Customize Main Toolbar > Initializing…
It say this
How do I solve this?
Thanks.
ok to solve this you need to update sql developer (even though when i went to view updates within the app it said there were none)
hmmm
Who said you needed an update? I haven’t taken a look at your original question yet, on vacation until Monday.
There’s not too much customization available for the main toolbar as it is, what are you trying to do in the first place?
I checked the version of a colleague who doesn’t have this problem and thought an update may help,
I was on 4.1.1, now i’m on 4.1.2 and all is well
As an admin/teacher when I have my class do a project involving writing SQL statements is there a way I can see if anyone has looked at someone else’s Saved SQL statement that they have written? Or can I only see if they ran someone’s Saved SQL statement?
Jeff,
I hope you see this question and reply back. I was worried when I saw some of these questions unanswered. I would like to know if I can or not see what I asked. I was thinking about using APEX for teaching.
Your question is about Apex, not SQL Developer. I was going to say “Go Ask Tom” as per the post above, but then, you are Tom already…
Give each student their own OS user, that should segregate the histories. Or, create a batch/shell script to launch sqldev that starts with deleting the history files
SQL DEVELOPER: Create Unit Test Wizard too slow, why?
Hi Jeff,
Why does it take such along time (it can take between 40-60 secs or more) to display the package list during ‘SELECT OPERATION’ step in the ‘Create Unit Test Wizard’?
Is there a way to speed it up?
Thanks.
you might need to update your data dictionary stats
Hello Jeff
I need a row limit for a query to testing purposes for our crm.
So i got from another section an query which i have to use with an row limit ( for example the first 10 rows based on Field FIRMANR).
But i came across with the location where the row colum command had to stand in the query. I thought before all correlations command like order an so on .
Here is the query:
SELECT DISTINCT RELES.AKTJAHR,
RELES.VORJAHR,
RELES.QUARTALE_1,
RELFIRMA.NAME,
RELFIRMA.KTXT,
RELES.VORVORJAHR,
RELFIRMA.FIRMANR,
RELANSCH.STRASSE,
RELANSCH.ORT,
RELANSCH.LAND,
RELANSCH.STAAT,
RELANSCH.PLZORT,
RELANSCH.PLZPOSTFACH,
RELANSCH.POSTFACH,
RELACP.VERTRETER2,
RELACP.VERTRETER1,
RELANSCH.LANDKNG
RELES.AKTJAHR,
RELES.VORVORJAHR,
FROM (((INFOR.RELFIRMA RELFIRMA
INNER JOIN INFOR.RELADRESSE RELADRESSE
ON (RELFIRMA.FIRMANR = RELADRESSE.FIRMANR))
INNER JOIN INFOR.RELANSCH RELANSCH
ON (RELADRESSE.ANSCHRIFTNR = RELANSCH.ANSCHRIFTNR))
INNER JOIN INFOR.RELES RELES
ON (RELES.DATENFELDER_1 = RELFIRMA.FIRMANR))
LEFT OUTER JOIN INFOR.RELACP RELACP ON (RELFIRMA.FIRMANR = RELACP.MNR)
WHERE ( RELANSCH.VERWENDUNG1 = 1
AND ( (RELES.SATZART = ‘UK’ AND RELFIRMA.VERWENDUNG1 = 1)
AND (RELES.AKTJAHR > 10000 OR RELES.VORJAHR > 10000)
From my understand it has to be in the near of thw WHERE clause or ?
thanks for help
best regards john
Hi Jeff,
I have only recently started using SQL Developer and find it very useful and flexible. I have a couple of questions re. SQL Developer functionality (please let me know if you would like me to post them as two separate comments):
1. How do I save my windows layout in SQL Developer? For my work, I like to have six separate document tab groups. I configure it when I start SQL Developer first time. But when I exit and re-open, it doesn’t have those document tab groups. Is there a way for me to save the look-and-feel, so I don’t have to reconfigure it every time I restart SQL Developer?
2. How do I see parent-child data in SQL Developer? This is similar to the master detail browser in TOAD? Is there a way for me to view parent and child information simultaneously such that if i change the parent value, the child value will automatically modify accordingly?
Thanks a lot in advance,
Niraj
Jeff,
Quick update…
I was able to figure out how to create master-detail report. But I would really like to hear from you response to my other question.
Thanks,
Niraj
I like what you’ve done here! Thanks for being so available to customers.
I have multiple Oracle clients, can I specify which Oracle client that SDDM 4.0.3.853 is using? Trying to get past this error when connecting to a DB.
Status : Failure -Test failed: Incompatible version of libocijdbc[Jdbc:112020, Jdbc-OCI:112030
Did you ever find a resolution to this issue? I’m running into the same problem
Use a thin jdbc connection, unless you really need a thick connection. What version of SQL Developer Data Modeler are you using?
Specifying the JDBC connection string solved it. Data Modeler v17.3.0.261
Steps to reproduce
1. File > Import > Data Dictionary
2. Connection Properties
3. Connection Type: Oracle – TNS
4. Cry
I have tried specifying the Oracle Client and TNSnames directory in the Preferences > Data Modeler menu without luck.
Seems like specifying a connection by TNS should work out of the box.
That’s the idea. There’s a spot in the preferences to define your tnsnames location, and another to force ‘thick’ connections. I’ll give the modeler a once-over tomorrow morning and make sure there’s nothing crazy wrong on our side. Sorry for the tears.
Hi,
Every time I open SQL Developer 4.0.1.14 it displays things differently. I want the main toolbar hidden (more on that in a mo) connections visible and everything else closed.
Sometimes I get connections sometimes i don’t, sometimes I get this crazy long toolbar sometimes I get a useful compact toolbar.
Is there anyway just to save your setting so you don’t have to set it up and change it every time you open it?
Thanks, Dave
Hi Jeff,
Can you tell me if it exists a way to display the parameters needed for a function or a procedure while coding.
Thanks,
yes, see this post
Thanks for your quick response, but it is not working for me.
Are there any special conditions or special configuration of the application to display these parameters?
Or should the database must be in 12c?
Thanks,
Hi Jeff,
This may have already been asked, but I searched for it and could not find it.
Within SQL Developer, is it possible to prompt a user to choose between 2 choices. I have them entering data into a prompt via :hierarchy_name but would like to offer them to choose between the 2 sets of text, they can choose from.
Basically each time a query is run they have a choice of entering into the field either “internal hierarchy name” or “external hierarchy name” and I thought it would be good if there was either an option to choose or at worst both of these showed in the field and they could delete one, but I cannot work out if this is possible?
Hope that makes sense!
Deryk
Hi Jeff,
Is there a way in SQL Developer to “save your place” so your worksheets and query results and everything open up again in a new session? My colleague’s SQL Developer does this for him every day, and I’d love to have this ability as well. When he opens SQL Developer tomorrow, it will have all his open worksheets and query result windows right there for him. To run anything, he just has to select the proper connection. But, he can’t figure out how he got it to be that way!
Thanks!
Laurie
Sorry, no. If you close SQL Developer with a document open, on restart, it should re-open that file for you. But that’s not what you’re really asking for…
Okay, thanks anyway, Jeff!
Hi Jeff
Is it possible for a sql worksheet to “view” the window of another connection in the same grid.
It would be a cool feature to have, if i can fire one query say “select rate_value from job_table” and i get to see the status of the rates in DEV, QA, PROD in one grid.
Not needed for any write transactions, a view only data(ie SELECT can support). 🙂
Thanks George
I don’t like this feature, but it’ll do what you want
Auto-DB Linking
Jeff, does SQL Developer allow specifying the edition to connect to when using Edition-Based Redefinition?
not today but we’re talking to the PM now for better EBR support