The Humble PL/SQL Dot

Like many other languages, PL/SQL has its own "dot notation". If we assume that most people can intuit or easily look up things like the syntax for '''IF/THEN/ELSIF''', that means that first-timer users might quickly run into dots and want to understand their significance.

The authoritative docs on the dots is in the Oracle Database 11g PL/SQL Language Reference, in particular Appendix B, How PL/SQL Resolves Identifier Names. As we can see from these index entries, the subject is mentioned here and there throughout the manual:

dot notation, 1.2.5.2, B.2
for collection methods, 5.10
for global variables, 4.3.8.3
for package contents, 10.5

When I was in charge of the PL/SQL docs, I rewrote that Appendix B to try and make it more helpful, to give more examples and state what kinds of problems you could avoid by knowing this information.

Today, as a PL/SQL programmer, I would go even farther in simplifying the conceptual information in plain English, and positioning the knowledge as important for troubleshooting. Something like...

Names that use PL/SQL dot notation can have many different meanings, such as a procedure inside a package, a column inside a table, or an object owned by another schema. In code that you write or inherit, you might use one of these idioms extensively. Which can make for a nasty surprise when your code stops working because someone creates a new table, schema, package, etc. with the same name as one of your dotted names. So, read that Appendix B to understand all the variations and the precedence rules.

Some additional tips I'll pass on where you can add or remove dots to get out of trouble:

If you are coming from Perl, where you use an expression like string1 . string2 to concatenate, the corresponding PL/SQL expression is string1 || string2.

When you want to refer to two items in different scopes that happen to have the same name, use dot notation for one of the references. For example, if your PL/SQL procedure accepts a parameter ID and then queries a table that has a column ID, the query won't work properly when you use a WHERE clause like WHERE id = id. Instead, write it as WHERE id = procedure_name.id.

If you are writing code for use with the PL/SQL web toolkit, you'll find the URLs are simpler and cleaner if you can keep dots out of them. That means using standalone procedures where you might normally use a packaged procedure, because the package notation would put the whole package.procedure name in the URL. You can get the best of both worlds by coding up the package as usual, then making a standalone procedure that simply called the packaged procedure.