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.
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.
28 Comments
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
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…
test
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
See this discussion.
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.
What do you mean by ‘auto’ import?
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
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
Yup, a bug.
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?
Yes, it WILL be fixed 🙂 This got broken when we fixed something else, it’s always a fun adventure around here…
With the new version 4.0.2.15, it doesn’t seem to work anymore…?
That’s pretty vague. What do you mean, doesn’t seem to work anymore?
Excellent example! Automatically adding commas between columns is a nice touch.
About the second part of the OP request (automatically generating PL/SQL code for an insert): maybe the Generate Table API feature[1] is better suited for this job. BTW, did I mention how amazing that feature is?
[1] http://www.thatjeffsmith.com/archive/2013/05/an-oracle-designer-feature-generating-table-apis-with-oracle-sql-developer/
Thanks 🙂
I learned 2 new things from that. Thanks.
Excellent!
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;
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.
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 ?
Create the partition, name it, and say the value list is MAXVALUE
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
You probably need to upgrade, it was a bug in 4.0, fixed in 4.0.1
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.
first example, drag and drop, 2nd example, copy and paste
I wasn’t getting commas either way, but I see that is a bug fix in the latest build…downloading that now. Thanks again.
yeah, quite a few bug fixes in 4.0.1, let me know if you have issues after you get it up and running