Infrastructure at your Service
One of the difficulty when writing a SQL query (static SQL) is to have in the same Where Clause different conditions handling Null Values and Not Null Values for a predica. Let’s me explain you by an example : Users can entered different values for a user field from an OBI report: – If no value entered then all rows must be returned. – If 1 value entered then only row(s) related to the filter…
By Franck Pachot . I know that lot of people are against the ANSI join syntax in Oracle. And this goes beyond the limits when talking about NATURAL JOIN. But I like them and use them quite often. Why is Natural Join bad? Natural join is bad because it relies on column names, and, at the time of writing the query, you don’t know which columns will be added or removed later. Here is an…
Last month, I present in the IT-Tage 2017 in Frankfurt am Main, a session about SQL Server 2017 overview. During my session I made a demo on one of the new string T-SQL Commands: TRIM. My demo was very simple but I have also 2 very good questions. Before, I write about these question, I will show you what is TRIM. This function is a replacement of RTRIM and LTRIM to delete all space character…
When working with Documentum, you will most probably have to enable the DFC traces one day or another and then work with these traces to analyze them. The purpose of this blog is simply to show how the DFC traces can be enabled, which tools can be used to quickly process them and what are the limitations of such things. Enabling the DFC traces can be done very easily by updating the dfc.properties file of…
By Franck Pachot . In SQL, ‘upsert’ is a conditional insert or update: if the row is there, you update it, but if it is not there, you insert it. In Oracle, you should use a MERGE statement for that. You are clearly doing it wrong if you code something like: begin insert… exception when dup_val_on_index then update… end;
This year again the APEX connect conference spans over three days with mixed topics around APEX, like JavaScript, CSS, SQL and much more. After the welcome speech and the Keynote about “Reconciling APEX and the Thick Database Paradigm” by Bryn Llewellyn I decided to attend presentations on following topics: – Temporal Validity and Flashback Data Archive – Universal Theme and Custom Themes in APEX 5.1 – Using REST Services in APEX applications the nice way…
Disclaimer: This is in no way a recommendation to avoid PL/SQL. This post just describes a case I faced at a customer with a specific implementation in PL/SQL the customer (and me) believed is the most efficient way of doing it in PL/SQL. This was a very good example for myself to remind me to check the documentation and to verify if what I believed a feature does is really what the feature is actually…
By Franck Pachot . LISTAGG was a great feature introduced in 11g: put rows into line with a simple aggregate function. 12cR2 adds an overflow clause to it. What happens when you have so many rows that the LISTAGG result is too long? SQL> select listagg(rownum,’,’)within group(order by rownum) from xmltable(‘1 to 10000′); select listagg(rownum,’,’)within group(order by rownum) from xmltable(‘1 to 10000’) * ERROR at line 1: ORA-01489: result of string concatenation is too long…
By Franck Pachot . Did you know that when you set isolation level to SERIALIZABLE, it is not serializable but SNAPSHOT? This isolation level is lower than serializable. I’ve never thought about it until I read Markus Winand slides about transactions. I recommend every developer or DBA to read those slides. This post is there to illustrate write skew in Oracle.
By Franck Pachot . Comparison of NULL can be misleading and it’s even worse for unique constraint validation. Having partial nulls in a composite key can be tricky because the SQL ANSI specification is not very easy to understand, and implementation can depend on the RDBMS. Here is an example with composite unique key and foreign key on Oracle.
