Here’s a quick run-down of FAQ’s I get when doing presentations or demos.

Can SQL Developer…

Extend a tablespace?

Yes.

Yeah, that one.
View > DBA. Add connection, connect. Go to the storage bits.

You can edit an existing data file or add a new one. We’ll also show you the SQL that’s going to be used so you can copy it to run in SQL*Plus or wherever. And if you wanna just do it in a SQL Worksheet, we can help you with the syntax there too.

Debug a stored procedure?

Do you mean run one and see what comes back?

SQL Developer makes debugging SYS.REFCURSORS easy!
SQL Developer makes debugging SYS.REFCURSORS easy!

Or see your DBMS_OUTPUT?

Unlimited isn't free!
Unlimited isn’t free!

Or do you mean stepping through code, one line at a time?

We make it EASY to see stuff. You can mouse-over the variable in the code editor, or use one of the data panels.
We make it EASY to see stuff. You can mouse-over the variable in the code editor, or use one of the data panels.

Make an ERD?

Yup. Database diagrams too.

This is an ERD. Those blue thingies are Entities which will later live on as tables.
This is an ERD. Those blue thingies are Entities which will later live on as tables.

This is a Relational Model, tables and foreign keys, diagram.

This is NOT an ERD
This is NOT an ERD

Export to Excel?

Gosh yes. But be sure to use the XLSX format – we can build those faster and with less memory than a XLS file. Or just write to a CSV using our /*csv*/ trick

Import from Excel to a table?

It’s the #1 post on this blog for like 4 years running. Yes, we can do that.

Yes, it's that easy.
Yes, it’s that easy.

Compare 2 schemas?

Yes. Or just some objects. Or two different schemas in 2 different databases. Or even 15 objects from 14 different schemas over here to 14 different schemas over there. And it’s free, and it does ALTER SQL scripting.

Read the script, TEST the script, apply the script.
Read the script, TEST the script, apply the script.

Compare my PL/SQL or any code for that matter?

Yup, don’t believe folks that say you can’t.

Line by line compare of your code with differences marked in the gutter for easy ID for large files
Line by line compare of your code with differences marked in the gutter for easy ID for large files

Format my code?

Yes. We’re doing some work for version 4.2 to make this much nicer. In a SQL Worksheet, right-click, format. You can set your formatter preferences…in the preferences.

before and after
before and after

Help me make my SQL go faster?

Yes. Explain plans, dbms_xplan, autotrace, reading trace files, SQL tuning advisor, real time sql monitoring – we have all that stuff.

Unlock a user or change their password?

Yes. User management stuff is in the DBA panel, under security.

Make a heat map report to show log switches (or something else?)

Yup, you just need to build a report.

This is from David Mann's GitHub project. He's got this and more for you, for $0.00.
This is from David Mann’s GitHub project. He’s got this and more for you, for $0.00.

Generate DDL?

Yes. For one object, for some objects, for all objects. In the GUI, or at the command-line.

Want GRANTS too? For a table, or for a user? Yes and yes.

You now have the code necessary to recreate your object privileges
You now have the code necessary to recreate your object privileges

Connect to MySQL or SQL Server?

Yes. Teradata, DB2, and Sybase too. Run queries, look at stuff, and of course copy and migrate things to Oracle!

Work with BLOBs?

We can upload them, look at them, load them into an external editor, export them by the truckload, or even view them as text.

Support SQL*Plus stuff?

Yes, run your code ‘as a script’ in the worksheet, or use SQLcl.

Run more than one query at a time?

Yes, you’ll need an unshared worksheet if your current connection is busy.

Export a resultset without running the query a second time?

Yes – but mind you it could be expensive.

Not get in my way while I try to type my code?

Yes, configure your code helpers to have a longer delay, or disable the ‘automatic’ helpers and use it on-demand.

Generate data dictionary reports?

Yup. View > Reports or use the Data Modeler.

One of a thousand different ways to generate a DD report.
One of a thousand different ways to generate a DD report.

Search the database and/or PL/SQL code?

Yeah. Hit the binocular button in the toolbar. Don’t forget ALT+G in a code editor to auto-search the text at the cursor 🙂

Search, browse, click to open.
Search, browse, click to open.

Block edit code?

Yes. You can block select code, or you can add multiple cursors to type/edit in a region or even NOT in a region.

Give this a whirl :)
Give this a whirl 🙂

There’s more, of course.

Use Google, use the search on this page, the answer is out there. If you can’t find it, come back to my ‘Ask me‘ page, I’ll help you find it.

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

14 Comments

  1. Thank you for share your knowledge.

    Can I ask the following?

    – Compare my PL/SQL or any code for that matter?, can you expand, please, how to access to “compare” option? * I can’t see it in my SQL developer instance.

    – Generate data dictionary reports – by using “Data Modeler”?, I want get the same results (I can see in your image post) in HTML format about the table: (columns, comments), i.e, the same (or even more data) show by pressing shift + F4 “while the name of the object (in this case, a table) is selected in the text editor”.

    In the “Block edit code?” final section, how you add commas? – I can’t see the option you selected (and well, I have set spanish language in my preferences), so, I’m still confused about which option you choose there.

    These are SQL Developer’s version I have installed:

    Version 4.1.3.20
    Intern Version MAIN-20.78

    PS: The “Ctrl+Shift+Click” is kind of annoying, because in Notepad++ by holding “ctrl+shift” and using the arrows to move in those directions- would be nice if these same behavior occurs in SQL Developer – just sayin’ 🙂

    PS #2: bear with me, I’m new in SQL Developer and PL/SQL – I used MS-SQL Server more often.

  2. Can I debug a SQL Server stored procedure with SQL Developer?

  3. In your block edit code video you are aligning the column names in the select, with a comma before the column name, and an indent before the comma … how?

    This is exactly what I want from the code formatter but can’t find how to do it in 4.1.3.20

    Cheers

  4. Amr Okasha Reply

    Hi Jeff,

    I can see a lot of good work and enhancements have been done to sql developer and sqlcl. Is it possible to have data comparison as well?

  5. Didn’t get that “Explode select *” trick, it went by a little fast. How’d you do that again?

    • thatjeffsmith

      there should be a little squiggle line mark under the * – put your mouse over that, hover in other words, and then you’ll get the column list in a popup dialog. if you click on the text, it will replace the *

  6. Hi Jeff,
    When is 4.2 is scheduled to be released? Or could we have an access to early adopters version?

    • It seems that the new version is delayed, is there any reason?

    • thatjeffsmith

      Delayed? We never published a schedule, so there’s no delay. We are working on multiple product releases around the same time period and coordinating with Cloud and DB product updates as well.

    • thatjeffsmith

      I feel bad for telling people they’re wrong, but that’s partly our fault as vendors for calling EVERY diagram an ERD for short.

Write A Comment