If you are anything like me you will have multiple virtualboxes running on your system. Sometimes you might want to run a copy of a virtualbox for different purposes. Like running an Oracle 11 Devdays instance as test environment but also running the same vbox for customer testing. If you copy the vbox and try to run it in the manager you’ll be presented with an error that a harddisk with the same UUID already (more...)
In SQL Developer you have a lot of options when connecting to the database. You can use the TNS entries defined but you can also give the hostname, port and SID or Servicename for instance. PL/SQL Developer doesn’t supply these options, but you can still use them…
In the logonscreen you can choose the database by choosing you TNS entry. But did you know you can put in the complete text of a tnsnames.ora (more...)
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?
I learnt a lot from the entire Oracle Technology Network and I still do. One of the best features of OTN these days, IMHO, is the ability to download prebuilt Virtual Machines. Since I am a developer and not an administrator, I don’t like to be bothered with stuff like how much disk space do I need, how many cores should I use etc. I can just download a Virtual Box image, import it and (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...)
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...)
What is an ANTI-JOIN? And what is the difference between the SEMI-JOIN and the ANTI-JOIN?
First of all, both SEMI-JOIN and ANTI-JOIN are not in the SQL syntax but they are more a pattern. You might expect to be able to write something like:
[PATRICK]SQL>SELECT d.deptno, d.dname, d.loc FROM dept d SEMI JOIN emp e ON (e.deptno = d.deptno) /
to get all the (more...)
Could you tell me what the difference is between NVL and COALESCE?
NVL returns the value of the first argument if it doesn’t evaluate to NULL, otherwise it will return the value of the second argument.
COALESCE returns the first argument that doesn’t evaluate to NULL. That can be any one of the arguments.
So they are definitely similar – but there are significant differences.
First of (more...)
I have got a website with a search form. I want to display a limited number of results to the user and have him/her navigate through different pages. Is this possible using plain SQL?
Of course this is possible. It might take some thinking, but that has never hurt anyone (yet). First we need a table with some randomly sorted data in it. In this example I (more...)