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,937 Comments
Hi Jeff,
I am using SQL Developer 4.0.3.16 and have been using it along with a JDBC driver (jtds-1.3.1.jar) to connect to SQL Server databases at our site. Recently, one of the SQL Server databases we connect to has been modified to only allow encrypted connections. My connection via SQL Developer now fails and the SQL Server error is Error: 17835, Severity: 20, State: 1 (dropped the connection since it was not encrypted). The error returned by SQL Developer is “I/O error. DB Server closed connection”.
I do not see any options on the SQL Server connection to set it to use an encrypted connection. Does SQL Developer support encrypted connections to SQL Server databases, and if so, how would you go about setting that up?
Thank you.
Bill
Hi Jeff,
i use following template for “Automatic Index” in data modeler:
{child abbr}_SUBSTR(5,5,FRONT,{parent abbr})_FK_I
I have only checked in DDL preferences “Generate Automatic Indexes” “Foreign Key Constraints “.
In created a parent-child table without index on the FK(which was created correctly(nearly same template in “Foreign Key” only without the _I).
In my generated DDL is find following code:
CREATE INDEX “{child abbr}_ent a_FK_I” ON …
Whats wrong ?
I have installed oracle sql developer 12c, I donot know how I can connect to database to create a new connection?Thanks in advance
Have you read this?
Hi Jeff
I have read that the data modeler supports EDITIONING option on views. I can’t seem to figure that out. I can import a data dictionary from and EBR enabled schema and the EBR related views and triggers. Does the data modeler support EBR like SQL Developer?
Sorry for the late response, I’ve been on vacation and then a quick trip to headquarters. Just now catching up. You can do this, that sort of information is stored in the Physical Model.
Relating to my previous post, the first scenario is an existing saved query. The second scenario is when I start a new query in the Query Builder.
I’m using SQL Developer Version 4.0.3.16. When I go into query builder to look at the tables I have chosen, it only displays the table name but not the columns. What can I do to see the columns? At this point, I am unable to even add another table to the query builder visual interface. When I start a new SQL Worksheet and connect and click on Query Builder, I am able to add tables and I can see the table name and columns. Your advice would be appreciated. thank you.
If we are not licensed for Tuning or Change Management packs is there a way (or need) to disable this functionality in SQL Developer?
I would disable access via database security, grants on packages for example.
Hi All,
I’m working with SQL Developer version 4.0.2 it always hanging up with period aproximately once per week. Sure I’m don’t saved code that I written during half of the day. It’s hangs up and all what I can do it just kill process from ProcessExplorer. What is a problem can be? It’s impossible to work.
Open a support case. Upgrade to 4.0.3. Try 4.1. Grab a jstack dump the next time it hangs.
what are you doing when it hangs?
Last time program up-time was 100 h. It always hanging when I switching between different connections and database objects from one Package to another and etc. I grab dump today but can’t find it, it’s gone)))). Ok, next time I going to open support case and attach dumps to it. This problem bit me not first time I experience it during long period of time from version 4 it always happens.
Try disabling the breadcrumbs feature.
Or close the app every few days??
Hi Jeff!
Today it’s hanging up twice))). last time was opened only one connection and only one tab. In both of cases I just typed a code in a package. I think I should disable features like pop up hints and etc.
Will try to disable this ‘breadcrumbs’
I using SQL Developer version Version 4.0.3.16,
Windows 7 Professional 64 bit,
Java(TM) Platform 1.7.0_55
I use SQL Developer, Version 4.0.3.16 on OS X (v 10.10.2) and I’m having trouble with the Connections tab. When the window is full and I want to scroll up or down, sometimes the interface sets it’s mind to displaying a certain portion of the list, regardless of what I’ve done with the cursor. Any ideas?
Window is full – you mean, you have more connections than you can fit w/o a scrollbar?
I’ll have to test that, but in the meantime – try using connection folders. You’ll save yourself a bunch of time and stress looking for connections.
Thank you for answering so quickly.
I mean that when I expand a connection to show its elements and eventually table names and column names, there are more results than can fit into the window without a scroll bar.
I only use four connections, but each has multiple schemas (Other Users) and each schema has hundreds of tables.
So you’re not seeing a scrollbar? Can you take a screenshot so i can see exactly what’s happening?
Hi Jeff
We are thinking about use SQL Developer for creating unit tests on our DB code.
But when we test this on SQL Developer we can’t see the private procedures, only the public ones.
Is there a way to access the private procedures without make them public?
E.g. using condition compile or similair
/Regards Fredrik
Hi Frederik,
i can not get that point. i tried and it worked. i could see all functions / packages. pls make sure you use the correct connection (upper right corner) during the creation of the test.
btw: what u mean “private” function? as far as i understood you can only test functions/procedures of a package that a accessible from outside (public) the package. just like you would use that package in your application source.
Hi
Yes, with private functions/procedures I mean functions/procedures inside packages.
We only see functions/procedures that are both in the body and the spec of a package.
If the function/procedure only exist in the body of a package we can’t see it.
/Fredrik
Hi Frederik,
i mean i’m not a pro in using testsuite. but as far as i understood the system, the tests just do run simple PL/sql statetements. they call whatever function that you configure and analyze the result.
so they behave just like ordinary pl/sql code. and for that reason you can only call published functions (procedures) from your package. just like any other user of your package would.
greetings
peter
Hi Jeff,
today i wanted to give the units testing feature a try. after following the tutorial, i created the first simple test unit>worked like charm.
after that quick success i turned to the real project: test the methods of my own object type model an their methods.
that failed poorly right at the start when creating the test unit.
the approach: to use the objects i in a package i developed a set of functions in a an procedures as a kind of api to access the methods of the underlying objects.
this packages will be used by the application developer. so it seems the ideal point for some test units. the creation of a test unit for a package function which needs any parameter of my object model, fails. the error message reads something like: the type “object” for the argument is not supported, argument 1 uses unsupported data type” (hope my translation of the message is understandable)
does this mean unit testing with object types is not possible? 🙁
greetings
peter
Hi Jeff,
With SQL Developer 4.0.3.16.84.
From menu Tools > Database Diff…
I have two issues:
1) Once the Diff wizard is finished and the Diff Report appears, there should be a heading/label on the two bottom panes, on the DDL tab, perhaps next to the heading “(View-Only)” to indicate which DB the DDL statements are related to. This is cosmetic and on the top there is an indication of format “DB1 > DB2”, but it would simplify things.
2) The Diff wizard on step 3 has a filter/window to choose the schema. But on execution the Diff compare is done with the user of the source connection.
the DIFF will always use the source connection schema for the DIFF. If you pick objects in the wizard outside the schema, then it grabs them too, but it will always use the source schema
Jeff, I beg to DIFFer. I tried it again. In my case my source and target connection schemas were SYSTEM. In the DIFF wizard step 2, I chose Object types “tables”, only. In step 3, I chose a non-SYSTEM schema and type: tables. The DIFF report only shows tables from the SYSTEM schema. And yes there are tables in the non-SYSTEM schema that I chose that will show if my connection schema is that same non-SYSTEM schema.
Why are you so lazy?
You stated “I had a hard, hard time finding a decent beer in Seattle.”
I say you just didn’t try very hard! The Northwest is home to the modern revival of microbreweries and brewpubs. Washington State (specifically the Yakima Valley) produces 75% of the nation’s hops.
But seriously, I just found you site and have already learned a ton from your tips.
Thanks.
That’s fair – I didn’t do any homework. I did try a half-dozen places and ask for locals…and even went to one micro. Just not the right ones apparently.
Thanks for the feedback, really appreciate it Troy!
Figuring out SQL Developer extensions has eluded me thus far. I can’t seem to find up-to-date documentation that relates to what I want to do. I’m starting to wonder if it’s possible.
I would like to highlight some data in the object viewer (Data view), and based on that selection, generate a “WHERE clause” or a filter that I can paste into the filter input of the current table or another table. Possibly in a right-click-on-the-selected-values context menu, similar to an export, or a right-click-in-the-filter-textbox context menu that would allow me to ‘Paste Special…’ or something like that.
I would like to auto generate some kind of WHERE clause formatted like:
[COLUMN NAME] IN (‘firstvalue’, ‘secondvalue’, ‘thirdvalue’)
Or even without the column name part. Just a way to quickly format an IN (”,”) query.
I can’t make sense of the documentation that I’ve found so far. Any help would be extremely appreciated.
Hi Jeff,
I did not find that in the SQL Developer: When a user gives You a List of VARCHARS without quotes:
Satz1
Satz2
Satz3
…
I want to put it to the in-clause and surround it by a ‘quotes’
in (
‘Satz1′
,’Satz2′
,’Satz3’
…
)
At the moment I switch to Notepad++ and replace
\r\n
with
‘\r\n,’
Is there a shortcut or a build in feature for that in the SQL Developer?
Thx and best regards from Germany 🙂
…answering my own post…maybe it will help others:
– CTRL + R
– enter “[/n]” for Search-String
– enter ” ‘\n,’ ” for Replace String
– Under Options: Check “Regular Expression” and “Substitute Escaped Characters”
– click OK
…answering my own post…maybe it will help others:
– CTRL + R
– enter “[\n]” for Search-String
– enter ” ‘\n,’ ” for Replace String
– Under Options: Check “Regular Expression” and “Substitute Escaped Characters”
– click OK
Hi,
during long running pl/sql develoment sessions (4 hours and more) i have an issue with the ea version (4.1.0.18.37-no-jre): Suddenly during coding / saving or executing pl/SQL the whole ide stalls completely. no chance to save the work, close the sql/dev. i have to kill the process via taskmanager running on M$ windows 8.1 and java 1.8.0_40.
is there any way, how i could provide you with more detailed information?
when using the “old” stable version 4.1.0.17.29-no-jre it works like charm, a whole office day long
greetings
peter
regards
Dear Jeff,
I know you won‘t like my question because it’s about ‘starting SQL Developer a second time (at least ;-)’ – but I would like to know if there is a way to achieve the following.
First of all, the Setup is probably not supported, but it’s the customers environment… so please feel free to skip over my question in the first place!
SQL Dev 4.1 EA2 (4.1.0.18 – build 18.37)
Windows XP (SP3) – 32bit
JDK 1.8 (Update 25 – 1.8.0_25-b18)
Additional Entries in /ide/bin/ide.conf:
AddVMOption -Duser.language=en
AddVMOption -Dide.user.dir=c:\some\local\dir (no spaces, special-characters, …)
AddVMOption -Dide.pref.dir=c:\some\local\dir
Please note that this works perfectly with SQL Dev 3.2 (3.2.20.09 – build 09.87).
Simply put, if you start SQL-Dev a second time it looks like a fresh start – i.e.
.) the open-files-tabs are gone
.) the views (like Connections or Reports etc.) are ‘reset to their default location’
.) even the SQL-Dev window itself doesn’t start in full-screen like it was closed the last time (yes I know I can fix this in the program-properties 😉
What I noticed is that each new SQLDev instance creates a directory called ‘system_cache’ under the ‘ide.pref.dir’. So the 2nd one is called ‘system_cache_1’ the 3rd one ‘system_cache_2’ and so on.
I’m afraid that the settings I referred above are stored in this directory, and if so I guess there is no way in ‘reusing’ the settings from the first instance?
But maybe there is a flag I can use in the ide.conf or in any other config-file so the SQL-Dev will reuse the settings for each instance.
And as mentioned above, the ide.conf settings are also used for 3.2, and this version will reuse the open-files/views settings each and every time.
I’ve also tried it without the ide.*.dir settings, but it doesn’t change the behavior.
Again, I know there are two reasons not to answer this.
1 – don’t start SQL-Dev a second time 😉 but sometimes it is necessary…
2 – unsupported environment…
But I hope you will have a look at my question anyway!
Thank you in advance!
best regards,
Tom
One word: ‘XP’
Wow.
I normally wouldn’t put those in ide.conf, i’d put them in product.conf – new for v4.0, and in the OS User/AppData/Roaming/SQL Developer directory, or in sqldeveloper.conf
And just curious, why start SQLDev a 2nd time?
Dear Jeff,
Well ‘XP’, as said above it is the customers environment, so certainly not my choice. And we are all external consultants in our office, so we surely have a lot fun with it….
Thank you for the tip about the configuration-entries, I obviously missed that one.
For your info, I rechecked the behavior on a Win7-64bit (jdk 1.8_40) system, fresh install without any config changes, still behaves the same.
Why a 2nd time – well, various reasons.
I usually separate my SQL sessions, and PL/SQL development. I’m well aware about the ‘unshared’ connection feature. I don’t want to go into too much detail about our environment, but lets just say a session (background job) access a package, and you try to compile this package, you’ll have to wait till the RDBMS will release the lock on it.
But this one will more or less ‘freeze’ SQLDev till the package lock is released. So you can’t do anything else during this time.
Depending on my patience I’ll work on something else (in the 2nd SQLDev), or I’m going to kill the gb-jobs…
Then there are tasks you’ve to perform on the production-system, which I like to keep completely separated. I know I know, unshared connection, session color-coding… don’t get me wrong, these are really great features (especially the color-coding). But I can’t help it I like to have these tasks in a separate ‘process’. Just to be sure I don’t do anything stupid in the wrong worksheet.
And another task I like to keep separated are reporting-queries for cvs or excel output. When these are long-running ones on production data, I also like to let them run in another process, so I don’t accidentally kill/cancel the wrong task.
I know this sounds more like personal than technical reasons, and they certainly are, but I’m working with Oracle for a number of years now, and these years taught me a thing or two 😉
But to make a long story short, if SQLDev will store these ‘layout-settings’ for each instance, I’ll setup two or three sessions the way I like, which will solve the problem for me.
I use SQLDev since Raport, and I really like it, so this is not a go/no-go decision for me.
Thanks you for your time! And have a nice weekend.
regards,
Tom
i do a lot development in packages source. after opening a package header in one sheet there is a n icon which opens package body in a new sheet. vice versa it opens the header when working in the body.
i badly miss this icon in object type sheets. the need to navigate back to the object tree, find the type entry, rightclick the mouse and choose “open body” is a bit cumbersome. is there a chance to get the nice little icon transferred from package source sheets ?
greetings
peter
Yeah, I totally agree. Will add this as an enhancement request.
..and maybe you can put this on the list also:
in the package header there is a tiny little arrow next to function/procedure declaration. that will jump in the body, right on the position of the implementation. would be a neat feature for big type implementations as well
greetings from germany
Jeff,
I am experimenting with DBDOC, and I am curious. I see that you can select which types of object to document, but is there any way to turn off individual tabs on the output? For instance, with packages and package bodies, if you decide to document them, can you turn off the code tab?
Please advise.
Thanks
— Mike
That’s not configurable. But you could create a batch script to do some perl or regex to update the files to strip those bits out, after the fact.
Hi Jeff,
i play around with SqlDev 4.1 EA2 sqlcli Interface. Looks great. But i have some questions:
1. Is there any Doc ?
2. The timing command dosn´t work, also
3. host pwd
4. Is there any chance for filename/dir in the command: apex export 100 ?
But anyway it looks great! Some features are inside i was looking for many years.
Kind regards
Marco
I am an independent developer and would like to develop a small application in Jdeveloper which formats my PLSQL code in the format I like (and possibly even convert it into another language). I was looking at some libraries available in the sql developer/lib/ and ext/ folders which are frequently mentioned online. Are there any licensing issues when using some of these libraries or I am allowed to use any of these libraries without any problems ?
Yes there are issues, you are only licensed to use that in the context of running it in SQL Developer itself. If you want to build your own application, you’ll need to acquire your own license(s).
Thanks for your dedication and quick reply. 🙂
So rather than developing a new application I need to develop an SQL developer extension instead, right ?
In that way I will be running it in context of running of SQL Developer.
Thanks !!!
Yeah, that way you’d be covered, assuming there was an API avail for you in the extension.
Hi Jeff,
On several versions of SQL Developer, including 4.0.3.16, I have had issues while editing data in worksheets. The first keystroke entered into a given cell gets duplicated, for example a 1 becomes 11, a B becomes BB, and so on.
This happens frequently on several Windows servers and desktops, and to coworkers, so it doesn’t seem to be my keyboard or typing skills. Nor is it isolated to any particular Windows version or hardware configuration.
My searches haven’t turned up anyone else reporting the issue. Have you seen this?
Thank you,
Bill
What version of Java are you running?
jdk 7u71 on one desktop, and jre 1.8.0_40 on another. Both with the same issue.
Hi Jeff,
Are there any special cases where last_ddl_time is not updated when we recompile an existing package in sql developer?Can you clarify on this..
Yeah, see this discussion on that very topic.
Hi,
I’m using SQL developer 4.0.3. My computer OS is WIN7-64bit.
When I login , open SQL developer and trying to connect to one of the databases, SQL dev hangs, freezes. It get solved if I keep on openning more SQL dev sessions. In the 4th or 5th sessions, it succeeds in connecting.Than, I can close all the freezed-hanged sessions.
I checked Google and Oracle-MetaLink, but could not find a workaround or solution.
Are you familiar with that problem?
Thanks.
Next time it hangs, run jstack from the java/bin directory – jstack -l # where # is the Process ID for sqldeveloper.exe.
Start a post on the forums and share that jstack output.
Hi Jeff,
With SQL Developer 4.0.3.16.84. Two questions.
1) On new installations is there a way yet to import all tnsnames.ora connections?
2) The behaviour of SQL “–” comments and the “;” are different between SQL Developer and SQL*Plus
For example, the following two lines, together, will error in SQL Developer but not sqlplus:
select sysdate from dual — comment;
select ‘TeSt’ from dual — comment;
Can we change the SQL Developer behaviour on this to that of sqlplus?
(“/* … */” comments are okay in SQL Developer.)