Tech PL/SQL- Self-Managing PL/SQL

"TECHNOLOGY: PL/SQL

Self-Managing PL/SQL
By Steven Feuerstein

Follow self-managing databases with self-managing PL/SQL.

Oracle has introduced significant management automation features in its flagship database product and has described the new Oracle Database 10g release as "the self-managing database." Given that achievement and direction, I thought it would make sense to talk about how to write one's PL/SQL application so that it is also self-managing.

What, you might ask, would it mean for an application to be self-managing? Let's dream for a moment: I have built an application composed of 50 tables and 200 programs. It is running in production, but the users have a request for an enhancement. This enhancement requires a new column in table A and changes in 6 of the 200 programs. Yet 55 of the 200 programs reference table A.

When I add the column to table A, all 55 programs are marked INVALID, marking, in turn, another 72 programs INVALID. I make the necessary changes to those 6 programs, testing them thoroughly. I then recompile any invalid programs, at which time they automatically adapt to the changes in my data structures and compile cleanly. My application is ready once again for production.

Doesn't that sound very pleasant? Unfortunately, most of us would not be able to enjoy such an experience. Rather, we write our code so that the slightest change in data structures wreaks havoc in our source, not only marking excessive numbers of programs INVALID but also requiring extensive and ultimately unnecessary modifications before the application is running again.

"