|
|
 Hopefully your code is cleaner than what runs through this every morning
I’m not sure if I can help with the mind part, but I can definitely add some perspective on the line numbers. Quickly, let’s define just what the ‘gutter’ is in the world of programming and database GUIs. I found a good definition on Quantum Whale, a .NET component developer, and I’m going to quote them:
Gutter:
The gutter area can be used to display bookmarks, line numbers and outline sections.
The gutter is the area to the left of the text, the purpose of which is to display miscellaneous indicators for the corresponding lines of text. Among these indicators are bookmark indicators, line wrapping indicators, line styles icons, and line numbers. All the images displayed in the gutter are contained in the gutter image list.
Notice the VERY last thing mentioned in that description. A place to display ‘line numbers.’
Now here is the question of the day -
Should your IDE show these by DEFAULT?
I’ll save you the suspense and tell you that many of the IDEs out there DO NOT, and that includes SQL Developer. There are probably a few reasons, and I can make up more, but it probably comes down to this:
- JDeveloper doesn’t
- Keep the coding area as clean as possible
One of those reasons is a technical reason, and end users could care less about those. The other is more of a personal choice / philosophical decision. When you make these types of decisions, you opt for something with minimal impact to the user base, and aim to ‘do no harm.’ Did we make the right decision here? For many of you, yes we did. For many others of you, no we did not.
In our defense I could publicly mention several other database IDEs that have the exact same behavior. But, I would also argue with myself that those people are no better off and that we should strive to be the best. Yes, I do tend to argue with myself quite a bit.
One of my Twitter friends asked me if SQL Developer was an IDE or was it Notepad. Ouch, being compared to Notepad. Although I will admit to Notepad being my default HTML editor and I have no problems not having line numbers there.
I get his point though, serious coders are working on programs with many thousands of lines of code. They need little helpers to get them through the day.
Lets take a quick look at the following picture:
 SQL Developer Worksheet with Line Numbers Shown So the error message is on line 11. This is where the Oracle parser gets upset that it’s not finding that COMMA that it expects. If it were complaining about line 24,327 it might be a pain to get to that line number, right?
Except.
Ctrl+G will open the ‘Go to line number’ dialog for easy jumping.
It’s a click-click to get the line numbers on. I suppose you could argue that folks might not know where to click. Right-mouse-click in the gutter space.
 Right-mouse in the gutter If you are in the PL/SQL Editor, you can actually click on the error message in the ‘Compiler Log’ panel and it will take you to the indicated line number for that error message.
Maybe I am too close?
What I see as intuitive, you see as a trick?
If you think we’re doing it wrong, please add your vote in the form of a comment to this blog post. You can also up-vote this request in the Exchange, although it needs re-worded.
Maybe we need a SQL Developer++? Notepad++ after all DOES show line numbers in their gutter by default 
 Oracle SQL Developer Job Run Log Starting with 10g and the introduction of DMBS_SCHEDULER, Oracle database began tracking and storing the execution duration of a job. By default this information is kept around for 30 days. You can of course change the logging level and the retention period for the job runs – read the docs!
The job runs can be viewed via the dba_scheduler_job_run_details SYS view. Oracle SQL Developer shows this data in the job details panel of the schema browser even. So, answering the question, ‘How long does this job normally take to run?’ should be relatively easy to answer.
It is, if you can think in terms of INTERVAL. You see, Oracle doesn’t store the time a job takes to run in seconds, it uses the INTERVAL datatype.
desc dba_scheduler_job_run_details
Name Null Type
----------------- ---- ----------------------------
LOG_ID NUMBER
LOG_DATE TIMESTAMP(6) WITH TIME ZONE
OWNER VARCHAR2(30)
JOB_NAME VARCHAR2(65)
JOB_SUBNAME VARCHAR2(65)
STATUS VARCHAR2(30)
ERROR# NUMBER
REQ_START_DATE TIMESTAMP(6) WITH TIME ZONE
ACTUAL_START_DATE TIMESTAMP(6) WITH TIME ZONE
RUN_DURATION INTERVAL DAY(3) TO SECOND(0)
INSTANCE_ID NUMBER
SESSION_ID VARCHAR2(30)
SLAVE_PID VARCHAR2(30)
CPU_USED INTERVAL DAY(3) TO SECOND(2)
CREDENTIAL_OWNER VARCHAR2(65)
CREDENTIAL_NAME VARCHAR2(65)
DESTINATION_OWNER VARCHAR2(128)
DESTINATION VARCHAR2(128)
ADDITIONAL_INFO VARCHAR2(4000)
So what is an INTERVAL?
Technically it’s INTERVAL DAY TO SECOND. From the docs -
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds)]
Stores a period of time in days, hours, minutes, and seconds, where
day_precision is the maximum number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.
fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6.
The size is fixed at 11 bytes.
So if I’m looking at an entry in dba_scheduler_job_run_details, and it shows ’0:0:1.0′, we can read that as ‘Zero days, zero hours, and 1.0 seconds.’ As you can imagine, asking for an average using the avg() function doesn’t turn out so well -
select avg(run_duration) from dba_scheduler_job_run_details;
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Error at Line: 3 Column: 11
How do I get a number out of an INTERVAL?
Oracle provides a function called EXTRACT(). It allows you to pull DAYS, HOURS, or SECONDS from an INTERVAL. BONUS FEATURE: It also works for TIMESTAMPS! However, there is no direct conversion function that would take an interval and return the total number of seconds or minutes or hours, or a fraction thereof. So we have to do some MATH. I know, I know – that hurts. But, we can always cheat and ‘borrow’ from the great Tom Kyte
Using Tom’s math – basically taking the number of days and hours in a day and multiplying out to get seconds and then adding to the seconds field gives me total number of seconds. In my case I’m OK with seconds b/c most of my jobs don’t take very long to run. You could adjust the math and get an avg number of hours or days instead. I’m going with seconds.
So here is my borrowed query from Tom:
select job_name,
job_name,
avg(extract( day from run_duration )*24*60*60 + extract( hour from run_duration )*60*60 + extract( minute from run_duration )*60 + extract( second from run_duration ))
from dba_scheduler_job_run_details
group by job_name, job_name
having avg(extract( day from run_duration )*24*60*60 + extract( hour from run_duration )*60*60 + extract( minute from run_duration )*60 + extract( second from run_duration )) > 0
order by 3 desc
Why did you put JOB_NAME in there twice?
Because I wanted a chart report!
I like pretty pictures – it’s why my career path looks so bright (this is a joke about managers, they love charts.)
 SQL Developer Report Showing Average Job Run Time with Detail Breakouts So clicking into my …_MAINTAIN_STATS job I can see that it ran in an avg of 5.25 seconds for a total of 4 times in the past 30 days. Averages can be misleading though. Four runs from 0 to 10 seconds is quite the range. You may want to build some logic in your report to restrict any average that doesn’t have at least a statistically significant number of data points.
Thanks to my Twitter friends for giving me something to chew on (and blog about!) this morning
I am just getting my feet wet when it comes to the reports in SQL Developer. The tool itself has quite a lot going on, everything from a database IDE to a migration platform, to a data modeling component. And then throw in the reporting stuff. I’ve already covered how to build a simple bar chart report in an earlier post, but now I want to take this report to the next level!
Basic reports are tied to a single dataset. Adding one or more child reports allow you to take a value from the parent dataset and feed that as an input to a secondary query and report. Maybe my report is showing a breakdown of breweries by country in a bar chart, and I want my report consumer to be able click on a country and then be shows a list of the breweries for that country. SQL Developer can accomplish this using a Child Report.
The Base Report
For the parent report I am going to use my previous beer example. It’s a chart report, and here’s the underlying SQL statement:
SELECT country,country, count(*) FROM beer
group by country, country
having count(*) > 100
order by 3 desc
Running this report gives me a chart that looks like this:
 My Breweries by Country Summary Report Chart Now let’s add the child report
 Step 1: Add the Child Report Right-click on the report in the tree and choose ‘Edit Report.’ We are going to go to the Child Reports tree and ‘Add Child.’ Note that you can add as many child reports as you would like, but I’m going to stick with the walking before the running.
Where do we go?
Where do we go now?
Where do we go?
I am going to choose a style of ‘Table’ for the report. I just want a grid style list of breweries by the selected country. The most important information to provide here will be the query that will be used to populate said grid. To tie the two datasets together, we need to use a bind variable. The ‘trick’ here is to uppercase the bind variable name to match the column name from the master query.
 The Bind Variable ties the Master and Child queries together For this particular report, the bind feeds off the COUNTRY name. So my child report query looks like this:
select brewery, city, state
from beer
where country = :COUNTRY
As I click on a Country in the master chart report, that value is fed to this query and will populate the grid for the child report.
Where do we go?
Where do we go now?
Where do we go?
The Completed Master-Child Report
 The report is interactive. The chart labels are active, click and get the details!
If you would like to read more about reporting in Oracle SQL Developer, then check out my predecessor’s article in Oracle Magazine from 2007, ‘Now Reporting.’
A new feature for v3.1 is our PDF reporting. We will have a video tutorial posted to coordinate with the release and will be available on OTN.
Where do we go now?
No, no, no, no, no, no
Sweet child,
Sweet child report of mine.
 Death by bullet points? On February 14-16, I’ll be at the Colorado Convention Center in Denver, Colorado for RMOUG’s Training Days Conference. This is the largest regional Oracle User Conference in North America and attracts presenters from all around the country and the globe. I’ll be presenting:
Presentation Name: SQL Developer Tips and Tricks for Developers
Abstract: You use the GUI because you want to be productive, right? Do you ever get the feeling you’re only really using a small fraction of what the database and SQL Developer have to offer? If so, then you’ve come to the right place. Stock up on tips guaranteed to save you time, and make your experience with your code and the database much more enjoyable. If you ever write SQL or PL/SQL, then this presentation is for you.
Get more information or register for RMOUG’s Training Days now!
My session details:
SQL Developer Tips and Tricks for Developers
Session 2
Wednesday 2/15/2012
10:45 am to 11:45 am
Room 407
If you haven’t had a chance to sit and squirm through one of my presentations before, then please remember that I welcome questions and snide remarks during and after the presentation. The last time I presented at RMOUG my PC froze, and I wasn’t able to recover. If you like Nascar for the crashes or Hockey for the fights, come on down and enjoy the show!
Can’t make it? That’s OK!
I’ll be hosting a web version of this presentation after the RMOUG event, probably around the first week of March.
It’s a relational database, so it’s only a matter of time before you stumble across an object that’s dependent on another object, right?
If you are looking at a table in the browser, be sure to check out the ‘Dependencies’ page. This page displays the database objects that will be upset if the selected table were to ‘go away.’ You’re probably familiar with these related objects, but if not, then there’s a quick ‘trick’ to investigate further.
Let’s take a look at SH.SALES as an example:
 Auto-navigate to the selected object in the grid This takes me to SH.CAL_MONTH_SALES_MV, a materialized view.
When I’m done poking around, I might want to go back to my SALES table.
The ‘Back’ and ‘Forward’ buttons on the main toolbar serve as my Most-Recently-Used tree navigation buttons.
 If you can work a web browser, you can mostly work SQL Developer, mostly
SQL Developer is built by Oracle, for Oracle. It’s the database IDE. That’s my current elevator pitch, at least. But, SQL Developer is also a fully featured data modeling solution (have you been enjoying my data modeling posts?) AND a 3rd party database migration platform.
The migration piece is the Migration Workbench. Since we support migrations from these other database platforms, wouldn’t it be neat if SQL Developer could connect and query those 3rd party databases too? Surprise, surprise – that’s exactly what we DO do today.
What ‘Support’ means
You can connect to, browse, and query (SELECTs only) SQL Server, DB2, Access, MySQL, and Teradata.
What ‘Support’ does NOT mean
- providing debuggers
- delivering database administration features
- write access to the database
Sound a little harsh or spartan? Going much further in any of these areas would just open a big ole can of worms, and we’re committed to providing an optimal experience for the Oracle database. But, since we already support these limited features, there’s no real reason to hide it.
So what’s it good for outside of migration projects?
You own one of those fancy-dancy MacBook Pros? Fall in love with Ubuntu? Need browse some SQL Server data? You sir/madam, are what we call a rebel! Instead of installing a VirtualBox Windows VM just so you can run SQL Server Management Studio (SSMS) or some other Windows-based database IDE, you can run Oracle SQL Developer natively on your machine and connect to that server.
This is pretty old news – here’s a nice blog post from 2007 talking about how to use SQL Developer to connect to SQL Server. But since the product name is ‘Oracle SQL Developer’, it’s probably a good idea to remind folks every now and then that SQL Developer is more than just a tool for Oracle developers
Download and Go
You will need the JDBC drivers for the database you want to connect to. These are conveniently indexed here. Couple of notes -
- You need to be a registered IBM customer to access their downloads
- The Teradata download manager page seems to be ‘missing’ – go grab the driver here
Once you have extracted the packages, you need to point SQL Developer to the .jar file
 Registering 3rd party JDBC drivers in SQL Developer Be sure to point to the actual file, not just the directory containing the jar.
Once you’ve done this, you can open the connection dialog and navigate to the appropriate database panel.
 Windows Authentication is mostly going to be tough from OSX, mostly
Data models sure are handy. They tell a story – how does the information get stored as data? Just what is a ‘customer?’ Sometimes though our applications die and only leave legacy scripts behind. Or maybe you are a DBA and someone has given you a set of scripts to create some new objects in your database. You ask them for their model, and they just look at you with that weird blank stare. “We have models, you say?” Of course, geeks have been building models since at least 1985.
The Reverse Engineer, or IMPORT, feature in Oracle SQL Developer Data Modeler will read information from an existing database to build your data model. But did you know it can also read in Data Definition Language (DDL) scripts? That’s right, those directories you have laying around with hundreds of CREATE OR REPLACE… scripts can be used to build a model.
For quick and dirty modeling tasks, I say just run that directly in SQL Developer (accessible under the View menu.) If you are going to get serious and build some new models from scratch, then you should probably run the standalone Oracle SQL Developer Data Modeler program. I also find the user interface much simpler in the standalone – it’s all about modeling. Some of the more interesting modeling features are ‘hidden’ if you’re in the main SQL Developer interface.
For this brief demonstration I’ll just run this in SQL Developer.
Disclaimer: This post was written with Oracle SQL Developer v3.1.
Open your model using the View – Data Modeler – Browser interface
You can now use the IMPORT feature
 It says 'Script' but you can import multiple scripts Select your file(s)
 Choose your DLL script files to be parsed and imported If you don’t happen to have any DDL scripts laying around, feel free to build some new scripts using the new Cart feature.
Tell us what RDBMS mapping scheme to use
It’s easy to think that SQL Developer only works with Oracle, but did you know the Data Modeler works with most RDBMS systems, including DB2 z/OS, DB2 LUW, and SQL Server?
 The Data Modeler needs a little help, tell it where your DDL is coming from. Review the results and proceed to your new model and Entity Relationship Diagram!
If there are any problems parsing the DDL script, the modeler will warn you about it. You can now review your scripts visually using the ERD. A newer feature for v3.1 allows you to automatically step into a SubView based on a Schema. So if your scripts are importing from several sources, you can look at all of it together, or just for user/database ‘x.’ Actually this feature is so new I saw it in a developer demo just today
People like to rag on java and it’s performance issues. But I was able to parse more than 1500 DDL files in less than a minute and have my model created for me. It’s still a fairly small model, only 269 tables, but that’s still pretty good if you ask me.
 From DDL Files to Model - why read when you can look at the pretty pictures?
A question that comes up fairly frequently revolves around how to see your errors when working with PL/SQL in SQL Developer.
Most folks are probably working in the worksheet – this is the default editor for your connection.
Let’s take a look at this sample program
create or replace procedure do_nothing is
begin
dbms_output.put(sysdate);
this should probably error out, right?
null;
end;
/
If we were to create this procedure, we would probably expect some errors. So let’s run this in the Worksheet. I’m using Ctrl+Enter to execute this single statement.
 I have warnings, oh no! Ok, but how do I see the errors?
This is the worksheet. The commands run here will run very similar to how they would run in SQL*Plus. So knowing this, if we change-up the process a little bit, we can start to get better feedback from SQL Developer.
Add ‘show errors’ after the create or replace, and use F5 instead of Ctrl+Enter. This will run the entire script, and ask Oracle to show us any errors for the session.
 A little better, but not as good as it gets You might be wondering why the line number is off. Oracle reports back a problem on line #4. If you’ll notice our program starts on SQL Developer worksheet line #2, but Oracle database only receives the actual statement, so you can do the math here to figure out the actual problem lies on line #5.
Viewing Errors in the Explorer
The current errors for the objects can also be viewed in the database explorer. Navigate to the object and open the ‘Errors’ panel.
 Viewing Errors in the Explorer Working in the Procedure Editor
As the name implies, the Procedure Editor is for developing and debugging your PL/SQL code. The full power of the IDE is realized here. Ok, so how do you get started with a Procedure Editor instead of a Worksheet?
In the object explorer, right-click on ‘Procedures’ and select ‘New Procedure.’ This will open a new Procedure Editor with the default procedure template code. Now when you compile, you’ll get much better error display support.
 Just in case you've never seen a PL/SQL Error, Here's One If you have a large program with several errors you can use the right gutter scroll bar area to scan for errors (denoted in red), then mouse-over to get the error message text. If you are looking at the feedback in the ‘Compiler’ panel below, you can double-click on an error message. This will move your cursor to the linenumber, curpos.
If you are working on a large script with multiple PL/SQL objects, I recommend using the Procedure Editor to perfect your procedure code, then copying that code back to your script and running that in the worksheet. Just don’t forget to add the ‘SHOW ERRORS’ line at the end.
Disclaimer: This post was written with Oracle SQL Developer v3.1.
I’m going to go out on a limb and guess that most people who use SQL Developer do not realize it has a fully-baked data modeling tool built into it. It’s not like we hide it, but we kind of hide it. To open the Data Modeler inside of SQL Developer, navigate to the View menu, and activate the Data Modeler Browser, like so -
 Running the Data Modeler inside of SQL Developer Once opened, you’ll see a new ‘Browser’ panel beneath your Connections. You can use the File – Data Modeler – Open dialog to open any of your models. Or you could create a new physical model by importing a ‘Data Dictionary.’ This is code for, ‘reverse engineer an existing Oracle database.’
Your model will then be displayed in SQL Developer.
 Why ALT+TAB when you can do everything in your IDE? If you’ve gone through all this and you still don’t see your model diagrams, right-mouse-click on the model in the browser tree, and select ‘Show Diagram.’ This will activate the diagram in the SQL Developer interface as a new document.
Reporting for duty!
At some point someone is going to ask your for a ‘data dictionary report.’ You might think you know what this is, or maybe you have seen one before. In my experience, it’s 100% necessary to ask and confirm with your end-user EXACTLY what they mean by a ‘data dictionary report’, because everyone seems to have their own variation of what that means.
According to Wikipedia, IBM defines a data dictionary as
A data dictionary, or metadata repository, as defined in the IBM Dictionary of Computing, is a “centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format.”[1] The term may have one of several closely related meanings pertaining to databases and database management systems (DBMS):
- a document describing a database or collection of databases
- an integral component of a DBMS that is required to determine its structure
- a piece of middleware that extends or supplants the native data dictionary of a DBMS
Again, ask your requester exactly what they are expecting in their report. I’m guessing that most of the time SQL Developer will be able to deliver on most of your requirements, for free!
New for v3.1!
Version 3 has this feature, but it’s limited to XML reporting and doesn’t allow for SubView filtering. To download v3.1 and read about all the new features, please visit our OTN page.
Ok, with that disclaimer out of the way, let’s get to building our HTML report!
With your model open and active in SQL Developer, go back to the File menu, and navigate to the Data Modeler – Reports item.
 Pick your poison, I'm going with HTML today You have a few options here. I enjoy working with sub models (which are known as ‘SubViews’ in SQL Developer.) When I generate a report I have the option to only include objects that are in a particular SubView. Hint: Subviews are very handy when dealing with very large models. They allow you to effectively filter your models and reports.
Once you click the ‘Generate Report’ button it will build out the HTML files.
Here’s a quick preview of what that looks like
 Share your models with folks who do not have SQLDev or access to the database There’s a quick look-up filter box. Type in your table name and the list will auto-filter to that text. Most folks deal with very, very large models – we’re talking many hundreds of entities and tables. Clicking on the table name auto-navigates you to the appropriate spot in the dictionary report.
If your end users also want the actual diagram component included, use the File – Data Modeler – Print Diagram interface. You can then include the image or PDF file to your HTML landing page.
I’ve uploaded my HTML report for you to go play with.
Hehehehehehe, he said ‘duty’
New for version 3.1, currently available as a EA download, when defining the queries used to populate views, the modeler can generate JOINs using either Oracle’s or the standard ANSI compliant syntax.
Let’s step back a second.
When working with a VIEW defined in a relational model, the modeler has twp choices for defining the driving SQL statement that is used to populate the contents of the view. They can code it by hand – ideal when it’s a pretty simple SELECT * FROM with a few WHERE clauses, OR they can use the visual query builder and draw it up.
When going with the visual design method, JOIN clauses by default will use Oracle’s database syntax. We’re Oracle and we like our syntax. But, sometimes folks have silly (mostly a joke, mostly) rules in place that require as generic SQL syntax as possible. Maybe they find it easier to read, or maybe they think it will make a migration easier down the road. That’s not really important, however it is important to realize that the Modeler supports both methodologies.
You can access the visual query builder support directly from the View editor -
 Click vs Type, will this battle never end? For this example I have done a reverse-engineer of the SCOTT, HR, and SH schemas. One of the views here is HR.EMP_DETAILS_VIEW. Here’s the default SQL statement for that view:
SELECT e.employee_id,
e.job_id,
e.manager_id,
e.department_id,
d.location_id,
l.COUNTRY_ID,
e.first_name,
e.last_name,
e.salary,
e.commission_pct,
d.department_name,
j.JOB_TITLE,
l.CITY,
l.STATE_PROVINCE,
c.COUNTRY_NAME,
r.REGION_NAME
FROM employees e,
departments d,
JOBS j,
LOCATIONS l,
COUNTRIES c,
REGIONS r
WHERE e.department_id = d.department_id
AND d.location_id = l.LOCATION_ID
AND l.COUNTRY_ID = c.COUNTRY_ID
AND c.REGION_ID = r.REGION_ID
AND j.JOB_ID = e.job_id
With the click of a button..
Automagically translate Oracle joins to ANSI or vice-versa
With this ‘magic’ button, I’ve saved myself probably a few minutes. And that’s what I’m after – a tool that can save me time.
 Magic buttons are rare, appreciate them when you find them And here’s our new code
SELECT e.employee_id,
e.job_id,
e.manager_id,
e.department_id,
d.location_id,
l.COUNTRY_ID,
e.first_name,
e.last_name,
e.salary,
e.commission_pct,
d.department_name,
j.JOB_TITLE,
l.CITY,
l.STATE_PROVINCE,
c.COUNTRY_NAME,
r.REGION_NAME
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
INNER JOIN JOBS j
ON j.JOB_ID = e.job_id
INNER JOIN LOCATIONS l
ON d.location_id = l.LOCATION_ID
INNER JOIN COUNTRIES c
ON l.COUNTRY_ID = c.COUNTRY_ID
INNER JOIN REGIONS r
ON c.REGION_ID = r.REGION_ID
Yes, it’s more VERBOSE, but some folks just prefer this method. And now you can choose a modeling tool that doesn’t make you choose just one implementation of SQL. And yes, you can go in the OTHER direction.
Bonus Tip: Validate your Query
Once you’ve defined your query, SQL Developer Data Modeler will allow you to test the query against a live database before saving it to the model. Being able to see the data that will come back when someone queries the view will give you a bit of a confidence boost, especially if you don’t trust these new-fangled ANSI JOIN statements. You can also inspect the Execution Plans. I’ve heard rumors that using ANSI vs Oracle join syntax can impact the plans the CBO optimizer will generate, but you should test this for yourself. Remember, I’m just a simple hacker at heart.
 Testing is a dirty word, but it doesn't have to always be so hard. 
|
|
Twitter
RSS
GooglePlus
Popular Posts