We have gone through a merger at our company where we are trying to merge the databases. The problem now is that we have duplicate records in our tables. We can of course go through all the records by hand and check if they exist twice. Another option is to build an application to do this. But using the Oracle Database there must be a better way to do this. Any ideas?
Here’s my contribution to the OTN Appreciation Day.
Edition-Based Redefinition (EBR) is a really great feature, or more accurately a set of features, that was introduced in Oracle 11.2 and allows for online application upgrades using hot rollover. If you know me then you know that I talk (ok, preach) about it a lot, and from a long and successful experience.
It is impossible to learn EBR in 5 minutes, but perhaps you’ll understand (more...)
Today is OTN Appreciation Day.
This day is the idea of Tim Hall, Mr OracleBase, and you can See his post here. The idea is that as a sign of appreciation to OTN we do a technical (or not so technical) post on a feature of Oracle we like. I’m going to visit an area I have mentioned before…
In my opinion, one of the key differences between good code and poor code is (more...)
The mission was not too hard: write about your favorite bit of Oracle Technology.
Not just because (more...)
We have bought an application that runs on an Oracle database. There are triggers defined on the tables, but we want to add our own code to these triggers, but we don’t have access to the source code. What is the approach we should follow to accomplish this?
There are two types of DML triggers. Statement level triggers and row level triggers. Then for these two types there are (more...)
Recently I heard someone talk about overloading in Java. What is it, is it possible in PL/SQL and if so, how would I use it?
Overloading is a technique of creating multiple programs with the same name that can be called with different sets of parameters. It is definitely possible to apply this technique in PL/SQL, in fact, Oracle does this a lot of times in their own built-in (more...)
Bryn Llewellyn (Distinguished Product Manager, Database Division, Oracle) presented at OOW2016 on new features in PL/SQL in Oracle Database 12cR2. One of the features that stood out was a new pragma deprecate that can be added to program units such as functions and procedures inside packages. This pragma is used to mark a program unit at deprecated, which typically means: the program unit is still valid, but has been superseded by a better option and (more...)
Last year I did a presentation on table functions at KScope. One of the questions I got was: ‘If pipelined table functions provide their results faster, why would you want to use a normal table function?’ I couldn’t come up with the answer then, maybe you can help?
Erik van Roon
Let’s start with explaining a bit what table functions are. Table Functions are functions that return a collection of (more...)
Even though a space is a regular character, the client didn't want spaces at the beginning or end of a string. Any spaces in the middle were fine.
Of course this could be handled by the application, but it must also be implemented in the database. Using a check constraint with a regular expression will prevent the end user from entering unwanted data.
To try things out, let's just start with a simple table with (more...)
Because my hero is Cary Millsap, I'm going to do what he did and publish my
Brendan Tierney and (more...)
If you need to send almost any message to almost any phone from your Oracle database, and you want to use straight PL/SQL, you may want to consider using my Clicksend API.
- SMS (Short Message Service)
- MMS (Multimedia Message Service)
- Text to Voice
I have released the first beta version of my Oracle PL/SQL API for Clicksend. Read the installation instructions, API reference and download the release from here:
Sending an SMS is as (more...)
Almost a decade ago (in 2007), Oracle released a whitepaper on Integrating Application Express with PayPal Payments Pro which used PayPal's Name Value Pair (NVP) API.
In the years since then, PayPal has made available a new API which is (more...)
column1 = column2 or (column1 is null and column2 is null)
Some time ago I was asked to assist in fixing or at least finding the cause of a performance problem. The application ran fine until the Virtual Private Database (VPD) policy was applied. Oracle claims there should be near zero impact on your application when you implement VPD, then how is this possible?
First of all, the policy applied was a rather complex one. A complex query should be executed to determine if the current (more...)
Somethings it can be a challenge to know what is or isn't a reserved word. Yes we can check the Oracle documentation for the SQL reserved words and the PL/SQL reserved words. There are other references and (more...)
Machine learning and neural networks in particular, are currently hot topics in data processing. Many tools and platform are now easily available to work and experiment (more...)
In short, the packages provided in the Alexandria Utility Library for PL/SQL allow you to set up a schema-level backup of files from your database to Amazon's Simple Storage Service (S3).
At the end of that article I mentioned that you should use AWS Identity and Access Management (IAM) to create a separate (more...)