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.
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.
40 Comments
hi jeff,
Can I format my code exactly just like PL\SQL Developer does. the example is
Select *
From SomeColumns
where condition
Group by columns.
see the double spaces ahead of From..
hope you got it.
you want
select *
from[SPACE][SPACE]HR.EMPLOYEES
?
Hi Jeff.
I need to congifure a Zebra GC420t printer on a PL/SQL Developer 10.0 server.
Is this posible at all?
I don’t know – you should probably go ask on the Allround Automations forum as they own PL/SQL Developer and not Oracle.
Hi jeff,
Is there a setting in SQL Developer or on the Oracle Database to limit query time. I was having a query running for a while and my TI departement was little upset.
Like if query run longer then XX minutes then return “process too long” or something like that. A delay task killer !!
Thanks
Not in the tool per-se, although I think there are jdbc timeout settings you could play with that might approximate the behavior you’re looking for. Your IT department should also look at resource consumer groups. They allow for a set of users to be given limits on CPU, memory, idle connection times, etc.
Then again, if a query takes a really long time to run, sometimes that’s what it takes, yes?
Hi Jeff,
I am using 3.0.04, is there any way to validate the syntax for various DDLs queries without actually executing it?
Actually, I have generated index file from a dump, but when I am running it, it is throwing error at a specific location saying inappropriate right parenthesis.
Since the index file size is huge, around 4MB, I am not able to track the incorrect area visually.
Thanks,
We parse/validate the code as you type it/enter it into the editor. You have grammar problems highlighted with sqiggly red lines – just like you’d see in your word processor.
Hi !
Great post I am working with 4.0 at the moment!
Quick question, is there a way to save the layout ? I use a lot of reports which I put in different panels (locks, free space, memory,etc) besides object explorer and it is really annoying to open everything up and arrange it every time.
Hi Jeff,
regarding your answer about disabling the SQL Monitor feature if you don’t have Diagnostic and Tuning Pack licenses, are you referring to the Licensing Preferences dialog in SQL Developer ?
This setting isn’t entirely foolproof in all circumstances.
I’m sure that you are aware of the issue with the ASH and AWR reports that come bundled with SQLDeveloper not being disabled by this feature.
Are there any plans to tighten this up in future releases ?
Thanks,
Mike
Hmmm, we do prompt/warn/force the user to acknowledge they’re accessing a Diagnostic Pack feature, and we tell you to manage the license config via the Preferences when accessing the ‘ASH REport For The Past 30 Minutes.’
When I de-select ‘Diagnostic’ in Preferences and then try to run the report, I get an error message ‘Diagnostic Pack Required, blah, blah, blah.’ So in my opinion, the preference is working, we’ve prevented someone from running that report on a restricted database.
I’m reading your bloggity-blog on the subject, and I’m confused. Are you saying if someone copies the SQL out of the report and runs it in the Worksheet that that’s our fault?
Wouldn’t that be akin to blaming YOU if someone found that query on your blog and ran it on their database?
Or maybe I’m not understanding the issue?
What do you mean by ‘not being disabled by this feature’ exactly?
I run SQL Developer on Windows 7. When I use the F8 key, in a SQL Worksheet window, it does not bring up my SQL History. When I do this: cd %appdata%\SQL Developer\, I do see History xml files.
So in the next version, there will be a button in the menu system to reset your windowing layouts – which is what I’m suspecting you need to do to get the SQL History to appear. So for now, you need to do this
Jeff,
In SQL Developer (3.2.09), I will be logged into PROD, using query builder, then want to test in PPRD (which I’m not logged in to). So I click the database icon in the upper right corner from PROD to PPRD, and it kills me every time. By that, I mean, I get the Connection Information box, my user name is there, but I am not allowed to enter text into the password box or click the Cancel or Help buttons. The only way to get out of this is to kill my session.
Am I doing something wrong or is this a bug that could be fixed? I can log into PPRD several different ways, only this way causes me to kill my session.
Thanks!
Patty
I’m guessing, you don’t have the password for that connection saved, yes?
Nope, I type it each time.
“..I am not allowed to enter text into the password box..” – the dialog is completely inaccessible? Or can you type into the USER piece?
Weird either way…I tried this and it seems to work for me.
What OS and version of Java do you have installed?
Also, as a workaround, if you save your password for the PPRD, does it ‘work?’
Sorry, got busy.
I have Oracle 10g and 11g using TNS; Java 6 Update 37 (not going to Java 7 yet).
I can’t type anything in the box, not even my username, but my username did show up.
If I save my password, all is well, not box comes up and I am automatically changed from PROD to PPRD.
Thanks for you help!
Patty
Hi Jeff,
Is there any way to edit data in worksheet it self if we use
for update or select rowid.
Many Thanks,
PPS
Grids in the worksheet are READ ONLY – but if you have the ROWID you could ctrl-click into the table from your query, use the ROWID as a FILTER in the table editor, and get to that row fairly quickly.
there is no cancel button in the table data tab like the one appears in the worksheet on execution, so cancel isn’t an option too..
thank your for your time
Thank you for your presentation.
I wonder if there is an option not to auto-query table when opening Data tab, but only when you press refresh.
So I’m thinking, if you don’t want the query to execute, don’t click on the data tab?
why not enter your where criteria before….??
So it’s taking a long time for the data to come back? And you want to avoid that by using a WHERE clause filter?
Are you just browsing, or will you be editing the data as well?
It depends, it takes some time occasionally and it might be useful as an option.
you should be able to cancel the initial fetch if it takes too long…also you can always just run a SELECT * FROM…WHERE in a worksheet if you just want to browse the data. Just drag the table to the worksheet, that will help you build your query, then you can execute when you’re ready.
Hi Jeff,
Thanks for your presentation, I picked up several great tips that I’m already using. I was the one who submitted a question about the PL/SQL editor background color. What I meant is, are there plans to allow the background color to be fully customizable, where a user can set it to ANY color they want? The reason I ask is that I prefer my editor background color to be off-white or light yellow (since it’s been shown to be the best for preventing eye strain), but this option is not included any any of the predefined themes. It’s a pretty common feature in an IDE to allow the user to do whatever they want with their editor colors, which is why it’s surprising that the most SQL Developer offers is pre-defined themes with no option to set your own background color. I know this is nit-picky, but really, it’s one of my top pet peeves about the program.
Thanks,
Meghan
I’m confusesd Meghan – you can change it to any color you want. You can override any color provided by the Scheme if you want.
Thanks for your quick response. Maybe I’m missing something here, or maybe my configuration is wonky, but I have never in 2 years been able to fully change the background color of the editor.
Here are the steps I follow: Tools –> Preferences –> Code Editor –> PL/SQL Syntax Colors. Choose a Schema from the drop-down (say, Classic with a blue background). Choose a different color from the Background palette, and hit OK. The only parts of the code that actually have my chosen background color are the comments; the rest of the editor window still has a blue background.
Am I doing something wrong? I’ve tried playing around the background color option under all of the things listed under “Available Styles”, but no dice.
I see what you mean now – I thought there was a ‘default’ item in that list box, but there’s not. Talking to some developers friends to see how to do this. I agree with you, it should be there.
Thank you so much. If this could be added, it would make me so happy 🙂
developer found the issue, it’s FIXED for next version – so stay tuned 🙂
Hi Jeff,
Why isn’t it possible to do the same in SQL Developer as in SQL Developer Data Modeler concerning the PL/SQL formatting and color? In the Modeler you don’t have that much options.
Learco
Learco,
I need more details. Are you saying you’re wanting PL/SQL Formatting colors to apply to the DDL generated in the Modeler in the DDL preview dialogs?
Jeff
Hi Jeff,
Thanks for your answer. Let me explain a little bit more on that. In the physical model you have the possibility to create packages and triggers.
When creating a package, it looks the same as in SQL Developer, but you can’t compile and do other stuff of course. But the formatting options are not the same, especially the fonts and colors are not adjustable. Another difference is the ability to see the individual functions and procedures of the package in the ‘tree’ in the left pane. In SQL Developer you can and in SQL Developer Data Modeler there is’n a way to see them.
For trigger there’s a different editor, why not using the same?
Maybe I’m missing things as I’m just beginning to use the Modeler as a replacement for Designer.
Thanks for your time,
Learco
Also, thanks for the feedback and questions! I’ll do my best to get the editor looking the way you need it to!
Hi Jeff. You mentioned above that the background colour change issue was fixed for the next version in April 2013. I am on version 3.2.20.09 and still don’t appear to be able to change the overall background colour. Was this fix ever released please?
Thanks.
The next version from April 2013 would be version 4.0 🙂 In version 4, set the ‘scheme’ to default, and then set Background to Black, or whatever floats your boat.
Got it Jeff, that’s great thanks 🙂