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’ 
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.
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.
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.
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.
34 Comments
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
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 – 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.
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 =)
**know
1.5 is about 9 years old – time to upgrade, then I can really help you Anoud.
Hi
i download the SD but can you send the link to downlaod JDK for win 7 32bit .
thanks
It’s the first hit.
i found 46 bit i want 23 bit
it’s right there
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
I cannot imagine that I would have to copy / paste my query into the “new” worksheet just to use completion insight!?
john3
I don’t have to. You do…something is up.
View -> Log -> Statements. Do you see the query spin up to pull the column names when you mouse over the * in your worksheet/query?
No, no spin up.
you have an oracle connection defined for that worksheet?
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.
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?
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
if you get time, we can do a webex, and I’ll talk you through it
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.
SQL Developer 4.1.0.19
we’ve had v4.1.1, 4.1.2, and 4.1.3 updates…i don’t think it’s a bug here, but you might have other issues addressed by one of those 3 patches
Running SQL Developer while remoted in to shared computer that everybody uses. We will not be able to update, any time soon.
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.
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
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?
Files only. But if you want to use it, just have a scratch.sql file you use for everything…
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?
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.
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…
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 .-)
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.
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
yeah, copy & paste is often faster and better
I only worry that folks ONLY know about copy and paste