SQL Developer Q&A from ODTUG Tips & Tricks Webcast
- by thatjeffsmith
Another great webcast yesterday – if you’re a paying member of ODTUG you can watch the show for yourself in their archives. If not, you can get my slide deck off of SlideShare.
About 150 of you brave souls sat through an entire hour of me talking and then 10 more minutes of Q&A. We went through everything rapid-fire style, so I thought I would post the questions and my refined answers here for your perusal.
In the order in which I received them:
You showed the preference to choose between resultsets in same tab or ain a new tab. I understand that we can not have it both using different hotkeys? For example: F5 run and resultset to same tab, ctrl-f5 same but to new tab? Sometimes you want the one other times the other.
The questioner is asking about this preference, Tools Preferences Database Worksheet ‘Show query results in new tabs.’ This is an all or nothing proposition. But, there’s another, perhaps better way: the document PINs. If you have a result set you don’t want to lose, ‘pin it.’
Pin multiple result sets or plans for review and comparisons.
You mentioned that sometimes it’s hard to remember where a certain preference is. I agree. So enhancement request: add a search-box to the preferences window. Maybe like in, for example, UltraEdit. It shows you all preferences containing your search criteria.
Actually, we do have a search mechanism
type the search string, we auto-filter the preferences
Is there a version of SQL Developer that will connect to an 8i database (Yes, I realize how old that database version is!)
Sorry, no. We also don’t have a version that will run on Windows 3.11 for Workgroups…probably.
How do we access your blog?
Carefully, and with much trepidation. When you’re ready, go to http://www.thatjeffsmith.com
Is there a way to get good formatting with predefined settings?
I believe the questioner is referring to the script output a la SQL*Plus formatting commands. Yes, there is. You can build your formatting commands into your login.sql script, and those will be applied for your script execution sessions.
Example here.
Why this version 4.0 doesn’t support external plugins?
It does, it just requires the plugin developer to re-factor it for OSGi. This came about when we updated the JDeveloper framework to the later 11g/12c stuff.
Any change in hookup with SVN?
The only change with Subversion is that internally we’re using 1.7 stuff now. You can use SQLDev to work with a 1.8 SVN server, but if you get a working copy with a 1.8 client SQLDev won’t be able to do anything with it…
Command line utilities ? improvements
Yes! The long answer is here.
Is that a Hint or a Comment?? /*CSV*/
It’s a comment – the database won’t recognize it, but SQLDev does when it goes through our statement pre-processor. We’ll redirect the output through our CSV formatter before displaying the results in the Script Output panel. That’s why this will ONLY work in SQL Developer.
Are you selecting “”Run Script”" to get that CSV or HTML output, rather than “”Run Statement”"?
Yes, the formatter hints like the CSV one mentioned above only make sense in a script output panel vs a grid.
How do you save relational models once they’re defined? I’ve had trouble with setting one up, “”saving”" it, then the design work I did is longer there when loading it later.
File – Data Modeler – Save. If you’re running the Modeler inside of SQL Developer, the menu’ing interface can get a bit tricky. That’s why I recommend using the stand along if you’re doing anything with a model that takes more than 5 minutes.
See how the Data Modeler menus are folded up under the SQL Dev menus?
Can u unplug and plug into another container in a database with only sqldeveloper?
Yes, you can ‘Detach’ a multitentant 12c Database ‘pluggable’ and plug it into another instance. You have the option to copy or move the files.
This isn’t a trivial operation, pay attention
Can you run APEX code directly on the adopter?
No, at least not as I understand your question. Give me an example and I can give you a better example.
Is there a way that when u click on a particular table it wouldn’t show the table with the info but just to see the columns underneath clicking on the node?
Yes, another one of my tips! Disable Tools Preferences Datbase ObjectViewer ‘Open Object on Single Click.’
Is there a patch to allow a double click on a procedure on an open package body to take you to that procedure in the editor?
This has been fixed for EA3 – to be released soon.
Can you open the spec with the body?
You can open the spec or the body, and then also open the other. But you can’t open both with a single click.
So if you want you can set it to CSV but can you also see it as a regular result set in rows and then click in the results to export to excel?
If you run your query as a statement with Ctrl-Enter, you can send the data to Excel via the Export dialog.
Will it do intellisense like using the alias and pop up the column, object names?
Yes!
You can select more than one column…
Can a DBA turn off items from a high level for users so the only thing they can perform would be selects?
A DBA should turn things ON, not OFF. Create a user with only CONNECT and required SELECT privs and you’re good to go, regardless of which application they are using.
I use PL/SQL Developer from allround automations and was SQL Developer illiterate and now I like this for myself as a DBA. Now I get to train developers on this tool since they have been asking how to use this tool. Thank you.
No, THANK YOU!
Can you run multi queries in the worksheet after you added it to the worksheet?
Yes, highlight what you want to run, and hit Ctrl-Enter.
Can you export the result sets to excel, etc.
Yes. In version 4.0 and going forward, I recommend you use the XLSX option for exports. It will run faster and consume much, much less memory.
Will this be available after the webinar?
If you are a ODTUG member, check out the webinar recordings in the archives. That’s worth the $99 right there. Ask your boss if they have $99 in their training budget for you. If not, maybe time to look for another job?
Can you run command lines from this tool? Like executes without issuing a command line prompt?
Ok, I’m stumped on this one. Not sure what you’re asking. You can setup external tools under the Tools menu, and from there you could probably rig what you’re looking for, but I’m not sure what you’re looking for…
This maybe?Where and when to put the program
Is there any way to save a copy database command set (certain tables/views etc) in a script?
Yes! Create a cart with the objects you want to be used in the Copy. Then use the new command-line interface to kick off SQL Developer to do the copy of those said objects.
How can we export the preference and then import them into different or same version of SQL Developer ?
Today, there’s no interface for this. But you could copy the files around manually…Kris Rice has a cool idea where you can set your preferences to be saved to your local drop box folder and then you can use SQL Developer from anywhere with the same preferences
What happens to SQL*Plus commands like COL & BREAK
Nothing. Those are not currently supported.
Is there a place where all “”hotkey”" functionality is listed? thanks
Yes. Tools – Preferences – Shortcut Keys. And you can change them!
Any tips for the DBA side of things? will the SQL generated for objects have more information (e.g. user privileges) in v4?
You can get this now. In Tools – Preferences – Database – Utilities – Export, check ‘Grants.’ Voila!
You now have the code necessary to recreate your object privileges
Is there a limit on the number of rows that could be imported / exported from/to excel ?
The only hard-coded limit lies in Excel. For best performance, use v4 and XLSX formats for Exports.
Is there a way to see/watch active sessions to see current SQL and the explain plan being used, etc. Kind of like that frog product.
Cough, yes. Tools – Monitor Sessions. Click on session, see SQL and plan. The plan was added in v4. If you’re not in version 4, use the Reports – Active Sessions to get the plans.
In the DBA section is there a way to manage say tablespaces to add data files, shrink, edit profiles, etc.
Yes, we support all of that. View – DBA. Connect, go to the Storage node.
Are you (Jeff) available for a live presentation at our Oracle User Group here in Indiana?
Maybe. Email me and we’ll see, jeff[email protected]
Where do I go to download sql developer 4.0?
The Internet of course!
Can you directly edit query results?
Nope. But what I think you’re asking is, can I edit the data in the tables that are reflected in my query results? You can change the query results by changing your query of course. Or this.
Can you show html example?
Sure.
I’d embed the HTML here, but it’s a lot of code, try it for yourself!
How can I quickly close many SQL worksheet windows, but not all?
Window – Documents. Multi-select, hit the ‘Close Document(s)’ button.
What does the vertical red line denote?
That’s the margin. Tells you when you’ve typed too far and it’s time for a carriage return.
Did DBA/Database Status/Instance Viewer make it officially into 4.0? It was sort-of included in the first EA.
I have NO idea what you’re talking about, WINK-WINK. No, it’s not in v4.0.
Is there a “”handy”" way to debug trigger code?
Yes, open your trigger. Hit the debug button. Works great as long as it’s a DML trigger.
Will you make your presentation file available for us ( in PPT and/or PDF format ) ?
It’s on SlideShare.
How do you get SqlDeveloper to escape ‘ correctly when you use the wizard to export data as insert statements?
If it’s not doing that, it’s a bug. I’ll take a look at that scenario ASAP.
