4.5+ million 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!
5,447 Comments
When you rest-enable a table or view, is there a way to add “Access-Control-Allow-Origin: *” to the headers of the response? I see there is a ORDS.SET_MODULE_ORIGINS_ALLOWED procedure, but it only works for modules. When I rest-enable a table (just ORDS.enable_schema and ORDS.ENABLE_OBJECT) I don’t have a module.
Thanks,
Geert
In SQL Developer 19.4 is there any way to set up Real Time SQL Monitoring to ONLY look for a specific user? I know I can order output either ascending or descending, but I really want to monitor just one user.
No
My issue is that it appears to lock up, even after setting a high/long refresh rate. Filtering out users could help. Where would one submit this for product enhancement?
It shouldn’t be ‘locking up’ – how many reports are there?
You can formally send in ER’s to MOS. But I’d rather see a bug on the ‘locking up’ part, and we could fix that.
Sorry, Jeff this is in regards to Data Modeler post
But, I have dozens of Data Modeler posts…which one? This would have been easier if you had asked the question on the post you had actually read 🙂
Hello Jeff,
BTW I love your blog. They say the picture is worth a thousand words, but the video is definitely worth a million words. Regardless I followed your straight instructions and cannot get it to work I can add the tables but there is no relation added automatically. Also I cannot add a table and related tables by holding CTRL key and dragging the table into empty space. There are no keys created as well ( no “P” besides the EMPLID). I picked the basic tables from Peoplesoft SYSADM schema. I tried PS_JOB and PS_PERSON. Is there something missing in the setup or configuration?
Thanks for you input
Mirek
Hey Mirek, thanks for the kind words!
This post has more than 5,000 comments, and my blog has more than 900 posts, so I’m not sure exactly that instructions you’re following.
It sounds like they’re trying to create a relational data model diagram for your peoplesoft schema?
Hello Jeff,
I would like to ask if something changed according to SSO login using SQL Developer and Windows AD Kerberos.
I tried search your website and current SQL Developer docs too, but nothing about SSO.
I have read the Laurent Schneider blog https://laurentschneider.com/wordpress/2014/01/the-long-long-route-to-kerberos.html.
But for enterprise usage for many users is not easy going task.
I know that DB version 18c has some new features for supporting Active Directory authentication (without OID), but I cannot find any step-by-step document relevant to SQL Developer.
Any hint would be very much appreciated.
Thank you.
Marian
we support OID, OUD, and Kerberos
you can use it with a thick or thin oracle connection
in Database Advanced preferences there’s a Kerberos Thin Config section – that’s what you would use if you want to just use the regular jdbc driver, otherwise you get it going with an Oracle Home (thick) connection, and it should just ‘work’
thanks Jeff,
it looks like easy task… 🙂
but, I have found this doc on Oracle My Support
Kerberos Authentication With Oracle JDBC Thin Driver And Microsoft Active Directory (Doc ID 1523651.1)
and this is 26! pages long white paper. This is the ONLY way to configure AD Kerberos authentication?
Hasn’t Oracle newer documentation? Because in 2020 using doc with mentioning Windows 2003 is quite unusual.
Thanks in advance.
Regards,
Marian
I’m not a Kerberos or OID/OUD ‘guy’…so I’m pretty much in the dark on this stuff.
I can ask around internally and find the RIGHT people, but I would need an exact question as to where you are getting hung up, as they are likely to just point me back to the Docs.
Thanks Jeff.
My exact question is:
Exist any current Oracle docs how to configure SQL Developer (on Windows) to authenticate to the Oracle database 18c or 19c using Single-Sign-on with Active Directory Kerberos?
Similar to the 1523651.1. but more actual – db version 18c or 19c.
Thanks for the effort.
Marian
But, it depends.
Do you already have it working with SQL*Plus? Because if so, you’d would go the Thick connection route.
If not, and you want to stay pure Java, you’d go with the JDBC Docs and the kerberos thin config files in SQLDev, then go with the Kerberos type of USER in your connection properties.
This is all assuming you ALREADY have Kerberos configured with your Oracle Database.
Jeff, thank you, I’ll try to configure it!
V 19.4.0
Monitor Sessions
The query to get the Active SQL has not changed since v19.1 when I reported that there is a missing condition
between gv$session s and gv$sql q
and s.inst_id = q.inst_id
We have 6 nodes and the query goes to neverland as before. You had said this would be fixed in 19.4!
Somehow I missed it. In the meantime you can build your own custom Sessions Monitor and put in the correct SQL. I’ve logged the bug with a note to check for this on ALL of the GV$ reports.
Hi Jeff,
could you please explain us that how can we setup/create sqldeveloper connection with oracle database using SSL.
Thanks,
Avinash
The easiest way is to set it up for SQLPlus, then tell SQLDev to use a thick connection and point it at the same Oracle Home.
You can also do it with thin jdbc, and the Oracle docs have examples you can look at.
Hello,
once more my problem with large table request:
Table with 125000 records, defaults.xml 150000
Why do I get 404?
debugPrint:
footer,header{display:block;}
html{font-family:sans-serif;-ms-text-size-adjust:100%;-webkit-text-size-adjust:100%;}
body{margin:0;}
h1{font-size:2em;margin:0.67em 0;}
pre{font-family:monospace, serif;font-size:1em;}
pre{white-space:pre-wrap;}
img{border:0;}
html{font-family:-apple-system,BlinkMacSystemFont,”Segoe UI”,”Helvetica Neue”,Arial,sans-serif;font-size:0.875em;}
body{background-color:#f9fafa;color:#333;}
a{color:#045fab;line-height:inherit;text-decoration:none;}
a:visited{color:#045fab;}
a:hover,a:focus{text-decoration:underline;}
a:active{color:#034379;font-weight:normal;border-radius:1px;}
h1,h3{color:#252525;font-family:inherit;font-style:normal;font-weight:normal;margin:8px 0;padding:0 0 2px;}
h1{font-size:2.429rem;font-weight:200;}
h3{font-size:1.429rem;font-weight:300;}
ul{margin:12px 0;padding-left:40px;}
ul li{line-height:1.25;}
.oj-text-sm{font-size:0.857rem;}
.oj-text-secondary-color{color:#4d4d4d;}
.oj-margin{margin:10px;}
.oj-panel{border:1px solid #d6dfe6;background-color:#fff;border-radius:1px;box-sizing:border-box;overflow:hidden;padding:0.8571rem;position:relative;}
.oj-panel-alt4{background-color:#ffe4e1;border-color:#ffcdc8;}
.oj-flex-bar{position:relative;}
.oj-flex-bar,.oj-flex-bar-middle,.oj-flex-bar-end{box-sizing:border-box;display:-webkit-flex;display:flex;}
.oj-flex-bar-middle{-webkit-flex:auto;flex:auto;}
html:not([dir=”rtl”]) .oj-flex-bar-end{margin-left:auto;}
@media print, screen{
.oj-sm-align-items-center{-webkit-align-items:center;align-items:center;}
}
.oj-web-applayout-body{overflow-y:scroll;}
.oj-web-applayout-page{display:-webkit-flex;display:flex;-webkit-flex-direction:column;flex-direction:column;box-sizing:border-box;min-height:100vh;}
@media screen and (-ms-high-contrast: active), (-ms-high-contrast: none){
.oj-web-applayout-page{height:100vh;min-height:0;}
}
.oj-web-applayout-max-width{margin-left:auto;margin-right:auto;width:100%;max-width:1440px;box-sizing:border-box;}
.oj-web-applayout-header,.oj-web-applayout-footer{-webkit-flex:0 0 auto;flex:0 0 auto;}
.oj-web-applayout-header{background-color:#f0f0f0;box-shadow:0 3px 3px rgba(0,0,0,0.1);}
.oj-web-applayout-header>div:first-child{box-sizing:border-box;}
.oj-web-applayout-header>div:first-child::before{content:””;display:inline-block;min-height:3.143rem;visibility:hidden;}
.oj-web-applayout-header>div,.oj-web-applayout-footer>div{padding-left:20px;padding-right:20px;}
.oj-web-applayout-header-title{font-size:1.143rem;font-weight:normal;color:#4d4d4d;line-height:0;padding:0;margin:0;}
.oj-web-applayout-content{-webkit-flex:1 0 auto;flex:1 0 auto;padding:10px;}
.oj-web-applayout-footer{min-height:3.571rem;box-sizing:border-box;border-top:1px solid #d9dfe3;background-color:#f0f0f0;padding-top:10px;padding-bottom:10px;}
.oj-web-applayout-footer-item{box-sizing:border-box;display:-webkit-flex;display:flex;-webkit-flex-wrap:wrap;flex-wrap:wrap;-webkit-flex:0 1 auto;flex:0 1 auto;}
@media screen and (max-width: 767px){
.oj-web-applayout-footer-item{-webkit-justify-content:center;justify-content:center;text-align:center;}
}
.oj-web-applayout-footer ul{margin:0;}
html:not([dir=”rtl”]) .oj-web-applayout-footer ul{padding-left:0;}
.oj-web-applayout-footer li{list-style:none;display:inline-block;}
html:not([dir=”rtl”]) .oj-web-applayout-footer li{margin-right:10px;margin-left:0;padding-right:10px;border-right:1px solid #d9dfe3;}
html:not([dir=”rtl”]) .oj-web-applayout-footer li:last-child{margin-right:0;padding-right:0;border-right:none;}
.traceBox{overflow-x:auto;overflow-y:auto;max-height:60vh;padding:0.8571rem!important;margin:0;}
.panelCollapsible{border:1px solid #d6dfe6;background-color:#fff;border-radius:1px;box-sizing:border-box;overflow:hidden;position:relative;}
.titleCollapsible{padding:0.8571rem!important;border:1px solid #d6dfe6;border-width:0 0 1px;margin:0;}
@font-face{font-family:”Segoe UI”;font-weight:100;src:local(“Segoe UI Light”);}
@font-face{font-family:”Segoe UI”;font-weight:200;src:local(“Segoe UI Semilight”),local(“Segoe UI Light”);}
@font-face{font-family:”Segoe UI”;font-weight:300;src:local(“Segoe UI Semilight”),local(“Segoe UI Light”);}
@font-face{font-family:”Segoe UI”;font-weight:400;src:local(“Segoe UI”);}
@font-face{font-family:”Segoe UI”;font-weight:500;src:local(“Segoe UI Semibold”);}
@font-face{font-family:”Segoe UI”;font-weight:600;src:local(“Segoe UI Semibold”);}
@font-face{font-family:”Segoe UI”;font-weight:700;src:local(“Segoe UI Bold”);}
@font-face{font-family:”Segoe UI”;font-weight:800;src:local(“Segoe UI Bold”);}
@font-face{font-family:”Segoe UI”;font-weight:900;src:local(“Segoe UI Bold”);}
@font-face{font-family:”Helvetica Neue”;font-weight:100;src:local(“HelveticaNeue-Ultralight”);}
@font-face{font-family:”Helvetica Neue”;font-weight:200;src:local(“HelveticaNeue-Thin”);}
@font-face{font-family:”Helvetica Neue”;font-weight:300;src:local(“HelveticaNeue-Light”);}
@font-face{font-family:”Helvetica Neue”;font-weight:400;src:local(“Helvetica Neue”);}
@font-face{font-family:”Helvetica Neue”;font-weight:500;src:local(“HelveticaNeue-Medium”);}
@font-face{font-family:”Helvetica Neue”;font-weight:600;src:local(“HelveticaNeue-Medium”);}
@font-face{font-family:”Helvetica Neue”;font-weight:700;src:local(“HelveticaNeue-Bold”);}
@font-face{font-family:”Helvetica Neue”;font-weight:800;src:local(“Helvetica-Bold”);}
@font-face{font-family:”Helvetica Neue”;font-weight:900;src:local(“Helvetica-Bold”);}
@font-face{font-family:”Arial”;font-weight:100;src:local(“sans-serif-thin”),local(“Arial”);}
@font-face{font-family:”Arial”;font-weight:200;src:local(“sans-serif-thin”),local(“Arial”);}
@font-face{font-family:”Arial”;font-weight:300;src:local(“sans-serif-light”),local(“Arial”);}
@font-face{font-family:”Arial”;font-weight:400;src:local(“sans-serif-regular”),local(“Arial”);}
@font-face{font-family:”Arial”;font-weight:500;src:local(“sans-serif-medium”),local(“Arial”);}
@font-face{font-family:”Arial”;font-weight:600;src:local(“sans-serif-medium”),local(“Arial”);}
@font-face{font-family:”Arial”;font-weight:700;src:local(“sans-serif-bold”),local(“Arial Bold”);}
@font-face{font-family:”Arial”;font-weight:800;src:local(“sans-serif-bold”),local(“Arial Bold”);}
@font-face{font-family:”Arial”;font-weight:900;src:local(“sans-serif-bold”),local(“Arial Bold”);}
.oj-collapsible {
margin-bottom: 0 !important;
}
.header-grid {
display: grid;
grid-template-columns: auto 1fr;
grid-column-gap: 1rem;
grid-template-areas: “ords-logo oracle-logo” “ords-logo product-name”;
}
.ords-logo {
grid-area: ords-logo;
}
.oracle-logo {
grid-area: oracle-logo;
padding-top: 0.2rem;
}
.product-name {
grid-area: product-name;
font-size: 1rem;
padding-bottom: 0.2rem;
}
Not Found
REST Data Services
404
Not Found
Debug Trace
Stack Trace
About Oracle
Contact Us
Legal Notices
Terms Of Use
Your Privacy Rights
Copyright © 2014, 2018 Oracle and/or its affiliates All rights reserved.
function noReason() {
if(document.getElementById(“reasons”).children[1] !== undefined) {
document.getElementById(“reasons”).removeChild(document.getElementById(“reasons”).children[0]);
}
else if(document.getElementById(“reasons”).children[0].children[0] === undefined){
document.getElementById(“reasons”).remove();
}
}
noReason();
Hi Jeff,
I’m using v19.1 SQL*Developer.
When I select the option File > Open… off of the SQL*Developer’s menu bar, a lot of recently used yellow folders appear in the left pane of the Open dialog box. How can I delete or reset the dialog box to show only the folder that has been set under Tools > Preferences > Database > Worksheet > Select default path to look for scripts?
I tried to search for a configuration entry in the (*.conf or *.xml) file in the SQL*Developer installation folder, but was unable to locate the folder entries.
Could you point me in the right direction?
Thanks in advance,
Steve Karetny
[email protected]
You’re seeing the last 5 or so directories you have browsed to for opening or saving a file using that editor, in this case, the worksheet.
Hello Jeff,
I’m using ORDS 19.1 with Auth2.0 authentication.
I have created a pre-hook function to log the cqalls to the ORDS Services as described on this link:
https://www.jmjcloud.com/blog/using-the-ords-prehook-function
The function provides some web session details using the “OWA_UTIL.get_cgi_env” function, but unfortunately no ORDS client details. I’ve tried to use the SYS_CONTEXT to get additional client information, but that gives back only RDBMS session details.
Is there a way to get in the pre-hook function the ORDS client details (e.g. user_ords_clients.name, user_ords_clients.client_id)?
Thank you in advance.
Kind regards,
Igor
I’m using SQL Developer 19.4 on Windows 10 with Dark Mode enabled on at the OS level. Does SQL Developer support W10 dark mode? If not, is there a way to use prefs to fully simulate?
Maybe
Hi Jeff – Thanks for all your great posts! very helpful.
I have create a custom JAASRealm which ORDS is using in order to authenticate users (basic authentication from a browser modal login window) but I am not able to properly configure Tomcat/ORDS so that it uses a custom login form. Can you provide me with some information on how to do this?
Many thanks. Rob.
Hello Jeff,
I am learning SQL privately and can sure do with technical help.
As I searched for the solution upon getting a certain error message in aggregate functions, I bumped into your web page Jeff Smith Blog. The link for the page is shown here-below.
https://weblogs.sqlteam.com/jeffs/2007/07/20/but-why-must-that-column-be-contained-in-an-aggregate/
I was impressed at the simple way you explain the error using clear explanation from day to day event. I figured that you could probably provide some guidance on recommending some simple SQL books, notes or even website. I need your clear down-to-earth kind of explanations on SQL.
Professionally, I am a civil engineer. In 2012, I designed a database using MS Access which I used for my church for several years. It is from this work that I developed interest in learning SQL.
Solomon Ragu
That blog post you found is from a, believe-it-or-not, a DIFFERENT Jeff Smith.
If you’re looking for a nice, easy to understand place to learn SQL, try this resource (LiveSQL).
It’s not new, but Kevin Kline’s SQL in a Nutshell is an easy read, and it’s helped thousands of people learn SQL.
Hello Jeff-
How can I use ORDS to query an external web service to import JSON into my Oracle database?
Thank You
No…ORDS is there to provide HTTPS access to an Oracle Database.
What you’re asking is how to get data from outside an Oracle Database, from the database itself, via HTTP. And there’s a DB package for that. You’d want to create a stored procedure and use the HTP package. You can use it to do a GET to find your JSON, then you can use the JSON packages in the database to transform that into relational rows.
I’m sure someone has an example of doing just that already.
HI Jeff , I would like to know if is possible using SQL Developer to generate an export from an On premise database environment, and import into an Autonomous Database ?
Yes!
I am interested in going to Kafka and getting JSON output
Hello Jeff,
I’ve used unittests in SQL Developer for some years, but now I’ve got a new computer with Windows 10 instead of Windows 7. When I tried to select my actual repository, I see that the menu-item is grayed out like all other menu-items for unittests. So I can’t use my unittests anymore. Have you any idea what I have to do to resolve this?
Best regards
Peter
The UT repo is there but SQLDev won’t let you associate your connection?
Hello Jeff,
yes, the repository is there since we have not change the database.
But I have to say Sorry because 30 second after I send the question I found in the View-Menu an Item Unittest too. If I select it, I can connect the actual repository in the unittest-menu under Extras (= German, perhaps in English it has an other name). Thank you very much for your fast reply!
Peter
Hi ,
When export table DDL from SQL developer(Tools => Data base Export), I did not get Constraints in the DDL.
So, I would like to know how can I get the DDL including constrains like NOT NULL.
Hi,
Could you reply above comment.
Database Tools – Export
It somewhat depends on what options you have set for the export. Are you checking both constraints and referential integrity constraints? Are you asking for constraints as ALTERs? And do you see the constraints when you open the SQL tab for your table when viewing it in the tree?
Thanks Jeff for confirmation! Also big thanks and congrats to you, Barry and the whole team working on SQLcl – it rocks :).
Jeff,
while we use Oracle as our DBMS we use liquibase to deploy our changes, I am wanting to use SQL datamodler but change the DDL transformation so we can output our standard liquibase format.
I can find various examples of simple transformations but I am struggling to find anything that documents the attributes and methods of the transformation objects i.e. these:
– model – relational model instance
– pModel – physical model instance
– table – the table in relational model
– tableProxy – table definition in physical model
The documentation that comes with sqlmodeler does not seem to adhere to the examples i.e. getAppView() method used in many examples does not appear to be in the documentation.
Can you advise of any resources where this is documented.
Regards
Hello Jeff,
I have written blog post about possible bug in SQLcl, as regards manually setting JAVA_HOME (required for example for 12.1 to avoid unsupported Java version errors).
https://changetracking.wordpress.com/2019/12/19/small-fix-allowing-to-use-sqlcl-os-authentication-with-oracle-12-1/
I will appreciate if someone can look at it and confirm it is a bug or tell me that I should rather avoid hot-fixing things in that way ;).
Best regards,
Szymon
Barry, the developer, asked me to log a bug for this. Your fix is just fine, I think.
Hi Jeff,
Reposting this in case you missed it after I commented on the wrong message.
When I make an error in a SQL script I do not get line and column number returned in the error message anymore. It just says
ORA-00936: Ontbrekende uitdrukking.
00936. 00000 – “missing expression”
*Cause:
*Action:
Any idea how to reenable the “Error at Line: 99 Column: 9” message after Action:
I’ve tried several SQL Developer versions (also 19.2).
I’ve enabled the OCI instant client.
We’re printing everything that comes back from the db for error messages.
I just tried
And got back
Hello Jeff,
We are using ORDS to create services for an Angular frontend on our database, which currently has a Forms interface.
Working with ORDS is a pleasure, but I would like to make a suggestion for an addition.
We have a template for services that are defined as pl/sql blocks and that works fine.
We can detect the user who is logged on (if any), the name of the service and all of the url parameters.
Using these parameters we have added some logic that uses sets of data to overrule the database values for some configuration tables.
We also use these parameters to conditionally turn on our instrumentation, and log the results to a database table.
But for a json/collection and a json/item service we cannot use this functionality to the full extent.
What I would like to have is the option to specify a pl/sql block before and ideally also after (for the instrumentation) any kind of service, especially the json/collection and json/item kind of services.
These block should run in the same transaction that the handler does, just like the GET to check the eTag runs in the same transaction as a PUT.
So that I could specify a handler somewhat like this:
ORDS.DEFINE_HANDLER(
p_module_name => ‘test’,
p_pattern => ‘test/:url_par/test’,
p_method => ‘GET’,
p_source_type => ‘json/collection’,
p_items_per_page => 25,
p_mimes_allowed => ”,
p_comments => NULL,
p_source => ‘select dummy
from dual
where a = :url_par
and (:search_id is null or b = :search_id)’,
p_code_before => ‘begin
test_pkg.init_handler
(p_module => ”test”
,p_pattern => ”test/”
,p_method => ”GET”
,p_q_par => :q
,p_par_name_1 => ”url_par”
,p_par_value_1 => :url_par
,p_par_name_2 => ”search_id”
,p_par_value_2 => :search_id
);
end;’
p_code_after => ‘begin
test_pkg.close_handler
(p_status => :ords_http_status
,p_json => :ords_json_body
);
end;’
);
ORDS.DEFINE_PARAMETER(
p_module_name => ‘test’,
p_pattern => ‘test/:url_par/test’,
p_method => ‘GET’,
p_name => ‘search_id’,
p_bind_variable_name => ‘search_id’,
p_source_type => ‘URI’,
p_param_type => ‘STRING’,
p_access_method => ‘IN’,
p_comments => NULL);
As stated above, working with ORDS is a pleasure. Please feel free to do whatever you like (or nothing at all) with this suggestion.
Kind regards,
Jeroen
It sounds like you’re describing our existing prehook functionality – have you explored this? Here’s a nice 3rd party writeup of what you can do with it.
Hello Jeff,
I checked https://www.jmjcloud.com/blog/using-the-ords-prehook-function and the Oracle documentation and this certainly helps.
Thank you very much for your swift reply.
Kind regards,
Jeroen
Hi Jeff,
I am new to create extensions for sqldeveloper, and I am not clear about how we can create – and examples are very confusing/mixed with jdeveloper extensions examples. Can you give me some steps to the first example of extension for sqldeveloper?
What are you wanting to build?
our developers and analysts want to be able to see the code package header AND body in SQL Developer (as well as procedures and functions) in SQL Developer. Our internal audit is questioning why we granted select_catalog_role and want us to come up with the minimal required privileges that would still allow them to see the code, bt noy have that select_catalog_role (and certainly not execute). IS there a way to do this? what are the minimal privileges that must be granted to have SQL Developer work ?
See this.
Hi Jeff,
it is possible to integrate Visual SVN Support into SQL Developer / Datamodeller?
See: https://www.visualsvn.com/support/topic/00145/
Regards
R.
If I understand your question correctly, no. But you could use that client to talk to a local SVN you setup with SQL Developer.