Oracle SQL Developer is a graphical user interface and integrated development environment for Oracle Database. As you can imagine, a few clicks can results in an awful lot of queries being executed on your behalf.
If you don’t like this fact, you can always use a command-line interface like SQL*Plus or the new sdsql. The only thing that runs there is what you explicitly type..mostly.
Anyways, in version 4.1, you can now see all of the queries going across the ‘JDBC wire’ so to speak.
Open the Log
The Statements panel is what we’re looking for.
Do Something in SQL Developer
You could start typing in a worksheet, or click on a table, or launch a report, or…just about anything. In this case I’m going to load the tables in tree.
I get this question ALL THE TIME: Why can’t I see my tables?
So, if you wanna see how we get your tables, now you can – without the effort of doing a trace or digging through v$sql_whatever.
So you can see we’re doing a lot more than just SELECT * FROM USER_TABLES here.
The ‘Tricks’
There’s no tricks really. We’re always logging. It’s done at JDBC level – there’s no performance overhead for using this feature. This will capture queries SQL Developer itself is generating, queries the user is executing, and even queries that the underlying framework provided by JDeveloper are using. It will even grab whatever queries are being executed by 3rd party extensions.
There’s no turning it ‘on’ or ‘off’ – it’s just a matter of opening the panel to look at the information or not.
You can clear the panel. If you want to see what the tool is doing, clear the log, and ‘do the thing.’ EZ-PZ.
You can filter. Type whatever text you’re looking for…
We capture the queries and record the sequence so you can step through what’s what.
And we capture the binds, so you can see what values are passed over into your queries.
So, What to Do With This?
I’ll be using it to help answer customers’ questions. Easier than drilling through the source code. I imagine many of you will be using it to see who we’re doing stuff, so you can go do the same stuff. Like, how is SQL Developer grabbing waits in the Instance Viewer? I want to do that same thing in MY report…and now you can.
I think this might be a popular feature. So does this guy.
@thatjeffsmith Holy crap. Nice!
— Stewart Bryson (@stewartbryson) December 9, 2014
25 Comments
JEFF SMITH,
Can SQL-Developer log the logic of the SQL execution ? (which condition of the WHERE clause failed ?)
example:
select 1
from Dual
WHERE
1 = 1
AND 1 = 1 + 1
;
Can we know the above sql statement failed at the line number 5 ?
Thanks & Apologize if the question is not suitable with you.
PV.
Nothing really failed…it’s just that your predicate clause 1 = 1+1 will always evaluate to FALSE.
But, is there a way to see the FALSE’s? Not that I know of, but this is a great question.
YES, thatjeffsmith. I mean so : true | false.
With complex|analytic SQL statement, I spend much time to debug the logic of SQL statement (comment – decomment line by line to check the condition).
Appreciate your feedback.
Thanks & Regards.
PV.
thatjeffsmith,
In case you have updated infomation or workaround solution or any advises on this, please update.
Thanks & Regards.
PV.
I was on vacation this past week. Even so, I don’t have any updates for you. You could try asking the AskTom team, they might know if something cool along the lines you’re looking for.
but this statement-logging is not available when you’re using jdbc. not when you checked “Use Oracle Client” right?
I meant it’s only available over jdbc, not over oracle-client
Correct.
Any way to turn this off? No matter what I do, it pops up with every mouse click or scroll. It pushes itself to the front of the “message” pane that actually contains useful information about my compile. Then I have to click on the message pane to see it. About 100 times a day….
click off of it, so make the ‘messages’ panel the active panel, then minimize the log panel. you won’t see the statements panels again until you click on it
There’s no “minimize” once the Message panel has focus. rt-clicking on the “statements” panel only offers “Close” and “Close All” as options. Closing it doesn’t help. It comes right back.
Don’t close it. Just click OFF of it and onto one of the other panels. Then minimize.
I’m running 4.1.0.19 on Windows 7. There is no minimize button. Only a “down arrow”. I’ve clicked all around, left and right…no “minimize” available in any menu anywhere that I can find (apologies if this is a head-banging moment for you…I’m a Mac user at home and only use Windows at work)
upgrade, I’m on 4.1.2
and just click onto Messages, and the Statements wont’ force its way to the front
Thanks Jeff! Appreciate the help.
I can confirm that you’ll need to go into Preferences > Database > Advanced, check the “Use OCI/Thick driver” box, uncheck the “Use Oracle Client” box, and restart SQL Developer for the Statements to appear.
I’m having a problem where the Statement Log is not appearing in subsequent runs of SQLDev 4.1. I’ve tried reinstalling the product fresh (except for importing my Connections), then using the product over the course of a day; the Statements Log works as expected.
Then the next day, the tab isn’t visible in the Messages pane at all! Unfortunately, I can’t find a pattern to re-create this!
Be interested to see what this bug might be!
Did you switch SQL Developer to run with Thick connections?
Just tried turning Oracle Client off with the Thick checkbox checked, and voila (sp?)! 🙂 The Statements are showing!
Jeff:
Whoops..should have mentioned that I’m running SQLDev under Windows 7 32-Bit.
Using a 12c Instant Client for Thin-Client duties to access 11g databases on 64-Bit Win2012 servers. (Thought I saw something whilst Googling which stated that a 12c client was required for this functionality to work.)
Jeff, I’m not seeing this feature in the Linux version of SQL Developer 4.1. I can open the log window, but the only two tabs visible are Messages and Logging Page. There is no Statements tab. Is there a preferences setting I might be missing?
What is sdsql? Found nothing in the web or in Oracle site
It’s a new toy for you, from us. Think SQL*Plus, but with all the SQLDev magic.
What is sdsql? I googled and found nothing
Cool, that’ll be very useful! Thanks!