Wednesday, December 21, 2005

Best Of OracleBlog

I got named in ComputerWorld's "Best IT Blogs on the Net." Granted, only in the "buffer overflow" section, but still - that's pretty cool!

Maybe it is another consequence of the Thomas Kyte Effect?

Anyway, I feel very fortunate for having this new audience, but I feel bad for having nothing really intelligent to say. So I thought I would link to some of my favourite earlier posts, and some of my most popular "hits", to give you an idea of what I like to chat about.

To my more frequent visitors like Doug, Eddie, Laurent, Gary, Peter, David, William, and so on, as well as any lurkers, I would appreciate hearing your picks, too.

Here is the brief list of my highlights:


I posted a few articles on this, because there are quite a few errors casual database programmers may make in their misunderstanding of NULL and how its treated in Oracle. To put it bluntly, NULL is NOT nothing! If ever I write a proper article for publication, it will likely be on this topic.

NULL vs Nothing:
NULLs in Oracle:
Using NULL to exploit the COUNT/NULL feature:

2. Best Practises

I like to blog a lot on what I feel are best practises for database programming. Please allow me to pick three of the most general of the bunch, where I have promoted Views, Constraints and Packages:

Use Views:
Use Constraints:
Oracle Packages:

3. Advanced Concepts

Occasionally I blog on a technique that would only come to the attention of someone who has used Oracle to solve a more complex problem. Here is one example of that.

Pivot and Crosstab Queries:

4. High-Hit Posts

There were two posts that, for various reasons, seem to have higher hit-counts than the others. Here they are:

Analyzing Query Performance:

5. Putting it all together

In "Oracle by Example" I brought together many concepts into one.

Throughout my blog you'll also see book reviews, top 20 lists, and lots of little tips and tricks I discover. As you'll note, I appreciate any feedback, and I get an email if you leave a comment even on an old post, so please feel free to do so.

I would also like to direct my new visitors to the blogs I enjoy the most. You can see the list on the right-hand side, but other than Tom Kyte's, which you have surely already visited (, I would like to draw particular attention to these two:

Doug Burns:
Eddie Awad:

I enjoy ALL those blogs, but if you only have time for a couple, please check those out.

Warning to Doug and Eddie: You might experience the first ever Robert Vollman Effect!

Tuesday, December 20, 2005

The Thomas Kyte Effect

Good grief!

I saw a spike in my statcounter and I checked it out to see if I got spammed. Instead I am the newest beneficiary of the "Thomas Kyte Effect."

Well, strictly speaking, the Thomas Kyte Effect is when your blog is referenced in a blog article of his, but in my case I was simply added to his list of links. And then bam - several hundred hits today.

With all due respect to Doug, Lisa and Eddie, in one day I've got more referrals from his blog than everyone else put together.

I told Tom we could "cash in" on the "Thomas Kyte Effect" with a few well-chosen ads, but sadly he wouldn't go for it.

Well, as long as you're all here, I would like to say two things:
1. Welcome to my blog!
2. Please look through my archives, some of my more interesting reads are in July/August.

Comments are very welcome, even on older posts (I get auto-emailed when someone posts something).

Also, I like to provide something useful in every post, so here it is: a link to an article by Steven Feuerstein about how to hide your code:

20 Beginner Oracle Questions

Continuing in my newfound tradition for lists of 20, here is a "cheat sheet" of 20 common beginner Oracle questions.

Before we begin, one simple warning. Very few of these are complete answers, consider them pointers or starting points or else the incomplete understanding could be dangerous.

Edit: This has been highly edited since its original version.

Oracle General:

1. What are the key environment variables?
Among many others, your home and instance:
export ORACLE_HOME=oracle_home_dir
export ORACLE_SID=instance_name

2. How do you shut down or start up an Oracle instance?
Must log on as a sys user:
sqlplus sys/***@instance as sysdba

3. How do you start and stop a listener?
lsnrctl status
lsnrctl start
lsnrctl stop

4. What are the key Oracle files?
They are in network/admin folder.
tnsnames.ora: list of database connection information (client/server)
sqlnet.ora: communication parameters setup
listener.ora: list of databases to listen for on this machine

5. How do you connect to the database to execute queries?
sqlplus user/password@server_instance
On a default system, sometimes you can use scott/tiger.

6. How do you see the errors from your recently created view/procedure?
show errors;


7. How do you output a line from PL/SQL?

8. How do you get the current date?
SELECT sysdate FROM dual;
SELECT systimestamp FROM dual;

9. What are some other syntax considerations?
Commands must end with a semi-colon;
Strings must be single-quoted


10. How do you show the structure of a table?
desc table

11. How do I re-execute the most recent query/command?

12. How do I see my most recent query?
l (for "list")

13. How do I see the PL/SQL procedure output?

14. How do I execute a SQL file?

More Advanced:

15. How do I see who is currently connected?
SELECT username, program FROM v$session WHERE username IS NOT NULL;

Or, courtesy of a colleague of mine, something like this:

SELECT p.SPID "OS_PID", to_char(s.SID,'999') SID, s.serial#,
SUBSTR(TO_CHAR(s.logon_time, 'DD Month YY "at" HH:MI:SS'),1,30) "LOGON TIME", s.program, s.machine
FROM v$process p, v$session s
AND s.username IS NOT NULL
ORDER BY s.logon_time;

16. How do I find all invalid objects?
Query dba_objects for status = 'INVALID', something like this:
SELECT owner, decode(object_type,'PACKAGE BODY','PACKAGE',object_type) OBJECT_TYPE, count(object_name)
FROM dba_objects WHERE status = 'INVALID' GROUP BY owner, object_type;

17. How do I recompile invalid objects?
Must be logged in with privileges, and use this:

18. How do I compute table and index statistics for a schema?
Answer from Steve Ensslen:
10g: Don't, it can be set up to analyze itself.
Pre-10g: Must be logged in with privileges:

19. How do I analyze the performance of a query/procedure (query plans, index choice, etc)?
Many ways, one way is SQL Trace with TKPROF, which I have explained here:

20. How do I tell which database am I in?
select name from v$database;
select instance_name, host_name from v$instance;


21. How do I set up an Oracle client?

22. How do I get data into and out of Oracle?

Monday, December 19, 2005

20 PL/SQL Coding Tips

Gary pointed me to a recent AskTom thread that contained a list of PL/SQL Coding Practises. The ensuing discussion added a few more, and I'd like to throw in a few of my own as well. I also think most of these tips are useful for other languages, but this is first and foremost an Oracle developer's blog.

Here they are, with the order and wording slightly revised in some cases (removed the biblical undertones for politically correct reasons), and my comments after each one. Each of these could be a separate blog on their own, but I restrained myself to only brief comments.

Design (Pre-Coding):

1. Ask Why

A popular philosophy in the Oracle blogging community, and championed by Tom Kyte, is the notion of asking why. Why is this code being written, why is it being done the way that it is, etc.

From Tom Kyte's Blog:

2. Gather requirements thoroughly

Based on the experiences of many, gather all the requirements and do not begin anything until all parties have agreed on what the end result will have to be in order to be considered successful.

3. Design, including for scalability and security, first.

With the exception of quasi-prototypes and small proofs of concept, no coding should begin until it has been designed. This is the time to think about scalability and security. Those aren't issues to tackle after the fact.

4. Set up proper testing/development/debugging environment

I find that developers are far more effective if they have a reliable, accurate and convenient environment for testing and debugging. Also, it is usually better to set that up first, rather than patching something together on the fly.

5. Use source control

"Consider source control non-optional, and a factor on which your job depends." - a former director of mine. That simplifies your decision, doesn't it?

6. Choose the right tools

Notepad and SQL*Plus might work for you now, but for a larger project you might want to consider a different IDE. You'll also want to look at source control systems, and code libraries.

7. Write test cases before coding

It seems like many people agree on this rule, but consider it a fantasy because how often do you see this done in practise? Ideally you would do this right after #2 (gather requirements thoroughly). That way you can say "when these tests pass, you are done."


8. Check and handle errors

Up front, decide a common way of handling errors in the EXCEPTIONS block. You can figure out what works best, customer error types, an error table, an error file, whatever works as long as you are checking and handling all errors.

For starters, check Chapter 7 of the PL/SQL User's Guide and Reference: Handling PL/SQL Errors:

9. Comment your code

This one resulted in a lot of debate on the thread. No, commenting your code is not a substitute for making your code readable, but then again, neither is making your code readable a substitute for commenting. Indeed, I agree with the one suggestion that you should write your comments BEFORE you write the code!

10. Use proper indexes

11. Maximise SQL and minimise PL/SQL

There are books written on the efficiency gain of preferring SQL over PL/SQL.

12. Instrument your code for debugging

Choose your favourite method: debug statements judiciously places throughout your code, or perhaps using DBMS_PROFILE (that is definitely a topic I'll do soon), or something else entirely. As long as you have a way to troubleshoot bugs and performance issues later on.

13. Make use of bulk processing

14. Minimise client code and maximise server code.

Generally servers are more powerful and built for this type of work. You also want to minimise trips back and forth to the server.

15. Use bind variables, not concatenation

Not just for performance reasons, but also for security (thwart SQL injection). I think Tom Kyte made his living off this topic for awhile.

I blogged about bind variables once:

16. Think in sets.

17. Use procedures/functions to name and modularize your code for reuse

Don't just create one big massive function that does many, specific things. Best to break it out into specific tasks, which can be optimized and reused. Also makes you think more about your detailed design.

18. Use unique and meaningful names

Unique names can be found easier in the code with "Find". Meaningful names make your code easier to understand. If you can't think of a meaningful name for a procedure/variable, maybe you don't have a clear idea on its purpose and you need to re-think your design.

Coding and Afterwards:

19. Stress test your code with significant data, and benchmark it

Doesn't matter how much time you spent thinking about performance and bottlenecks during the design and implementation, you might still have missed a few things.

20. Perform a code review with your peers.

Code reviews are good to find mistakes, but also for knowledge transfer. I also think you take more pride in your work when you have the opportunity to share it, rather than just hide in a cubicle as an anonymous coder.

Comments are welcome, but I also encourage you to visit the thread and follow up to the wider audience there:,

Steven Feuerstein is also one of my favourite champions on good PL/SQL programming. Here is a previous blog on one of his articles on refactoring (the link to which is within):

Thursday, December 15, 2005

Oracle and Perl

I work with several different databases for several different clients on several different operating systems, often with only remote access to a shell (as opposed to a desktop). Therefore I like to become familiar with very common and simple technologies, like vi, sqlplus (for Oracle) and Perl.

Writing my useful tools in Perl is very handy because I can run them anywhere, and they can work for all databases just by having a switch on the connection string. But of course today's example will focus on Oracle. Perl is also very good at handling and manipulating data: perfect for database utility scripts.

There are many different ways to write code in Perl. Some refer to Perl as a write-only language because it is a lot easier to write than it is to read. But I'll keep my sample simple.


use DBI;

# Get a database handle by connecting to the database
$dbh = DBI->connect("dbi:Oracle:host=servername;sid=dbname",
'scott','tiger', {RaiseError => 1, AutoCommit => 1})
or die "Can't connect to database $DBI::errstr\n";

# Put together your query string
my $sql = 'SELECT * FROM emp';

# Instead you could do $dbh->do($sql) or execute
$sth = $dbh->prepare($sql);
while (@rows = $sth->fetchrow_array()) {

# You can access a specific field like this: $rows[0];
print "@rows\t";
print "\n";

# If you did an update, you could $dbh->commit()
# or $dbh->rollback() before disconnecting

For more information, here is a quick and dirty FAQ:

Here are some really good on-line samples

There is a book on Oracle and Perl that described a bunch of Oracle DBA utilities written in Perl. I have not read it myself, so check it out before purchasing (and let me know what you thought).
"Perl for Oracle DBAs" by Andy Duncan and Jared Still

This page is powered by Blogger. Isn't yours?