ThatJeffSmith

SQL Developer: I’m Looking at a Record, How Do I Edit It?

thatjeffsmith — Tue, 18 Jun 2013 14:55:09 +0000

You’re doing some data clean-up, or maybe you’re just trying to answer a business question for a user, and you notice the data doesn’t look quite right.

Name that tune…

You want to click in that ‘(null)’ cell and tell the world that Tyler and Perry composed that song! But, we’re in a query result set, and not a table editor. I’m the ultimate lazy-dev, so what’s the easiest way to get what I want?

Throw In a Little Copy & Paste with some Mouse Magic

Copy the WHERE and ctrl+hover into the table

Copy your WHERE clause, minus the actual word ‘WHERE’
Ctrl+Mouse hover over the table or view name in the FROM

This should get you here:

Every Table and View Editor Has a Filter Control

Now paste in your text from where you copied it in the worksheet and hit

Edit away!

But Why Can’t I Just Edit the Data in The Worksheet?

Suffice to say there are philosophical and technical challenges at play here. We’ve decided to leave the worksheet as a place where you go query and run scripts. Interactive (read+write) grids are not on the road map currently. Obviously many of you feel differently as it’s the #1 request on the Exchange.

But instead of just ‘being stuck’ I hope you find this alternative workflow better than just writing your UPDATEs and DELETEs on your own.

No, you can’t always get what you want
No, you can’t always get what you want
No, you can’t always get what you want
But if you try sometime, you just might find
You get what you need — Mick Jagger/Keith Richards

Querying Your Oracle SQL Developer Data Modeler Designs

thatjeffsmith — Wed, 12 Jun 2013 19:39:26 +0000

Oracle SQL Developer Data Modeler has a multi-user repository that makes it very easy to control and version changes to your data models and designs. However, it’s stored in a Subversion (think files) repository. Many of our users are accustomed to having EVERYTHING stored in the database.

And when something is in a database, then it can be accessed via SQL.

So how can I leverage all the data in my designs without having to use the client tool? What I really want to do is use SQL. And the answer is:

The SQL Developer Data Modeler Reporting Schema

When you have your model, you’re able to save it to your database. Everything but the Physical Model information will be stored in a reporting schema. Yes, we’re going to be adding support for the Physical Model information at a later date.

So:

Create design
Build model
Save model
Export Model to Reporting Schema

I’m doing this from inside SQL Developer but it’s also there in the standalone Modeler application

But wait, I don’t have a reporting schema yet!
No worries, after you select the connection for your reporting schema, SQL Developer will build the repository for you automatically. I recommend you create a separate user JUST for the reporting schema.

Select your connection (database/user) and go!

Voila, reporting schema created!

Before You Write Any SQL, Check Out the Reports First

We supply a few simple reports for you. You can check out the ‘Data Modeler’ Reports node and try running a few of them.

Supplied Relational Model Reports

Now if you want to get an idea of what the data looks like in this new reporting schema, you could of course import from the data dictionary to yet ANOTHER model, or you could get lazy like me and just browse the schema. The table and column names are pretty self-descriptive. And if you get even lazier you can copy the supplied reports down to the user reports, and then use the editor to grab the SQL…

select t.table_name "Table_Name",
       m.model_name "Model",
       d.design_name "Design",
       d.published_by "Published_By",
       to_char(d.date_published, 'YYYY-MM-DD HH24:MI:SS') "Date_Published",
       t.ovid "Table_Ovid"
  from dmrs_tables t, dmrs_models m, dmrs_designs d,
       (select design_name, published_by, max(date_published) latest
          from dmrs_designs
         where (:Design is null or upper(design_name) like upper(:Design))
           and (:Published_By is null or upper(published_by) like upper(:Published_By))
        group by design_name, published_by) z
 where t.model_ovid = m.model_ovid
   and m.design_ovid = d.design_ovid
   and d.design_name = z.design_name
   and d.published_by = z.published_by
   and (:Table_Name is null or upper(t.table_name) like upper(:Table_Name))
   and (:Model is null or upper(m.model_name) like upper(:Model))
   and (:Design is null or upper(d.design_name) like upper(:Design))
   and (:Published_By is null or upper(d.published_by) like upper(:Published_By))
   and ((:Date_Published is not null and to_char(d.date_published,'YYYY-MM-DD HH24:MI:SS') like  ate_Published)
       or (:Date_Published is null and 'TRUE' like upper(:Most_Recent_Design) and d.date_published = z.latest)
       or (:Date_Published is null and 'FALSE' like upper(:Most_Recent_Design)))
 order by "Design","Date_Published" desc,"Model","Table_Name"

Or You Can Write Your Own SQL, Of Course

Or you can run reports out of the Data Modeler directly from the Reporting Schema instead from out of the designs. This means anyone could run those reports that had access to the reporting schema vs having the actual designs loaded on their machines.

Run your data modeler reports from the reporting schema vs the loaded designs

This is a ‘Read Only’ Type of Repository

Meaning, I can’t use DML to update my models. That still happens via SQL Developer Data Modeler.

Any questions?

A Sneak Peek: Oracle SQL Developer Version 4.0

thatjeffsmith — Tue, 04 Jun 2013 16:13:52 +0000

In a few weeks I’ll be speaking at the Database Symposium at the ODTUG KScope13 conference. My topic, ‘SQL Developer v4.0′ will be one of the first times I spend an entire hour showing off our new features in the upcoming release.

While not yet publicly available, and while I can’t comment on when that will change, I can say we’re getting closer

I thought I would reward my loyal readers with a quick ‘sneak-peek’ here first. I don’t say ‘thanks!’ enough, but thank you so much for all of the comments and feedback you folks share here. It really does help us make a better product for all of our users.

So I’ll let the picture do the talking here. And if you’ve been paying close attention to post pictures from the past few months, you may have noticed other changes coming down the pipeline!

A few minor tweaks that could save you a lot of time…

Monitoring Scripts as They Execute in Oracle SQL Developer

thatjeffsmith — Wed, 29 May 2013 18:15:49 +0000

Interesting question from the forums:

I’m doing multiple Inserts on a table using a script file (.sql) in SQL Developer.
Since it has a lot of statements, I would like to check the progress of it somewhere in the application window. But I don’t see any progress of lines being displayed anywhere. The focus simply stays on in the first line.

It would be helpful if the script execution either points to the current DML line, or at least displays the ‘progress’ info e.g line 234 of 1000, in the status bar. I have no way of knowing which statement is being Inserted at a given point of time.. and it is very annoying as I’m not able to estimate when the script will complete.

Appreciate your response!

So when asking a question regarding software, it’s always important to mention what version of the software you’re using. Our user is indicating they’re not seeing the script progress as it executes – that should be working in the current version of SQL Developer. Let’s take a look and see!

Here’s a couple of animated GIFs demonstrating what you should see when executing a script, from a file or an unsaved worksheet.

But I want to see what’s going on from a different window!

Then make sure you open the Task Manager.

The Task Progress panel will display the current line being executed.

Ok, but what line am I on?!?

Look down a bit further in the editor gutter.

Here’s you’ll get a very close estimate to what line # your script is on.

I say ‘very close’ because generally we can update the panel with the line number faster than we can advance the buffer and highlight a line as it executes…in this screenshot it’s off by about a half-dozen lines, but not bad for being about 12,000 statements overall.

Of course you could always use the Tools > Monitor Session screen to see what the current statement is, or do a query on your table that is having the INSERTs ran for to see what the row counts are…there’s always more than one way to do something, especially when it comes to Oracle.

How to See Two Tables at the Same Time in Oracle SQL Developer

thatjeffsmith — Mon, 20 May 2013 13:22:29 +0000

Another post from the FAQ files:

HOW TO OPEN MULTIPLE TABLES IN SQL DEVELOPER SIMULTANEOUSLY

I’m guessing the user is in a hurry, hence the shouting and lack of punctuation. But that’s OK, I think we’ve all been there.

There’s a couple of different ways to get this end result:

Use the Pins
Change the Preferences

Pinning Objects

At the top of every editor in SQL Developer, you’ll see a push pin button in the toolbar. Toggle that push pin to ‘Freeze Content.’ It doesn’t ‘freeze’ your table in the database, but it does force the table editor to persist even as you open another table editor.

The pin is pushed – the editor is now ‘frozen’

Now if I open another table, the pinned table editor will remain.

Table two is open, and table one is still there.

But I want to SEE both tables at the same time!
Then we need to pull out another trick – ‘New Document Tab Group.’ Right click on the table editor tab and ask for the new document tab group. Voila.

Tab Groups are shown concurrently in the SQL Developer desktop display

Change the Preferences

By default, as you open a database object in SQL Developer, it will cause any previously opened object to close. However, you may want to leave these open until you explicitly close them.

This preference, if enabled, will auto-pin any database object you open.

I’m not a big fan of this preference, but it’s there if you want it. Just don’t forget to close objects when you’re done working with them.