I make no secret of the point that I love the webcomic xkcd and if I blogged every strip I like I would basically end up mirroring the entire comic here.
With that in mind, there is no way I could pass up posting this commic:
Next time someone asks you what a SQL injection is you can point them at this, then explain nicely.
I’m always amazed how much traffic some of my articles on the Oracle date datatype get, but dealing with dates in SQL can be as daunting for beginners as it is tedious for the experts.
Well, here’s another resource to help you sort out those pesky dates. This one focuses on the to_char function and how it can be used to get dates to output in the format you want.
This article from Don Burleson offers a couple handy examples of the to_char function, but more importantly has a table listing the options for output of the date format.
For more reading on the Oracle date format check out my other Oracle stories.
Here’s a quick and dirty method to compare the contents of a couple tables:
See all the rows in hr.employees which do not appear in oe.employees
SQL> select * from hr.employees
2 minus
3 select * from oe.employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
210 Groucho Marx
gm@lifeaftercoffee.com 26-JUL-07 ST_MAN
And the converse can be done by switching the order of the tables:
See all the rows in oe.employees which do not appear in hr.employees
SQL> select * from oe.employees
2 minus
3 select * from hr.employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
207 Jon Emmons
jon@lifeaftercoffee.com 26-JUL-07 AD_VP
208 Gwen Emmons
gwen@lifeaftercoffee.com 26-JUL-07 AD_VP
This can be very useful if you need to, say, determine what has changed between two different databases. I have been using this over a database link recently to compare tables in a development instance to the tables in a production instance.
The caveats: The two tables must have the exact same column layout. If they are close but not exact you should still be able to make this work by specifying all the fields which are the same instead of ‘*’.
If you want to exclude a field (like an activity date) you will need to specify all the fields you want to include from both tables. The types and order of the fields must be identical between the two select statements.
I describe this as “quick and dirty” because it is. You should not use a query like this in a production database without first looking at how it will affect your database. This will cause full table scans and could kill performance.
Nobody will argue that testing your code is an essential, but often neglected step to good development. Effective testing not of the whole application, but portions of it is the focus of the Oracle Development Tools User Group PL/SQL Test-A-Thon to be held Febuary, 28-March, 1 of this year in California.
Here’s how the challenge works:
After the end of sessions on the first day, you will be presented with four programs that perform typical operations—nothing exotic. Along with those programs come supporting test data, a list of tests that you need to perform, and the results you should get for each test (most will be successful, but some will fail). You will then have one hour to write a test to show which tests succeed and which fail for the programs. Your test results should be self-verifying. That is, we will not manually verify your tests to see if they worked or not.
Check out more about the contest and about the Oracle Development Tools User Group conference. While not overly active the Oracle Development Tools User Group site has some interesting content as well.
After spending a couple days picking through Oracle statspack reports for clues on what could be causing some database latency I finally got the chance to try out the new, free Statspack Analyzer from Texas Memory Systems, Inc. and Burleson Consulting.
StatspackAnalyzer.com is a FREE SITE provided to the Oracle community by sponsor companies including Texas Memory Systems, Inc. and Burleson Consulting.
…
Our shared goal is that the advice provided by this website evolves as community feedback indicates that the heuristics should be updated. If you like what you see, please let us know. If you don’t like what you see, please tell us what you would do to improve the site and which if any decision rules should be updated.
Just paste your whole statspack or AWR report into the analyzer and it will do the heavy lifting and give you custom recommendations on what areas you can possibly tune to increase performance. I had the chance to run several reports through the analyzer this week and it came up with many of the same conclusions I did in a fraction of the time.
Tools like this aren’t about to replace the DBA but rather help take the edge off intensive tasks like tuning. Check out the sample report to get a better idea of what the analyzer output looks like, or better yet try it out! After all, it’s free!