I normally don’t blog on Sundays. But I was going through our Exchange, triaging your enhancement requests, and I found one that is ALREADY in the product. Actually I found a dozen or so that are already in the product, but I thought I’d talk about this one in particular.

From Describe (Shift-F4) allow “Copy column names to clipboard” like SomeOtherTool
Description:

For example, I want to clone one or more existing records in a table, but replacing certain columns with something else:

INSERT INTO mytable
(a,b,c,d,e,f)
 
SELECT a+10000 AS  new_a, NULL AS new_b, c, d, e, f
FROM mytable;

Or when writing PL/SQL code to insert into a table, I want to explicitly list the column names, then provide the values:

insert into mytable (a,b,c,d,e,f) values (cur.a, cur.b, cur.c, cur.d, cur.e, cur.f);

This is slow to hand-code when my table has 20 or even 100 columns

So yeah, we can do that. Popup your DESC window (SHIFT+F4) and copy. Here’s a little demo.

Copying Column names from a DESC popup window
Copying Column names from a DESC popup window

PS Please document your requests as much as possible.

The more vague you make your requests, the easier it is for me to say, ‘yeah we already do that.’ Not that I have a problem with this, but I want you to get what you want, not what I THINK you want.

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

28 Comments

  1. Hi Jeff,
    I realise this is an old thread, but I am hoping that we can still get an answer to a question that was posted a while ago by Nigel Richmond and which I am also interested in.
    First I want to say how impressed I am by SQL Developer as a tool. I am new to Oracle, but have been using a major competitor’s product extensively for a couple of decades. I must say that whenever I go now to the other product I hugely miss many of features from SQL Developer, like the Describe popup Shift-F4 for example…
    Now down to my question. I know that dragging fields from the Shift-F4 popup surrounds them with single quotes. I also know that CTRL-copy/paste writes the fields without quotes in a row-by-row list.
    Neither of these however are suitable for quickly building a Select statement with a lot of fields in it…
    Is there a way to drag (or copy-paste) some fields from the Describe popup and paste them as a comma-separated list of unquoted names?
    Alternatively, more generically, is there a set of magic keys that, when kept pressed, enables the user to control the single quotes and the commas for that operation?

    Thank you

    • thatjeffsmith

      New to Oracle, welcome!

      I had to make a decision, what was more likely – that users would want to build IN lists using values of data in the grids, or column names from the shift+F4 DESC pop-ups. This was because the developers weren’t able to tell whether the user was on a COLUMNS page in a DESC popup dialog or in the grid from a query result. I decided that it was more likely that users would be in the data camp – which just happens to be the opposite camp you are in.

      For your use case, I would suggest using the control+space popup. It’s fewer steps and faster than what you’re doing now.

      Type
      select — invoke here with ctrl+space
      from table

      we’ll give you a list of columns on that object, select them, hit enter, and you’ll get exactly what you want.

      There are other ways to get column names for your queries into your SQL. You could use the Query Builder, you could drag and drop them from the connection tree itself (expand the table item in the tree to see a list of columns) for example.

      I hope it’s ok that I just answered your 2 questions here in the first place you asked…

  2. Nigel Richmond Reply

    I realize that this is a really old post I am commenting on, but anyhow..I notice that when I drag and drop columns from the popup DESC window in 18.2.0.183 that the column names are surrounded by single quotes in the sql editor. Is there a way to turn off the quotes? Thanks

  3. Hi,

    I am new to oracle database and I wanted to know how to auto import excel data into the database. I am using sql developer Version 4.0.2.15.

  4. Mine is not working the second option you showed me. I selected the particular coumns with the help of CTRL and then I pasted it using CTRL+V but it’s copying all the columns upto what I seleted last.my oracle is Version 3.2.20.09. Please help me

  5. When doing a shift+ F4 on a table, then selecting some column_ names and dragging them between in the query select from beer;
    The columns are not comma separated but just pasted one below the others

    • Dragging and dropping selected columns in version 4.0.3 still does not work (no commas in between). Is there any chance this bug gets fixed in the upcoming release?

    • thatjeffsmith

      Yes, it WILL be fixed 🙂 This got broken when we fixed something else, it’s always a fun adventure around here…

  6. With the new version 4.0.2.15, it doesn’t seem to work anymore…?

  7. Jeff… Thanks for all the hints. They do help a lot.
    This one will get a lot of use.

    Just a suggestion for the column copy.
    It doesn’t honor the alias on a table so the following results in the following after doing the copy from DEPT.

    ORA-00918: column ambiguously defined
    select DEPTNO,DNAME
    from SCOTT.EMP e,SCOTT.DEPT d
    where E.DEPTNO=D.DEPTNO;

    • thatjeffsmith

      Well, the DND, doesn’t ‘know’ about the alias…although maybe we could have it check. Not sure how feasible that would be.

      However, you could just type d. and select your columns, and we will use the alias. Faster than desc/DnD too.

  8. Rajeshwaran Jeyabal Reply

    Jeff,

    Working with sql data modeler 4.0.1, where I have to define a table as Range partitioned table with a partition ‘pmax’ holding all default values (MAXVALUE).

    in the Physical model -> Table properties -> General -> partition Type = RANGE , But how can I define the Table with single default partition ‘pmax’ I don’t find any options here to do that? Can you help me ?

  9. Jeff,

    When I attempt to do this, I do not get a comma after the field names. Is this a preference setting, or an issue with the version I am on. I am running version 4.0.0.13

    Thanks,

    -S

  10. Nigel Richmond Reply

    Thanks Jeff. How do you get the commas between the column names to populate automatically? It seems the commas came in automatically in your first example, but not in your second.

    • Nigel Richmond

      I wasn’t getting commas either way, but I see that is a bug fix in the latest build…downloading that now. Thanks again.

    • thatjeffsmith

      yeah, quite a few bug fixes in 4.0.1, let me know if you have issues after you get it up and running

Write A Comment