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
In this stackoverflow post (http://stackoverflow.com/questions/10886450/how-to-generate-entire-ddl-of-an-oracle-schema-scriptable) you said that it was possible to use the command line interface to automate exporting ddl for schemas in the database.
I can’t seem to find any documentation about this. I see cart, dba, format, migration, reports, unitttest and utility, and I can find dbcopy in dba, but not export. I would really appreciate some help here.
I am trying to export ddl in automated fashion using sql developer rather than dbms_metadata.
Thanks.
Sure thing Jake, but you do realize we are just making dbms_metadata calls via the Cart, yes?
Is it possible to get SQL Developer to format all reserved keywords (Oracle and PL/SQL) to uppercase while typing, while format everything else as lowercase (including non reserved keywords).
Would have been nice to set for the “Completion Insight” in the “Code Editor” and “SQL Formatter”
yeah, set the Completion Insight to Upper Keywords or in 4.1.1 Upper Keywords, Lower Identifiers. That will take care of what you type, assuming you don’t type in UPPERCASE.
You can set the Formatter to ‘Keywords Uppercase’ as well. I’m not sure that will change non-keywords to lowercase though when you format. But if they’re already in lowercase then you’re set.
Thanks for your quick reply, but my experience suggest that it doesnt quite work that way.
The difference here is RESERVERVED, the clients code standard which we can not influence do require that only the reserved keywords should be in uppercase, all other keywords and identifiers are supposed to be in lowercase.
i only gave you options, you need to test those and see for yourself what combination works best
hopefully your client cares also about how good your code is 🙂
Hi Jeff,
For versioning in Subversion – is there somewhere a legend for the state overlay icons and labels?
Thanks!
Hi Jeff,
Is it possible to populate entity attributes from the glossary or another list, versus re-entering from scratch? I need to create a denormalized table that will be a combination of attributes from other tables in the model.
Thanks!
Hi Jeff,
Relatively new to Modeler…. Learning additional features as needed. Love the app. I tried to find a post on Glossaries on your site and how to share glossaries in a team environment. Maybe I missed it. Do you have a post on this?
Thanks!
Hello,
I’m trying to export a csv file with Line Terminator CRLF, but it always returns LF only.
I’m running SQL Developer 4.1 on a Mac. I’ve already set the Environment setting for CRLF, but it does not seem to do anything. Am I doing something wrong?
I just tried this, and it seemed to work for me. I see 2 chars at the end which i’m assuming is an CR LF pair when i open the file in the worksheet and toggle on ‘show whitespace chars’
I also setup the Export preferences for csv and only see one character at the end of each line. Same when I export as CSV and look at it in TextWrangler. I’m guessing it’s a setting I’m missing somewhere?
I didn’t set anything in the preferences, I set the line terminator style directly in the export dialog, using CSV (or maybe delimiter) as the formatter.
Jeff,
I am running SQL Developer 3.2, and 4.1.1. Nowhere can I find the file ide.properties that you mention in your article on setting the UI font size. I really need to be able to do this, but I am afraid the info in your blog just doesn’t match the reality on my PCs.
I am running 3.2 on a Win 7 Pro box, and 4.1.1 in a Win 8.1 Home box.
The folder C:\Users\Joe\AppData\Roaming\SQL Developer\system4.1.1.19.59, on my Win 8.1 box, has over 100 o.xxxxxx folders in it, but they are all empty.
SQL Developer runs fine, I just can’t read it.
if those directories are empty, you have a problem
4.1 on my mac, it’s here
┌─[09:31:57]─[wvu1999]─[MacBook-Air-Smith]:~/.sqldeveloper/system4.1.0.19.07/o.sqldeveloper.12.2.0.19.7$└─>pwd
/Users/wvu1999/.sqldeveloper/system4.1.0.19.07/o.sqldeveloper.12.2.0.19.7
┌─[09:31:58]─[wvu1999]─[MacBook-Air-Smith]:~/.sqldeveloper/system4.1.0.19.07/o.sqldeveloper.12.2.0.19.7$
└─>ls -l ide*
-rw-r--r--+ 1 wvu1999 staff 5255 Jul 31 10:04 ide.properties
And I edit the file, add the line, restart app and voila:
Hi Jeff-
I’ve noticed a peculiar behavior between having a startup script defined and substitution variables working.
I have a rather simple login.sql file created:
PS C:\home\database> more .\login.sql
set pagesize 5000
set linesize 30000
In SQL Developer 4.0.3.16 x64, the following query will prompt a dialog prompt to enter the substitution variable when the statup script is defined in preferences.
select ‘&hello’ “HELLO” from dual;
However, using the same login.sql, in SQL Developer 4.1.1.19 x64, no dialog prompt is provided and only “Substitution cancelled”[sic] is displayed. If I open preferences, remove the startup file path from the preference, click Ok, re-connect (eg. no SQLD restart) and run the query, I then receive the expected dialog prompt.
I haven’t been able to find any details regarding this, so I’m not sure if there’s something else I need to define when using a startup script to enable substitution variable functionality or if I’ve managed to stumble across a bug. If the former, could you point me in the right direction? If the latter, where do I report this?
Many thanks!
-Jared (@smplnerd)
Hi Jeff,
I’m using SQL Dev 4.1.1 (the latest at the time of typing) and there is something peculiar behaviour I have seen – more than once. A report gets run only once, which takes a while. If I go to Reports -> Database Administration -> Sessions and have a look at the sessions because the query was taking too long, there were in this case about 100 sessions all running the same query (SQL_ID) from the same machine and OS User. Have you heard of this happening before? The user is adamant they only ran the report once. Seen this one before?
Is there a way to disable the “Import preferences from a previous SQL Developer Installation” on startup of a clean install?
The reason I’m asking is that it is possible for the users to browse to an invalid location, click “yes”, and what follows is an infinite loop that creates folders until you hit max filename length in windows.
Jeff, I have been trying to attend your weekly sessions for several weeks now and have been mostly unsuccessful. Can you let me know where the “invitation” is so that I can get the right url?
We didn’t have one today. But we did have one last week, and the recording is up on YouTube.
There’s a zoom link on the meeting page, did you try that last week?
What topics would you like to see covered going forward?
And if you have a group there at work, I’d be happy to put together a session just for you and your colleagues.
Hello Jeff,
Thanks for your blog it’s very interesting above for me who are a new on pl/sql.
I have to find some potential duplicates on a database using the Jaro_winkler similarity. I’ve applied my algorithm (here below) by extracting the data from the database to run it but it is too slow(very large data it’s too slow).
I discover the pl/sql and now, I’d like to run it directly from the database to update some informations on only one table(members_tbl is the name of table) if some conditions are OK. the algorithms is :
id_dup := 1;
FOR (i in 1 to Nrow(members_tbl)) THEN
{
FOR (j in (i+1) to Nrow(members_tbl)) THEN
{
IF(members_tbl(i).iddbl IS NULL) THEN
members_tbl(i).iddbl := id_dup
IF (((members_tbl(i).DBIRTH ==members_tbl(j).DBIRTH) AND (UTL_MATCH.jaro_winkler_similarity(members_tbl(i).SNAME,members_tbl(j).SNAME) > 80) AND (UTL_MATCH.jaro_winkler_similarity(members_tbl(i).FNAME,members_tbl(j).FNAME) > 80)) AND (members_tbl(j).iddbl IS NULL)) THEN
{
members_tbl(j).iddbl := id_dup;
members_tbl(i).flag := 1;
members_tbl(j).flag := 1;
members_tbl(i).dblcrit:= 1;
members_tbl(j).dblcrit:= 1;
}
}
id_dup := id_dup + 1;
}
How can I translate it into a pl/sql procedure ?
Thanks in advance for your great blog!
Best Regards
Try asking Steven, he might have some really good resources for you. Looking at your code though, it looks like PL/SQL offers everything you’ll need to make it work.
Thx Jeff I’ve sent à private Message to Steven, hoping I’ll get an answer.
Best regards.
That “C” in my previous comment was supposed to be ctrl – C
I’m using sqlcli on a Solaris database server of ours and noticed that C acts different than in SQLPlus. I can stop a DML statement in sqlcli with C same as in SQLPlus, but I cannot stop execution of a PL/SQL procedure (like dbms_stats.gather_table_stats, or a “clone” procedure I have in my “scott” schema).
Oracle version = 11.2.0.4 with July2015 PSU, sqlcli version 4.2.0.15.177.0246 RC
probably a bug, we were just recently plugging in the cancel stuff across all of the different OS’s
Hi Jeff,
I am not able to open de debug window. When I open an *.pks or *.pkb file I see the standard SQL Worksheet view instead of PL/SQL view.
When I open my package in the navigator I don’t see the compile icon.
If I go to tools–>preferences–>File Types my .pkb & .pks extention are of SQL script file type. I can’t change these settings. If I add a new extention I can’t select an PL/SQL file type. In the tab default editors I see PL/SQL but there is nog file icon.
I have tried several things but I can compile the code but I can’t see the error messages. Do you have a suggestion?
Kind regards,
Dietske
What version of SQLDev are you running?
Hi Jeff,
I am using Data Modeler Version 4.1.1.888 transformation scripts (javascript). I needed to change to naming of constraints, indexes and also the FK columns in relational model (engineered from logical). The name templates did not provide with enough flexibility.
I could change FK column names and also FK costraint names without problems.
However I got stuck when I wanted to change PK constraint name: either it did not provide the name setter / KeyObject setName does not exists / or it did not allow me to change it / DesignObject setName returned false without changing, ContainerWithKeyObject setPKName simply did not change it /. I just cannot believe it is not possible.
Could you please give me hints how to implement the followings:
1. change PK constraint name
2. change index name (for a PK constraint)
3. the same 2 things above for for unique keys (change constraint and index name for unique key)
4. do I have possibility to access full path name to the glossary file from model (currently I hardcoded the full path in script)?
Thank you!
Regards,
Steph
I recently upgraded from SQL Developer 4.0.3 to 4.1.1.19. One thing that’s changed is that script output is more like sqlplus output. However I quite liked the way SQL Developer used to include the whole column header. Version 4.1.1 now truncates the column headers. Is there an option to switch back to the old behaviour?
I understand a workaround is to specify individual column format statements but this is quite an annoyance.
The only alternative I can see is to restore the old version.
Why is initial setup so finicky and frustrating? I’ve been using SQL Developer for several years now, across a couple of laptop upgrades. I now have another new laptop running Windows 7. I have installed JDK 1.8.0_51 and SQL Developer 4.1.1. On initial run, it asked me to point it to a JDK, so I pointed it to my java.exe file in C:\Program Files\Java\jdk1.8.0_51\bin\java.exe. It then gave me an error message stating “Unable to locate a Java Virtual Machine. To point to a location of a java Virtual Machine, please refer to the Oracle 9i JDeveloper Install Guide (jdev/install.html).” Every time I try to run SQL Developer now, regardless of what I do, I continue to get this message. I’ve tried editing the sqldeveloper.conf file in ~sqldeveloper/sqldeveloper/bin. I’ve tried editing and then deleting the product.conf file in ~AppData/Roaming/sqldeveloper. I’ve also tried deleting everything in the ~AppData/Roaming/SQL Developer directory and deleting the entire installation and installing fresh from the zip file, but I still just get that error message now.
The release notes for 4.1.1 suggest that you should actually select the java home directory, not the java.exe file when prompted for the JDK location. From the SetJavaHome line that was in product.conf, it looked like it did actually deduce the correct java home directory – C:\Program Files\Java\jdk1.8.0_51.
I do seem to recall on a previous version that I installed last laptop upgrade that you needed a 32-bit JDK, however, I could not find any such direction in the 4.1.1 release notes other than to use JDK 1.8 or later. If 32 bit is still required, this information should have been included in the release notes. In any case, I’ve tried pointing it at both the 32 and 64 bit JDKs I have installed by editing sqldeveloper.conf, but still no change.
What do I try next and can you not make this initial setup easier? At the very least, when it can’t find the JDK, it should at least prompt me to provide a new location, rather than just die. It managed to ask me on the first run, why can’t it do it now?
Other issues I’ve encountered so far trying to troubleshoot this issue:
1. The jdev/install.html file referred to in the error message does not exist in the release ZIP file.
2. I did find some documentation in ~sqldeveloper/doc/en/welcome.html, however the links to documentation in there seem broken due to custom:external: on the front of the actual http url.
3. When I manually removed the custom:external: from the front of the url, it actually took me to SQL Developer 3.1 documentation.
Thanks.
What do I try next and can you not make this initial setup easier? At the very least, when it can’t find the JDK, it should at least prompt me to provide a new location, rather than just die. It managed to ask me on the first run, why can’t it do it now?
Download the copy with the embedded JDK. Unzip. Run the EXE. I can’t make it any easier than that.
Wow – that was a fast response!
Actually, finally resolved it. I think the issue was using sqldeveloper.exe as the launcher instead of sqldeveloper64W. In any case, I edited product.conf to point to the 64-bit JDK and ran sqldeveloper64W.exe. It prompted me for a JDK again which was only the second time in dozens of attempts to start SQL Developer where I was asked for this. It already showed the correct path – I assume from the product.conf file I edited, so I left it untouched and SQL Developer started. It seems to be working fine.
Hi Jeff,
is there a possibility to prevent that data modeler open all diagrams if i open a design ?
Additional the file Diagrams.local is modified(different line order) still i didn’t change anything – bug or feature ?
Regards Günter
is that 1 question or 2?
in newer versions, when opening designs, there’s a dialog asking you which models you want to open, including physical (which can take a lot longer, esp if you have thousands of partitions)
That are 2 questions/topics:
I open always logical and physical problem – no performance problem.
1. First one is regarding the subviews that shows up after opening a design(just saw there is a property visible – so this question is answered)
2. Even if i don’t change(much less save) anything the file Diagrams.local is always changed. Same content but different line sort order.
Seems this happens only if the “visible” property of the subviews is set. After removing it there is no modification of Diagrams.local.
If i use “show diagram” or “Go to diagram” the “visible” property of the subvies is set again, also leading to a change of Diagrams.local visible=”true” for this subview. Can i prevent thsi automatism ? I don’t want that “visible” property is set bei opening a diagram only if i set it explicitly.
I’am using latest version Version 4.1.1.887. Should i open a SR in ORACLE Support about that issue ?
Hi Jeff,
thanks for running this blog, this was my main source of knowledge for hacking report away !
As I am supporting an vendor application that stores zipped xml in BLOB column, I have built a Java object to unzip BLOBs and turn to them CLOB so I can display the xml in a Report (Style: Code).
This works fine, but running java on a vendor DB is being frowned upon, so I am looking for a way to do this ‘unzip+to_clob’ step in the SQLDeveloper report.
Is there a way to build an java extension that would be added as Report Style ?
Best regards,
Etienne.
>> but running java on a vendor DB is being frowned upon
but storing xml as a zipped file in a BLOB isn’t?
Sorry, couldn’t help that 🙂
Maybe you could do it in a stored procedure.
Hi,
there is a way to unzip files (and i think blobs as well) using nothing but pure pl/sql. maybe u want to look here https://technology.amis.nl/2010/06/09/parsing-a-microsoft-word-docx-and-unzip-zipfiles-with-plsql/ and give the package “as_zip” a try.
I use it in a productive environment to unzip files in the fly, without any known issues.
Thank you for that Peter, I will check it out.
Thanks Jeff for the very quick answer.
I know, I know. Just trying to have the strength to accept what I cannot change…
I’ll be sure to look at the pl/sql solution.
But I take from your answer that extending the reports style list is not possible.
Anyway, great product and great blog, thanks again !
First day using Oracle SQL Developer. I’m in love. TOAD is dead to me 🙂
Your posts have been a great resource. I have everything configured the way I want except for one item. The Query Builder is great, but I would like to be able to toggle the “Create Oracle Joins” off before adding a table that is used for all the lookups in our schema. When dropping this table 30 links appear requiring 29 of them to be removed. I have to drop this table repeated. The “Create Oracle Joins” is available when right clicking the query builder. There is a check mark on it, but the text is gray like it is disabled. Thanks
Hi Jeff-
Server: Oracle 12c
SQL Developer : v4.1.1
the issue i am facing that whenever i use “sys.dbms_sql.return_result(ref_cursor);” I get resultset in well formatted text which is cool if the columns count is small and fits in the pc’s screen width;
but i want result set in grid as in “Database > Worksheet” Grid in checker board or Zebra pattern. Is there any option hidden or this feature is yet not included ?
Please let me know how to get it in a grid.
Thanks
Sayam
Hi Jeff,
the more i use carts the more i love them. while working on different db objects, i collect them in a cart. after the work is done, i use the export of the cart to
1) transfer all objects into the costomers db. i put them in one single sql file which i execute in their db. all fine
2) transfer all changed objects in svn. for that i want to export every object in a single file, in a object type directory (all trigger defs in one, all package defs in the next, and so on)
so far, so does it work. only the name generation when exporting in separate directories is a bit cumbersome. if the object already exists in the target dir, a new one is created with a trailing sequence number. so before updating the svn repos i have to delete all old sql(exported)files and rename the new.
is there any way, to supress the generation of unique filenames and make “replace existing files” the default option for this kind of export process?
greetings from germany
peter
Is it possible to filter the connections list (not the objects in the connections). I know that I could add them to folders but wondered if there is an option to hide those I don’t need to see. I have over 40 and I prefix their names to bring them together in the order I want but it would be good to quickly hide what I don’t want to look at.
>>folders
yeah, that
In SQL Developer, it seems that the refactor functionality to extract a procedure doesn’t create parameters, but rather just wraps the code that was selected with a ‘procedure as begin’ and an ‘end <the_name_you_gave' even though that there is the option to 'call with named arguments'. Even the most basic code that I try to extract doesn't result in a procedure with parameters.
Should the extract procedure functionality create parameters?
Is there something non-obvious that should be done to enable it?
what did you select?
if i select
dbms_output.put_line(stuff), it creates a proc with a STUFF parameter