My impressions from the Oak table seminar with Tom Kyte.
I knew Tomas Kyte only from the book ‘Effective Oracle by Design’ and was interested to hear from him live. The contents of the seminar did have a lot of stuff which can also be found in the already mentioned book. But I did hear some interesting new things.
Most impressive I found the way Tom was able to explain the most complex things. Having a few examples and small test cases with only using SQL plus made things very clear.
Before the seminar I had never visited his site ‘ask Tom Since the seminar I have visited it several times and I must say the answers you get there are as cristal clear as it can be.
Things I found the most interesting:
Background on the read and write consistency in an Oracle database and the way it influences the firing of before or after row triggers. Never use code in those triggers that cannot be rolled back, because you may end up with unexpected behaviour!!
I have recently used the LOG4PLSQL software for logging in a small application. I missed the possibility to change the logging level at runtime. In the production environment code changes are only allowed during specified deploy time windows, so changing the DEFAULT_LEVEL parameter in the PLOGPARAM package specification was not an option to change the logging level.
I adjusted the DEFAULT_LEVEL constant in the BLOGPARAM package to a function with the name DEFAULT_LEVEL, returning the level as it is stored in the database (In a table with global parameters for the application). To avoid a read of this table everytime the DEFAULT_LEVEL is used in the logging code, I store the value in a package variable (lv_default_level).
The package variable is updated at regular intervals (lv_check_db_interval), to avoid that changing the database parameter only will have effect on the sessions that are started after the parameter change. Continue reading