Here’s my contribution to the ODC Appreciation Day. Overview Last week I had the privilege to participate in the EOUC Database ACES Share Their Favorite Database Things session at Oracle OpenWorld, so I think that the best topic to write about, as part of the ODC Appreciation Day, is the one I talked about in […]
RETURNING INTO – Enhancement Suggestion
The RETURNING INTO clause is one of my favorite features. It returns data from the rows that have been affected by the DML statement, and as I wrote in this previous post: For INSERT it returns the after-insert values of the new row’s columns. For UPDATE it returns the after-update values of the affected rows’ […]
Implementing Arc Relationships with Virtual Columns? Or Not?
I wrote a post some time ago about implementing arc relationships using virtual columns. Recently, Toon Koppelaars wrote a detailed and reasoned comment to that post. Since I admire Toon, getting his point of view on something that I wrote is a privilege for me, regardless if he agrees with me or disagrees (and just […]
Write (Even) Less with More – VALIDATE_CONVERSION
I wrote the post Write Less with More – Part 8 – PL/SQL in the WITH Clause in November 2015, when the latest released Oracle version was 12.1. In that post I explained about PL/SQL in the WITH Clause – a new 12.1 feature – and demonstrated it using the following example:
PL/SQL in SQL in View in SQL in PL/SQL
I presented “Write Less (Code) With More (Oracle 12c New Features)” yesterday at OGh Tech Experience 2017. One of the features I talked about was PL/SQL in the WITH Clause. One of the restrictions of this feature is that you cannot embed a static SQL query, that contains PL/SQL in the WITH clause, in PL/SQL […]
RETURNING INTO
The RETURNING INTO clause is one of my favorite PL/SQL features. It allows to write less code, improves readability and reduces context switches between PL/SQL and SQL. In this post I’d like to highlight some less-known characteristics of the RETURNING INTO clause and emphasize differences that exist when it is used in different DML statements. […]
Adding a Column with a Default Value and a Constraint
The Constraint Optimization series: Part 1: Optimization of Check Constraint Creation Part 2: Optimization that Violates Data Integrity Part 3: Optimization of Foreign Key Constraint Creation Part 4: (Lack of) Optimization of Unique Constraint Creation Part 5: Adding a Column with a Default Value and a Constraint In the previous parts of this series I […]
COLLECT DISTINCT in PL/SQL Works in Oracle 12.2
About a year ago I wrote the post Subtleties – Part 1 (SQL and PL/SQL). I wrote there: Almost every valid SQL statement (i.e., that is executed successfully by the SQL engine) can be embedded successfully as a static SQL in PL/SQL. Almost, but not every statement. One example is the COLLECT aggregate function with […]
(Lack of) Optimization of Unique Constraint Creation
The Constraint Optimization series: Part 1: Optimization of Check Constraint Creation Part 2: Optimization that Violates Data Integrity Part 3: Optimization of Foreign Key Constraint Creation Part 4: (Lack of) Optimization of Unique Constraint Creation Part 5: Adding a Column with a Default Value and a Constraint In the previous parts of this series I […]
Optimization of Foreign Key Constraint Creation
The Constraint Optimization series: Part 1: Optimization of Check Constraint Creation Part 2: Optimization that Violates Data Integrity Part 3: Optimization of Foreign Key Constraint Creation Part 4: (Lack of) Optimization of Unique Constraint Creation Part 5: Adding a Column with a Default Value and a Constraint In a recent post I showed that Oracle […]