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,828 Comments
Hi Jeff,
I’ve seen in your “My favorite SQL Data Modeler features” slideshare that you can get tables (and maybe even entities) to show comments/notes embedded in the box. Where do you set that?
Question 2: How is the “New Picture” icon used with LDM diagrams? It seems to work ony for relational diagrams. On a logical data model, I tried to insert a graphic with that; a dialog came up to select an image, but I didn’t see any results.
Thanks!
I did it, but a bit confused with the user and roles. I am not able to understand how ut_repo_administartor, ut_repo_users roles fit into the picture. Are they getting created at the time of repo creation? I am not able to trace them in the structural code which I get after export database without data.
so you want the script, or you want the data model?
I need the script.
So install the UT repos, and RE the schema/DB. You’ll have your script.
Or install 4.1EA and use the new logger feature to capture the repository commands as they’re sent to the database – see today’s post.
Hi Jeff,
I wan the script which will run at background when someone is creating a unit test repository for first time. Can you please provide me the script? Again this is a part of automation, that’s why I need it.
I’d have to pull that from our source code…you should be able to reverse engineer it yourself once it’s deployed.
Windows 7 64bit.
SQL Developer 3 and 4.
Hex codes are possible in a filename eg: Filename%23.sql . %23 is the hex code for #.
When opening a file with this type of naming in SQL Developer, a new blank file is created: Filename#.sql (the original file’s contents are not visible in the new file).
It does not seem to be possible to open and edit files with % in the filename. Is there perhaps a way around this?
I am using Mac Book Pro OSX 10.9.5 and Oracle SQL Developer 4.0.3.16. I have dowloaded drivers from salesforge site and tried both the jtds-1.2.5-dist and 1.3.1 jar files to connect to a MS SQL server over VPN. I am getting the SQL server tab – no problem there! There is no error in the userid/pwd, SID etc. I have tried both the SQL and Windows authentocations. But I am getting ‘connectiomn failure’ messages (after a while) before it can retrieve the databases. I could connect to Oracle databases over the same VPN. No problem!
What am I missing?
Thnaks
You’re not doing anything wrong. If you have the driver and the tab shows up, that’s all you can do. Something is happening – check your server logs to figure out what that is.
Hello Jeff,
your website is great. Thank you! I found your how-to to create *.csv-Files with SQL-Developer. Does it work from the command line or the taskmanager (windows 7), too? And if yes, then how :-)?
Thank you an best regards from Germany
Christoph
Do you mean the /*csv*/ hint in a query? If so, then you need to get version 4.1 when it comes out. We’ll have a solution for you there.
If you mean exporting tables to csv – you CAN do this via the cmd line today. Use the Cart feature. Add your objects, then you have the cart export your data out to csv files.
Thank you, Jeff – I am going to install the Developer.
I indeed thought of the /*csv*/ hint, but I’ll try now the Cart feature, cause I need to get to run the task as soon as possible. It should work, cause the data needed are collected in a view.
cy Christoph
Jeff,
I’m using Version 4.0.3.16 “Oracle SQL developer” tool for migrating SQL-SERVER to Oracle 11g. The tool is in my laptop and source and target DB are remotely connected. When I use “copy to oracle”, the table gets copied to target (oracle), properly. However, If I use the migration process with online option, it does capture nicely. But it doesn’t generate the actual tables in target database. I can see the tables are captured in repository. If I try to generate offline, the script is not created either.
I think, in the earlier discussion with “Paul Darling” same issue was discussed. But, I think you guys took it offline for discussing the resolution on this issue.
So, can please explain me if this product defect or there is any resolution?
Thanks,
Santhosh
Jeff,
After started my sql developer 4.0.3.16. All my database connections setting and their folders (including username, passwrod database name etc) are disappeared. Need to add those information again. Do you know why? how to restore my all settings?
Thanks,
Shunan
Sorry, no idea.
But it sounds like something ‘very bad’ happened.
What OS are you on? Settings for SQLDev on Windows can be found in your AppData/Roaming/SQL Developer folder. In Linux/OSX it’s in your $HOME/.sqldeveloper folder. They are named systemX.X.X.X by the version number. So you could try deleting the system4.0.3 folder and run it again and let it import your stuff from an earlier version…
Hi,
I’m using SDDM 4.0.3. I reverse engineered the datamodel from the datadictionary. I then added a tabel manually in the model, however, SDDM refused to generate DDL for it through the ‘export’ menu option. It does show the preview DDL, though, when I open the properties.
Any ideas what might be causing t5his behaviour ?
Thanks,
Richard.
Hello Jeff,
first of all – your blog is cool – thank you for all the good work !
I have just created some simple database model using SQL Developer \ Data Modeler 4.0.1.14 and exported it via [File/DataModeler/Export/In CSV – Rational Model]. Now when I examine exported files (ususally named DM_*.csv) it seems that some data is incomplete. I can not see any Check or NotNull constraints. The DDLs for creation of views are missing as well. Do I have to tune export settings somehow? Shall I use another (which?) format to export a model?
Thanks
Have to correct myself. The check constraints are there, but I am still missing named not null constraints and scripts defining the views. On the other hand, if exporting model to DDL then everything is all right. Did I find a bug 🙂 ?
Hi Jeff,
How to perform the unit testing on a function that return oracle types.
Example:
create type emp_info_row as object
( emp_no number
, emp_name varchar2(100)
);
create type emp_tab is table of emp_info_row;
create or replace function f_get_emp_info
( i_emp_id number)
return emp_tab;
Could you please guide me on how to perform unit testing using SQL developer Unit Testing feature.
Thanks & Regards,
Srinivasan P G
Hi Jeff,
I’m using SQL Developer 4.0.3 and want to run a procedure without parameters from an package using the “Run PL/SQL” dialog but the procedure doesn’t show up. All other procedures, which use parameters by the way, are listed correctly in the dialog. Is there an option to enable parameterless procedures for “Run PL/SQL” ?
Thanks
Marcel
Hi Jeff,
Update.
On Oracle DB 11.2.0.3.12 it runs fine. The dialog shows the parameterless procedure, but on Oracle DB 12.1.0.2.1 the procedure doesn’t show up.
Thanks
Marcel
It also works fine in a 12.1.0.1 database, weird. Logged it as a bug, but if you want to report bugs going forward, please use My Oracle Support and open a Service Request.
Hi Jeff,
Im looking in the Scheduler -> Jobs folder and see that Enabled and Disabled jobs are shown with the same visual representation, i.e. the same icon and font. Is there an option to display them diffrently, so it’s easy to set what jobs are currently disabled\enabled?
BR
Einar
Nope, but I’ll add that to the list.
Thanks
Hi Jeff,
How can I generate DB Docs in SQL Developer from the command line?
Thanks!
/B
You can’t do this today. It’s on our list of things to do going forward though.
FWIW: I am surprised more users aren’t interested in this. It seems so very useful. We manually generate the html docs and incorporate them into our Wiki. Very handy!
However, it is a hassle to have to gen the docs manually.
Hi Jeff,
Can I make a connection from oracle database in Oracle SQL Developer to Android Application? Can you please give me “hint” about this?
Thx.
What are you trying to do, or in other words, if you COULD connect to to your mobile app from the database, what would you do next?
Actually, I want to create an android application that shows the coordinates on the map. These coordinates are stored in the Oracle database in Oracle SQL Developer. Oracle SQL Developer is stored on Windows Server 2012. Later, my android application is only read data from the database. Are you experienced in this case? Please give me your hint. Thanks.
Well that’s different. What you want to do is probably create a REST API in Oracle REST Data Services that lets you pull back the coordinates in CSV or JSON…or build a Fusion App or build an APEX app and run it on your mobile.
Client -> Server NOT Server -> Client
How to view/kill sessions in Sqldeveloper DBA window?
I found it – it is on tools monitor sessions.
But I think it should be underthe DBA view
Maybe. But not just DBAs care about sessions, waits – developers often need to see this information as well.
In version 4.1, we’ll have a new performance dashboard you can drill into sessions. This will be in the DBA panel.
Hi Jeff,
How to use mapviewer in sql developer4
In connection to my previous post, I am using SQL Developer 4.0.2.15 with Oracle 11g release 2 database.
Hi Jeff,
I am in a situation where I have to create a unit test repository and grant execution access on test cases to users. And I have to do it through SQL commands, I can’t use GUI, because automatic environment creation for unit testing. And it is going to get implemented on multiple databases. So no other go than automating the entire process. Please help me.
Create the UT repository via the GUI to a new schema. Then use whatever tools you’re comfortable with to reverse engineer the schema to your scripts. SQL Developer offers this via the Cart or Tools > Database Export.
Hi Jeff,
I am facing a problem in Oracle SQL Developer. Here is the situation. There are two databases in two servers as server1 and server2.
In server1 I am having the unit test repository and all the unit test cases.
In server2 I have uploaded the entire repository (using reverse engineering) of unit test cases from server1. But I do not have SQL developer in server2.
Now, I am running a test suite on server2 with the help of the UNIX shell script from server1. It is running successfully but as a resultant it is failing the suite run with failing test cases. It is throwing error with message “synonym translation is no longer valid”.
But, while I am trying to connect to the server2 database using SQL developer (available in server1) manually, it is running successfully and in resultant also it is a success no failed test cases.
My db is Oracle 11g release 2 , SQL developer 4.0.2
Can you please help me out?
A lot going on here, please post this as a new thread on the OTN Community Forum.
Hi Jeff,
We are using DM 4.0.3. Is there a way to change the “Source DB Connection” – ideally for all tables. I know there is the redirect option but we need up our model.
Thanks
Reinhard
You can clear it, but there’s no way that I know of to change them en masse – what are you trying to achieve with that? Just save time on the connection redirect bit?
Thanks for answering.
We want to save time and for some reasons we have different sources what we need to migrate to one for quality reasons.
… the different sources are redirect to one database
BTW, how to be cleared the information? xml file editing? Thanks Reinhard
Right-click, clear source stamp
Thanks but the source db object will also be deleted. How can I avoid this or redefine it?
You’ll probably need to use a global search and replace against the XML files to do this en-masse. Or send the question to the Modeler Forum on OTN and see if someone there has a better idea.
Thanks
Hi Jeff,
when I use SQL Developer Excel export the cells are formatted with different fonts.
It seems that number columns are formatted with ‘Dialog’ whereas strings, dates and empty numbers (!) are formatted with ‘Calibri’ which looks a bit naff.
Is it already on your list of enhancements, or should I open a request on Exchange?
Best regards,
Sabine
I have sucked my data dictionary into SQL Developer Data Modeler and then renamed a column in one of the tables.
I then use the “Synchronise Data Dictionary with Model” button to generate a script to make the change to the database. I all settings as default.
I review the generated script and here is what I see amongst a whole bunch of stuff:
1. An alter table command to rename the existing database table to “bcp_”;
2. A create table command to create a new table called “” but this table contains columns for both the old name and new name;
3. An insert statement that copies data from “bcp_” to “” where columns for both the old name and new name exist.
Now am I missing a setting somewhere, or have I just discovered a bug?
It sounds like you added the column in the middle or to the top of the table..so the modeler is respecting the column order and is rebuilding the table. There’s an option to not do this.
See today’s post in case that’s the issue, I highlighted the option you need to turn off if I’m barking up the right tree…
Yes that appears to be it.
Thank you.
Hi Jeff,
We are trying to integrate our use of Git and SQL Developer for all of our Packages. So far there are a few nagging issues that I am hoping you have seen and can answer.
1. I would like to manage my package body and specification in a single file and have SQL Developer recognize as the source for the compiled code (and allow for compile all in one shot). As far as I can tell I need to keep them as separate files and give them file extensions of .pck – which is ironic as .pck is supposed to mean the combined ;-), or use a file extension of .sql or .pls but then I lose the compile option.
2. If there is a compilation error in my .pck file, I am given a line reference, which is cool. However, not as cool as when you compile the real code – that gives you both line numbers AND red squiggly lines and gutter references. Is there a preference I am missing to enable this for my .pck compilations?
3. I filed this as a bug, but the Git integration / Windows explorer does not allow for file name changes, nor does it recognize files that were deleted. You need to do all your file name changes in file system (outside of SQL Developer) and then commit the file Deletes in some other Git tool.
Any help is greatly appreciated. Thanks for the blog, it is very helpful.
Kind Regards,
Sean
Since they’re separate objects in the database, we store them in sep files…no way to combine them in SQLDev today.
So, you don’t want the squiggly lines and gutter markings to show you your errors? If you don’t want the squiggles, disable them in Tools > Preferences > Code Editor , PL/SQL Syntax Colors, PlSQL Errors (uncheck ‘Enable Highlight.’)
What’s your BUG # and I’ll take a look and see if it’s been triaged yet.
That was fast! The two files are fine, I’ll just use .pks and .pkb.
And I want the squiggly lines and red color when I compile my .pkb file and there are errors (say a bad table name). Right now I only get an error message (which links to the correct line – so that is fine). The code editor settings seem to only work if I am recompiling the actual code.
I don’t have a bug yet, just a SR SR 3-9925808991, I’ll let you know if they recognize as a bug.
>>if I am recompiling the actual code
Sorry, when exactly are you NOT getting the compilation error indicators?
I am not getting the error indicators when I compile the external text file (.pkb). It compiles with errors, and the error messages are informative and link to the correct line. But no red indicators.
I do get the red indicators when I compile from the database connection tree, but this defeats my purpose a bit as I don’t want my developers making code changes directly to database.
Thanks again.
Hi Jeff,
i have a question regarding unit testing in SQL Developer (current version 4.0.3.16).
While doing some unit tests i saw an increasing number of open database sessions
from my machine when querying v$session in sql*plus.
It looks as if there’s a new database connection being opened for EACH test run
i execute. Those connections remain open, even if i disconnect from the test repository
i’m using (the dedicated connection to the repository disappears when i detach from
repository).
Is this intended behaviour and if so, is there a possibility to avoid the increasing number
of open sessions?
Best regards
Thomas
No, that would not be the intended behavior. I would contact Oracle Support and open an SR/bug on that.
Hi Jeff,
thanks for the quick response. Btw, great Site.
Best regards
Thomas
Hello thatJeffSmith,
i’m working a lot with SQL Developer while my colleagues still use a third party tool for development.
My main task is to develop PL/SQL Database Packages and i’m still wondering why the SQL Developer has this high memory consumption and often crashes after (permanently) using 50% of cpu power (which is max on our development terminalserver).
My Setup is: SQL Developer 4.0.1.14, Java 1.7u51 on Windows 2008R2 (x64) Server virtualized on VMWare.
Java Startup Options: java -Xmx640M -Xms128M
Could you please give me some tuning tipps for the SQL Developer?
Thank you for your time.
I’m not worried about high memory consumption, unless you’re running out of memory. If you do, you will see the application hang. But you said, ‘crash.’ How does the ‘crash’ present itself exactly?
The one issue I know of now is the application freezes with high cpu when pasting code into a PL/SQL editor. We think we will have this fixed in v4.1.
Hi Jeff,
your’re right, it doesn’t crash, it “just” hangs (or freezes) when pasting code into it. Not everytime but often enough to get mad about it.
When could we expect v4.1? I really like the SQL Developer but those freezes result in killing the sqldeveloper.exe to get back to work… that is really time consuming.
Another problem is that everytime i doubleclick on a line in the Compiler-Log the SQLDeveloper doesn’t just jump to that faulty position, no, it opens the sheet a second, third or eleventh time and jumps to that position. This is really annoying because i don’t want to keep that file open that much not knowing which was the last i worked with and applied my latest changes.
Regards, Ralf
Relief is on its way for both items. All I can say is ‘soon.’
Thank you for your time, i’m really looking forward to that release.