Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

SQLDev Tip and Trick: Drag and Drop to Worksheet

3 Mins Read

Updated 9/28/2018

Writing Code is for Suckers

Yes, I’m joking, mostly. But if you’re in a tool, and it can help you save time, then why not?

Here’s a simple scenario: building a SELECT statement for one or more tables/views.

Dragging and dropping tables to the worksheet is a pretty obvious feature – it copies the object names over for you. But did you know you had the option to get more bang for your buck?

Drag and drop preferences

Once you do the drag, you can select what you want copied to the Worksheet. If you don’t see this dialog, open the Preferences and go to the Database – ‘Drag and Drop’ page. Personally I would set it to what I want and toggle OFF the ‘prompt every time.’ I like getting the JOINs written out for me.

For this example I’m only dragging over 2 tables, so the benefit over manual typing is decreased. However, imagine instead I am dragging 15 tables over. Then I think this exercise starts to get exciting.

Just curious, what’s the largest number of TABLES you’ve seen joined in a single statement? 15 seems to be the magic number for smaller audiences. However I’ve ran into a 3 VIEW JOIN that had 50 joined TABLEs each for the 3 underlying VIEWs, so I know there’s some pretty crazy stuff out there.

Tip: Disable Open On Single Click

This default behavior makes it difficult to select and perform operations on more than one object at time.

Disable this if you don’t want an object to open when you click on it

Ok, back on our ‘trick’ – here is what you get after dragging the tables over.

Woohoo - no SELECT *!

OK, I don’t want all those COLUMNs, so I toggle over the Query Builder view…

Tuning Tip: Query only what you need.

…and toggle off the columns I don’t want. For larger tables, right-click on the TABLE and ‘Uncheck All’, then come back in and toggle the COLUMNs you want. Go back to the Worksheet and…

Less typing, more clicking = less time, more code

…voila, our statement is ready!

You can read more about using the Query Builder and Worksheet together here.

But Jeff, I HATE(+) Those JOINs!

No worries, in version 18.2, you can toggle that over to ANSI style using the Query builder.

Author

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

Related Posts

28 Comments

  1. Michael Reply

    Hey Jeff,

    okay, there is a preferences setting, to turn off the option “open object on single click”.

    But is this a common ui behaviour?

    I’d prefer to use a switch like CTRL+click or CTRL+(drag and drop) to select some objects and paste their names into the worksheet when dropping.

  2. Sharon Reply

    Hi Jeff,

    What is the trick when I click a table but don’t want the tab to appear with the table attributes, data types etc.

    I only want to drag table/tables to the query builder.

  3. Dave Evers Reply

    I am not seeing the “Join” dialog after selecting 2 or more tables.

    I have gone to Tools > Preferences > Database > Drag And Drop and selected “Select” + “Join” radio buttons & unticked “Prompt every time” & saved my changes.

    Thanks,
    Dave

    • Dave Evers

      Hi Jeff,

      Thanks for the quick response.

      The following is what I am seeing:

      —————–
      SQLDev Version 17.3.1.279; Build 279.0537

      Steps:
      1) Start SQLDev & create new ‘SQL worksheet’ (Alt-F10)
      2) Click into ‘Query Builder’
      3) Tag 3 tables
      4) Drag the selections to ‘Query Builder’
      5) Dialog does NOT display
      6) Selected tables display in the ‘Query Builder’ and are not linked

      Thanks,
      Dave

  4. John Garmon Reply

    Query builder suddenly changed, and it doesn’t show the table columns. Each table is represented by only the table title with no columns.

    I just closed out of sql developer for lunch and came back to go to work. How can I fix this?!

    john3

    • thatjeffsmith Post Author

      I just checked and the column names are there just fine…i’m in 18.1 connected to 12cR2 on windows, running java 9.

    • John Garmon

      I’m using 4.1.4.21, and it looks like Mgt. doesn’t want me upgrading.

      Note: Column names reappeared later! I did see a msg. something about connection getting closed? Could that relate?

    • John Garmon

      If connection closing causes this, I have been unable to find a straightforward way to ‘bring back’ column_names, after this ‘breakage.’

      Are there instructions?

      john3

    • thatjeffsmith Post Author

      Instructions for what to do if your connection breaks?

      Not sure what ‘breaks’ means.

      Also, your version is quite old…

    • John Garmon

      I can reconnect to the database. I need to know how to reconnect in such a way that the columns immediately ‘come back.’

      So far, no luck.
      john3

  5. John Garmon Reply

    “…and toggle off the columns I don’t want. For larger tables, right-click on the TABLE and ‘Uncheck All’, then come back in and toggle the COLUMNs you want. ”

    Jeff,
    Where exactly can I right-click on TABLE to select Uncheck All?

    Thank you,
    john3

  6. John Garmon Reply

    Why don’t I get the ‘a.’ and ‘b.’ prefix as shown here?

    When I drag a table to place in query builder, it prefixes with ‘tablename.’ instead.

    • John Garmon

      Could this relate to the fact that our Dbs don’t have foreign keys?

      john3

  7. Jack Reply

    Hi Jeff,

    Thank you for the blog and your time helping us SD users.

    I am running SD 4.0.2.15 on OS X 10.9.5.

    I cannot find a way to use the drag and drop functionality to create a SELECT statement with a join.

    I click on table 1, I click on table 2. This always opens up the table interface in a new tab.

    I click on the original tab containing the SQL worksheet. Both tables are still highlighted in the connection navigator. Now when I click either table to do the ‘click and drag’ the other table loses focus and the “Join” radio button is greyed out.

    Am I doing something wrong?

    Jack

  8. john s. Reply

    Hello Jeff,

    I’m completley new with sql developer

    May you can help me

    I have the Problem, that if i take some tables with the drag and drop select join option, theres no row inthe result for an join just only the select commands for the tables which i using …

    What I’m doing wrong ?

    best regards john s.

  9. Braim Reply

    Indeed, you’re right. Thank you 🙂

    I didn’t have the latest version of SQL Developer. Now that I have updated, I have the ANSI syntax for JOINs and the FULL OUTER JOIN available.

    Thank you

  10. Braim Reply

    Unfortunately, I haven’t found any way to generate ANSI syntax for JOIN… And no way to have a FULL OUTER JOIN. Maybe in a future release?

    Braim

    PS: Nice blog 🙂

    • JeffS Post Author

      Actually you can do that today. Right click on the join and toggle on the ‘SELECT all rows’ for both sides. That will give you something like this –
      SELECT…
      DEMO_ORDERS.CUSTOMER_ID
      FROM DEMO_ORDER_ITEMS
      FULL JOIN DEMO_ORDERS
      ON DEMO_ORDERS.ORDER_ID = DEMO_ORDER_ITEMS.ORDER_ID

      I’ll send you a screenshot to your gmail.

Write A Comment