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!
6,410 Comments
I have used the UNLOAD command to save a CSV file for a database table and it works great. I like it a lot better than select field1||’,’||field2||’.’||field3 string from table_to_export;
If the table is partitioned (in 4 or 8 partitions), would it be possible to have each partition save to a CSV file at the same time? I was not sure if this functionality already exists or if this could be an enhancement that would be helpful to others as well.
I would like to use this functionality to create delimited files that can be used by other systems. Some of these are created daily, some weekly, etc. Some are complete dumps of large tables that happen daily, and I believe if there was a mechanism to export partitions simultaneously, it could perform well.
One important detail I forgot to post is that I am using SQLcl
create delimited files that can be used by other systems.
Like…? As you can imagine, this is a really expensive way to share data in a database with other systems.
Yes, I understand that. The large tables are currently handled by Pro*C code. It works well and is able to get the large tables to flat files within 15 minutes (using four sessions/threads for each table). The small flat files are created with UTL_FILE pl/sql package. Just like math is the “universal language”, I feel that flat files are the universal language of IT. Every company and system can output and input flat-files. You do not need to give outside people access to your system. The reason for my question is that I am hoping that the future could provide us with a solution more elegant and maintainable. I believe that SQLcl could be part of that solution. The other possibility would be if external tables could be written to and turned into flat-files. It is my understanding that a binary dump file can be created, but not a flat-file with external tables. Either SQLcl or external tables seem to be the most elegant solutions in the future, but currently…. neither has the feature to support this.
No, I’m wondering what piece in your IT system needs to consume the data in said flat files. Because you could also look at REST APIs. Pretty much any library/language can handle HTTP+JSON…and no expensive IO writing out all that data, and worrying about how stale it is.
20.4.1 Is it possible to disable ‘Query Builder’? Every time I accidently click on it (the tab next to “Worksheet” it locks up SQL Developer for 1min +. I never use it.
SQLDeveloepr Version 20.4.1.407
we tried to migrate the SQLserver database and in the dbo.tables it has 44 tables.
Migration returned SUCCESSFUL but when we log on to the Oracle 11g database we notice that under the Migrated Schema created, we have 30 tables instead of 44 tables.
checking the MIGRATION logs we see in the target status 30 tables and 14 UNKNOWN
please advise how to fix this issue
I can’t, not without a lot more information.
I recommend opening a service request with My Oracle Support.
Can you connect using secure external password store with sqlcl?
I’ve tried to noavail sql.exe /@someentryinstore
Thank you…
You see the Vault command?
SQL Dev v21.2
What do I have to do to open a Job? Double click does nothing
Regards
Did you try right-click, open?
I’m able to open a Scheduler Job in 21.2. If that still isn’t’ working, do you see any messages in the Log panel?
(sorry for the double post, first time was within someone else’s thread)
Hi Jeff, long time listener first time caller. We use Oracle SQL Developer daily as contractors within one of the military branches. We’re currently authorized to use a previous version and I have asked to get the latest version (20.x) reviewed and put on the “approved” list. InfoSec came back and said “Oracle SQL developer has these vulnerabilities and the vendor will need to provide mitigations”. They also said they will need an Open Software Support Letter from the vendor which is a generic “we promise to maintain our code well” thing.
How to I engage with Oracle on something like this where Oracle SQL developer isn’t covered under a support contract? Is opening an SR the best path?
Email me at [email protected]
We strenuously maintain lists of known security vulnerabilities and update 3rd party libraries as necessary. It’s like…a BIG DEAL here.
Hi Jeff,
a question about autoident code when pasting code into the editor.
When I have a piece of code like
select *
from foo ff
;
and I want to replace foo with an ad-hoc view like
select x, y, z
from bar
I would copy the code of the ad-hoc view, open parenthesis in the main query and would past in the clipboard content where the x is
select *
from (
x — past clipboard here
)
;
But the result is:
select *
from (
select x,y,z
from bar
)
;
But it actually should be like this
select *
from (
select x,y,z
from bar
)
;
This is what I mean with autoident. There are editors out there (textmate and others) that prefix code with the necessary space characters, so the relative formating to the first line is preserved.
Is there such a feature in sql developer? I couldn’t find it (neither in sql developer, not via Google search). In other editors it’s called something like `auto-ident on paste`.
Now I have always to do paste, select the pasted content except the first line and hit tab, tab, tab (as many as are needed) and that kind of sucks 😀
I was doing that for the last 7 years, but enough is enough 😀
Thanks!
PS:
Yes, I could do Paste, then select everything and autoformat, but I couldn’t get the autoformatter to do exactly what I want it to do.
So all the spaces got removed in my post. And I miss-spelled indent. Sorry for that.
Here’s a screenshot of what I meant: https://drive.google.com/file/d/1RTP9IjIBdA8VkSb6zfHVu0lBRT389cqk/view?usp=sharing
thanks!
There’s a code editor preference that LOOKS like it should do what you’re describing…but enabled/disabled, I don’t see it fixing anything.
Adjust Indentation When Pasting: Corrects the indentation of a pasted in item that includes indentation.
I’d be happy to file a bug for you.
I found that option now. I have the German Translation, so didn’t see it earlier. But yes, it doesn’t do anything.
I’d be happy if you would file a bug for me 😀
Thank you very much!
For documentation purposes: Here’s the Option in the German translation
Beim Einfügen automatisch Einzug anpassen
Hi Jeff, long time listener first time caller. We use Oracle SQL Developer daily as contractors within one of the military branches. We’re currently authorized to use a previous version and I have asked to get the latest version (20.x) reviewed and put on the “approved” list. InfoSec came back and said “Oracle SQL developer has these vulnerabilities and the vendor will need to provide mitigations”. They also said they will need an Open Software Support Letter from the vendor which is a generic “we promise to maintain our code well” thing.
How to I engage with Oracle on something like this where Oracle SQL developer isn’t covered under a support contract? Is opening an SR the best path?
Totally OT “dabase”, but ..
on this website the “share-icons” (RSS, Twitter, ..) are always moving to the left side, middle height of the screen – and disturbes the reading of the text. Tested with Win 10, Edge and Firefox, on two PCs, both on 19″ monitor.
Only my problem?
No, I need to fix that. Thanks for the reminder!
BTW, another 3rd-prio cosmetic issue:
The website says I wrote my posting “5 hours ago”. That’s wrong – it’s approx. 1 hour. maybe is has problem with the time-zones (I’m writing from Central Europe Summer Time, now it is 11:30 a.m.)
Don’t think I’ll chase down this bug 🙂
I’m in SQL Developer (version 20.4.0.379) and have a worksheet open to a development database. I execute a query of ‘select * from sys.dba_users where username = ‘DL23′ (time is 11:20 AM) and get a row back in Query Results. Note: I am logged in as user DL23.
Then I go to Tools -> Real Time SQL Monitor… and the Real Time SQL Monitor worksheet opens. Under User I do not see reference to DL23. I scroll to the right to Start Time. The top two entries are ’08-JUL-2021 11:25:00′ and ’08-JUL-2021 11:00:02’.
Neither are my query.
Also Auto Refresh is set to ‘5.0 s’, but nothing appears to be refreshing. When I click the button to the right of the Auto Refresh value nothing happens.
So as I was typing this it appears that it did refresh and there are two new queries at the top of the worksheet. Both where by user GTSIS… which isn’t DL23.
Shouldn’t I be able to see my own query in Real Time SQL Monitor?
Not all queries get monitoring reports. Use the monitoring hint to FORCE a report.
That works… for the query I entered in the worksheet. Which was an ‘example’.
Have a third-party application development instance that is logging an ‘ORA-00942: table or view does not exist’ for a user. What is isn’t logging is either the table name or SQL. So I thought I could use Real Time SQL Monitor to catch the statement. Not happening. And I can’t get into the third-party app set set the statements to include hints.
Is there a solution?
Yes, enable session trace to grab ALL OF THE SQL/PLSQL issued on the connection from your 3rd party app. Then browse the trc file(s).
Hello Jeff, thanks in advance for any help.
I don’t want to appear a bit arrogant, but this feature I expected to be implemented rather soon:
https://apex.oracle.com/pls/apex/f?p=43135:7::::RP,7:P7_ID:361
and nobody developed this in so many years ? Maybe are the few to request such things but…
Thank you.
I’m not sure sure how it got marked as ‘Accepted’ – maybe by my predecessor? I don’t have good news for you, it’s not on our list. There are good reasons for this, but I doubt you’re interested in hearing that it’s not coming.
Hi Jeff,
In Oracle SQL Developer Data Modeler v21.1.0.092, is there any way to display the alignment grid as default? At the moment, for each new sub-view that I create (over 400 and counting!), I have to right-click, select Show, and then select Grid.
I would like to be able to:
1) Enable grid display at the SDDM level for new designs.
2) Enable grid display at the Design level to show the grid on all sub-views, even if the grid was previously hidden.
3) Enable grid display for all new sub-views.
Hope this makes sense.
Thanks.
John.
Not that I know of, sounds like we need an application level preference under the Diagram section.
Issue: User is running a query that takes a long time, ie 30+ minutes to return the results. This example is the same for any query we runs. If I click the X to cancel the query in some cases it cancels immediately, SQL Developer does not freeze. If there is no activity bar while the query is running there is no way to cancel the query. At this point SQL Developer is completely frozen and has to be killed in Task Manager. The query will come back with results if we wait. Problem is SQL Developer cannot be used for anything else as it is frozen. Additionally SQL Developer will freeze when clicking the X to cancel and must be terminated in task manager. Running 20.4 and 20.4.1.
Run your long running queries in unshared worksheets.
Hi Jeff –
We sometimes use sql developer to connect to postgres databses with the third party odbc driver, as our developers are familiar with it from working with oracle, and prefer it to some postgres tools. However, we are unable to do things like run a script that inserts data with an ampersand.
Sql developer prompts for the value because that’s the default for an oracle substitution variable.
“set define off” only works for oracle.
I searched in preferences for “define”, “substitution”, “escape” and did not find anything relevant.
I think I understand why it always prompts, but should there not be a way to disable prompting for substitution variables on non-oracle connections?
We are currently on version 20.2, thanks.
set scan off…
Thanks, but that doesn’t work either, I assume because the command “set scan off” is being sent to the postgres engine, instead of just telling the client to not prompt for a value when it sees a &. “set scan off” is not a valid command on postgresql command and results in:
ERROR: syntax error at or near “off”
If you want a really nice experience in Oracle SQL Dev, work with an Oracle Database…
That’s a little snide. We do work with Oracle as I mentioned, and have some developers that prefer sql developer to the natively available postgres tools and wanted to use it for everything they can. You’ve seemed pretty helpful and a champion for sql developer across multiple sources like here, stackoverflow, reddit, or even random other areas. That attitude is a little unexpected.
It didn’t seem completely unreasonble to me for a client side(client being sql developer) change or setting to tell it to not prompt for a substitution variable but we’ll just continue to use other tools.
It’s realistic…helping you on postgres is not something I’m opposed to. But i can’t put $’s from Oracle into it. All of those are reserved for Oracle DB. Maybe if we ever offer postgres on Oracle Cloud, that story will change. I don’t want to promise you something I have no power to deliver.
Here’s someone asking the same question for SQL Server with a slightly different answer, but should work for you as well on psql.
I guess I think of it more like making sql developer the best product it can; you even have a blog post detailing how to use it to access other dbs with the drivers. Seemed like a little thing to keep people in the oracle ecosystem, not asking for full blown postgres support.
Thanks for the link, but as mentioned we tried both set define off and set scan off and neither worked.
The best tool we can, for Oracle.
Other connection types are provided for one use case only, for migrating those platforms TO Oracle.
I want to run a number of queries that read from different tables but have the same output structure, delivering a single row for each query. I export all the outputs to a single csv file, but it leaves a space between each result. Is there any way of telling it not to leave a blank line between each result?
Maybe, but you could run a batch script against the output file removing any blank lines…
You could try setting feedback off to see if that helps.
Hi Jeff and all,
I am using sqlcl to connect remote database via local tns. But it worked only via TCP port, unable to connect via TCPS port in SQLCL. Can you please help to connect via TCPS port ?
What happens when you try?
SQL Dev 20.4
Memory Settings 10G for both
When I start CPU = 0% Memory = 2.2G
Open Shared Connection CPU = 0% Memory = 2.8G
Open Unshared Connection CPU = 0% Memory = 2.87G
Open Report SQL Active Sessions CPU = 0% Memory = 3G
Open Real Time SQL Monitor CPU = 8-10% Memory = 5.2G and effectively frozen i.e cannot sort on Status or even select a row
Run a Query CPU = 8.5% Memory = 8.5G
How to get things not to freeze?
Also keep getting message ” .. not responding” which goes away and then repeats
When it freezes, grab a jstack, then we can see what’s really happening vs taking a guess.
JStack will be from F12 output?
Google jvm jstack, how to. I think I have a post here even somewhere…
Here is the link to my OneDrive for the JStack and SQLDev Environment
https://1drv.ms/u/s!AhoSxCZeaLHD0EKTy3jMmD2ZboBw?e=eLHoGL
Please post to the forums, I’ll be on vacation until next week…
Hi Jeff,
Couple of clarifications regarding AutoRest of a Table.
1) Can we restrict the columns that are part of the response ?
2) Do we have any parameter setting which would get the total rows count similar to SaaS API’s totalResults=true
Thanks
No and no…
You could make a view it build your own module.
In Oracle we don’t know the rowcount.. Not without doing as count()
“Did you ever try the code templates? You can pull them up by name…”
Yes I use that as well for frequent used script and nice feature for quickly insert the code if you remember the name. but it is for different purpose. By the way, code template doesn’t offer search feature as well.
I have hundreds of snippets saved in sqldeveloper for my system support work.
It would be so nice if I could search and find my snippet quickly and drag and drop to the sql editor.
Any chance add this to next release 🙂
Support for Tommy’s request.
I use partially external SQL files because of better search and find and edit, but I would prefer a better Snippet-tool
You can search your templates I’m pretty sure…
I can’t offer enhancements on the snippets but I’ll look into what’s possible for maybe 21.4 (December).
Hello Jeff,
We are facing an interesting issue and not able to find the root cause, it will be great if you could give any hint.
We have a table in ATP we are doing REST GET on that table based on a parameter say project_number it has 15k records for that we are using limit and offset to get 2000 at a time we are seeing that REST is returning duplicate data.. in some case some time. In the table we have confirmed we have 15k data.
Should we add a sort by in the GET call to avoid this?
It’s very possible rows move around the ‘window’ of records by page. Each request, we run the query, again – just for a different requested window.
So yes, if ordering is important, always include an ORDER BY.
records in database are unchanged. is there a guranteed way to pull records with paging, without duplicate?
hi Jeff,
Is there a chance we can have snippet search feature in sqldeveloper?
I have been asking for this feature a few times over the years now either in your blog or Oracle forum.
I really love sqldeveloper and save a lots of snippets in sqldeveloper over the years.
Now the trouble is finding them and edit them. It took quite some time to find the one I want in the list and click edit, then it brings up the list again and I have to go through the list again.
Did you ever try the code templates? You can pull them up by name…
Good morning Jeff,
I’m a novice and have SYSTEM ADMINISTRATOR access. I would like to know how to get to the source SQL that’s behind either an Oracle canned report or one a former colleague (who is no longer with the organization)?
What I would eventually like to do is two-fold: 1) copy an Oracle canned report and then customized it, and 2) when I’ve satisfactorily created either a customized SQL report or customized a canned report, then be able to make it available as a CONCURRENT REQUEST for end-users.
I appreciate your time and guidance.
Copy the canned report down to the User reports, then you can see the SQL. I show you how to do this here.
Hi Jeff,
I have a question regarding setting User Defined Properties in SDDM using scripting.
I tried below method but it sets Dynamic property-
table.setProperty(p_name,p_value)
Could you please help me with the method which sets User Defined Property?
Also, what is the difference between the two?
Thanks,
Neetesh Lodhi
how can i get total count for a select with ORDS?
select col1 ,col2 , count(*) over() total_rows from table1
do i need to use a stored proc? can’t seem to do it with a view.
thank you
what happens when you try, and you say view but your query says table1
Hi Jeff,
I am setting up APEX on one of our databases. Did this on a test database some time ago, which was version 11 base and it worked fine. Now we have upgraded the databases to 18.7 and this time I receive an insufficient privileges error when it tries to run “execute immediate ‘alter session set “_ORACLE_SCRIPT”=true’;”. I am using my superuser that is logged in as SYSDBA (not using SYS for security reasons). Wondering if there is an extra privilege that I need to give to my superuser so that this will work.
Thanks in advance.
APEX installs, better to ask the APEX folks/community.
Also, 18c is officially dropped from Support in like 2 weeks.
Hello Jeff,
I have a bug with the latest clSQL coming with SQL developer 20.4.1.407.0006 but it does not seem new.
The format file command does not work although putting file in buffer then formatting it works.
Apparently you already checked that issue, see link:
https://stackoverflow.com/questions/63568322/oracle-sqlcl-format-ignores-custom-rules-when-formatting-file
Thank you for your help,
Yes, it’s fixed in version 21.2 – available for download now.
SQLcl: Release 21.2 Production on Wed Jul 07 15:10:16 2021Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Wed Jul 07 2021 15:10:17 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> help format
FORMATHR ¦ orcl ¦ viins ¦ None ¦ No time
---------
FORMAT BUFFER - formats the script in the SQLcl Buffer - Loads formatter preferences file from SQL Developer export.
FORMAT RULES
FORMAT FILE
SQL> format file C:\Users\JDSMITH\Documents\bad.sql C:\Users\JDSMITH\Documents\good.sql
SQL> !type C:\Users\JDSMITH\Documents\good.sql
SELECT
cust_id,
AVG(amount_sold)
FROM
sh.sales
GROUP BY
cust_id
HAVING
AVG(amount_sold) > 1250
SQL>