The Late Show with David Letterman used to have a bit called, ‘Stupid Pet Tricks.’ I quit watching because I prefer his Scottish and funnier future-replacement, Craig Ferguson. But I will take advantage of some Google-Fu to drive some traffic to ye olde blog (can you tell I’m in the UK this week?)
So most folks GET copy and paste in Windows and Mac. Ctrl or Apple + C for copy, and + V for paste.
In SQL Developer if I do this in a grid, I can get my data out onto the clipboard.
Most folks are OK with this, and here’s what it looks like:
Copying Data Out of a Grid WITHOUT Column Headers
Everyone gets this.
What folks need a little help figuring out is how to do the same operation BUT
WITH Column Headers
The magical mystery keystroke sequence is
Ctrl + Shift + C
That’s it.
This works on a single cell or row selection as well.
[text]
ENAME
ALLEN
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7566 JONES MANAGER 7839 02-APR-81 12.00.00 2975 20
[/text]
For what it’s worth, my dog, Bart the pug, only does one trick. He goes crazy if you don’t give him a doggie-treat when leaving or entering the house. What an awesome dog.
45 Comments
Thank you, Thank you, Thank you for putting this out there on the interwebs to find! I have been using developer for several months and every time I copy and paste I have to go back and hand type all of the column headers in. The export seems to have a limit on how many rows it will export, but the copy paste gets everything.
This is going to save me so much heart ache and time!
Hey Nigel, sorry to hear you’re having problems with exports – there shouldn’t be any limit.
How many rows and what export file type are you working with? If Excel make sure you’re using XLSX and not XLS export option.
I have a consistent problem with various versions of SQL Developer and Copy/Paste.
I almost always get the PREVIOUS copy when I paste to my code window. Doesn’t matter the source, could be text, sql server, another SQL Dev window. I frequently (always?) have to ctl-v paste, then ctl-a and delete, then ctl-v paste again.
I’m on an up-to-date Windows 7 laptop but I usually use a virtual PC that is closer to my databases (on another continent) and these may not be so up-to-date but they are on Windows 7.
Any ideas? Thanks.
what version are you on? I’m using Win7 and haven’t seen this issue on 4.1.3 or 4.2 Early Adopter.
I saw this on the default version installed on my first Oracle based workstation, it was in the 3’s. Lately I’ve been on 4.0.3.16 and still see the problem, even on a different Virtual Workstation run by a different outsourcer.
AND, I am evaluating a copy of 4.1.5, so I just fired that up and it the first Paste worked properly but the second Paste just repeated the first, even though the text was different! So I did it a third time: I copied your answer above from the website, and pasted into the Query Worksheet, and got the results from Copy #2.
Hope this all makes sense. It is basically lagging one clipboard entry behind. Two versions, two different Win 7 machines. No other apps show this symptom. Making me crazy!
Thanks for the response.
weird…I’ll play with copy and paste tomorrow to see if i can make sense of what you’re seeing
what does edit > extended paste show – anything amiss?
Extended Paste works fine, and after displaying Extended Paste I can either use it or a regular ctl-V and it also pastes. AND subsequent Pastes seem OK AFTER displaying Extended Paste! EP seems to fix the issue for me. But if I do NOT use Extended Paste, I always get the previous copy pasted.
I am going to install on my local Fat Client and experiment. It could be the RDP link is messing with me.
FYI the virtual servers are with several outsourcing companies in France, so we are going
1. California Laptop to
2. VMware in France via LAN/SSL to
3. Virtual Desktop with RDP in France.
Lots of moving parts. If you don’t have any great ideas then it’s OK to close this thread, but maybe see if anybody else reports it in the future.
As I said above, SQL Dev is the only app that does this. I also use SQL Server Management Studio regularly, and even SQL Server Query Analyzer for an old almost-dead ERP. Never a problem with Paste… but I can live with it.
Maybe I’ll start using Extended Paste, it’s new to me. Thanks for that tip!
Keep up the great work. With the improvements to SQL Dev, I almost don’t mind that they locked me out of Oracle Enterprise Manager a few months ago!
Steve in FLYOVER, CA.
In SQL Developer, The “Messages – Log” window used to be anchored on the bottom of my screen and I don’t know what I did it went floating. did a right click anchor and it now is anchored on the left side of my screen taking a good chunk of real estate and its annoying. and I can’t figure out how to anchor it back on the bottom of the screen. Is there a trick of getting it back anchored on the bottom?
Thanks and Regards
Drag it back where you want it, reset your desktop, or call me on Monday.
After my Export to Clipboard suddenly (after company updated Oracle DB) started taking 10x longer than my query (with all rows selected) , I found this blog and solution…love it!
You’re a credit to the profession, Jeff.
I just started using 4.0.3.16 and for the life of me I can not figure how to export the query results to Excel. It was easy to do this in previous versions. Tool>Database Export seems promising but it requires a “Connection” but the drop-down is empty. Besides, I just ran a query so I’m connected to the database. I’m not sure why/what this “Connection” is when attempting to export query results.
just right-mouse on the grid where you see your query output, export > XLSX (much faster/better than XLS in SQLDev when exporting)
Is there a Mac equivalent here? Adding Shift to Command + C make it “Copy Path” — not Copy with Column Headers.
I came here looking for a Mac equivalent too. It’s amazing how different the tools are between OSes.
In order to do it on a Mac, you will do the same thing. Control+Shift+C, to paste with headers you do Command+V.
Thank you so much! This really helps.
Any time 🙂
Thanks. Exactly what I was looking for. Appreciate it!
I have an issue that just suddenly popped up: When I try to copy from SQL Developer into Excel, or anything else for that matter, it doesn’t separate the columns by spaces or any other delimiter. Therefore, Excel simply puts the whole row into one cell. Since there is no delimiter, I can automatically separate the fields… This just started happing… What gives?
I dunno, all I can say is that it works for me…select * from scott.emp, select all, copy, paste into Excel.
Can you paste the data into an editor what has a HEX mode? Then you can see what’s actually between the values to get an idea of what’s going wrong.
Wow, thanks for the fast reply… I did paste it into Notepad++, and I see regular old tabs between the data! I have never seen Excel just igore tabs… Tried changing the format of the cells to text, same issue. I tried every permutation of copying from one program to another… If I copy from Excel (where I see no tabs) into Notepad++, the tabs are still there… Something is causing the tabs to be ignored in Excel…
I know it’s a bit late, but maybe someone else encounters this, too.
This behaviour depends on the text-to-columns settings.
If I disable tabs there and choose a different delimiter then tabs will be ignored from then on during *every* copy&paste action – until I change it back.
Looks like Excel needed to be reset… Wow what a pecuiliar problem. All is well again…. Hopefully this will help someone else.
it did. thanks!
And 1 year later it just helped me as well. I was happily using my ctrl shift C everyday and then all of a sudden it stopped pasting parsed. This fixed my issue thank you very much.
Thanks for this, I had it on my mind that there must be a way of doing this copy easily, rather than export
You’re very welcome!
Wow! That is way toooo cool!!!!
Hi Jeff,
As always thanks for very informative blog post.
Just sharing my personal experience using the feature.
I found that 9 time out of 10 when I make “Ctrl + Shift + C” for one of several columns I am adding them to where cause to limit select further down.
Just an idea for an enhancement request. Introduce an additional hot key to copy HIGHLIGHTED COLUMN1=’FIRST ROW VALUE’ AND HIGHLIGHTED COLUMN2=’FIRST ROW VALUE’ to clipboard. This will make my life easier.
Lazy DBA, Yury.
Dude…I love it! Let me see what we can do 🙂
I like this idea. Can you add it to our exchange on sqldeveloper.oracle.com? Don’t forget to get your friends to vote it up for you 🙂
Done:
https://apex.oracle.com/pls/apex/f?p=43135:7:13571988633788::NO:RP,7:P7_ID:36121
I’m really glad to keep following all these amazing tips, all of them have become handy. Even I already conviced some of my colleagues to uninstall “other vendors” IDEs and keep using SQL Developer for everything. Seriously, the Ctrl + Shift + C made my day.
Keep up this cool website.
@dresendi
And you comment just made my day! Thanks for sharing Diego!
I agree Diego. I had to have my work laptop reimaged 3 weeks ago due to some OS issues. I took the opportunity to leave Toad in the dust. I am enjoying the innovative features that make my life easier. The screens I miss from Toad were mostly informational and since I already had queries to get a lot of this info I am filling in any gaps with my own User Defined Reports. Also have a long list of things that just make life easier (easy install, lots of config options, quick data modeling).
Any reports in particular you think we should build to help fellow users like yourself make the transition David? You can also look fwd to better reports and more reporting options as SQL Developer updates are made available.
I am slowly converting my jumbled home directory of helpful SQL queries and scripts into UD reports as I come across situations where I need to use them.
The current incarnation of my UD report collection is available here: https://github.com/dmann99/SQLDevUDRepPack/zipball/master
I probably need to spend some more time getting familiar with the DBA Navigator so I don’t duplicate any operational functionality. But I am loving the UD reports for my research tasks – click the reports I have set up, get results quickly and nicely formatted so I can drill down and find issues. I usually have OEM open at the same time… When I don’t get the info I need from OEM I jump to my custom queries in SQL Developer.
I’ll be posting some visualizations I am working on in SQL Developer HTML reports on my blog soon for a table statistics ‘heat map’. I am doing as best I can with PLSQL/DBMS_OUTPUT/HTML – what renders that HTML anyway? Curious about the capabilities…
You can also share reports up on our SQL Developer Exchange
Here’s an example of a PL/SQL-HTML report I put together for Martin
http://www.thatjeffsmith.com/archive/2012/04/substitution-variables-html-reports-in-sql-developer/
We pick up the serveroutput (DBMS_OUTPUT) and render the HTML inside the report. No need to setup HTP.P or anything like that.
thanks for your awesome blog, Jeff!
it is great we can copy data with a header but let me ask you – why do I need to google to find this out? 🙂 would it be more user friendly to see that option on the right mouse click on a data grid? but the good thing i found your blog thanks to that, may be it was your intention?? 🙂
The goal is to be as intuitive as possible, not to hide things.
You can use the export feature to send the data to the clipboard in the format you want with column headers.
Adding a context menu might not be the ideal solution here, but it’s an idea worth considering.
Thanks for sharing your feedback!
Export/clipboard worked wonderfully – somehow I did not see clipboard option when I looked there.
P.S. I am new to Oracle and my company uses PL/SQL Developer which I hated from the first time I used it and I am blown away by totally free SQL Developer. I showed it to my boss today and he was very impressed as well. Please keep up the great work!
0833 is a string, If you open Excel and check the formatting of that column, is it set to Number? If so, change it back to a string and I’m guessing the leading 0 will come back.
Sweet, will check out the exchange and probably package up and contribute if I am worthy heh.
I found an old HTML report I threw together 3.5 years ago … was wondering more about the capabilities. I haven’t tried any CSS or Javascript inside my HTML reports – is it possible to venture into that territory or should I just stick to Web1.0 style simple tags…
http://ba6.us/CodeExamples/SQLD-LongOps/SQLD-Longops-ReportRunning.png
-Dave
Our docs only mention ‘HTML’ so I’m assuming CSS and Javascript are non-starters, today. Tomorrow may bring more exciting possibilities 🙂
SQL Developer strip down 0 if its in front of figure, which caused problem b/w me and one reporter
eg original value
emp_id
0833
when i export it via sqldeveloper export to cvs/xl or just copy/paste it strip down 0 and give resutl
833