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
Thanks for the update! Please could you point me in the right direction to raise this ER
You would need to create a Service Request (SR) with My Oracle Support (MOS)
Hi Jeff,
Thanks for the information – I have completed an SR for this enhancement.
I also mentioned in the SR a potential bug within the SQL developer unit tester when specifying the expected exception number. Shouldn’t this number be negative to be consistent with the PL/SQL routine “raise_application_error” and the PL/SQL compiler pragma “exception_init”?
Using a negative exception number in the unit test does not catch the exception. e.g. specifying -6510 or +6510 doesn’t work but 6510 does.
Many thanks,
Mark.
Hi Jeff,
MOS suggested I raise a SQL Developer Enhancement Request and the SR has been closed. Here is the number of the feature request: #45481
Hi Jeff,
I am using Oracle SQL Developer version 4.2.0.17. My question is : Does it support to migrate Sybase ASE 16 to Oracle 12.2, as I don’t up to Sybase(15) in the options.
Regards,
‘Deep’
Dear Jeff,
Could you please tell me how to get tab-separated column results under SQL Developer 4.2? Under 4.1, I was used to using the following script:
col TAB# new_value TAB NOPRINT
select chr(9) TAB# from dual;
set colsep “&TAB”
select * from table;
but that seems to no longer work in 4.2. Is it a bug?
Many thanks,
Matyas
Is there a way to script the export function?
I have a report setup that I can export to a xlsx file. I would like to script the report and export so I can run it as a scheduled task from the OS. Even better would be to send it via email.
I know there are many ways to accomplish this, just thought I would try with SQL Developer.
Thanks,
Rob
Hi Jeff,
I am new to the unit testing functionality within SQL*Developer v17.2.0.188 so forgive me if this question is obvious. When creating a new test, I must decided whether the desired outcome of a PL/SQL subprogram invocation is successful or an exception is raised.
When detailing the expected exception, the product allows a number or the word “ANY” which is similar to the WHEN OTHERS generic handler. Is it possible to handle PL/SQL named exceptions rather than relying on system or user-defined exception numbers?
Many thanks in advance,
Mark.
Hi Jeff,
Do you have an update on this please?
Many thanks,
Mark.
if the UI doesn’t currently support it, i’m guessing the answer is no, but it sounds like an excellent ER
Jeff,
Will the webcasts you are starting be recorded for viewing at a later date?
Thanks,
David
If I said ‘yes’ would that mean you’d not try 😉
But, yes.
No, I will be on, but a colleague that was looking forward to it has a conflict. Thanks for the quick response.
I use SQL developer pretty much every time I touch Oracle so kudos on your efforts. Do you remember working in the Honors office?
Oh wow, of course I remember! Do you miss that Access system – I do not!
Hi Jeff. What happened to Snippets in SQL Developer V4.2.0.17.089? It doesn’t appear in the View menu like it did in 4.1.3.20.
they’re there in v17.2 – pretty sure that wasn’t a known bug for version 4.2 though
Hi Jeff,
I need to create multiple data export files by executing 5 sql scripts in succession, each spooling to a different CSV output file.
I can do this in SQLDeveloper v4.1.5.121 but it prefixes the output with the CarriageReturn and LineFeed characters ( CR + LF. )
How can I get rid of these ?
Below is a listing of one of my scripts and the top 2 lines of the output
I was hoping sure this is a simple task but I searched for hours for this fix without success.
Took me 4 hours to figure out how not to get the Prompt lines (SQL >> @ C: … ) at the beginning of the file !!!
Thanks for the help.
Aubrey
<>
set SQLFormat CSV
set echo off
set feedback off
set HEADING off
set verify off
spool C:\WorkInProgress\text1.txt
select * from VDS_USER;
spool off
<>
“ID”,”NAME”,…
<>
@ C:\WorkInProgress\testSQLDev.SQL
a TON of work went into the script engine (the SQL*Plus stuff in sqldev) between version 4.1 and version 4.2. In the latest SQLDev, I don’t see any leading blank lines when running your script. Can you try upgrading?
Please provide a walkthrough for creating data flow diagrams in the Data Modeler.
I cannot find anything in the documentation. Usage is certainly not intuitive.
could you post this on the modeler forum? I’ve never used it, so I would have to learn how to do it first
SQL Dev Version 17.2.0
Database 12.2
I have 3 separate “apps” running mainly because if I have a long running job then SQL Dev basically freezes the app and I cannot multi-task on the same connection. It seems every day I get connection reset
“Your database connection has been reset. Any pending transactions or session state has been lost” pop-up on one of these 3 SQL Dev apps running.
I would like to be able to submit a query or insert or update in the worksheet and at the same time look at another table or view or Package.
Regards
use an unshared worksheet for your long running queries
Your database connection has been reset. Any pending transactions or session state has been lost – you shouldn’t be getting these on your v17.2 connections…you’re seeing it frequently or…?
Every 20 – 30 minutes
But we are also running into ORA-00600 Error in the same session. So may just be that error which is the culprit
If you’re getting 600’s you have a serious database problem.
I have the same issue but it occurs repeatedly and not due to long running DML and not tied to an ORA-600 error that I am receiving, though it happens on those as well. I typically have two instances of SQL Developer running on a Windows platform, each with multiple tabs and multiple instances connected. The lost connection issue when it occurs does not affect open connections to other instances. I have re-installed 4.2.0.17.089 Build 17.089.1709 and it still occurs. Searching the web shows others with the same exact issue but no real work around. This should be an active bug that is being addressed. What can we do?
This should be an active bug
If you can describe a reproducible scenario in 17.2, then probably. Assuming it’s not the DB or Network kicking your connection.
And, then you should open a service request with My Oracle Support.
I am trying to set up SQLDeveloper to use LDAP, I have SQLPLUS working great but SQLDeveloper gives the error when every I select the LDAP Servers.
“Status : Failure – [LDAP: Error code 32 – No Such Object].
Configuring an advanced connection using “jdbc:oracle:thin:@ldap:/………” also works.
Any idea what I am missing?
Thanks
Roger
SQL*Plus doesn’t use a thin jdbc driver, so if you want SQL Developer work with ldap as you have with SQL*Plus, you have to have SQL*Plus use a THICK connection so it will see your sqlnet.ora/etc.
Thanks for your quit response,
Do you mean have SQL Developer use a THICK connection?
If so have tried setting “Use OCI/Thick Driver” and still the same error.
Testing the Oracle Home located at C:\oracle\product\12.1_64bit\client_64bit
Testing client directory … OK
Testing loading Oracle JDBC driver … OK
Testing checking Oracle JDBC driver version … OK
Driver version: 12.1.0.2.0
Testing testing native OCI library load … OK
Success!
Thanks
Roger
that should be enough, assuming you only have the one client on your machine, and you’re using the right tnsnames file…I would open a ticket with My Oracle Support
Hi Jeff,
2 questions for you:
1. Identity. I read your post about it (http://www.thatjeffsmith.com/archive/2014/01/defining-12c-identity-columns-in-oracle-sql-developer-data-modeler/), but can’t get mine to work….
I created a relational model. Checked the following preferences (main ones, for the app):
* preferences -> data modeler -> model -> rdbms -> 12c
* preferences -> physical -> Oracle -> default identity ddl -> identity clause
* preferences -> physical -> Oracle -> default auto increment ddl -> default clause (though I also tried it with ‘none’ and ‘trigger’, but didn’t see a difference)
When I create a new table, add a number (12,0) column, designate it a primary key, and preview DDL, there is no mention of identity… I can double-click the column to see general properties. Neither “auto increment” nor “identity column” are selected automatically… (why not? would’ve expected that as a default for foreign key…)
I select both “auto increment and identity column”. Apply. Preview DDL. A trigger is generated. Click the column again, go into “auto increment” preferences. The “generate trigger” checkbox is selected. Unselect. Generate DDL again. Get a sequence…. It doesn’t seem to be able to get identity going….
I added a physical 12c model (though not doing anything with it at the moment). Looking at column properties from there, I see “auto increment”, but “generate DDL” is empty. I select “identity”, save everything, but DDL preview still generates a sequence, not an identity clause…. Could you please help?
(As an aside, main preferences seem to be a bit busted on a Mac. There is no scroll bar on the windows with the checkboxes–you can see them scroll into the box, but you can’t get to what’s below the window line….)
on the relational model, table properties. select the column, hit the properties button – do you have ‘auto increment’ and ‘identity’ selected there?
It’s working for me in 17.2
Yes, have both checkboxes. Both are unchecked. Check them. Then if the “trigger” checkbox under “auto increment” is checked, I get a trigger, else I get a sequence. Been struggling with this most of the day today, can’t get it to work in 17.2. Could it be getting overwritten by a higher level property or some such? What is your “preferences -> physical -> Oracle -> default auto increment ddl” clause set to (though I’ve tried every option…)
There is no ‘trigger’ checkbox under ‘Auto Increment’ – what dialog exactly are you looking at?
No-no,
I’m saying I’ve checked all 3 levels of preferences where this is mentioned…
1.
– Create a dummy table. Column name = ‘id’, logical, numeric, (12,0), check ‘PK’ box.
– Double-click column. Click ‘general’. The ‘auto increment’ and ‘identity’ check boxes are not checked. Check them. Apply.
– Click at the ‘auto increment’ link. Note that there is a ‘generate trigger’ checkbox at the bottom, and it’s checked.
What I’m saying is that if I leave that ‘trigger’ box on the ‘autoincrement link’ page checked, I get a regular non-identity column and a trigger. If I uncheck it, I get a regular non-identity column and a sequence….
2. I looked at the relevant preferences (main ones for SQL Developer):
* preferences -> data modeler -> model -> rdbms -> 12c
* preferences -> physical -> Oracle -> default identity ddl -> identity clause
* preferences -> physical -> Oracle -> default auto increment ddl -> default clause (though I also tried it with ‘none’ and ‘trigger’, but didn’t see a difference; what do you have here?)
3. I made sure that I have a 12c physical database.
Looking at column properties from there, I see “auto increment”, but “generate DDL” is empty. I select “identity”, save everything, but DDL preview still generates a sequence, not an identity clause….
Hi Jeff,
Identity not generating is still a problem… Do you kno what it might be down to? What are your global preferences set to (see my last response for mine).
Thanks again!
No idea. It should be working. Try opening a post on our modeler forum, and the developers can delve deeper, and we can share screenshots back and forth more easily.
Yes, other changes are persisting… Upgraded to 17.2 this morning. Deleted tables/FKs. Changes persist after re-opening, so yay.
However, from clicking “open” menu to getting the box to select model to open took about 10 mins. Tried a few times, same deal. Running on a Mac (Sierra) with 5-6G memory free (8G total)… A bit puzzled by that…
Also, opening DM (or SQL session), top icon bar is pretty bare, and I have to click “Window -> Reset to factory settings”. How do I make it remember it? Similarly, opening a DM, the file selection box defaults to an old directory. How do I make it remember the location of the last file opened?
Many thanks!
Ok, we’re talking about multiple problems now. Let’s stick with the model one.
Can you create a new design, from scratch. Then add 2 tables. Save it. Then delete a table. Save it. Close the design, and re-open – is your table ‘gone?’
If so, then I think there’s something wrong/corrupted with your other design.
Hi Jeff,
Following the upgrade to 17.2, the deletes were working correctly (so perhaps a 4.2 issue on a Mac??). The problem was how long it was taking to load a model.
I found your article about resetting to factory settings permanently (http://www.thatjeffsmith.com/archive/2015/08/how-to-reset-your-sql-developer-preferencessettings/). Having done that, the icon bar now shows up properly AND the time to open the model is now only a few minutes. Whatever was causing the long delay looks to have been cleared. (Default directory also wiped clean!)
This whole thing with deletes and upgrade looks to be fixed now. Thanks again for your help!
May have spoken too soon… Was fine quitting and restarting earlier (in terms of clicking “open model” and getting the directory window to select model)–was taking about a minute after resetting preferences. Just started it up again, and for no good reason it’s back to taking 15 minutes to give me the selection box (to open the dmd file)….
I read a post about an Oracle SQL Developer user that was sick and tired or it….but you were kind enough to email him and you addressed most of the items he was frustrated with. (This was a post from about two years ago.) He listed one of the items that has been bugging the “heck” out of me…but he did not include any of your advice/suggestions.
When I am working in SQL Developer, on a data grid, I can press the tab key to advance to the next column…then, for no apparent reason, the next time I use the tab key, it will cause the “edit value” dialog button to appear in the field and I can tab from column to column anymore. The only way that I have found to “restore” the tab key is to exit out of the application and then re-launch it. Is there a setting or preference that can control this behavior? Thanks in advance for any feedback.
On Windows, in version 17.2, I can use BOTH the tab and left/right arrow keys to move around the cells of a result set data grid.
Hello Jeff,
this might be a simple Task and I’ve tried to find the answer, but it’s still not clear.
We’re using Windows user profiles with space restriction for Appdata.
Now the SQL Developer (Version 4.1.2.20.64) is storing the System Cache data within the roaming Profile.
I’ve found a Workaround to add “AddVMOption -Dide.user.dir=C:\sqldeveloper” at “\sqldeveloper\bin\sqldeveloper.conf”.
Do you know any other way to solve the Problem?
Many thanks in advance!
Bastian
Increase your quota? I’m on vacation this week. This could be a bug…have you tried a newer version such as 4.2 or 17.2?
Hello Jeff,
this might be a simple Task and I’ve tried to find the answer, but it’s still not clear.
We’re using Windows user profiles with space restriction for Appdata.
Now the SQL Developer (Version 4.1.2.20.64) is storing the System Cache data within the roaming Profile.
I’ve found a Workaround to add “AddVMOption -Dide.user.dir=C:\sqldeveloper” at “\sqldeveloper\bin\sqldeveloper.conf”.
Do you know any other way to solve the Problem?
Many thanks in advance!
Bastian
Hi Jeff,
After I delete tables from a relational DM (right-click, “delete object”), save the model, and exit SQLDev, when I start it up again and open the model, all those tables come back!! I don’t seem to be able to delete them permanently… How do I do that?
Even worse, when I delete foreign keys from a table, they *also* come back!! If I rename/recycle the tables these were once keyed to, the FKs resurrect themselves under the old names and an empty relationship.
Happens every time… How do I make deletes permanent, esp. around FKs? Is there some kind of magic “purge” option?
Running SQL Developer 4.1.1.19 on a Mac (Sierra). I had a newer version on a different laptop late last year, and it was having the same problem.
Thanks in advance!
Could be a bug…there’s been a 4.1.2, 4.1.3, 4.1.5, 4.2, 17.2 since.
You might me removing them from the diagram but not the model?
I know, I had 4.2 before, and I remember seeing the same behaviour.
Not sure what you mean by removing from diagram but not model… Right-clicking in the diagram has “remove object” and “remove from view”. When I select the former, the table disappears from the navigator dropdown of tables….
(Right-clicking tables in the navigator and clicking “delete” also doesn’t stop them from coming back after restarting the application 🙁 )
If you’re saving the design and it’s ‘coming back’ on a reopen, there’s something wrong with the file. Are other types of changes persisting?
Hi Jeff,
I just got a new Lenovo Yoga 910 with a 13.9″ UHD (3840 x 2160) display. Unfortunately, SQL Developer does not scale UI components (Menu, Buttons/Icons, Font). I’ve tried Ide.FontSize but it does not really help. I mean the fonts get bigger but everything else remains tiny. It is impossible to work with the tool.
How can I make SQL Developer properly scale on UHD displays?
Sorry, submitted accidently, I was not finished…
Thanks for your time and help!
Beat
Change the look and feel from Oracle to Windows…that might help.
I’ll try, but I’m running Fedora 26. Any suggestions for that?
Thanks!
I’ve only got “Metal” and “Oracle” look and feel options. Both don’t scale. Will there be UHD support in a future release?
Had this same issue, take a look at
http://www.thatjeffsmith.com/archive/2013/12/how-to-change-the-ui-font-size-for-oracle-sql-developer/
Hi,
Thanks, I already followed the advise in the link. It just makes the fonts a little bigger but anything else remains tiny (Menu, Buttons/Icons, etc.). And with the bigger font, some items are overlapping and get unreadable.
I was hoping for a proper high DPI scaling solution…
Best regards,
Beat
BREAK ON … DUPLICATE does not work ?
16:26:42 NFP2TST1:APPSV@newfieldtst1>BREAK ON SECTION SKIP 1 DUPLICATE;
16:26:42 NFP2TST1:APPSV@newfieldtst1>select ‘test’ section , 1 from dual
2 union
3 select ‘test2’, 2 from dual
4* union select ‘test2’, 3 from dual;
SECTI| 1
—–|———-
test | 1
test2| 2
3
EXPECTED OUPUT IS
SECTI| 1
—–|———-
test | 1
test2| 2
test2| 3
I’ve never used Duplicate before, but it should work. If not, it’s a bug.
COLUMN LIKE is not supported ?
COLUMN FORMAT 999,999,990.00 is not supported ?
— begin of transcript —–
13:16:48 NFP2PRD1:APPSV@newfieldprd1>col
COLUMN posted_flag ON
FORMAT a11
COLUMN account ON
FORMAT a10
13:16:30 NFP2PRD1:APPSV@newfieldprd1>col posted_flag like account;
SP2-0158: unknown COLUMN option “like”
13:16:50 NFP2PRD1:APPSV@newfieldprd1>col d2 format 999,999,990.00
SP2-0246: Illegal FORMAT string “999,999,”
Hi Jeff,
When I format the code, Is there any way to keep multiline comments intact?
I use Java-style code comments:
/ **
* Retrieve the value from the associative array
*
* @param p_array the associative array
* @param p_key the array key
*
* @return the value if exists
* /
And SQL Developer formats it to:
/ **
* Retrieve the value from the associative array
*
* @ Param p_array the associative array
* @ Param p_key the array key
*
* @ Return the value if exists
* /
Dear Jeff,
I installed APEX & ORDS on a test DB and they work well togheter. I created a workspace in APEX, some sample tables, then configured Restful services, and I can access them using the url:
http:///ords/test/hr/employees/
I own another Oracle DB with a user, a tablespace and few tables filled with data I want to access via REST from a Webapplication. I don’t want to install APEX here, I don’t need it and I don’t want to create a new tablespace like APEX_xxxxxxxxxxxxxxxxxx.
I just want to create REST handlers (GET, PUT etc) for those existing tablespace/tables, even by plsql calls (it’s not a problem for me, I don’t care about a GUI) using ORDS.
Is it possible?
Thanks for your attention. Best Regards,
Mario.
Absolutely. Just install ORDS – no need to install/configure APEX. Then use SQL Developer or SQL*Plus to setup your RESTful Services. Click on the ‘ORDS’ link up top and you’ll see my tutorials on the subject.
break on .. duplicate also does not work.
16:26:42 NFP2TST1:APPSV@newfieldtst1>select ‘test’ section , 1 from dual
2 union
3 select ‘test2’, 2 from dual
4* union select ‘test2’, 3 from dual;
SECTI| 1
—–|———-
test | 1
test2| 2
3
expected ouput is
SECTI| 1
—–|———-
test | 1
test2| 2
test2| 3
COLUMN LIKE is not supported ?
COLUMN FORMAT 999,999,990.00 is not supported ?
— begin of transcript —–
13:16:48 NFP2PRD1:APPSV@newfieldprd1>col
COLUMN posted_flag ON
FORMAT a11
COLUMN account ON
FORMAT a10
13:16:30 NFP2PRD1:APPSV@newfieldprd1>col posted_flage like account;
SP2-0158: unknown COLUMN option “like”
13:16:50 NFP2PRD1:APPSV@newfieldprd1>col d2 format 999,999,990.00
SP2-0246: Illegal FORMAT string “999,999,”