It has been almost a year (March 21st 2022) since Alex asked me if I knew someone who could be his co-author for a new book he was asked to write. Before I really knew what I was getting myself into I said yes, I know someone. I’ll do it.And thus began a period of […]
Read moreCategory Archives: SQL
Constraints
The other day a friend called me with a question on constraints. “I have a table where the combination of two columns must be unique”. My response was: “Use a Unique Constraint on these two columns”.That should work, however…
Read moreLog errors
Just a quick note of something I ran into at my current assignment. I have to import data from external tables into persistent tables. This proces normally is just inserting data into the table, but sometimes a file can be sent in more than once.
Read moreRecursive Subquery
At my current assignment we are processing files coming from an online system to be inserted into our database (kind of a data warehouse). This is done using external tables and a scheduled job. The job just checks if there is a file available and will process this. The trouble is that the files might […]
Read morePolymorphic Table Functions – Part 2
In my previous post I wrote about a possible use-case for Polymorphic Table Functions. I also wrote that I had some ideas to implement extra functionality to make the function more generic. After attending DOAG Konferentz und ausstellung 2018 and UKOUG Tech18 I finally came around to publishing my new version.
Read morePolymorphic Table Functions
I have been working on a presentation on Polymorphic Table Functions. During this time I was looking for a real use case for Polymorphic Table Functions. I came up with an example which is not very useful in real life, but very useful to explain the technique. At my current job I came across a […]
Read moreComparing queries…
How do you compare a rewritten query to its original version? Most of the time I just run a MINUS operation on the original and new query. Actually I execute two. Old query (A) MINUS New query (B) and vice versa (B) MINUS (A). Both should result in no rows. That way I thought I […]
Read moreHow can we use Oracle to deduplicate our data
Dear Patrick, 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 […]
Read moreWhat is overloading and how and when do I use it
Dear Patrick, 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? Ramesh Cumar
Read moreWhen would you use a normal table function?
Dear Patrick, 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
Read more