Weblog for the AMIS Technology corner
Archive for September, 2005
Pop-quiz: VPD policy that depends on a table with a policy…
Sep 26th
What happens if the policy function on table A returns a predicate that refers to table B and table B has its own policy. For example: in a Master-Detail situation, we have a policy function on the Master table. We also want to enforce that a user can only see child-records from the masters he or she is allowed to see. Does the policy function on table A also kick in when table A is referenced from the policy predicate on table B? And as which user is that policy function executed – the current User or the owner of the Policy Function?
This may look like a merely theoretical exercise to you, but it is something I am actually running into at my current project. So I need an answer. Read the rest of this entry »
Select Trigger in Oracle Database – introducing Fine Grained Auditing
Sep 26th
Does Oracle provide a SELECT trigger in the same way that we have INSERT, UPDATE and DELETE triggers? No, it does not. However. It does offer something that comes amazingly close to a create trigger emp_on_select_trigger before select on EMP statement. It does that through the Fine Grained Auditing framework, implemented in the DBMS_FGA package. This package allows us to define a PL/SQL procedure, stand-alone or inside a package, that is called whenever a SELECT statement is executed against a certain table. Read the rest of this entry »
Data Profiling with Oracle Warehouse Builder 10gR2 (article in Dutch)
Sep 23rd
The 10gR2 or Paris release of Oracle Warehouse Builder is attracting a lot of attention. During Oracle Open World 2005 of course, and in a number of Blogs by among others Mark Rittman and Nicholas Goodman. At AMIS, we have participated in the Beta Test program since December 2004 and we have published several papers and weblog articles on OWB 10gR2; see for example: Oracle’s next generation of Busines Intelligence Tools – Drake goes to Paris to cook some BI Beans. The latest article is in Dutch, to be published next month in Optimize, a Dutch Oracle Developer Magazine:
Oracle Warehouse Builder is Oracle’s product voor ETL – Extract, Transform en Load van data, uit diverse bronnen via een transformatie naar een doel-database, meestal een Data Warehouse. Oracle Warehouse Builder 10g Release 2 – ook wel aangeduid als de Paris Release – speelde de hoofdrol in een artikel in Optimize nummer 3 (zie: Over de PARIS release van Oracle Warehouse Builder – Analyse van een Beta versie voor dat artikel).
In dit artikel hebben we gezien hoe je in OWB diverse databronnen – flat file, Oracle database: tabel, view, queue, PL/SQL API, 3rd party database, ERP application etc. – kunt registreren. Ook de doelen voor ETL kunnen worden geregistreerd of ontworpen met en gegenereerd vanuit OWB. Warehouse Builder bevat visuele ontwerp-tools voor het ontwerp van Tabellen en Views maar ook een Star- of Snowflake schema voor een Data Warehouse, gevormd door Dimensions, Measures en Hierarchies.
Bronnen en doelen worden in zogenaamde Mappings op visuele wijze met elkaar verbonden. Binnen een Mapping kunnen allerlei operatoren en transformaties worden opgenomen, van Filter, Deduplicate en Join tot Match Name and Address, Pivot en Custom (PL/SQL gebaseerde) Operators. OWB kan vervolgens de voor Oracle 9iR2 of 10g geoptimaliseerde PL/SQL code genereren om de transformatie uit te voeren. Deze code kan de ETL processen Set based en/of Record based uitvoeren. Alle nieuwe database features, van Bulk Collect to Merge, worden toegepast in deze code. Ook is de code geïntegreerd in het run-time framework van OWB voor auditing en logging, fout afhandeling en scheduling. Tenslotte ging het artikel kort in op de mogelijkheden om met OWB op basis van de Data Targets complete Business Intelligence modules te ontwerpen en genereren – een ingerichte Oracle Discoverer End User Layer of een BI Beans applicatie.
Hoewel dit eerste artikel in belangrijke mate het ontwerp en de generatie van het Data Warehouse beschreef, bleek ook al hier dat Warehouse Builder een vlag is die de lading lang niet dekt: OWB kan veel meer dan alleen een Data Warehouse construeren. Gewoon relationeel database design, ontwerp en generatie van ETL processen en van BI applicaties zijn aanpalende activiteiten, maar gaan toch al een stukje verder. In dit artikel kijken we met name naar de Data Profiler in OWB, een volledig nieuw stuk functionaliteit en wel heel ver af van Warehouse Building.
To download the paper: OracleWarehouseBuilder10gR2Deel2_DATAPROFILERA.doc
JoSQL
Sep 22nd
I just read an announcement of a new release of JoSQL, SQL for Java Objects. My first reaction was a bit reserved but after a quick glance at their website I am quite impressed already. JoSQL provides functionality to use SQL statements to query a collection of Java Objects (Strings, files, any java object). Read the rest of this entry »
SQL*Plus or Report style Break Groups in SQL Query
Sep 22nd
Rob just asked me if he could easily create break groups in a normal SQL query, somewhat like BREAK ON in SQL*Plus and break groups in Oracle Reports. The idea is that certain column values – Group Labels we can call them – are only printed for the first record in a group. If we select for example all employees from table EMP, grouped by DEPTNO, and we want to display the DEPTNO for the first Employee in that Department – how do we do that? Read the rest of this entry »
Oracle 10gR2 – New Feature: the Rules Manager
Sep 21st
I noticed in the Oracle 10gR2 New Features Guide a piece on the Rules Manager. I have not yet figured out what it does exactly. But it certainly sounds interesting enough to return to later on. Perhaps it intrigues you as well:
Rules Manager is a new feature of Oracle Database 10g Release 2. It enables developers to create applications that process and respond to events of any complexity using rules and policies defined in the database. It can evaluate events using data from the application and from database tables. It stores intermediate results to quickly evaluate the next event in a long running composite event (an event made up of two or more simple events). Rules are defined using XML and SQL and can have complex conditions using conjunctions and disjunctions, and specify a set of events, time, and non-occurrence of events with or without a deadline. Event policies control how rules are processed and the duration of an event. Rules can trigger actions that are user-defined procedures running inside an Oracle Database or actions in another application.
The benefit of this feature is that rules that are managed in Oracle Database keep pace with changing business conditions and are always up-to-date; rules are easily changed with SQL and are not included in your application or loaded into a memory-based rules repository. Rules can be evaluated efficiently with the complete business context stored in your Oracle Database and data provided by your application. Event response is flexible; rules can trigger actions in Oracle Database or your application.
Additional advantages of Oracle Database over other approaches include:
* Manageability, by storing rules and event policies with your application data.
* Performance, by evaluating rules and coordinating multiple events and application threads with the full capabilities of Oracle Database.
* Scalability, by evaluating sets of rules of any size.
See also: Oracle Database Application Developer’s Guide – Rules Manager and Expression Filter for details
PL/SQL Profiling
Sep 21st
How do you trace performance issues and bottlenecks in your PL/SQL applications?
Since 8i the Oracle database has been equiped with the profiling tool dbms_profiler. It is quite simple to use: start the profiler, execute the code, stop the profiler. The profiler analyzes the execution of PL/SQL statements and stores the results for later usage. As expected, Oracle does not provide tools to analyze the data. You can do that by querying the three profiling tables yourself and have a hard time interpreting the data. Fortunately other tools like TOAD (and also PL/SQL Developer) can support you with this analysis, e.g. by presenting the data in a nice down-drillable graphic and by displaying the code along with the data. This article gives an excellent introduction.
Read the rest of this entry »
10gR2 New Feature: DML Error Logging
Sep 21st
Just read an excellent overview of the new 10gR2 feature of DML Error Logging: 10gR2 New Feature: DML Error Logging by Natalka Roshak on Oracle FAQ’s. It gives a clear overview of the syntax, the steps in setting it up, the results and when to use it. I wish I had written it, since it is really very good.
Improvements in Oracle SCM Check Out and Check In mechanism (better Oracle Designer Source Code Control)
Sep 21st
Oracle SCM – the Software Configuration Manager – provides the backend source code control services for Oracle Designer. It offers among other things functionality for Version Control: Check In, Check Out, Branching and Merging and it records Check In/Out notes, Version Labels and ‘version events’. In comparison with several other tools for version control, it has a few oddities or lacks some functionality that might have been very handy. It turns out that most of that functionality can be created fairly easy, with additional triggers on the Oracle SCM tables. I have demonstrated in a previous post how two of these additional pieces of functionality can be implemented: Building Check In protection into Oracle Designer/Oracle SCM – Check In only by the user that did the Check Out. I intend to write further articles to demonstrate implementations of all others enhancements listed in this post: Read the rest of this entry »
XML technologies
Sep 21st
