More Column Copy Tricks in Oracle SQL Developer

thatjeffsmith SQL Developer 23 Comments

Tell Others About This Story:

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.

Tell Others About This Story:

Comments 23

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

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

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

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

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

    1. thatjeffsmith Post
      Author

      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.

  5. 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 ?

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

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

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

        1. thatjeffsmith Post
          Author

Leave a Reply

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