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,
How one can do continuous integration with SQL developer for unit testing of PL/SQL objects?
Thanks,
Mangesh
Do not worry if you want to remove the blocked files or too long path files from your system, here I suggest a smooth way. Use “Long path tool” software and keep yourself cool.
Hi,
ever thought about a script manager? Don’t know the TOAD feature, but I know, we have added sql*plus script execution feature via ANT into eclipse, for the scripts you use so many times during the day. E.g. 1) create an application user (with the defined privs) 2) create the data objects in an app schema 3) fill the app schema with test-data 4) drop the test-data 5) drop the data objects 6) drop the user 7) analyze etc 8) create indexes on fks 9)…
These are just some examples, why this makes sense. You should be able to choose (by checking) some of them and execute them in the order you can choose. The sql*plus output should be directed to the script output windows of the sqldev.
Could perhaps be also developed as an extension…
Hi,
I installed sql developer on my new mac, but cannot find the folder location to move all of my snippets to. I looked in userhome\Library\Application Support but do not see a file for sql developer.. am I looking in the wrong place?
Thanks!
└─>pwd
/Users/wvu1999/.sqldeveloper/system4.1.3.20.78
I’m guessing you’re not ‘wvu1999’ on your machine – but that’s where you want to put stuff. I’d just zip up the entire directory and move it over.
I am facing the following issue while opening SQL worksheet in SQL developer
Popup with Error Message –> “An error occurred while opening ide.worksheet.file:/C:/users/%USERNAME%/Appdata/Local/Temp/worksheet…..”
More information on error:
java.io.IOException exception loading
Some where down the line in error log :
Caused by : java.lang.NoClassDefFoundError: oracle/ide/model/TextNode$gaurdedDocProvider
After clicking on OK I am able to continue work as usual.
I am not able to determine what is causing this problem. I do not want this pop up coming up every time I start a new worksheet.
What I have tried:
1. Reinstalling SQL Developer
Any help is highly appreciated.
Regards.
please post the scenario with the entire error log to the forums, or even better, as a new ticket with My Oracle Support
In SQL-Developer if I format my PL/SQL-code which contains a variable declaration for a PLS_INTEGER-Variable, the keyword PLS_INTEGER is not formatted to UPPERCASE.
A similar VARCHAR2-declaration is formatted to uppercase.
Is there a possibility to define the keywords to be formatted?
Thanks for your answer.
Kind regards,
Marc
PLS_INTEGER isn’t a keyword, it’s an IDENTIFIER. That’s because it’s a type/subtype in the STANDARD package.
If you set Preferences > Code Editor > Completion Insight > Chase case as you type to ‘ON’ and the dropdown to ‘lower keywords, Upper Identifiers’ – you’ll see pls_integer go to PLS_INTEGER as you type.
In v4.2 the formatter let you set case for keywords and identifiers independently, and you can set identifiers to UPPER, which will cause pls_integer to go to PLS_INTEGER on a format
Also, no, you can’t add your own keywords to the list. We maintain the list.
Thank you for your answer.
So I will wait for v4.2… 😉
Greetings,
Marc
Is “Chase case as you type” still an option? I’m having difficulty finding it under Completion Insight…
No, that was removed a few updates back.
If you want to change your code, then you can setup the formatter to do that, just hit ctrl+f7 as needed.
We did make the formatter such that you can have it ONLY change the case for keywords and identifiers, if you don’t like the other things the formatter does.
Is there a particular XML DB Repository path under which a Resource must exist in order for it to appear under the SQL Developer connections pane object “XML DB Repository”?
When an XML schema is registered using DBMS_XMLSCHEMA.registerSchema, it is automatically assigned the path:
/sys/schemas/user_name
and that schema file is then visible in SQL Developer in the connections pane object “XML Schemas”.
Following that pattern, I’ve created user-named directories in the Repository, trying both upper and lowercase, placed under the directories:
/
/public
/home
and nothing is appearing in SQL Developer.
Each directory is populated with a file to provide something to display.
Both SQL Developer versions 3.1 and 4.0 have been used.
The objects were created as the same user who is logged into SQL Developer, who has the XDBADMIN role.
The ACL properties of a Repository object have been compared with a Schema object, and there is not a permissions/privilege difference.
Thanks for any help.
yeah, this is way out of my field of expertise – I’d add a post on the XMLDB space on OTN, or open a SR with MOS
First, thank you for this useful blog and the information it contains!
My question is about the panes in SQL Developer. I have a report written that I use mainly as shortcuts to things I access frequently. When I start SQL Developer, I generally run this report and then pull it as a tab next to the Connections tab on the left side of my window. Is there a way to ‘stick’ it there, so that it will appear there when I open Developer again?
Thanks for any info you can provide!
Hi Jeff,
Thanks for the very informative blog! I return often for tips about using SQL Developer.
Could you write a post on the Advance Format (ctrl-shift-F7) feature? Having just discovered the feature, I just spent the last hour trying to figure out how to output a quoted string for use in Java code. I did finally figure it out but I think your readers could save themselves an hour if you were to post it – perhaps in your “Shortcuts” entry.
What confused me was the “Enclosed In” option – namely, I kept choosing the double-quote from the drop-down list which blew away my code. When I accepted the default, “Not Enclosed” it worked as expected.
The other thing that I found confusing was the Output Destination “Clipboard” option; I assumed that selecting clipboard would result in SQL Developer copying my code, formatted as specified, to the clipboard when in reality, I learned from the Help page that I had to copy the code to the clipboard first and then the Formatter would format it.
Would you know, in the SQL Worksheet, how to toggle the viewing the line feed characters on and off? I accidentally hit some combination of keys with my left hand (Ctrl+something near that) and now the <<P hidden character is displayed at the end of every line. I have searched Google and your website, and tried various combinations, but cannot figure out how to hide these characters. Any suggestions would be immensely appreciated. Thank you!
I’m not aware of a kb toggle…but in the preferences you can set this.
Thanks! Your response (and ‘tabs’ comment) got me searching for “whitespace” … and I found that there *is* a hotkey combo … Ctrl+Shift+W (see http://stackoverflow.com/questions/36068187/unwanted-sign-on-each-line-of-worksheet).
Problem solved! I appreciate your quick response!!
awesome!
my kb preferences doesn’t have that mapped to a key sequence, maybe b/c i’m looking at my Mac…glad you found it though
Hello Jeff,
Just wanted to drop you a quick note (not really a question) to say that I like your blog… a lot! I came across it looking for information on how to do certain things in Oracle Developer (what are sub views, what is a display, etc) and even clicked on the “About” button and read your brief bio 🙂
I like the way you write and it even got me excited about database design again, even though it’s not my prior task at work (I’m in integration, SOA and that sort of stuff).
Thanks and keep up the blogging!
J
Hi Jeff:
I noticed that if I directly grant ‘execute’ on a procedure to another account, then log in to that other account, open the ‘other users’ node, then navigate to the account that owns the procedure, I will see that procedure listed.
But when I grant the same privilege to a role that has been granted to the other account, and then log in to that other account and navigate to the account that owns the procedure, I do NOT see that procedure listed. Why is this happening? I want the other user to be able to see all the procedures I’ve given him ‘execute’ to through a view. Thank You.
Sorry, I meant “through a role.”
Probably this will help…not a SQL Developer question per se, but an Oracle security and privilege issue.
This might not be a SQL Developer question per se, so bear with me.
I was able to get an HTML report from SQL Monitor output in SQL Developer 4.1.3, however, when I try to view it in any browser, I get a blank page! Initially, I thought perhaps I don’t have Flash installed on my VM (Windows 7), but even after fresh installation from Adobe (and restarting my browser), I was still getting a blank page. Then I assumed it must be something about Chrome that is not displaying the HTML report. I switched to IE (11) and the latest Firefox — still the same.
Then I thought maybe there is something missing from my VM environment. So, I copied the report to my host machine (Windows 10), and I still cannot view the reports in either Firefox, IE or MS Edge! I can view Flash videos on all these browsers in both environments.
One last ditch attempt I made to exclude possible issues. I used SQL Developer 3.2 to generate a report. Same thing. For both environments, in all the browsers.
Funny thing is, I can view reports generated via SQL Developer 4.0 on my work machine (both the VM, Windows 7 and the Host, Windows 10).
What are the requirements to view SQL Developer Monitor reports?
And I just tried to load the HTML reports, which was generated on my home machine, on my work machine, and it still gives me a blank white page!
But I can view the generated HTML reports on my work machine just fine. What’s going on?
there’s an OEM bug that’s breaking the report. if you open the source of the HTML, you’ll see a reference to 12c or 12.1.0.1 (or maybe 2). if you change that to 11.2, the report will work
we’re building a completely brand new real time sql monitor interface for v4.2 that doesn’t rely on any OEM code or even an internet connection
Excellent. There were several places I had to replace “12.1.0.2.0” and “12.1.0.2” with “11.2”, and it did the job. Thanks.
P.S. Do you guys have a release date for v4.2?
P.S.S. Apologize for omitting the database version in my original post.
hope to have v4.2 in a beta where folks can provide feedback later this year – can’t get more specific than that, but we’re not that far out
I have a large folder of .sql files on my Windows 7 64 bit machine. When I double click on 1 .sql file the script will open in sql developer. Any subsequent .sql file will not open from within the folder. The file types are associated with sql developer on the windows and application level. It is as if there is an instruction set that states that only 1 .sql file will be opened by double clicking from within a folder. After this no others can be opened in this manner. I can’t find a registry key that would dictate this behavior. Getting to the hair pulling stage and just had to ask That Jeff Smith.
there’s something up with our app, or with java on windows – for now you can drag and drop the file from explorer into sqldev, or use the Files dialog in SQLDev to get easier access to your files
i’m working to have this ‘fixed’ in v4.2 if it’s something we have control of or access to when it comes to Windows
Hi,
I am exporting a table to a CSV file using SQL Developer version 4.1.2.20.
I need ALL columns to be enclosed in double quotes ” (or any other quotes). However, only strings and NULLs are enclosed, but not numbers/dates, e.g:
65,1,””,28-FEB-14 00.00.00,552.85,0,”OPEN0000000124″,12027,81361,1,1213,8366,1019,1,1061,,,4345584,4345633,””,””,92,4107039,””,4345633,8361,”B”,””,65,,,,195,,,,,,,,,
Is it possible to have quotes for all columns?
Thanks a lot
Pawel
sure thing, just TO_CHAR() the number columns, and write a SELECT * FROM on the table
Thanks – the most obvious solutions are always the best ones. Works fine for me.
Hi Jeff,
When we run following code on different versions of SQL developer, it returns different results. Would you please shed some light, why this ?
—————————-
/*Enable the SERVEROUTPUT to display block results*/
SET SERVEROUTPUT ON
/*Start the PL/SQL block*/
DECLARE
L_STR VARCHAR2(10);
L_COUNT NUMBER :=0;
BEGIN
/*Capture the system time before loop*/
L_COUNT := DBMS_UTILITY.GET_TIME;
/*Start a loop which assigns fixed numeric value to a local string
variable*/
FOR I IN 1..1000000
LOOP
L_STR := 1;
END LOOP;
/*Print the time consumed in the operations*/
DBMS_OUTPUT.PUT_LINE(‘Time Consumed:’||TO_CHAR(DBMS_UTILITY.GET_TIME
– L_COUNT));
END;
/
———————————————
RESULTS:
SQL DEVELOPER Version 4.0.2.15 – Time Consumed : 0
SQL DEVELOPER Version 3.2.20.10 – Time Consumed : 12
Thanks in advance.
what does sql*plus show, what does v4.1.3 of SQL Developer show?
which result do you consider to be the ‘correct’ one?
there are probably environmental differences between those 2 different SQLDevs, specifically the JDBC driver and your NLS parameters in Tools > Preferences
The two output are correct for their versions.
Version 4+ have optimizations turned on, since the loop had no side effects then it was optimized away.
Thanks for the quick reply.
Can we turn off the optimization in 4+ version?
What optimization?
In below code also we don’t have any side effects of loop but it gives same result on both the version.
SQL DEVELOPER Version 4.0.2.15 – Time Consumed : 4
SQL DEVELOPER Version 3.2.20.10 – Time Consumed : 4
/*Enable the SERVEROUTPUT to display block results*/
SET SERVEROUTPUT ON
/*Start the PL/SQL block*/
DECLARE
L_STR VARCHAR2(10);
L_COUNT NUMBER :=0;
BEGIN
/*Capture the system time before loop*/
L_COUNT := DBMS_UTILITY.GET_TIME;
/*Start a loop which assigns fixed numeric value to a local string
variable*/
FOR I IN 1..1000000
LOOP
L_STR := ‘A’;
END LOOP;
/*Print the time consumed in the operations*/
DBMS_OUTPUT.PUT_LINE(‘Time Consumed:’||TO_CHAR(DBMS_UTILITY.GET_TIME
– L_COUNT));
END;
/
there’s no…optimization. We just submit the code, and Oracle runs it.
You could use DBMS_PROFILER to see the exec time of each line of PL/SQL being ran in your anon block
main changes between those versions are the jdbc driver being used and possibly your NLS parameters are different – which can cause oracle to choose different execution plans
Preference->Database->PL/Sql Compiler->Optimization level.
Thanks for the quick response.
SQL DEVELOPER Version 3.2.20.10 shows correct result.
I will check with sql plus and SQL developer v4.1.3 and get back to you.
Hi Jeff:
How to get report published to a user via email in Excel format from SQL Developer?
I would use a reporting solution, Oracle offers several. SQL Developer doesn’t do email, but you could use it to write a store procedure to do it – since the Database DOES do email. It’s pretty straightforward with jobs.
And i’m pretty sure someone has a stored proc that can create Excel files. Or you could just write out CSV.
Howdy Jeff,
Really appreciate the good work in developing SQLDeveloper into a nice-to-work-with tool – it makes my life much easier!
Currently, we are attempting to reconcile a number of non-connected databases to a common standard (they are standalone databases that cannot be connected to anything else).
Doing this will help us conform to the DoD DISA STIG requirements for these environments, enabling us to verify all objects and configurations conform to a specific baseline.
At this point, we are using a combination of SQLDeveloper (Database Diff tool) and hand-wrapped processes, and have experienced a couple issues in the SQLDeveloper arena.
What would be the best way to approach resolving these issues from your perspective (on ThatJeffSmith.com or OTN or Oracle MetaLink)?
Recently we have experienced three issues while using SQLDeveloper 4.1.3.
1. Add tablespace generates bad DDL for temporary tablespaces. The generated SQL is ‘add data file’ instead of ‘add temp file’.
2. Where are the Policies on Tables when using SQLDeveloper? We can locate them with manual SQL, but they do not seem to be visible within SQLDeveloper.
3. Constraint differences are corrected via ‘Create Table’ instead of ‘Alter Table’. We are moving all the non-named constraints (SYS_####) to named constraints. Using the generated correction SQL of ‘Create Table’ will be very difficult to implement. It would be much easier to implement with ‘Alter Table’ instead. We are manually making these modifications by using the dbms_metadata.get_ddl routines.
Signed … YetAnotherJeff
I would open an SR with MOS since you most likely have an account – and we’ll have a ‘written record’ to make sure nothing slips through the cracks.
#1 would def be a bug – and an easy one to fix. There’s like 3 different ways to add a datafile to a tablespace, so make sure you tell us exactly how you’re doing it in the UI.
#2 we just don’t show them – but you could add it yourself. have you see this?
#3 weird, we’ll need a kind of before and after to know for sure, but adding like a check or not null constraint should already be doing an alter, or even just a Create Constraint…
Many Thanks for the quick response!
#1 We will document it and send it to Oracle Support.
#2 Thanks for the reference link. I have not yet added the policies, but this looks VERY useful.
#3 You mentioned ‘WEIRD’. Is there a parameter we can change to get SQLDeveloper to produce the ‘ALTER’ or ‘CREATE CONSTRAINT’ commands instead of the ‘CREATE TABLE’ with the constraints listed in-line?
give me a reproducible scenario on #3 and I’ll see what I can find out
Are there any issues with SQLDev v4.1.13 running on Windows 10? When I startup it goes into a long process of loading and then it seems not all options work in Load Data method. I am using a no-jre and also a embedded jre versions. Both have the same behaviour.
no known issues – sounds like an ‘install’ issue – maybe you put it down into an existing copy of sqldev? or maybe a file got corrupted.
try installing to a fresh directory
could also be an OS user privs thing – you need full read/write access to your appdata, roaming profiles folder for sql developer
Hi Jeff,
in my projects i intensely utilize carts to save source of Database Objects in our subversion scm.
I collect all changed objects in a single cart, and when reaching certain milestones i export the whole cart to update the svn repository.
No matter which export i use (separate-files, type-files or separate directorys) none of them overrides existing files. each export generates a new set of files adding a number to the filename. to get a version history in svn i manually have to delete the existing files before exporting. is there a way to instruct the cart export to override existing files?
and one more thing:
when exporting in multiple files a a collection of calls for each of them is generated in a single sql file. in my opinion using the complete path to call the subsequent sql-files is a bit cumbersome. for example “@C:\Compile\p.ettinger\kundenanpassungen\Schmoll\Sonstiges\APEX\SQLDEV\TAUTHTYPEROW_2.sql”
when deploying the set of scripts to the customers system, i have to edit all the paths or build up the same environment on the customers machine that i use on my dev box. is it possible to generate relative paths like .\TAUTHTYPEROW.sql instead?
Greetings
Peter
Peter,
Pardon the cut in, I have an example on my site.
http://plsql.me/carts-and-sql-developer-command-line-interface/
It auto generates the carts each night and then extracts them to empty directories. then uses svn to save in source control.
An I strongly agree with having the Generated.sql be relative. I genereate on Unix by most my user deploy on windows.
TR
Thanks for your reply. But your solution does not realy fullfill my needs. But i will store your approach for later use.
maybe Jeff could comment on my question and our request for relative paths
Jeff? Would you please comment?
Sounds like a good idea for an enhancement to SQL Developer.
However, I will suggest the alternative approach (which I use) which is only to generate the initial set of DDL scripts once, check them into source control, then from then on do your editing on your local copy of those scripts – i.e. don’t edit the objects directly on your database.
Cheers 🙂
Jeff (but not That one)
My client has version 4.0.1.14 running in her VM. For many months now she runs a simple SQL scripts that spool several output files to her local drive. In SQL*Developer she just types
@C:\’ and clicks the ‘run’ arrow. Starting last month the script now runs for a short time (minutes) and then seems to halt. I’ve looked at the process and it’s now in an INACTIVE state and generating zero (0) I/O’s. The script does nothing more than a series of ‘spool , select * from , spool off’.
I also tried it from my laptop and got the same results.
There is plenty of disk space in both our environments.
We have made no changes to the SQL*Developer setup.
Any ideas why this has just now started happening?
Is there a setting somewhere that limits the number of output lines?
Thanks very much for your help!!!
-gary
script output is limited in that version of sqldev ‘Max rows to print in a script’ on Worksheet preferences
4.0.1 had a few bad bugs, can you upgrade to 4.0.3 or even 4.1.3?
Hi Jeff,
We are adopting Data Modeler as a replacement for Oracle Designer. But we are facing some issues. We used to generate our changes in DDL against one or more existing databases.
We have exported our Designer Workarea and got a logical and relational model. Is there a possibility to generate form this model against the database. There seems to be no link to any database in the model and no way to establish one. It looks like the redirect option connects to the database but does not recognize our existing tables.
I also tried to import the existing database schema. This works but to generate our changes we would have to apply every change manually to (domains, etc) to this relational model, which comes with a lot of extra work.
Is there a way to work with Data Modeler in a similar way as we did with Oracle Designer?
apply every change manually (to domains, etc) – can you elaborate more here?
As you’ve observed, the compare to db/synch works best when the source objects come from a database reverse engineer.
The relational model that we exported from designer (R1) differs significantly from that exported from the data dictionary (R2). The latter contains no domains for example.
I tested with a simple change in a domain. Our analyst changed the domain in R1. As R2 was extracted from the database the datatype was varchar2 and it had a List of Value containing the domain values. I had to change the datatype and make sure the domain was used to be able to generate a simple alter table.
If I have to go through all of this for every domain, we will have a lot of work to synchronize our relational models. Not to speak of other changes…
If necessary I can provide you with some screen shots of the situation we are facing and what we are trying to accomplish.
Hi Jeff,
what about (possibly planned) user defined foldings within SQL-Scripts. Often scripts spans much more than 1 editor “page” and supplied standard foldings e.g. of a WITH-Block are not enough to focus on actually developed script lines …
kind regards
Edward
Hi Jeff,
I’m a new user to SQL Data Modeler, but am working my way through it. So far, so good. My question is: we use ClearCase for source control. I’m saving my design with the multi-file option. Exactly what files should go into source control? The product has generated almost 2000 individual files.
Hi Jeff,
Just love the productive gains SQL Developer has given to me and the team!.
I have made some custom reports and have using the tip from http://www.thatjeffsmith.com/archive/2012/07/using-html-to-mark-up-your-data-in-oracle-sql-developer/ to mark up the output. This is great for on screen display but form time to time I need to export to xls or pdf and it shows the HTML markup. Is there a way to export with out going to a non-markedup vesion? I was hoping I could use Variants, one for screen and one for export.
Thanks for all you support!
TR