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 running Sql developer Version 4.0.0 and I notice in some of the connections it takes ages to get the query results.
For ex. there is one table with just one row and it takes 20 mins to retrieve the data from that table. When I run the same query in Sql Plus its gets the result instantly.
I tried using thick client, but the results are still the same.
I downloaded the latest version(4.0.3) and its the same.
Then I had a old version 3.2.2 and connected to the same DB and it returned results in 2 seconds.
Then I repeated the same thing in my colleague’s computer and same resuts, version 4.0.0 and 4.0.3 simply dont return any data. But 3.2.2 runs like a rocket.
Any idea about this strange behavior?
Excellent, thanks!
Hi Jeff,
Now that I’ve followed the advice from many of your posts and tweaked a number of SQL Developer preferences, is there an easy way to copy all those preferences to another computer?
Thanks!
Tim
Zip up your $USER$/AppData/Roaming Profiles/SQL Developer/versionX folder and move it over – that has everything.
Hi Jeff,
I have recently discovered your web site while trying to learn Oracle DB. Many of my questions have been answered for SQL Developer via your site, and I have learned a lot reading through your posts and answers to other questions.
As stated, I am wanting to learn Oracle DB. At home, I installed Oracle DB Express 11.2 on a CentOS server under VirtualBox on an Ubuntu host, and SQL Developer 4.0.3 on the host as well. I can connect to the database installed on CentOS, so communication between SQL Developer and Oracle DB Express works well.
The problem I am having is probably an Oracle SQL privilege problem, but since it manifests itself visible on SQL Developer, I’ll ask it anyway. Please excuse me if this is a newbie question, but I am a newbie to database!
If I log into the HR schema (as per the 2-day developer document), and create a new table called OFFICERS with some of my favorite fictional characters for data, and populate it with the data via the data pane, I can see it in the SQL Developer table detail table in the data pane tab. I see the columns headers for the columns I created on top, then row numbers for each row I created and the data in each row.
Now I created my own personal schema, e.g. MYNAME, and then connected to that schema and attempted to repeat the process of above. I can create the table with the columns using the New Table wizard. But once the table exists, and I go to the data pane tab on the Table detail, I see nothing. No column headers. When I hit the Insert Row button in the data pane tab, I see a +1 appear, but I have nothing to click on to enter data like I did with HR.OFFICERS.
Now if I go to the SQL Worksheet for the troublesome table MYNAME.OFFICERS, and do some INSERTs, e.g.
INSERT INTO OFFICERS VALUES (‘Captain’, ‘Kirk’, ‘James’);
INSERT INTO OFFICERS VALUES (‘Lieutenant Commander’, ‘Spock’, NULL);
COMMIT WORK;
SELECT * FROM OFFICERS;
I get in the Query Result window:
RANK LAST FIRST
———————————– ——— ———
Captain Kirk James
Lieutentant Commander Spock NULL
But if I go to the Table detail tab for MYNAME.OFFICERS, and look at the data pane, all I see is two row numbers for 1 and 2. No column headers and no data. Just the row numbers.
Why can I see the data in the data pane of the HR.OFFICERS table detail tab, but I cannot see the data or column headers in the data pane of the MYNAME.OFFICERS?
Is there a GRANT privilege(s) that has to be set for MYNAME that was already set for HR in order for this to work as expected?
Thank you.
Well, I figured it out eventually. I was going through the 2 day DBA document, and creating an example user “NICK”. When I had used “MYNAME” in the example above, I had actually made the user’s name in the format of “SMITH.NICK” (but actually another name). And I was not seeing data as detailed in my original post. When I created “NICK”, everything worked properly. So I tried creating the user “SMITH_NICK”, and lo and behold, it worked fine!
Evidently SQL Developer 4.0.3 cannot show data in the data pane if the user name has periods in it. The SQL Worksheet can use the user name with the periods (as long as I put it in double quotes), just not the data pane.
Is there a rule, recommendation, etc. somewhere documenting the format for a user/schema name?
Thanks to all who looked at this, and hopefully it will help somebody else learning Oracle DB.
Create following Three Tables.
table name ARTISTS
ARTIST ID ART_NAME
101 KISHORE
102 RAFI
103 LATA
104 SUKHVINDER
105 ASHA
table name SIMILAR_ARTIST
ARTISTID SIMARTISTID WEIGHT
101 102 4
103 105 3
table name ALBUMS
ALBUMID ARTISTID ALBUM_NAME
1001 103 ZZZ
1002 101 LATA
1003 101 LLL
1004 102 RAFI
1005 105 TTT
1006 104 FFF
1007 104 CCC
table name TRACKS
TRACKID ARTISTID TRACK_NAME LENGTH
201 103 AAA 456000
202 101 BBB 870000
203 104 CCC 650000
204 102 DDD 550000
205 105 EEE 530000
table name TRACKLIST
ALBUMID TRACKID TRACKNUM
11 1002 21
12 1001 12
13 1004 66
14 1004 51
15 1003 32
16 1002 19
17 1001 07
=============================================
Solve following request with the help of SQL query.
==============================================
1. List all of your table names in the database
Ans: select * from tab;
2. Find name of art_name with ‘A’ in their name.
Ans: select * from ARTISTS where ART_NAME like ‘A%’;
3. Find the names of all Tracks that are more than 10 minutes (600,000 ms) long.
Ans: select * from TRACKS where LENGTH >600000;
4. Find the names of all Artists who have recorded a self-titled Album (the name of theAlbum is the same as the name of the Artist).
Ans: ???
5. Find the names of all Artists who have recorded an Album on which the track is named“EEE”.
ANS: ???
6. Find the names of all Albums that have more than 30 tracks.
ANS: ???
7. List the name of each Artist, along with the name and average Track length of their Albumthat has the highest average Track length.
ANS:???
8. Find the average length of the tracks
ANS:???
9. Find the artist name and the track number for the trackid “204”.
ANS:???
Jeff,
Thanks for the 30 days Sql developer Tips! Its been great help for quick tips and whats happening in the Oracle World with runs 🙂
Lately, I have been using a lot of SQL Developer Data Model and recently installed Sql Developer 4.0.3 as well. I have got couple of question.
1. One of my fellow developer asked me, is there a way to color assign a column in Data Modeler like ERWIN (for calculated fields)?
2. When I open my data model (which was saved in 4.0.1) in 4.0.3, i keep getting error message as “Some objects are not loaded correctly. See the log file for details.”
How do I get around and fix this?
Appreciate your timely help.
Thanks
Moggie
Jeff ,
In my machine I have two version from SQL Developer , “4.0.2” and “4.0.3” .
In “4.0.2” shortcut key for list “Alt+0” it’s working fine .
but in “4.0.3” it’s not , I assigned new key , and no hope ! .
is this shortcut key work with you ?.
Thanks ,
Hi Jeff ,
In your “Tips in 30 Days, Day 24: Hiding Schemas That Don’t Own Anything” :
is there any way , using the DBA panel , to see just “Schemas That Don’t Own Anything” ?
Many thanks,
No, not as of today. Sounds like a good idea though to add filtering support to that tree.
Hi Jeff,
I’m on SQL Developer 3.2 had have a couple of 8i databases that I’d like to connect to … is that possible? If so, how?
Thank s Jeff
Sorry, we no longer support that version of Oracle. The JDBC drivers we use won’t connect to that ancient version.
If you grab an archived version of the tool, say version 1.2 maybe, you should be able to do some basic stuff with 8i.
Trying to work with ORacle Developer Data modeler . Installed jdts but can’t connect to SQL Server…
Failure error
Can you help
What version of the tool and what version of the jtds jar/driver did you get?
1.2.5
Today I started my VirtualBOx again and now I’m getting this error
Status : Failure -Test failed: I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.
When testing the connection to SQL Server…..
Currently investigating this error. Thought I read something about this….
You can use v1.3 if you’re on SQL Developer 4…what exactly do you have running on VirtualBox?
I created a Global Temp table from SQL Developer.
I then closed the connection, exited, and re-opened it.
To my surprise, the table was still cataloged !!
When do these tables get removed ?
Hi Jeff,
I get error message “svn: E200015: authentication cancelled” in SVN Console Log after my attempt to commit changes.
But I don’t see any problem in browsing SVN repository from Data Modeler, so it doesn’t look like wrong password problem.
What can it be?
Thanks in advance,
Ilia
That sounds like a support case to me – I would open an SR with My Oracle Support.
Hi Jiff,
I’m using SQL Developer Version 4.0.*, I always open new worksheet and write many of select query in it :
Why I cannot put a bookmark in specific line, while I can do that inside (procedure)?.
Is there shortcut key for collapse/Expand statement/procedure in plsql ?.
Jiff, what you are doing is a mighty work in every sense of the word.
Many Thanks,
I don’t have a good reason. I’ll keep asking and see what we can do to correct that!
I have a related question. I love the collapse statements feature in SQL Worksheet and code editor screens. Is it or will it be possible to collapse all plain sql statements the same way? I can collapse my sql statements one at a time, not all at once.
Also, I would like to be able to lock the collapsed view, so that I can edit code without uncollapsing everything.
Using Version 4.0
Hi Jeff,
When I try to connect a database using sql developer-4.0.1.14, I am getting below error
An error was encountered performing the requested operation:
Listener refused the connection with the following error:
ORA-12520, TNS:listener could not find available handler for requested type of server
Vendor code 12520
But to the same database I am able to connect from sql command prompt. What is the problem here.
Hi Jeff: v4.0.2.15.21 —
This isn’t a major thing but I notice that the grammar checker gives me a squiggly yellow line with SQL like
WITH THING AS(
SELECT OWNER, OBJECT_TYPE, COUNT(OWNER) OWNERCOUNT
FROM ALL_OBJECTS
GROUP BY OWNER, OBJECT_TYPE)
SELECT * from THING;
under
SELECT OWNER, OBJECT_TYPE, COUNT(OWNER) OWNERCOUNT
it says ‘select list inconsistent with GROUP BY – change GROUP BY to
GROUP BY OWNER, OBJECT_TYPE, COUNT(OWNER)
If you do as it suggests the query fails of course
ORA-00934: group function is not allowed here
00934. 00000 – “group function is not allowed here”
*Cause:
*Action:
Error at Line: 4 Column: 29
but if you take the offending line out of the CTE, the grammar warning goes away …
Obviously not one of the worst bugs of all time, but meh false positives.
This happens whether COUNT(OWNER) is aliased or not – the grammar checker seems to have a bit of difficulty parsing CTE’s …
When running in SQL developer, its second nature for me to hit Ctrl-Enter (Run Statement). However, I noted an odd behavior when creating triggers.
When creating a FOR EACH ROW trigger (attempting to use Ctrl-Enter) that references :new or :old, I am prompted to enter values for my bind variables new and old.
Is that expected behavior and is there a preference I can use that to disable it except when I specifically want to use bind variables?
Thanks so much!
JDK 1.7.0.67
oracle IDE 4.0.2.15.21
I will have her open a ticket. Thanks.
Sql developer hangs when I try to expand the tree for views? Any idea how to resolve this?
This is what we run when you go to expand the VIEWS panel in the tree
select * from ( SELECT ao.OBJECT_NAME, ao.OBJECT_ID, ” short_name,
DECODE(ao.STATUS, ‘INVALID’, ‘TRUE’, ‘FALSE’) INVALID, ao.OWNER OBJECT_OWNER, ao.CREATED, ao.LAST_DDL_TIME
FROM SYS.Dba_OBJECTS ao, sys.Dba_views av
WHERE ao.OWNER = :SCHEMA
and av.OWNER = :SCHEMA
AND ao.OBJECT_TYPE = :TYPE
AND ao.SUBOBJECT_NAME IS NULL
and ao.object_name = av.view_name
and av.editioning_view = ‘N’
union all
SELECT OBJECT_NAME, OBJECT_ID , syn.SYNONYM_NAME short_NAME,
DECODE(STATUS, ‘INVALID’, ‘TRUE’, ‘FALSE’) INVALID, SYN.TABLE_OWNER OBJECT_OWNER, O.CREATED, O.LAST_DDL_TIME
FROM SYS.Dba_OBJECTS O, sys.user_synonyms syn, sys.Dba_views av
WHERE syn.table_owner = o.owner
and syn.TABLE_NAME = o.object_NAME
and o.object_type = :TYPE
and o.object_name = av.view_name
and o.owner = av.owner
and editioning_view = ‘N’
and :INCLUDE_SYNS = 1
AND SUBOBJECT_NAME IS NULL
)
Runs with these params when prompted in the worksheet
“SCHEMA”=”your schema”, “TYPE”=”VIEW”, “INCLUDE_SYNS”=null
How long does that take to run, and how many views are loaded/retrieved?
I am actually working with a person offsite that is having this issue, when I expand the views on my pc, it runs ok, but when I run it on her pc I get the same issue, it just hangs.
I ran the query on my pc and it was instantaneous and returned 861 rows. I am waiting for her to get in so we can try it on her machine. I will reply when I get further results.
Ok- she is able to run the query and get results right back. The issue is just when she exands the tree for views in the connections window. She has version 4.0.2.15 and I have 3.0.4.
Any suggestions?
Let’s fix you first – upgrade to v4 🙂
It sounds like something isn’t happy on her machine when we go to paint those 804 tree node items. The app PROBABLY isn’t waiting on the database since the query comes back very quickly. But 804 items is relatively a large number of things for the UI to paint.
I have more questions, but might be easier for her to just open a Service Request with My Oracle Support.
Questions like, what OS is she running? What version of the JDK? How much memory on her machine? What does she see if she runs a jstack dump when SQLDev freezes?
Hi Jeff,
I have multiple rows in my table, and i want to generate multiple update queries at once, So i dont have to write that many update queries. Let me know how it is done?
Thanks.
Jitu
You should write an update that touches the multiple rows using a WHERE clause predicate.
So, if you have employees in dept 10 you want to give a raise, you would run
alter employees set salary = salary + (salary * 0.15) where deptno = 10;
Hi Jeff
I’m using sql developer version 4.0.2
How i can change the equel opertion, from (-) to (=)?
The defualt display is like that: DEPARTMENT_NAME – ‘Marketing’
and I would like to change it to: DEPARTMENT_NAME = ‘Marketing’
Thanks in advanced
Elad
Where exactly are you seeing this?
when I write the (=) sign. On the screen is written like this (-).
Thanks,
Elad
weird..what font are you using?
The font name is: Dialog.
I assume that this the default font, because I didn’t change the value…
Elad
I see several useful transformation scripts samples provided by tool. However, I would like to apply the transformation rules to a smaller set of tables present in my sub view. I researched and found the following function.
mySubview = model.getSubViewByName(“mySubview”);
Can I use this to get the list of tables in the sub view ? Any examples of how to use this function will help me to understand better.
Thanks
Brad
The API is documented here..similar directory for the full data modeler installation.
…\SQLDeveloper\sqldeveloper-4.0.2.15.21-x64\sqldeveloper\sqldeveloper\extensions\oracle.datamodeler\xmlmetadata\doc
If in the Query Builder I put multiple statements, like:
SELECT * FROM SPRIDEN WHERE SPRIDEN_PIDM = 12540;
SELECT * FROM STVTERM WHERE STVTERM_FA_PROC_YR = ‘1415’
then highlight both statements and click ‘Run Statement’ I get two query results windows. The first is labelled ‘Query Result’ and the second is ‘Query Result 1’. And the result tabs can be re-named.
The question is: is there a way so that when the statement executes and generates the result tab to have the tab automatically renamed so the user knows which tab is the result of which query? Query one might be renamed ‘Burdell’ (i.e.: after the name of the person it is querying) and the second ‘Aid Year’ (i.e.: after the aid year that it is querying).
mouse over the query result tab – it shows you the sql that was used to generate the results
Hi jeff,
My question is about the Oracle SQL Datamodeler.
I have some schemas created in my solution on datamodeler, like “Security” and “Person”.
But when i export the ddl file, my objects that are linked to schemas are generated without schema prefix name. e.g. a table named “User” linked with a schema named “Security” should be exported like “Security.User”, but it is being generated as “User” instead.
Thaks!
are you sure you select schema name “Security” or “Person” when you build User table , you should select your “schema name” in schem from “Table Properties”.
I am using Oracle SQL Developer Data Modeler Version 4.0.2.840.
RDBMS type is SQL Server 2008
When I “Generate DDL” schema names are not included in the CREATE TABLE, or ALTER TABLE.
How to add the schema name into the generated DDL?
Thank you
OMG, i have the same question :/
See my answer for Damir, and yes, that’s pretty weird 🙂
Open the physical model. In the physical model, add your schema, and then assign it to your table. It will then show up in the generated DDL.
*-* Thanks a lot man!
I did exactly that but no success.
Is there a way to add a screenshot to the question?
Not here, but you can on our OTN Forums. We have a Forum just for the modeler…
Can you share the Modelere OTN Forum link please?
Sure thing – open the modeler – go to the start page, click on the ‘Data Modeler on OTN’ button. Then click on the Forums button.
I have posted my screenshot here https://community.oracle.com/thread/3607679
Will you be able to tell what the problem is with the schema name missing in generated DDL?
Thank you
Did you see Philip’s reply in the forums?
Yes, it works now. Thank you
just corrected my issue just previously posted.
I changed my connection properties from using a connect identifier to netword alias and the vendor code 0 went away. Nothing had changed in my tnsnames.ora, but apparently something behind the scene had changed (maybe port number .. ?)
Doing that forces you from a thin to a thick connection – and that means the jdbc driver and your oracle client version needs to match. If you care to have it work both ways, otherwise, I wouldn’t worry about it.