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,793 Comments
Hi Jeff,
Thanks you for all the helpful hints in your blog and showing all the useful features that are hidden in sqldeveloper.
What I really would like to use is the possibility of “stock” charts in a user defined report, but I did not manage it to work. Also searching with google, the sqldeveloper help and reading your blog posts about user defined reports and charts did not help.
It would be really great if you could provide an example for how to create a working “Stock – Open-Hi-Lo-Close With Volume” Report!
Thanks,
Daniel
Hello Jeff
Going back to SQL Developer after a long time..You have Shift+F4 for DESC, why not add the column “data” along with the pop-up (as in Toad)? Plus, right click “Open Description” only shows “data” column for Tables/Views and for a Synonym one should check the Base_Object…Can’t we make it easier?
Ctrl-Click into the object instead.
There should be a link from the syn to the base object.
Hello Jeff,
First of all, I really enjoy your posts! Thanks!!
I’m looking for some documentation about “sdcli cart export” command, because i’ve been using it to export some data to CSV and also text output formats and workd perfect.
But now I’d like to export other data to excel format and the “sdcli cart export” using a “export_tool.xml” in xlsx output does not write the file. It DOES create the file in filesystem meanwhile the export is in execution but once it has to write the data on the xlsx file, the file disapears and no error is shown even in -logfile file.
Is it possible to run sdcli export to excel formats? or only works in “normal text” formats such as csv, delimited, etc??
Thans in advance for your reply!
yeah, that should work – what version are you using?
sdcli cart help will show you examples and all the documentation on the commands
I’ve used a 17.4 and a 18.3. In 2 different workstations on Windows 7 and on windows 10. And if use the same cart file but I change the export_tool.xml to flat file, it works. Output formats that doesn’t work are the Excel 2003+ (xlsx), old excel (xls) and Excel XML formats.
All the others (even xml “standard”) seem to export well.
I’ve already read the help examples, none is showing Excel related formats.
Any ideas?
PD: in sql developer the same cart and export_tool files are generating the excel file. But I’d need to do it with sdcli in the middle of a script .
Hi Jeff
I have oracle database 9i
Can I use apex 18.2 ..to develop app …I already install apex 18.2 in separate dB 11g
Can I user ords with oracle 9i
Thanks
The current jdbc drivers won’t go back that far, and we’ve never tested 9i. So if it works, it would be a miracle.
Hey, Jeff
With all the current confusion around java, can we still install the latest versions of SQL Developer with a JDK and everything is still free and will there be security concerns over an “embedded” JDK release that ages?
Since we’re still with Java 8, you’re good.
We won’t let the embedded jdk age so much that you’ll have security concerns.
Do you see a need to only use SQL Developer with an open source JDK, e.g., (AdoptOpenJDK 8)?
Not yet.
So, it it true that if I have downloaded the latest SQL Developer with the JDK, I’ll need a license for Java 8 “…team explains that Oracle Java 8 will need to be licensed post January 2019 and no versions of Oracle Java OpenJDK will have the required support”?
So, it it true that if I have downloaded the latest SQL Developer with the JDK, I’ll need a license for Java 8
— you’re covered, as we embed a jdk that’s free for use as of today
“…team explains that Oracle Java 8 will need to be licensed post January 2019″
— if you grab a NEW jdk 8 update starting in Feb I think, THAT will require a commercial license. What you have now, what we will distribute as of 18.4, will not
“and no versions of Oracle Java OpenJDK will have the required support”
— once we get to Java 11, we’ll support OpenJDK. And in fact, OpenJDK11 is a direct port of the Oracle Java distribution. There’s in effect, no difference.
So what will be the consequence in 2019 if somebody dislike licencing and pay for Java JDK. Not to download and use alternative products?
The openJDK solution is free and will be supported by us. So, not sure this is an issue.
Hi,
I downloaded SQL Developer 18.3 and started using it. In the Data Modeler I found this great feature that DDL Preview is now in the context menu. But when I opened the DDL Preview for a table (physical model is open) the window does not show the code for the triggers. The behaviour is the same in the standalone Data Modeler. Up to at least SQL Developer 18.1 this worked.
Is there a preference with which I can switch the generation of the triggers on again in DDL Preview?
Thanks for your help
How do i take backup of REST API? Does it come as a part of EXPDP/IMPDP or something else needs to be done.
REST Services aren’t top level schema objects, so a data pump wouldn’t include them…unless you data pumped out the ORDS_METADATA user.
But that’s not what you’re really looking for.
So I wrote this for you – let me know if you have any follow-up questions here.
I just upgraded to SQL Developer 18.3 x64 on my Windows machine, hoping that Code Completion Insight would be improved. Code autocomplete in SQL Developer has always been hit-and-miss for me. I’ve been using SQL Developer for about five years now, and I appreciate all your work on it.
I’ve been experimenting with reducing the time for code completion pop-ups, down to 0.2 or 0.3 seconds. I have Code Completion turned on both for worksheets and PL/SQL. I have suppress pop-ups for over 20 matches set. It’s still hit-and-miss. Especially down at the 0.1 seconds. It works better at 0.4 seconds and above, but I wish it would pop-up more quickly and reliably, like the other SQL IDEs I work in. Many of them have code completion similar to how Google auto-suggests things when we’re doing an internet search.
Code completion and autocomplete may not seem like a major issue, but for those of us who spend hours each day working in these environments, having effective code completion is a huge time saver.
I know I can always invoke code completion with Ctrl-Space, but that’s just more keystrokes that I wish I didn’t have to make.
If I might make an additional suggestion, code completion and autocomplete can be even more powerful when we can choose to *autoselect* the first matching entry, and all I have to do is press enter or space to have it put in to my code, rather than having to arrow down to select something from the code completion pop-up/drop down.
These are all first-world problems, but if you’re looking for some feedback that might make SQL Developer even better, I offer this is that spirit.
Thanks for all you do.
I’m thinking you’re suffering from network or DB latency.
If you open the View – Log panel and observe the Statements page, you can see us fire off the SQL that satisfies your Insight request.
How long are those taking to execute vs your delay setting.
Full disclosure, I run with auto disabled and I have to ask for help more than once at times as those queries can take awhile and the insight feature will time itself out so as not to interrupt your editor respsonsiveness.
Hi Jeff,
I am not able to run this statement in the Oracle APEX 18.1 , in page designer, but working perfectly in the SQL commands window –
select distinct instance_number D , instance_number r
from SYS.dba_hist_snapshot
order by 1;
It throws the error “table or view does not exist”
Would appreciate your help on how to approach this issue.
Thanks,
Rads
Is a select sql statement available which can report a list of objects and their corresponding types which are referenced in a specific sql statement of interest?
Thank you.
Corresponding types…you mean column data types?
Hi Jeff,
Object types – as in general, a sql statement could reference views, functions, sequences besides tables. Would also like to know if that specific sql statement is referenced in some stored procedure or package.
Thank you.
pl scope handles the 2nd part of your request
Still not clear what you’re asking for, but sounds like a data model based on the structures involved in a query.
If a schema object is referenced in a sql statement and that object is later modified in any way, that statement in the Shared SQL Area is marked invalid. So a track is kept for all objects referenced in a sql statement – can that object list also be obtained via some sql?
Thank you.
The AskTom team tells me
‘You can get a reasonable approximation by querying v$sql_plan, eg
select distinct object_owner, object_name from v$sql_plan where sql_id = ‘….'”
hi Jeff
how do I create a master-detail report?
like toad has an option where you choose a master table and then a child table.
is there something in the pipeline for such functionality?
it would be great if it is.
regards
You just use our reports feature. Create a user defined report, you can have as many children as you want. And if you want grandchildren, you can do that too.
SSH Hosts
Hi Jeff, is there a way to export and import SSH hosts connections on SQL Developer?
Should be nice to have this feature on next release.
Hi Jeff.
There is some way to “compress” the json that returns ords (I mean, without indentation or line breaks).
When reviewing in postman the response headers ‘Transfer-Encoding’ is chunked.
PD: I build the json from plsql with the apex_json package.
Thank you.
Ords doesn’t pretty print/output json responses..that’s APEX doing that
We auto take your query results and jsonify it for you – have you tried that?
Hi Jeff, thanks for the reply.
What I do is build the json manually with the apex_json package. I mean,
apex_json.open_object;
apex_json.write (‘a’, ‘value’);
apex_json.close_object;
Then from sqldeveloper I create the Modules, URI Templates and Resource Handlers.
Right..I think your question is for the Apex team.
How do you get the list of tables you have access to to show up in the Tables object under each database? I click on the PLUS sign next to Tables to expand but there is nothing showing.
See this
What if there is nothing under SYNONYMS?
Then your schema doesn’t own anything – you need to go to the Other Users section of the tree and dig around.
or use the object search
or manually query all_objects where object_type = ‘TABLE’
Hi Jeff,
I was executing a Stored procedure in Oracle SQL developer. I enabled the DBSM output to monitor the logs in my PLSQL script. It took around 2.5 hours to complete running the script and meanwhile the SQL developer window got frozen. From another session i could run some sql scripts and made sure that the execution of the stored procedure is over(this is an archiving process where a bulk set of data get moved to archive tables and gets removed from the original tables so by observing the original tables i can decide whether the SP is completed running or not). The problem is since my original SQL develoepr window where i ran the SP having DBMS output on freezed i want to know is there a way that i can view them from another session. Will it be saved in something like a log file.
Thank you
Is the main query still busy? can you open a worksheet and run a query on the same connection you executed the stored procedure?
Most folks will write messages to a logging table as access to dbms_output messages is generally not ideal for real world scenarios, although quite useful in dev/test
Hi Jeff,
The company I work for there are some developers that still use SQL Developer 3.2 (too old I know). Here is the point, when I finally have got them to SQL Developer 18.3.0 we learned that the export connection file from SQL Dev 3.2 isn’t recognized by SQL Dev 18.3 import connection tool. When I tried to import the connection file, SQL Dev 18.3 says: No connections are defined in C:\TEMP\CONNECTION_SQL_DEV32.xml
Jeff, is there a way to import the exported connection file from SQL Dev 3.2? If not, is there a workaround to overcome this issue?
Thank you in advance for your help!
Regards,
Anderson
yeah, that’s a bug in 18.3, so you can have them go to 18.1 or 18.2, move the connections over, and then run 18.3
hey, nice work getting them upgraded!
Hi Jeff,
I did some changes to custom format in V18.3. After applying changes format does not work and i cannot load format window in preferences tab. How do i reset my changes. ?
Can you share what the changes were? Then we can file a bug and make sure this same change doesn’t break others’ formatting rules.
You might need to delete the format.arbori file in your AppData SQL Developer system18.3 directory.
Hi Jeff,
A question regarding the formatter.
I am trying to get the first (named) paramater in a function or procedure call to align with the rest of them, but I cannot seem to get that working.
e.g.:
—
begin
do_something (
p_param1 => ‘value1’
,p_param2 => ‘val2’
,p_param3 => ‘val3’
);
end;
—
where I would like it to be:
—
begin
do_something (
p_param1 => ‘value1’
,p_param2 => ‘val2’
,p_param3 => ‘val3’
);
end;
Can you help? Thanks in advance
This would not be a ‘problem’ if you went for line breaks AFTER a comma, and not BEFORE.
You are right about that, but commenting out a line is a lot easier with a comma BEFORE.
Hey Jeff,
I have another one for you. I REST enabled a table that has sysdate set up to fill the column with a default value and it’s set to not null. I get an error when making a POST call. Any ideas what’s up?
Thanks!,
Wayne
Hi Jeff,
I have an APEX app, ORDS and Glassfish. I need to access this app through an Apache Web Server on DMZ using Reverse Proxy. I couldn’t find any significant documentation so could you help me with some tips?
Thanks.
Diego.
90% of that will happen in glassfish and Apache. Then you just tell ords to a certain the user info on the header…see our slides on SlideShare
Hi Jeff,
If I’m running on 11g using ORDS. How would you suggest I post a record to a master table and return the sequence created PK_ID to be used as the FK in the detail table POST?
Wayne
Here’s one way.
Schweet!
Many thanks Sir!
Hi Jeff,
Will the new Oracle Java licensing cost policy impact those of us using SQLDev? Specifically if one of our users adhoc updated the version of Java shipped with SQLDev+Java download bundle.
Tim…
When we go to Java 10 or 11 we’ll probably switch to OpenJDK.
Hello again,
Thanks for the help last time, we got the SYS password and everything worked well and I’m testing out ORDS and its working great, One Question I have I can’t seem to find the answer to online,
In I GET Collection Query there are “Links” for next, prev and so on, but I would love to have a “$last” but that is not by default, is there any way to add a link to the “Base” links?
I know how to add $link to each item in the items resaults, but I want to the links of the base json object like this image shows https://imgur.com/a/kDwRaPa
The problem is that we do not know how many records are in the result set – we read them one page at a time, plus one record. If the +1 record is not there, we know we’re at the end.
So there is no way for me to do that manualy in my GET handler?
Add a “$last” under “links”?
it would be easy to get the offset number for the last page by Floor(rowCount() / :offset) * :offset
Right, you’d just use sql to count the number of rows and then join/union that to your main query and add a link to the end that does the page with the proper offset
It can take as long to do a rowcount as it does to run a normal select, so you’d be potentially doubling the amount of time on the call.
I take it back, I dont’ think you can do this..can’t think of a way to do this in SQL, you’d need to use PL/SQL and there’s no paging for PL/SQL, only SQL
Hi Jeff:
We user SQL developer to export DDL from the and save the output in separate directories. We have been noticing that it only creates directires for database links, sequences, tables and types for a particular user that has DBA role.
but it works for other userids without DBA role. Is there anyway to debug this issue ?
Thanks
I would start by trying to get the ddl for the individual object, by opening it and looking at the SQL page.
Thanks Jeff for the response. All the individual object types open without any issues in sql developer and I can see the DDL. Is there any way to trace the export queries that SQL developer runs ( in debug mode) and find out what is running and what is failing that is causing not all types to be exported in their own individual directories.
Thanks
So looks closer to a bug on our side now…
no need to do a trace, you can see all the SQL/plsql we submit on the View – Log panel. There’s should be a page there that says ‘Statements’ – look at that.
Hi Jeff,
many thanks for your very informative posts and replies!
Need your help regarding using Oracle rest data services 18.3 with Transport Layer Security (TLS) – is there a manual or just an example how to set up them to work together?
You can follow the directions in the install guide regarding standalone and SSL, paraphrasing here:
…After doing this, you can include the path to yourdomain.der when prompted by Oracle REST Data Services, or you can modify the following entries in conf/ords/standalone/standalone.properties:
ssl.cert=
ssl.cert.key=
ssl.host=yourdomain
Also, ensure that jetty.secure.port is set.
Otherwise if you’re using WLS or Tomcat, that happens at that level, and you just tell ords NOT to accept requests on http, only https