Applications often have the need to have selection lists present and/or limit the possible choices for entry in a field. Some lists need to be static, and others need to be a bit more flexible in terms of adding new values. I’ve seen several solutions to this, and having been “stuck” on 11g for a long time, I thought I’d test out a “new” way of doing it using a composite foreign key and hidden columns.
During a session on Oracle OpenWorld I had a pleasant time watching Keith Laker use LiveSQL.oracle.com extensively to walk through a tutorial on SQL pattern matching. Very powerful indeed! Both the pattern matching and the way the tutorial was built up.
So, having used it a little bit before, I thought I’d share an example of the in-database archiving functionality in 12c with my team, using Live SQL.
I first jotted down an example on my own, since I like to work through stuff on my own to explore caveats which sometimes are not obvious in the examples. I was surprised when my example didn’t work and spent a bit of time going through documentation.
I also tested an example from the fabulous library of Tim Hall‘s blog posts – and that didn’t work on Live SQL either.
However – both examples worked flawlessly in my VirtualBox database.
Going through the Help-pages of Live SQL i realized that this functionality probably isn’t supported due to session privileges. In fact – I found an example, added by Oracle, a couple of weeks ago demonstrating in-database archiving, where I found the following comment:
Note that the WHERE clause has been added for demonstration purposes in Live SQL. Outside the Live SQL environment the WHERE clause would not be needed because the ROW ARCHIVAL VISIBILITY session setting can be determined.
So – bottom lines:
- nice theory... 