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,791 Comments
Hi Jeff,
You are doing great work, thanks.
I have a quick question, maybe it is already answered in your form, i tried little bit but couldn’t get the answer.
When I create a new connection, by default it goes to Basic Connection Type, however I want to use my tnsfile so I select TNS then it shows database (services) names, but they are multiples, I mean several entries for one database/service name, even though there is only one entry in tnsnames.ora file.
Please post a solution or workaround at your earliest.
Thanks and regards.
How many TNS* files do you have in that directory? I’m betting, more than one.
We read and use all of them – because SQL*Plus does. It’s just not noticeable in SQL*Plus because there’s no GUI there to demonstrate it so easily.
It is. you are the man.
Yes, I had several tnsnames.ora files because we have one golden on a server and whenever it gets updated, and we reboot our own laptops the local tnsnames.ora is updated from the golden and before that a script renames the local file to something different (with time and date stamp). I moved the other files out of the Admin folder and I see only one entry. Thanks a lot.
So it reads from every file, even though it has different name like tnsnames_sep20.ora ?
Anyway, thanks for your reply and the fix.
SELECT name, usable_file_mb, round(usable_file_mb/(case when type=’NORMAL’ then total_mb/2 when type=’HIGH’ then total_mb/3 else total_mb end)*100,2) as percentage
FROM v$asm_diskgroup;
This might have been asked/blogged about before as it’s been a bug for years; but I’ve not noticed it when Googling. The percentage column shows null in SQL Developer. In SQL Plus it shows the real value. A bunch of other v$ queries have the same issue, is this fixed in a newer version of SQL Dev (I’m on 4.0.3.16)
New sql developer 17.2 hangs when I try to debug. It will not open the anonymous block and run the program. Shows like running, watch window pops up but nothing happens.
Previous 5 versions all took settings from each other and never had problems.
Can you post to the forums? I’ll be able to help you better there.
I’m currently on release 3.2.2 of SQLDeveloper and would like to upgrade to the 4.2 release. I currently have a major application developed in my 3.2.2 version. Question is, can I upgrade to the 4.2 release with no effect on any of the Procedures, sequences, tables etc. to my current application or do I need to export and re-import the application after upgrading it.
No worries, those things are completely independent of each other. Go ahead and upgrade.
Jeff,
This may seem very elementary but i am trying to find a video tutorial on how to build a RDBMS from scratch.
Im looking for the basics to building
I understand and took classes on sql plus
and querying and such but im lost in starting from scratch.
I have the oracle 12c
and also the express 11g
could you guide me??
pupmike
Try this. I love his videos.
Previously there were more options to generate code (Advanced Format, now Embed/expose – Ctrl Shift F7). Is there any way to configure other formats like c#, vb like before or is it only limit to java, Python and pl/sql?
We just support those 3 now. Are you missing both c# and vb, or would you probably just use one over the other?
for c# i can use the java though the @ santax for Strings is more nice.
So most needed is the vb
I miss having the VB format option… Didn’t use it often, but when I did it was extremely helpful. I’m currently working on several Python projects, and get the impression that the TO/FROM Python is actually generating something more like C#. Is it possible that the code generation is configured incorrectly for Python? When I expose/embed to Python this SQL:
SELECT
1 AS FIRST
, 2 AS SECOND
, 3 AS THIRD
FROM
dual
gets translated to:
var sql = “SELECT\n”+
” 1 AS FIRST\n”+
“, 2 AS SECOND\n”+
“, 3 AS THIRD\n”+
“FROM\n”+
” dual”;
I’m running 17.3.1.279
Thanks again for all the great work!!!
Hi Jeff!
I just started using SQLcl and it looks great! Running scripts and looking for error messages has always been a pain and I was thinking that if I were able to highlight the ORA-messages in the output that would be awsome. What are the chances that some form of output parsing rules/functions would make it into SQLcl?
Maybe there already is functionality in there that I could use for this?
So…not easily. The best I could offer is to use something like this to ADD a note after an error.
Hello, Jeff. I would like to ask if there is a way to find my already stored database passwords on my Databases and if there is a way, can you please analytically explain the procedure step by step. I have tried through an Oracle Sql developer extension that exists on the internet and it didn’t work. Moreover, there are some python or java codes but didn’t work for me, too. These codes exist in the case there is an .xml export of the databases (in which the oracle sql developer asks a password for encryption). The version of Oracle Sql Developer is Version 4.2.0.17.089. Thank you, very much! BR. Telis
You’ve lost your password, but SQL Developer knows what it is, b/c at one point you told it to change your password?
If this is the case, simply use SQLDev to change your password once you’re connected.
Thanks for the immediate answer. I forgot the password, but sql developer knows it b/c I had used the save option in order to get stored automatically and never need to insert every time I log in.
Is there any other way without the option of resetting it (the password)? Thanks again, Jeff.
There’s a 3rd party extension that claims to recover passwords. I don’t endorse it, but if you google it, you will find it.
Hi Jeff,
Just looking at the DBA=>database=>tablespaces=>select tablespace=>sort by size tab.
The sort decreasing (of the objects) starts with 9 and then finishes with 1 (no matter how many digits). I think this is because it is attempting to sort a character string not a number. I’m using version 4.2.0.16.
Are there any settings I have missed out on to get the sort to function correctly?
Regards
Colin
Yeah I logged a bug on that.
Hi Jeff,
I would like to use the Object browser / Data to change content of a view that is having insteadof triggers. Running update statements directly works of course fine but in the Object browser / Data, the cells are grayed out – not for editing. Are there any preferences or.. to make the view content (data) available for update?
Thanks in advance!
Jo
We look to see if the view columns are avail for updates, see ALL_UPDATABLE_COLUMNS, then we try to pull a ROWID for each row. If we can’t, then you can’t update the view with the grid. You can also look at the ‘Use ORA_ROWSCN for DataEditor insert and update statements’ preference.
Thanks for the good explanation. The instead of trigger can bypass the ALL_UPDATABLE_COLUMNS. But still there is a need for a rowid/ora_rowscn in the where condition of the update statement from the grid to the database. My best option is to add a primary key constraint (novalidate..) to the view but I guess this is not good enough for the update statement.
Br
Jo
I installed Oracle Sql developer 17.2 on my mac, trying to add third party JDBC driver but there is no option of preference present in tools. So not able to add third party JDBC driver.
Please help.
Thanks Prasad
Mac apps have their preferences on the main menu, so look under SQLDeveloper menu.
Hi Jeff,
I learned about the Db Doc functionality in SQL Developer thanks to your previous post “JAVADOC for the Oracle Database a la DBDOC”. I am running SQLDeveloper v17.2.0.188.1159. Currently the DB Doc will not generate for standalone functions or stored procedures, only those that are encapsulated in packages. Can you point me in the right direction for finding out if/when this feature could be available? It would be extremely beneficial to my organization.
Thanks for all the great information re: SQLDeveloper BTW, truly appreciated.
Argh! That would def be a bug if I can reproduce it. It SHOULD be and was working previously.
Hi Jeff,
when exporting a cart using the “separate directory” option multiple times the exported files are never replaced. with every export a new set of files is created with a prefix to make the filenames unique. that is a bit cumbersome because i want to check in the changed file in our svn. in the moment i have to delete all files prior to exporting them.
is there an option to deactivate the behavior so that exporting will overwrite existing files?
The cart has a CLI available using the SDCLI exe in your bin directory. Script it such that a bat or bash script deletes the old files first, then run your cart. Then check your stuff in.
Hello jeff,
Below query gives me complete table description, relationship, primary key foreign key etc.. could you please help me translating below sql server query to oracle
USE [Database_Name]
— ===============================
— Description: GENERATE DATA DICTIONARY FROM SQL SERVER
— =============================================
CREATE proc [dbo].[spGenerateDBDictionary]
AS
BEGIN
select a.name [Table],b.name [Attribute],c.name [DataType],b.isnullable [Allow Nulls?],CASE WHEN
d.name is null THEN 0 ELSE 1 END [PKey?],
CASE WHEN e.parent_object_id is null THEN 0 ELSE 1 END [FKey?],CASE WHEN e.parent_object_id
is null THEN ‘-‘ ELSE g.name END [Ref Table],
CASE WHEN h.value is null THEN ‘-‘ ELSE h.value END [Description]
from sysobjects as a
join syscolumns as b on a.id = b.id
join systypes as c on b.xtype = c.xtype
left join (SELECT so.id,sc.colid,sc.name
FROM syscolumns sc
JOIN sysobjects so ON so.id = sc.id
JOIN sysindexkeys si ON so.id = si.id
and sc.colid = si.colid
WHERE si.indid = 1) d on a.id = d.id and b.colid = d.colid
left join sys.foreign_key_columns as e on a.id = e.parent_object_id and b.colid = e.parent_column_id
left join sys.objects as g on e.referenced_object_id = g.object_id
left join sys.extended_properties as h on a.id = h.major_id and b.colid = h.minor_id
where a.type = ‘U’ order by a.name
END
Thanks and Regards
Sindhu
We have a migration tool for sqp server…it will bring your table and data over…and translate your stored procedure. Have you tried that?
Hi Jeff,
Is SQLcl available in any open repository like Maven central, Github, some oracle repo ?
If not is there any ways to get the latest stable version in an automated way?
Right now I am downloading it manually from the oracle website, but if we decide to use it as a replacement of SQLPlus there must be some way to automate the installation.
Regards,
Mitko
How are you automating your SQL*Plus installs today?
We’re working on open sourcing SQLcl, so hope to get it in our GitHub project soon.
To be honest I personally have the same problem with the current oracle client installation.
I hope one day we will have it in Homebrew, Apt-get or Yum.
In the java world the jdbc is already in maven, but it’s still quite cool to use the sql formater from SQLcl.
The main difference is that it’s well know and in a corporate world it either comes as part of the OS image or there is already an approved procedure to do it.
The opensource version would be quite cool.
Cheers
Hi
This is not a comment but a question: There used to be a spatial add-on called Raptor. We deal with a lot of spatial data and I am interested in using SQL*Developer to manage spatial data. Are there any spatial tools available that I can use within SQL*Developer ? If there are none then do you know IF there will be any available any time soon ?
There’s a 3rd party extension for Spatial, but we also ship with Spatial support out of the box.
I’m trying to use the select /*csv*/ option to create a csv file and then load the file using bcp. My problem is something to do with line terminators. When I export the file from the grid using export everything works fine, but when I try to spool the file using the script option and try to load the file, I’m getting unexpected EOF issues.
I’m using version 4.2.0.17.089 build 17.089.1709
Load it to SQL Server?
Actually – I’m trying to load it into Sybase – as I said, if I export the data from the grid into a csv file it loads fine using bcp. However, I have over 150 tables and I want to use the spool option
spool “H:\Migration\08282017\CATSDB_08282017.csv”
select /*csc*/ * from SAPSR3.CATSDB;
spool off;
If you were trying to load it to another Oracle instance, I could help you. Spooling it to csv for SQL*Loader to Oracle is apparently working just fine.
OK – Thanks – I’ll keep trying
FYI – I added ‘set feedback off’ to my script and this solved my issue
Hi Jeff,
Is it possible to use expression to define foreign keys names in Data Modeler 17.2?
I would like to name my foreign key like this {model}_{table abbr}_SUBSTR(1,3,FRONT,{column})_FK_I_DECODE({relationship},SUBSTR(1,8,FRONT,{relationship},”,{relationship})
Best Regards,
Antonija
Not in the UI but you could probably write a transformation script to do it. So it would run and dynamically rename all of your FK names. You’d have to write it in javascript, o you wouldn’t have access to oracle SQL functions.
Another question, can I apply transforamtion to just one table in relational model? If yes, how?
Best Regards,
Antonija
Probably, you’d just have to code it that way. If name = …
Thank you for helpfull answers.
Best Regards,
Antonija
In SQL Developer Data Modeler, could you please tell me how the Overlapping Attributes part of the Entity Properties is supposed to work? I’ve tried checking various boxes and clicking Apply then OK, but have yet to see any affect from it. The boxes in the Folded column won’t allow me to check them.
Also, there doesn’t appear to be anything in the help which explains this feature.
I’m running version 17.2.0.188 Build 188.1159.
Thanks
I don’t believe you’ll see anything ‘happen’ until you engineer your logical model to a relational model, see this
Jeff-
Application developers often have to copy data from our Production databases into our Test and Development databases to debug data errors happening in applications. Right now, application developers have to request that a DBA export Production data and import the data into Test and Dev. This often takes days because of the DBA’s workload. Developers have access to all the data in Production, so is there a way to use SQL Developer to copy the data instead of bugging the DBA’s all the time?
Lots of things actually. Tools, database copy. Drag and drop. The cart.
Why in 17.2 don’t work copy and paste (to Worksheet) in list of database objects (tree Connections)? in previous version 4.x works ok. I know is work drag and drop but I like use C&P.
Because bug. It broke in 4.1 or 4.2 I think…should be fixed in 17.3..stay tuned.
Re: How do I compare two query result sets by comparing grid to grid ?
If I run one query – the result set gets output to the grid – and then run another query with it’s result set output to another grid is there a way to compare the grid result sets and output the differences to another grid or window ???
Is there a plugin for Oracle SQL Dev 4.1 that will do this ??? I would LOVE to have this functionality as I’m always comparing data from one query versus another query. The export to Excel and it’s comparison functionality is painfully slow and NOT user friendly.
Thank You 🙂
No way to do that, unless you want to use SQL to do it…check out the MINUS command.
Thanks Jeff, I’m familiar w/the minus command. I just want to select two grids, right-click, compare and see a third grid w/the differences.
And what happens if each grid has 100,000,000 rows in it? That’s the conundrum i face each time I look at this feature request. Comparing a few hundred or thousand rows is no big deal.
Jeff,
I am trying to copy objects from one scheme to another across different connections. Table ddl statements fail due to missing table space. Is there a way to turn off table space option when selecting objects in Tools -> Database Copy ïƒ Object Copy.
Thank you
try dragging and dropping your tables from one connection to another, I’m pretty sure that skips over tablespace info
Thanks Jeff! That worked. However, I would like to point out few things for consideration:
1) constraint ddls fail because they are executed twice – once as part of table ddl, second time as an alter statement. It should be one or the other when both are selected.
2) Package bodies need to be explicitly selected otherwise only specs are copied. I have not found an easy way to select multiple package specs and bodies, short of clicking one by one.
3) Dragging could be impractical/cumbersome with tens of connections in your explorer window. It would be nice to right click on selected objects and select schema to copy to + options.
4) It would be nice to fix Database Tool copy to remove tablespace clause or make it selectable.
Thank you!
I agree on #4…and I can’t remember why it’s like that now…but I know there MUST be a reason. I’ll take a look into it. #1 – can you provide an example?
How do you use Spool to export Query results to Excel? I specifically need it as Excel and not CSV.
you’d have to spool to CSV and let Excel convert it…no way to spool a binary file format like Excel
Hey Jeff,
Love your info and product. My question is where did the equivalent to the /datamodeler/xmlmetadata/doc that a few folks seeking info on the SQL Developer Data Modeler object API for scripting were referred to end up for releases 4.2+ of SQL Developer Data Modeler? I cannot seem to find this folder or the docs in most recent releases.
We believe that some custom scripts for our shop may be helpful to streamline certain naming, standardization and generation workflows. How can we find out more about the objects, properties and methods available within scripts?
Thanks,
Jim