Stupid Copy & Paste Tricks

thatjeffsmith SQL Developer 43 Comments

Tell Others About This Story:

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

Copying JUST the data with Ctrl+C

Everyone gets this.

What folks need a little help figuring out is how to do the same operation BUT

WITH Column Headers

Throw a SHIFT into your Ctrl+C, get the column headers too!

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]

My pug in his natural state.

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.

Tell Others About This Story:

Comments 43

  1. 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.

    1. thatjeffsmith Post
      Author
      1. 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.

        1. thatjeffsmith Post
          Author
          1. 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.

  2. 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

    1. thatjeffsmith Post
      Author
  3. 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.

  4. 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.

    1. thatjeffsmith Post
      Author
          1. thatjeffsmith Post
            Author
  5. 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?

    1. thatjeffsmith Post
      Author

      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.

      1. 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…

        1. 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.

      2. Looks like Excel needed to be reset… Wow what a pecuiliar problem. All is well again…. Hopefully this will help someone else.

          1. 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.

    1. thatjeffsmith Post
      Author
  6. 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.

    1. thatjeffsmith Post
      Author
    2. thatjeffsmith Post
      Author
  7. 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

    1. thatjeffsmith Post
      Author
    2. 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).

      1. thatjeffsmith Post
        Author

        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.

        1. 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…

          1. thatjeffsmith Post
            Author
  8. 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?? 🙂

    1. thatjeffsmith Post
      Author

      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!

      1. 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!

  9. thatjeffsmith Post
    Author

    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.

      1. thatjeffsmith Post
        Author
  10. 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

Leave a Reply

Your email address will not be published. Required fields are marked *