ThatJeffSmith

Questions & Answers from SQL Developer Tips & Tricks ODTUG Webinar

We had a great webinar yesterday with more than 350 people attending. Even tacking on 10 minutes at the end, I didn’t have enough time to answer all the questions (31!) So I’m going to tackle them here in quick order. If you want a more detailed answer, use the search form on your right as I’ve probably covered the topic you’re asking about in more depth and detail.

If you missed the ‘show’ and you’d like to take a look at the slides, they’re up on SlideShare.

So without further ado, let’s jump in!

Q: is there any max number of rows to get?
I’m guessing you mean max number of rows to get in a query resultset. There is, but it’s not defined by SQL Developer, it’s defined by your available system resources. We fetch rows as we need them in the grid. As we fetch that data, we’ll need more memory…

Q: I have installed sql developer on windows 7 but when i start it it asks for “”ENter the full pathname for java.exe”" ..i have latest version of ava installed in my system ..can you please help what i am missing here
You need to tell SQL Developer where Java is installed. See my post on ‘Getting Started with SQL Developer in 5 minutes or Less.’

Q: Is there a preference to turn on auto table aliasing for drag and drop queries?
I believe that’s controlled by the ‘Generate Column/Table Aliases Automatically preference.’

Q: Can you please show us that how we can make a grid output on SQL query results? e.i one line yellow, one line white
You’re referring to the ‘zebra’ visual style of the data grids. In preferences you want to enable the ‘Grid in checker board or Zebra pattern’ preference under Database – Worksheet.

Q: Will it be possible to access PosgreSQL in sqldeveloper 4.0 ?
Support for PostreSQL connections and migrations is not on the immediate roadmap.

Q: Can you rename an worksheet while its open?
Yes, try this set worksheetname ‘stuff and such’and execute.

SQL Developer Editor Display Preferences

SQL Developer Editor Display Preferences


Q: Are there any plans to allow the PL/SQL editor background color to be customized in future releases?
You can do that today actually. See the picture…

Q: Monitor SQL requires Licensing of Tuning Pack right ?
11gR2 and higher feature, requires Enterprise Edition + Diagnostic & Tuning Packs – so YES. Please check with your Oracle Account Manager for all licensing questions. You can disable this feature if you’re not licensed for it.

Q: Do I have to download a new version to upgrade or can I just do check for updates?
You have to download new versions. Check for Updates will let you upgrade and install extensions only.

Q: How do you show explain plan? What Function key will perform this?
F10 in the worksheet. Or use the toolbar button to do an Explain.

Q: If you do not have foreign keys setup can you use the modeler to show references from one table to another manually without causing any foreign keys to be added to the database?
Yes. You can manually add the relationships or INFER them based on column names. Nothing is saved back to the database without you doing it via a script you run against the database.

Q: Can you enter more than one SQL select statement in the window and have the different tabs show?
Yes, select what you want to run (one or more statements) and hit Ctrl+Enter. You’ll get one grid per query resultset.

Q: Is the sql history list geared to the user?
It’s geared per installation – so every query that SQL Developer executes via the worksheet will be logged to the history. You can have multiple users on SQL Developer on a workstation and each user can have their own preferences and workspaces though.

Q: Does v4.0 have a better support for version contyrol?
Subversion 1.7 support is on the way, so yes. What specifically are you looking for though?

Q: can i put my own dict as rlwrap does? on slow connection it will be usefull
If I understand the question, the answer is ‘No.’

Q: share sql dev 4.0 ^_^ i see dropbox in your tray
Ha! No :) You will get it soon enough.

Q: How can we make code formatting look like Toad…for example I want SELECT INTO FROM WHERE all right aligned, but in SQL developer it does left aligned by default
Right Align Master Keywords formatting preference should do this.

Q: Is the query history stored by worksheet or is there one history for all open sheets?
One query history for everything. You can sort and filter by connection, runtime, etc.

Q: The Ctrl + highlight + click brings up the package specification. Is there a way to change that to open the package body?
No, but you can toggle from the SPEC to the BODY – see the toolbar buttons.

Q: is there a quick way to find uncompiled objects, and recompile
Yes, there’s an invalid objects report you can run. Then you use the ‘recompile schema’ on the main connection node context menu. Run that and set ‘ALL OBJECTS’ to ‘false’ – that will only recompile invalid objects in that schema.

Q: Is anything special to be done in order to activate drag and drop? I am using 3.2.20.09
No, but check to see if you disabled any of the migrations extensions in your preferences. You’ll lose that feature if you disable the main migrations extension. That’s fixed for next version.

Q: Is there a way to import “”User Defined Reports”"?
You can copy and paste them into the folder.

Q: For delimited output can you specify tabs by default?
Yes, you can set it to anything. See previous blog post for example.

Q: Where is the snippets shortcut in view?
View – Snippets

Q: I did not undertand the Clobber function
If you use SQL History to recall statement, default behavior will CLOBBER (or replace) the existing editor contents with said query. You want to use the APPEND option if you don’t want to lose the contents of your worksheet.

Q: advantages with use with sql developer with other tool for DBA
SQL Developer helps you. Use it alone, use it with other tools. Don’t use it at all. Do what makes you productive. I’m just trying to let our users know what they CAN do with the tool – it’s up to you how you decide to implement our tools.

Q: What does the SQL runtime include? Is it accurate? Does it include the overhead of bringing the data to the screen? With large amounts of data this overhead could be huge
Is what accurate? Everything you ask for has overhead. Asking for a billion rows has a cost associated with it. That’s why we only bring over a few records at a time. Be careful what you ask for, you’ll get it.

Q: Is there a keyboard shortcut to comment/uncomment a line
Ctrl+/ and you can change this in the preferences if you prefer something else.

Q: I have SQL Developer installed on 2 laptops with the same hardware. They run at different speeds when extracting data. Any idea why and how to speed up the slow one?
Check the SQL Array Fetch Size preference – otherwise not sure. Need more info.

Q: Can you execute the code in sqlplus immediately /without saving and signing in again manually) ?
No, but this is a current request on our exchange – go vote for it.

Q: What unit is the SQL history limit?
That’s defined by the preferences, but I wouldn’t go too crazy. A few thousand at most is what I would advise.