Steve Adams is in town – Oracle with a calculator
Today I attended the workshop that Steve Adams is giving in the Netherlands this week. We were with a group of four AMIS employees. The day started in a …
Category: Database
PL/SQL Source Code Control inside the database – After Compile trigger for automatic archiving
![PL/SQL Source Code Control inside the database – After Compile trigger for automatic archiving]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "PL/SQL Source Code Control inside the database – After Compile trigger for automatic archiving")
Source code control for PL/SQL is often discussed in the organizations that I encounter. The database itself will always hold the latest version of the PL/SQL objects we are …
PL/SQL post compiler to fix TAPI – dealing with the 'TAPI cannot deal with MERGE' problem
Recently I wrote a post about the failure of the TAPI (table API) generated by Oracle Designer to work together with Merge operations: Designer TAPI triggers fail on SQL …
Assasination attempt on MySQL? – Oracle Announces the Acquisition of Open Source Software Company, Innobase
Interesting news on Friday: the Oracle acquisition – the eleventh this year – of Innobase. Innobase? You may very well have never heard of it. Unless you are a …
Tuple Rules! Using NVL2 and NULLIF.
While working on a current project I ran into the requirement that a column was mandatory, depending on the value of another column. The other way ’round was also …
SQLX How to easily generate XML
I recently had the need to generate XML files based on data stored in relational tables. This was done via an XML DOM implementation on the project I am …
Poor man's VPD – Virtual Private Database before 8i and in Standard Edition Databases
The concept of Virtual Private Database – aka Row Level Security or Fine Grained Access Control – is quite powerful. Without any impact on the SQL used in the …
Death of the End Date – How LAG and LEAD help fight redundancy
Yesterday just prior to our AMIS Query on ADF Faces I was talking to Toon Koppelaars about quite something else: the end_date column in tables that contain contiguous records …
Small introduction to SQL*Net debugging [client side]
Say it's that day again. You try to connect to a (new) database and once again you are facing SQL*Net problems. Blast!. What can you do? Once in a …
Summary results for all dates, including the ones that I do not have data for – example of using Partition Outer Join – Oracle10g SQL Feature
Trying to make myself useful by answering some of the questions on the OTN SQL and PL/SQL Forum, I came across this question: Group Above Report problem…not able to …
Another Pop-Quiz: Whose VPD policy is used when executing SQL in a (definer rights) package?
Virtual Private Database does not let me go. Today I had another burning question to which I could not see the immediate logical answer. I know that when a …
Returning a Ref Cursor based on a PL/SQL Collection
The question on the OTN Forum was: How do i add rows to a pl/sql table and then use the table to open a ref cursor.. Somehow I was …
Good Citizenship – Have Client Applications register themselves with the database
Years ago I have read about the package dbms_application_info for the first time. This is a standard supplied package, shipped with every Oracle Database. It can be used to …
Debugging PL/SQL with DBMS_DEBUG
A very good instruction on the programmatic interface to Orace’s PL/SQL debug facilities. If you do not happen to have a GUI PL/SQL debugger – such as in JDeveloper …
Pop-quiz: VPD policy that depends on a table with a policy…
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 …
Select Trigger in Oracle Database – introducing Fine Grained Auditing
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 …
Data Profiling with Oracle Warehouse Builder 10gR2 (article in Dutch)
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 …
SQL*Plus or Report style Break Groups in SQL Query
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 …
Oracle 10gR2 – New Feature: the Rules Manager
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 …
PL/SQL Profiling
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 …
10gR2 New Feature: DML Error Logging
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 …
Oracle JDeveloper 10.1.3 EA1 New Features
It is not yet a production release, but hey: who cares when you see what it contains in terms of new functionality. Apart from the UIX design time support …
Oracle 10g R2 does not support Oracle Warehouse Builder.
Oracle’s latest database release 10g R2 does not support OWB (Oracle Warehouse Builder). All OWB’s up to and including the current version 10.1.0.3 are not certified by Oracle 10g …
Oracle Label Security, the easy way to secure your data
Besides VPD (as described in my previous posts) and views with functions you can also secure your data using Oracle Label Security (OLS). OLS, in contrast to the other …
Standard for Database Development – Getting rid of USER from PL/SQL and SQL – no longer is USER equivalent to End User
Many applications built in the Client/Server era made use of the fact that every end user had his or her own Database User Account and every Database Connection and …
VPD part 2; The added functionality in 10g
In my prior post about VPD I stated that VPD is not a new functionality Oracle added to 10g Enterprise Edition. In fact it was already available in the …
Virtual Private Database, securing your data
I was not that aware of the fact that there were other (better) ways to secure data without using functions in views until I was involved in a project …
SQL Question: Find overlap in periods – using Analytical Functions (LAG and LEAD)
Today one of my colleagues asked me “What again was your query for finding overlaps in a table with periods, demarcated by start_date and end_date. I think he was …
Role of the Database in a J2EE Architecture – Dumb Data Store? Only Data Tier? Also Business Tier?
The other day, we had a very interesting debate about the role of the database in (our) Java/J2EE projects and architectures. Should you limit the use of the database …
Introducing Spring JDBC – frequently the best introduction of Spring in an organization
Spring consists of a number of modules, such as the IoC Container, AOP, MVC, Persistence, DAO and Remoting. These modules are relatively losely coupled: some can be used without …
Book Review: Beginning Oracle Programming
Title: Beginning Oracle Programming Authors: Sean Dillon, Christopher Beck, Thomas Kyte with Joel Kallman and Howard Rogers Publisher: Apress ISBN: 1-59059-286-7 Publication Date: Sep 2003 Website: http://www.apress.com/book/bookDisplay.html?bID=253
OERR – In search of Error Messages/Events
Sometimes you want to search for Oracle database messages, their meaning or you are in search of specific database event settings. For instance, a long time ago i was …
Bizgres 0.7 Released – Open Source platform for Business Intelligence and ETL
I just read in a Dutch IT magazine (Computable) about the release of Bizgres 0.7. Bizgres is an open source project: The Bizgres Project (www.bizgres.org), a Greenplum-sponsored, community-supported, open …
Oracle Lite Part 1, how to get started
This post is the first one of a set that I will write about Oracle Lite. I have been working with Oracle Lite and I am impressed. Not by …
Hierarchical query with nodes from different tables – DEPT and EMP nodes in one tree
Hierarchical queries using CONNECT BY logic are discussed quite frequently. Often in terms of EMP, the famous demo table that has a self referencing foreign key – MGR to …
Bill of Materials and Hierarchical Queries – Which Component contains a component that…
My two previous posts discussed presenting data in a tree-like fashion and filtering hierarchical data sets on certain query conditions while still returning a tree-like result set. This post …
Selecting a 'pruned tree' with selected nodes and all their ancestors – Hierarchical SQL Queries and Bottom-Up Trees
Hierarchical queries return data in a tree like structure. The query is performed by walking the tree that is made up of parents and children, each non-root-node linked to …
Ant, Stored Procedures and dbms_output
Running sql (scripts) from ant using the ant task is quite easy and straight forward. Unfortunately, the ant sql task is not really suited for running pl/sql, e.g. exec …
Oracle Database 10gR2 for Windows Download
Just noticed the Windows download of the Oracle 10g Release 2 database (lagging the Linux release by almost a month!). And: it is still a developer preview, not even …
Oracle Rules… the world?? Design and Run-time Rules Engine – also for PL/SQL?
I was triggered by the news item on OC4J on OTN’s homepage, ” Download Oracle Application Server 10g (10.1.3) Developer Preview 4 – including … Oracle Rules” to resume …