Stop Doing That! – In Oracle SQL Developer

thatjeffsmith SQL Developer 34 Comments

Tell Others About This Story:

One of the coolest bits of The Matrix, was the ability for the main characters to ‘download’ new skills or objects into their virtual reality. Like Neo could learn Kung-Fu with a simple button click.

Today, I want all of you to take the (Oracle) Red Pill, and download your SQLDev knowledge pack.

These are things that I see people doing ‘the hard way.’ After just a few minutes or reading (ha ha, more like you scrolling looking for pictures), you too will be a black belt in SQLDev and ready to take on any Agents of wasted time and clicks.

Firing up another copy of the app

If this one sounds familiar, that’s because I already talked about it. Here.

Basically, either use unshared worksheets or colored connections – and I bet this will make 90% of you that much happier.

Scrolling through the tree every time you want to open an object.

Ctrl-click to it, or use the search
to get to your table/view/proc/whatever, faster.

Select, alt+g, and 'Go' :)

Select, alt+g, and ‘Go’ :)

Copying and pasting data to Excel

Um, we have this Export feature that can do that for you?

Just right-click on the data grid and say ‘Export,’ then pick your format. Too many clicks? Build a SPOOL around your SELECT, and add the format of your choice, a la

SPOOL c:\DATA.csv
SELECT /*csv*/ first_name, last_name, salary, department_id FROM hr.employees;
SPOOL OFF

Writing queries from scratch, every time.

Start with the history first. And if you use it enough, promote it to a SQL Template or Report.

Using *’s in your SELECTs

Writing it is easy. Supporting it later, just sucks. If you want all the columns, write out all the columns. But at least do it the easy/lazy way.

Ahhh, better.

Ahhh, better.

Putting DBMS_OUTPUT.PUT_LINE all over your PL/SQL

for ad hoc debugging!
We have a debugger. Use it. No more hacking up your code to see where you are and what X is equal to.

Had to add the italicized text, thanks to Tim for reminding me to be a bit more specific in my chiding 🙂

Using the Create Table Dialog

Don’t hack up a table. Design it in the Modeler. Then generate the DDL, and run it in SQL Developer.

Putting a Windows VM on your Mac so you can work with Oracle

First of all, you can run SQL Dev native on your Mac. Secondly, you can also run our VirtualBox appliance if you really need a copy of the database on your Mac too.

I like to rename it here, so I know what version I'm opening when I access it from the Dock

I like to rename it here, so I know what version I’m opening when I access it from the Dock

Copying and pasting your code out to your favorite text editor.

Don’t do that. I mean, use your favorite editor. But don’t copy & paste – setup the external editor and make it auto-magic.
sqldev_vi

Caveats

Yes, I know you will have many valid reasons for doing all of these things. And they are all valid. In which case, this post wasn’t for you. But for everyone else, stop it.

PS

Some of you aren’t putting in foreign keys for your related tables. There’s no good reason not use foreign keys.

Tell Others About This Story:

Comments 34

  1. This “expand a * to every columname” feature is nice … but you could do it way more easy accesible:

    instead of needing a tooltip to wait and hover,
    I’d rather jump with the cursor right next to it – and reveal being a windows user – so that hitting context menu / right click of mouse would offer that as first or last liner.

    because right now it took me several minutes to figure out the existing solution and it bugs me if I can use keyboard only on most tasks to be done but that structural helpful one is only accessible via a *** mouseover tooltip.

    thanks, dear product manager.
    I’m using 4.6

  2. hi jef,=)
    i have 2 question plz help
    1- is there a way to change the encoding to utf-8 that i can add it the below script .
    ( select /*csv*/ * from test_1 order by TO_NUMBER(sequence); )

    2-i run the below script in sql developer , but it doesn’t create the file

    spool c:\test.csv
    select /*csv*/ * from test_1 order by TO_NUMBER(sequence);
    spool off;

    thank you.

    1. thatjeffsmith Post
      Author

      1 – maybe, not sure if you can do that via spooling the encoding information directly to the file or not…will have to Google myself
      2 – did you run using F5/Execute as script? Do you have write privs to c:\? What version are you running? This works for me – I know that doesn’t help you, but sounds like something environmental on your end rather than the software.

      1. Do you have write privs to c:\? location in the c drive

        did you run using F5/Execute as script? F5/Execute as script

        my version is 1.5 =)

        about question 1 do you now any other solution to aotmaticly extract the data in csv and utf_8 .

        thank yoou =)

        1. thatjeffsmith Post
          Author
          1. thatjeffsmith Post
            Author
          2. thatjeffsmith Post
            Author
  3. In trying to “expand” an * in a select, auto-assist (completion insight) does nothing. I notice that it seems to be turned off, even though it is turned on in preferences. I noticed that completion insight does work in a worksheet that gets created by a new connection, but will not work in a saved query that I reopen?

    I must be doing something wrong? Doesn’t most work people do involve reopening a saved query?

    Help?
    john3

    1. I cannot imagine that I would have to copy / paste my query into the “new” worksheet just to use completion insight!?
      john3

      1. thatjeffsmith Post
        Author
          1. thatjeffsmith Post
            Author
          2. I / we have only been creating one connection per database. I am new to SQL Developer as of June 1st this year, and I have had to share what I have learned to my team.

            If I have to create distinct connections for each query, I’m not really happy about that.

          3. thatjeffsmith Post
            Author

            I don’t understand. You only need one connection. When you open a file, just tell it WHICH database you want the file to be executed against.

            We don’t do automatic file association/connections with worksheet/.SQL files – automatic thing generally cause more problems than they solve.

            So are you saying it works after your worksheet has a database connection going?

          4. It never works.

            I run the select * query against the database.

            Then I try to get column names out of the *, and nothing happens.

            I’ve got to return to work. This has already taken too long.

            Thanks anyway.
            john3

          5. thatjeffsmith Post
            Author
    2. thatjeffsmith Post
      Author

      Hmmm, what version are you in? There’s no preference to enable the * – explodes. ‘In a saved query that I re-open’ – do you mean a file? If so, have you associated the worksheet to a connection? If not, it won’t work…we need the db to ask it what the columns are.

        1. thatjeffsmith Post
          Author
          1. Running SQL Developer while remoted in to shared computer that everybody uses. We will not be able to update, any time soon.

      1. Yes, a file. If it is easy to tell me how to associate a worksheet to a connection that would be great, but google is my friend.

      2. Just out of curiosity, why can’t (doesn’t) developer save the connection information from when I created the query? That information seems to be available and might be saved in the file, one would think?
        john3

  4. RE: Copying and pasting your code out to your favorite text editor

    Sorry, but doesn’t this work only if the code you’re currently editing in SQL developer is already stored in a text file on local filesystem? It’s almost a non-existent use-case for me, if I’m editing a file and I need external tool to edit it, I’ll just open it in that external tool instead of SQLDev from the start.

    Can I set up SQL developer to allow editing temporary worksheets and, better yet, stored procedures in an external editor?

    1. thatjeffsmith Post
      Author
  5. Very helpful, thank you!

    About Excel
    Problem: Excel and SQL Developer sorts undescores “_” differently: one at top, another at bottom.

    I see this when I compare a BEFORE and AFTER database restore.
    e.g. comparing a list of tables or columns in all_tables or all_tab_cols for a particular OWNER to see if any changes occurred.

    Work around is pasting both into Excel and re-sorting, a necessary but wasted step. The various order options for country codes doesn’t seem to help.

    Is there a switch or configuration option for this like there is for NULLs?

  6. Is there any way that user can specify the name of tab “query result” before run statement ? I know that I can use right-clicking on tab and choose “rename” when query is finished but sometimes when I run multiple queries at the same time this solution isn’t optimal for me. Sorry if my English isn’t clear for you.

  7. RE: Debugger – A debugger is not a substitute for instrumentation. Admittedly, DBMS_OUTPUT is not the best solution for instrumentation, but anyone that avoids instrumentation because they have a debugger is not a professional programmer in by book. Imagine if Oracle did that and we had no trace events and wait interface because a kernel developer could fire up a debugger. 🙂

    I know I’m being pedantic, but I see this all the time and it *always* ends in ruin…

    Cheers

    Tim…

    1. And no excuses that instrumentation is hard to implement. For those who don’t want to reinvent the wheel there are tools like Log4PL/SQL (http://log4plsql.sourceforge.net/) and you can even choose dbms_output if you insist.
      In our company we made the debug option available for our users. Whenever an action fails they turn on debug in the application and retry before calling us. With the messages in the logging table it is usually easy to find the cause. Compare this with the effort to reproduce an error in test/development and then to search for the cause. The ROI is fantastic.

      I have to admit that I use the debugger only for applications without propper instrumentation – none of those where I have anything to say during development .-)

    2. thatjeffsmith Post
      Author

      Sure, but using dbms_output for debugging purposes is not the same as using dbms_output for instrumentation

      I’m not saying use the debugger for everything, i’m saying, use the debugger to debug.

      Otherwise I’m with you.

      I think.

  8. I agree with one exception:
    Copy and past to Excel is often the best solution.
    Have you ever tried to open a CSV with data like “1.1” in a German Excel? Excel tries to be smart. You get 1.Jan in your cell and there is no way to find out what the true value is, except closing the file without saving and opening it in a text editor.
    And doing it with a right click export is not working when you have date columns. They will be exported as string and you can’t really use them later to e.g. sort.
    When you c/p date values, then Excel is smart and recognizes them as date values. You only have to mark the columns where you insert data like 1.1 as “text”, so Excel is not too smart.
    Did I mention that I don’t like Excel 🙂

    Marcus

    1. thatjeffsmith Post
      Author

Leave a Reply

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