Miscellaneous Tips from Bill Nigh

Over many years, I have worked as a developer and instructor who developed proficiency in HTML, Sybase, Unix (AIX), Powerbuilder, AREV and Oracle, and have worked with business users and other stakeholders to provide desired standards-based functionality. As a diligent supportive instructor,  I took it upon myself to write short notes and handouts for students. They sought to firm up understanding of concepts covered that day, to give more examples of topics, to reinforce a novel concept or powerful feature we had seen that class day. A representative selection of this content is provided here.

SQL Server and NULL handling in queries and procedures
In earlier releases of Sybase SQL Server (and its cousin MS SQL Server), you could actually code a statement like:
IF @myvar = NULL as well as the correct IF @myvar IS NULL
(notice the equals sign in first example--this is not ANSI!)

With SQL Server 6.5 and later, an environmental setting will enforce ANSI syntax, meaning that '@myvar IS NULL' would compile, whereas the earlier syntax will not!

AIX Shell Notes

How Transactions can torpedo an Application
One of the things which makes applications lose user acceptance is poorly constructed transactions. These usually cause no problems when the database is small, but they will force more and more locking and contention as the database grows in size and number of users. In order to address this problem: 1.) be sure to make transactions span only as many update/delete/insert statements as logically belong together 2.) do not permit user interaction from the client app while a transaction is pending 3.) try to put all update/delete/insert actions within stored procedures, for faster performance.

Java ENUM types
For a handy set of constants, and built-in way to return an array of that set, for boilerplate iteration, look at the enum class of Java. By convention, data members of that class have their names uppercased, as they are equivalent to system constants; the 'values' method of the ENUM type feeds you an array of the data values you assign to the enum object.

WebLogic Tips
The 't3' protocol that various processes listen on in WLS is a reference back to Tengah WebLogic before BEA bought the small San Francisco based company. This is optimized http.

In order to fire up WLS, you need to have configured an instance of a server within a domain. A domain is an administrative definition that relates to administering one or more servers on one or more machines. If only one server is present, WLS assumes it is the admin server.

WebLogic Server is a Java class that is passed as the argument to the ‘java’ command, and therefore runs entirely within a single JVM instance. Due to this behavior, it is legal to have multiple versions of it running on one box; Prasanna has several versions on his, for example, in addition to JBoss, etc.

There are useful command prompts that are provided normally that you use to get to a command line, rather than the vanilla ‘Run’ command on Windows. Using them causes all environmental vars like CLASSPATH to be set for you.

Domains are useful administrative entities in WLS, with an Admin Server and several Managed Servers. Each Server maintains its own log, with useful filtering options for recording the severity.

Username and password can be entered in plain text initially, then the file gets encrypted; WLS wants to always be the agent that gets that info and encrypts it. This is why just copying a valid file from elsewhere does not work, as it tries to encrypt something already encrypted.

The term ‘Managed Server’ can be misleading, as the only thing that they get from a so-called Admin Server is the configuration at startup; once they are started, they can run indefinitely, even if the Admin Server is down. The access to config info from the Managed Server end, however, is always read-only.

The Admin Server is a single point of administration for a given domain, but NEVER a single point of failure.

The start script for a particular instance has arguments that identify the URL of the admin server. This is an optional argument.

Domain-level logging is available on the Admin server. Managed servers propagate (filtered) contents from their local logs to the domain log. This is not queued in a hard queue; if the admin server is not running, the log is incomplete. REMEMBER, however, that the complete logs are ALWAYS maintained locally for each server; diagnostics would typically use that local log anyway.

Domains can be created from templates. Templates are created with a useful wizard, from config_builder.cmd or .sh; one template is provided by default, since you need a template in order to create a template. You can include resources, such as files, and Database scripts, in the domain (via the template you are building).

Unix Regular Expressions
A 'regular expression' is a Unix term for a fairly common concept, one seen in DOS and in Windows search programs, namely the use of 'wildcard' characters to specify matches based on partial name or text line contents, and to extend that with 'or' and 'and' capabilities. Regular expressions use characters such as '^' to indicate: 1.) 'starts with' at the beginning of a regular expression 2.) '(if within the '[ ]' characters, means 'not', to negate a range of values, like the SQL NOT IN (SELECT...

How to create an empty Unix file most easily
If you use the touch command followed by a new filename, you will be able to create an empty file, in any directory you specify. Otherwise, touch can be used to update file attributes.

Subtle 'Gotcha' in Dreamweaver
I love Dreamweaver, but like any development tool, it has some quirks. Here's one (a minor one admittedly):
If you are only modifying the title attribute of a page, the page will not be considered modified initially. You need to click somewhere else after modifying it in the edit line.

Recent Dialogue on array basics and persistence in TrueBasic
Hi Bill, Thanks for offering to help! I'm taking a programming final for my Computer Science class in a few days and I'm having trouble with arrays. I think I understand the point of using arrays but I'm not really sure how to write them into a program. In the past, I've had trouble with actually getting the data to store from one execution to the next. I've usually been using loops in conjunction with arrays, not sure if that makes a difference. Any help on explaining this? I'd really, really appreciate it!

Thanks, Cat

Bill: I think it's important to understand what a 'session' is, as it is related to a particular login at a moment in time, and implies 'statefulness'. This simply means that data in variables is reliable, but ultimately any session ends, and the data, in whatever format, simple data type or array, either has to be 'persisted' or stored, or else it just evaporates. This is also what an 'instance' is like, namely the 'statefulness' aspect. Keep asking me questions. I'm glad to help.

Cat: Ok, that makes sense. Now, in the actual writing of the program, I know I need a DIM statement, with the variables and dimensions, but then how do you actually tell the program to associate a value or string with one number within the dimensions set? How would you write that out?

Bill:This is where my actual syntax knowledge is lacking, but some things are the same across arrays in any language I've seen. One common theme is addressing with a subscript. The actual way you automate the addressing is the main difference, of course. The answer to me starts with identifying the source of the values. Is it being keyboarded? Is it coming in from a file? From a database through a connection?

Cat: It's keyboarded.

Bill: You want to set up a variable and a loop. The variable is used to slide through the array using indexing. Indexing syntax typically supports use of a variable to subscript/address the elements in an array.
Initialize the variable OUTSIDE loop, above it, and:
Prompt for input
Get the string
Increment the index var
Use var's value as subscript
Loop until some condition is met.

Outside the loop, you have the array. You can count the number of elements in the array and do a for..next type loop and subscript to address one element at a time, show the element (if you want), loop until no more elements

Cat: Oh! I get it! Awesome, that helped a lot! Thank you so much!

Bill: Good; glad I was able to break that down for you. Not sure if your language uses 0 as first index value, or 1, but when you initialize that var, set it to one less than the desired starting value at top of loop. Or, you can use the starting value set above the loop, and after the processing of input is done, increment the var by one. Good luck!

Workload balancing
It is possible to perform the same work on either the client or the server in a client-server installation. Those operations relating to sorting or filtering should ideally be done on the server, since it can be optimized to do this work much more easily than the client application. For example, the query optimizer on RDBMS products can inspect the incoming SQL query and determine the most efficient way to satisfy the query, using indexes.

Just because you do the initial sorting in the query doesn't mean the user wouldn't benefit from being able to shuffle and temporarily ignore (filter) data records on the client application, so try to include this functionality in the early versions of your application, and you will win user acceptance more readily.

Caution - Datatypes make cross-platform DDL problematic
If you are designing a database and you have not yet identified the datatypes available to you from that database product, you'd better take a look. For example, I was surprised to discover that the datatype of 'date' did not exist in SQL Server and Sybase! Rather, a compound 'datetime' datatype is the only choice. This can mess you up if you are expecting to store effective date and effective time in separate columns. Overview discussions of SQL are appropriate for those who are trying to design in a platform-independent style.

Shell programming can be easy to learn
The simplest type of shell program is a batch of commands typed into a text file.  Shell programs are not compiled, and are run by simply typing their name.  In addition to standard interactive command sentences, looping and conditional execution of blocks of code are available.  Combining the various utilities of Unix, the file expansion and the Regular Expression capabilities of the shell, and the procedural language, you have a potent combination

Unix Files
In Unix, a file can be an executable program (which would comprise shell scripts), a log, or some type of text, often comprised of 'records', or lines whose 'columns' are set off by tab or space delimiters.  Using grep and awk, you can process these files, doing powerful searching, filtering and modification operations.  Note that modifying the actual file is most typically and safely done by creating a minimum two-line script (shell script) which will create a new file from the processed 'source' file, then use an 'mv' command as the next 'batch' instruction, to rename the new file to the old name. Files and directories are accorded 9 (actually 12 with some versions) permissions flags, for read/write/execute permissions on the owner, group and public level, also known as user, group, others (ugo).  Using the initial of the set, and the permission being extended, initially granted or reduced, you use the 'chmod' program to accomplish your task, as:
    chmod 777 myprog
to give all operations to all classes of users.

Files can be hidden by using the period as the first character in the name.  Names can conceivably consist of printing AND nonprinting, 'control' characters.  Beware.  Some commands such as AWK are not case sensitive, but assume that something is case sensitive and you will not go wrong.  Files can be created by 'piping', where the output of a program can be sent to a file.  Files such as those in the dev subdirectory are actually devices, but you can read from them and write to them---pretty cool.

Copying or moving Unix files to the current directory
When you use the cp or mv commands, and you are already in the target directory, use the '.' (period) character at the end.  This is the way to say 'current directory'.

Timing a long-running job
You can use redirection and batching to note the start date and time for a process, run that process (with suppress output option) and note the date and time when the process finished.    Here is a sample:
   echo "Starting processing at:" > report.txt ; date >> report.txt ; myprog1 > /dev/null ; date >> report.txt  
This single command line 'batch' runs the whole set of commands.
Note:  If you use the 'batch' command, you can then put these commands (on separate lines) into a stream which you terminate with Ctrl / D.  The commands then get scheduled, and a likely time for batch completion will be shown.

The Unix 'awk' utility
If you want to process a file (normally text, to include non-English character sets), then you should know about awk, named from the initials of its creators.  'awk' expects to open and process, line by line, as many files as you pass to it (see 'metacharacters' to have shell provide directory search returns).  The file(s) will be processed in a loop, where every line (as determined by a 'newline' character) is parsable into columns, and can be searched using Regular Expressions.  If a line meets the criteria fed to awk, then you can print it in whole or part, with literals, to an output stream and capture that in another file, or pass it to a pipeline.  'Pre' and 'Post' loop syntax options are found, as are built-in and user-defined variables.

Outer Joins
Under fairly natural conditions, database joins can appear to 'lose' data. What happens is that if data in one table does not match similar data in another table, even the existing data in the first table is suppressed, because joins are based on matching data values in related tables. To show 'outliers', (but only in one direction at a time), use an OUTER JOIN.

IIOP - a robust, stateful cousin to http, for object-to-object messaging
When an HTML page is requested and delivered, any connection between the browser and the server is ended.  Each request is a separate connection, so the server has no 'memory' of what you transacted with it last time.  This 'stateless' nature of http is why it cannot support the 'memory' of who the current user is, (unless cookies are used, which can be blocked).  Enter Internet Inter-ORB Protocol.  This utility provides persistent connections between processes, and is one way that Jaguar CTS talks to its PowerBuilder, VB, or HTML clients

Frustration-saving vi tip
Getting a lot of beeps when you work with vi? The characters you are typing are not appropriate for the current mode of operation of vi. Safest way to recover is to press the {Esc} key. This will take you out of any special mode such as insert, and return you to the starting mode of vi, which is related to commands, rather than actual text creation/editing. From there, you can use the hjkl characters for cursor movement, and aAiI for editing initiation.

Three modes of operation within vi
There are three modes of operation within vi.  The first is the command mode, which I find misleading, because it also relates to cursor movement.  This is how you start vi.  The next mode of operation is EX mode, when you can enter more powerful commands such as w, to write out changes from the buffer to a filesystem file.  From the EX mode you can properly exit the vi session.  The insert mode is for editing.  Note the overwrite capabilities here, and the special significance of the '$' character. 

Pipeline tips
You may think of a pipeline as an assembly line, with each piped utility call adding (or maybe not effecting) the 'stream' of data passing down the pipeline.  Utilities such as grep and awk may filter out lines of data based on criteria you pass, while sort and pr adjust the display of the data set you're processing in the pipeline.  The assembly line metaphor helps us remember the 'additive' nature of the pipeline.  This additive behavior suggests the following tip:  Use as many invocations of grep, awk, sed, tr and others as needed.   A close alternative to the pipeline is the batch of commands which uses temporary files (see /tmp directory) to achieve the same effect. Use any combination of the two to maximize your possibilities (see 'cron' processing).

AIX Command history
If you type ( or have in your .profile ) the command:
    set -o vi
then you will be able to use vi commands to modify any previously typed sentence. Your 'command history', if you will, is stored in the .sh_history file, and is therefore persistent beyond any given session. To invoke the editing options, press {Esc} then type k. This will bring back the last command. Use k to move back, then j to move forward through the 'stack'. Once you find a useful command, you may use the standard commands to move along the line to edit the sentence. Press {Enter} from anywhere in the modified sentence to re-run the command.

Command History editing/display
You may display your command history with the 'history' command, which is an alias for fc -l, the 'fix command' with list option. If the word 'history' does not work, incidentally, type:   history='/usr/bin/fc -l'.

Type man fc at the shell prompt to see full detail, including the 'r' command of history to re-run a listed command string by its number. You may also try: set -o vi or set -o emacs or any editor program, to modify your command history as well as recall and re-run it.

One advantage of Views
A view can cover over the complexity of a data structure, not just the complexity of syntax. For example, certain types of expressions may not syntactically coexist under all circumstances, such as aggregates and scalars. Create a view of a GROUP BY query, and the rows which appear to be in that 'flat' structure are themselves summary data. This can then be used in a join to show such calculations as percent of the total, greater/less than group average, others.

Select Into/ Bulk Copy option with SQL Server
A procedure which builds a table on the fly with 'insert into' may fail based on settings in the database.  The Insert Into/ Bulk Copy option must be on.  (Later versions added a parallel sort option). No matter whether its two or three choices, they are either all off or all on.

Search capabilities in Unix
In Unix, there are a number of powerful utilities which can be used to search for files, directories, or other objects based on their name and external attributes such as last date updated, permission level, and owner, and there are utilities for searching within files for specific content. A lot of these utilities are native to Unix in general, because C programmers and shell programmers created these features largely for easier code maintenance.

The 'find' command searches for files and directories which meet search conditions, and supports an AND/OR capability for complex search conditions. Once you find any files/directories which meet your search conditions, you have the ability to indicate an action which is to be performed on those items. Such actions can be automatic, or you can ask your 'find' operation to prompt you, to permit selective application.

The 'grep' and 'awk' programs can comb through files for lines (always entire lines by default) within a file which meet search conditions. 'awk' supports a powerful procedural and high-level programming language, with print and extract operations, and is so powerful that some people have created compilers using awk. Both grep and awk also can be put into a 'pipeline', (i.e., they can be 'fed' the streamed output from other utilities).

How to handle annoying duplicate characters in TELNET
If you are getting duplicate characters while you enter commands in a Telnet session, (example:  user123 is displayed as uusseerr112233), then use the appropriate interrupt command to return to the Telnet prompt, if necessary, then type unset LOCAL_ECHO.

Sybase SET SHOWPLAN Tips
When using SET SHOWPLAN ON, you can also use SET NOEXEC ON.  (Despite the way the phrase reads, when NOEXEC is 'on', then the actual query you are measuring will not execute).  Sample: SET  SHOWPLAN ON, NOEXEC ON
If you are trying to see the query plan for a stored procedure, you cannot EXEC it and see any detail.  Instead, you need to copy the source code which created the sp (without the CREATE PROCEDURE and AS statements) and paste it into the ISQL or whatever tool you are using as a client app. This may require that you trump up the code to account for parameters in the signature of the procedure you are testing.

How to process large sets of files automatically
 The Unix 'find' command supports the ability to do rich searches for directories and files.  In the files area, you can run a 'find' operation, much like what you get in MS Windows now.  However, Unix goes Windows one better by permitting you to take all files which meet a condition, then passing them through some other process, such as awk or sed.  Check it out.

Make your Sybase life easier with centralized, simplified ownership of database objects
Each time a stored procedure is executed, the objects which it references (other sp's, views, etc.) are checked to see whether the user running the sp has permission to execute / select or otherwise use those referenced objects.  This can create a nightmare of 'surprises' as previously good triggers, procedures, etc. fail when they are next referenced.  Have a single individual (dbo) own all objects, and make good use of groups and aliases to control this area of possible complexity.

Unix: Write, message blocking and WHO
The WRITE command lets you send messages to a specified user elsewhere on the Unix system to which you've logged in.  However,  that recipient may have used the command mesg n to block messages.  To determine whether this is so, use the command who -T, which displays an additional column.  If that column has a minus sign, messages are blocked to that user.  (Tip:  use the H switch also to see column headings, as: who -TH, remembering that these commands are case sensitive.

Unix: The 'at' command
To run jobs (commands) at a later time, use at. The at command lets you specify commands to run, and when to run them. You may use 'pronouns' such as today and tomorrow, specific times of day, offsets from current time, and lots of other temporal options. You may also specify which shell is to be used to run the particular job, as c for csh, k for ksh. at can be used with piping too, as in this example:

echo uuclean | at 3:00 pm January 24

If a file named /var/adm/cron/at.allow exists, only users whose login names appear in it can use at. A system administrator can explicitly stop a user from using the at command by listing the user's login name in the /var/adm/cron/at.deny file. If only the at.deny file exists, any user whose name does not appear in the file can use the at command

Autocommit attribute of a connection
If you understand that you can connect to a database with a login, and that you then have a session which may have more than one connection, then: consider the fact that the session can have a flag set which can mess up your day. Check the autocommit setting, if present. If it is 'on', or 'true', then you will not be able to support a basic logical unit of work, or 'transaction'. This may be the default, then any insert/update/delete actions you are doing are automatically committed.  There is no syntax available to 'recover' modifications to support 'undo'.   You cannot use ROLLBACK; you never had the chance.Set it to false to 'clump' DML, then turn it on when you're ready. You may use a COMMIT statement to apply pending changes in the transaction (which also releases locks-be sure not to hold locks).
AutoCommit is found in TOAD, SQL*Plus, JDBC, PowerBuilder and no doubt many other developer tools/protocols.

Two main 'flavors' of the SQL language
The Structured Query Language (SQL) has gone through two major 'versions'.  Despite its name, the SQL 92 version is the more recent of the two.  The various software vendors who sell Relational Database Management Systems (RDBMS) do not have entirely identical implementations of that standard, or its predecessor, SQL 89.  If you are shopping for specific features in an RDBMS, make sure you get enough detail.

Humongous join, or step-wise solution?
Because of locking, it is usually better to avoid having too many tables in a join. This is especially true for very popular tables. It may be better to create a temporary table in a procedure from the larger tables, where the WHERE clause is use to maximum effect. This first result can then be used in subsequent joins against the smaller or less contentious tables.

How to get detail about the current Unix directory
When you want to get some good detail about the current directory, use:
   ls -d .
(That's a period at the end). The period indicates 'current directory', and the -d option provides more detail.

Warning about Microsoft DTS
If you use the DTS feature to move/copy objects between databases, beware.  Trying to overwrite a view on a target machine will cause tables to be deleted if you choose to include dependent objects (the checkbox, I mean.)  I ended up deleting a number of production tables, before I got suspicious and cancelled the job.  I don't know if I saved any tables, but it was an unnecessary headache.  In my opinion, a table can NEVER be 'dependent' on a view, since the opposite it true, a view being a derived well, VIEW of data in another view and/or table.

Java in the Server
To provide a dynamic way to generate HTML from an RDBMS, vendors are now weaving Java more tightly into their engines.  For example, Oracle permits the use of Java on the server side, which is not that revolutionary.  What is appealing is that the JVM runs in the same program space as the server, obviating  the need for any connection to the database server from the Java program.  Developers can code stored procedures in either PL/SQL or Java.

Chained and unchained Transactions
Sybase and Microsoft SQL Server support either chained or unchained transactions, which is not even a choice with such RDBMS as Oracle.  Sybase databases can use the SET command to permit chaining with  SET CHAINED ON.  In later versions of Microsoft SQL Server, use SET IMPLICIT_TRANSACTIONS ON.

The ANSI standard does not recognize the syntax BEGIN TRANsaction; in fact, such a statement will not even be accepted by Oracle.  Rather, upon logon, a Transaction is deemed to begin upon issuance of any DML statement, and from that point on a transaction is pending on that connection until a COMMIT, ROLLBACK or DISCONNECT statement is sent to the server. What Oracle provides to give more of a finer-grained control over small transactions (logging) within larger unit-of-work transactions is the pragma for AUTONOMOUS TRANSACTION.

What's in a name?
Can two objects with the same name exist in a SQL Server database? The answer is 'Yes or No', depending on what you mean by 'name'. The 'normal' view of an object name is one-part, as seen in a simple query like 'SELECT authors'. However, every name is finally fully-qualified. A fully-qualified name uses dot notation to define a hierarchy of objects and an unambiguous name for any object on an installed copy of Microsoft SQL Server, or its Sybase cousin, for any release, going back to at least version 4. Therefore 'authors' can mean the same as 'pubs2.dbo.authors'.

The name of an object is fully qualified by putting the name of the database, name of the creator/owner of the object, and actual object name. The special role of 'dbo' must be mentioned here, for, in a well-behaved application (one without any unnecessary ambiguity), the dbo (database owner) is usually charged, or claims for him/herself the right and responsibility for creating all objects. Here is a fairly typical isql session:

1> create view vw_titles_pubs ( title, publisher)
2> as select title, pub_name
3> from mytitles, mypublishers
4> where mytitles.pub_id = mypublishers.pub_id
5> end Msg 2714, Level 16, State 1: Server 'FTPAR1', Line 9:
    There is already an object named 'vw_titles_pubs' in the database.

In such a case, you might want to run the query SELECT db_name() to see the current database context, which can be determined two ways, and then run SELECT suser_name()

Applications in a Distributed Space
The Graphical User Interface (GUI) which is typically all the 'end user' sees, is an important part of any application, no matter how ambitious or humble. 

The ability to understand and navigate an application can be a deal breaker between users and developers, and what are we getting paid for after all than creating useable apps to solve business problems?

Note that many design approaches will separate the retrieval of data  from its presentation to the user.  This is where application servers come in.  Taking advantage of resource optimization design features, they can connect to 'back end' data sources such as relational databases, and store/maintain/update that data for you.

Check out the MVC (Model View Controller) design pattern when considering Servlet or JSP/ASP design. If using Jaguar and PowerBuilder, check out DataWindow synchronization with elements of an n-tier application, using get- and setFullState methods.

Triggers in Oracle
In addition to the user exits called 'triggers' in Forms and Reports, more 'traditional' (is that word even appropriate in this industry yet?) examples of trigger functionality are found in Oracle, and are more formally known as 'database triggers' and 'Instead-of triggers'.  The former is applied to the DML actions on tables, like other RDBMS, but Oracle also supports row-level triggers vs. statement-level (set level) triggers, and has a BEFORE and AFTER syntax requirement which specifies when the trigger fires.   Business rules regarding validation would be on the BEFORE side, whereas auditing / tracing / profiling stats would be written out IFF the DML is permitted.  A statement-level trigger on an UPDATE will precede any row-level processing, giving you two shots at attempted changes, with the same capabilities for INSERT and DELETE, of course.  All in all, given all combinations, you get 12 trigger opportunities with Oracle.

UML, XML and SQL
The Structured Query Language is associated with the relational theory of databases, and its various vendor implementations.  It is based on a rigorous model. It provides an ANSI standard dialect which controls all actions needed to maintain databases.

Using SQL you can do powerful things to data in database tables or views (views are updateable under certain conditions, even though they are merely a reflection of data stored in underlying, 'base', tables.)

SQL seems to straddle a lot of generations of client/server, n-tier, distributed applications, and will continue to be a useful career skill for decades to come, since it is available on and for essentially all platforms, certainly business desktops, and laptops, but also phones, pagers, PIM's and email.

XML is a language which lets you define data entities, and have them interpreted by an unknown 'client' program; they are presented in plain text, so could be bulky, but their hierarchical data structure and freeform nature make it possible for cooperating organizations to pass data back and forth between their heterogeneous database and application systems.

The Unified Modeling Language, which is an amalgam of several different OO notational systems, serves as an intermediate language between business owners/users and IT architects/coders.  Interaction diagrams are documented in UML and show scope, method calls, and order in which objects should be instantiated in the application program space. The UML is being taught in many univesities in the world, and since it fills a useful niche, look for it to continue to grow in importance.  It is relatively easy to get used to, and there are many IDE's which help you build diagrams.  Such IDE's often permit full round trip software engineering.  Even years ago, Rational Rose was able to produce interfaces, stubs and classes that fit into an architecture.

Useful intermediaries for migrating RDBMS data
Both PowerBuilder and Access provide easy ways to attach to multiple data sources, and move/copy data from one database format to another. The ability to massage the data on the way to the new location is supported. PowerBuilder uses both ODBC and native drivers (DBLib / SQLNet ) to talk to data sources. Access uses ODBC.

FOREIGN KEY Constraint in DDL
The FOREIGN KEY constraint is the most powerful declarative constraint there is, since it invokes so much automatic functionality.  It is used to maintain both referential integrity and domain integrity.  INSERT or UPDATE statements which try to alter or insert a value in the constrained column which does not match a primary key are rejected. 
 
Here's something you may not know about the FOREIGN KEY constraint, however:  By default, the minimal syntax for this constraint implies an ON DELETE RESTRICT clause, meaning that not only must the foreign key value match a primary key in the parent table, but also no parent row can be deleted if there are child rows present.  Two other possibilities are available as well.   ON DELETE SET NULL will actually remove foreign key value when the parent row is deleted, effectively 'orphaning' the child row.  (If the foreign key column has a NOT NULL constraint, however, the attempt to set the child row value to null will fail.  ON DELETE CASCADE will actually delete child rows as well as parent rows, a cascading type of delete across tables. Considering how the child table may be a parent to another table, this could be a profoundly powerful design choice.  There may be other options in the works, for example a possible ON DELETE SET DEFAULT which will use the default value in the column-level DDL to replace the vacated value, this, of course taking care of the NOT NULL conundrum mentioned above.
 
When NOT IN ( subquery ) fails
(Thanks to Kim Evangelista for this one: )
If you have a nested query which returns column contents from a table, be sure to eliminate nulls, or else the subquery will return null.

Two ways of promoting Domain Integrity - Decoding values in a Data Domain
If you agree that storing a short code is efficient, then trying to turn a potentially cryptic code into a more natural description ('Decoding') becomes a consideration.  If you use the CHECK constraint to limit what can be stored in a given column, you typically use the DECODE function to turn those codes into fuller narrative values. If you have chosen to do your 'runtime lookup' against another table, then you can use two ways, DECODE, and more typically, a runtime operation which uses the common stored value in a JOIN to get the longer description.  The ease and usefulness of Joins favors the use of separate tables for verification and decoding.

Searching for the underscore or per cent sign using LIKE
Since the '_' and '%' symbols are used to indicate two types of search patterns when used with LIKE, what do you do when you are actually LOOKING for one or both of those characters?  What you would do is to extend the LIKE operation and indicate any arbitrary character which is used to preceed the _ or %, as:
   WHERE BOOK_TITLE LIKE '%7\%%' ESCAPE '\'
   to mean, 'search for book titles which contain the actual string 7%'. 

Notice how the other occurrences of the % sign play their usual role.

(Note for Unix folks:  you must actually type the word ESCAPE and specify the escape character, which is not limited to the backslash)
 
Business Rules, and where to apply them in the Oracle environment
In Oracle, you may install business rules right on the table level, using ANSI-compliant CONSTRAINTS.  Among these are constraints which do column-level checking, such as (must be a positive number, must be in this range, must match this discrete set of values), validation of one column's data against other columns in the same row, uniqueness enforcement (over and above the standard enforcement provided by a primary key), and more powerful checking, such as cross-table verification and actions in triggers.  You may also use declarative referential integrity to do run-time lookups of a candidate value for a column in other tables, and to either restrict or cascade deletes based on child data.  I would argue that any business rules which can be reduced to these forms should be implemented using these features, but I would also suggest that application programs should do timely validation and error notification as well.  Note that Oracle Forms has a lot of predefined 'hooks' for validation, both standard and programmatic.

More on the Oracle References Constraint
You should always try to use the built-in capabilities of the RDBMS server, and the use of declarative referential integrity is probably among the most profound examples of how a little goes a long way.  When you say that a colunn in a table REFERENCES another table, you are requiring a 'run-time lookup' on that foreign table which denies any INSERT/UPDATE which tries to change that column's value to one not found in the referenced table. 

You can add the on DELETE CASCADE phrase to cause a 'parent-child' delete if the referenced row is deleted from the referenced table.  You may also use the CASCADE CONSTRAINTS clause during table delete to clean up reference constraints in child tables. 

There are 4 possible variations on the ON DELETE operation from the 'parent':  the implicit ON DELETE RESTRICT, aforementioned CASCADE, then SET NULL and SET DEFAULT.  Triggers can extend this functionality beyond declarative to procedural, with remarkable ease of use and bulletproof applicability.

Using NVL with Aggregates
COUNT, SUM, AVG, STDDEV and VARIANCE provide the ability to operate on a column across multiple rows.  Only COUNT has the ability to operate on entire rows, not just specific columns or column-level expressions, with its special COUNT(*) syntax. 

With the exception of COUNT(*), all other forms of aggregate expressions need you to pass them a column name or any expression of the appropriate datatype.  For example, you can't do much with SUM(last_name), for SUM of course wants to work with numeric values. Here's an example of doing a 'what if' calculation on the fly, where we want to know what the impact of an across-the-board 12% pay raise would mean on the total payroll:  SELECT SUM( salary * 1.12 ) "Projected Payroll" FROM INSTRUCTOR.  What about only those instructors in Denver (DNV)? SELECT SUM( salary * 1.12) "Denver Projection" FROM INSTRUCTOR WHERE HOME_SITE_CODE='DNV'.

By default, all forms except the COUNT(*) will ignore null (missing) values in the column they are operating on.   Assume that the instructor table in your sample database has a lot of new rows, and due to some issue with data entry at the time, none of the salaries has been filled in for the new instructors.  Over half of the rows in instructor have null in the salary column.  If you type SELECT AVG( salary ) FROM INSTRUCTOR, you will only get the average of the rows which have a value in that column.  What if you wanted to temporarily substitute a value to more appropriately 'pad' or adjust the salaries for the purpose of doing a better average?  You can use the NVL function to provide runtime substitution.  Assume that we generally believe that all the new instructors have an average of 50,000. 

Therefore you can type SELECT AVG(NVL(salary, 50000)) FROM INSTRUCTOR. By default, all aggregate functions use all values in that column (as of course determined by any WHERE clause in the query, which would limit rows based on criteria). 

Autonomous Transactions in Oracle
Oracle, with Release 8, provides a way to submit DDL and not have it disturb a transaction, to have triggers write log entries even though the DML which caused the trigger to fire is itself is rolled back, and otherwise have better control over the environment. 

Use the PRAGMA AUTONOMOUS_TRANSACTION directive in your declaration section and you can then (and in fact must) issue the COMMIT or ROLLBACK from within your program. 

Anonymous blocks with nested blocks can only compile if the pragma is in the topmost block.  If you do not issue a ROLLBACK/COMMIT before your block goes out of scope, an exception will be raised and a ROLLBACK implicitly issued by Oracle.

The value of roles in SQL Server
Logins to a SQL Server are associated with a default database at time of creation, and may also choose to log in to other databases, assuming their login gets them access to SQL Server.  Once logged in, you operate as a user of a given database.  Users may be assigned to groups, which can then be subjected to common GRANT and REVOKE behavior.  A User may have a role assigned to them, which also accords access to commands and objects.  Here is a typical listing from ASE 11.9:

sp_who output:
 name                          
 ------------------------------
 dbo                           
 guest                         
 navigator_role                
 oper_role                     
 public                        
 replication_role              
 sa_role                       
 sso_role                      
 sybase_ts_role                

(9 rows affected)

Oracle Trigger Notes
In addition to schema-level and database-level triggers based on common events such as startup, login, connect and various others, the Oracle developer can use numerous trigger events and scopes for tables, objects and views, to great advantage.  You can use a statement-level (set level) scope for pre-update to set a global variable, then access that value in row-level trigger processing on that same DML statement.  You can do things both before and after any DML (except SELECT).  You can do things conditionally based on what column(s) is/are affected, you can inspect the attempted insert or update data, and enforce  business rules, do distributed calls, post to a message queue, operate on other tables, run stored programs, use cursors, and other possibilities.  Other RDBMS's I've worked with usually have one trigger per DML operation, not before and after and different scope.  Using the INSTEAD OF trigger option for a view, you could make any DML attempted through the view get mapped to multiple table DML, to service the underlying tables/views.