I, too, dev and maintain Oracle systems with BL implemented in SPs.

Most of the time, the code I work with operates on a handful of records per transaction; bulk operations require entirely different tradeoffs and I don't have much to offer here.

All of the problems I've seen stem from the understandable urge to treat the database as a giant global variable. Oracle provides a myriad of conveniences to facilitate this from within pl/sql so developers do exactly that:

- In the middle of some logic and realised you needed another piece of data to work with? Just splat another inline SELECT in the code and move on.

- Want to update a table but you can't find an existing SP which writes what you need? Just splat an inline UPDATE in the code and move on.

Over time, these SQL statements littered throughout the stored procs become a complete mess. Another issue is that client code is often given unfettered access to the base tables. If those tables don't force data integrity either by constraints or triggers, then god help you if you want to update any column anywhere in your database: you simply cannot know which clients may break.

Oracle pl/sql offers both FUNCTIONs and PROCEDURES and these form the core of a number of idioms I've come to recommend:

- if your pl/sql performs any side effects, it's a PROCEDURE. End of.

- FUNCTIONs never contain side effects (or use OUT parameters).

- PROCEDURE status is "toxic": if a FUNCTION calls a PROCEDURE, it must also become a PROCEDURE.

- client code never accesses base tables. Wherever possible, try to provide access through a package API: instead of SELECTs, call functions which return REF CURSORs, instead of UPDATEs and INSERTS, call PROCEDUREs. These mechanisms are all supported by both jdbc and .Net though you'll have to do a bit more work.

The key thing is to put your BL in functions, not procedures. They're much easier to test, especially if your variables can be passed in as parameters rather than being lifted from tables via the seductive inline SELECT option.

PROCEDURES are always tricky to test. Try to keep them as simple as possible and try to minimise conditional logic leaking into them.

Data integrity is difficult to get right. Oracle provides table constraints and triggers but beware of putting BL into either of them (the dividing line is often blurry and the risk is that your future applications may be overly constrained whilst your existing ones may come to rely on leaky assumptions if you relax the integrity guarantees). I've not seen constraints or triggers used to great effect anywhere, to be honest. Most shops avoid them, it seems.

The easiest way to stay on top of data integrity is to force all updates through higher level SPs. Don't let clients touch base tables and, instead, force them to call facade packages. Write one facade package per client - Oracle will mark the package as invalid if you modify anything in the execution path of said package and this provides a valuable tool for impact analysis.

CI is nearly impossible in a live database unless you're running a version of Oracle with Edition Based Redefinition. The only alternative I can think of is pulling your logic out into middleware libraries.