INTRODUCTION I was recently confronted with an error ORA-01502 error. This occurred as I was attempting to perform a DML operation on a table. Initially, I was surprised but after some digging I managed to get to the bottom of the issue. Here’s what happened. SETUP Firstly, we create a sample table, create a unique […]
A bit of Background I have been reading a lot about autoupgrade. I see lots of oracle technologists talking about it on their blogs and videos and they recommend it. Having got tired of watching from the sidelines, wondering what all the fuzz is about and also due to my own enormous FOMO, I decided
Most data problems don’t come from bad SQL — they come from bad assumptions. And the worst one is believing that constraints can be “handled in the application.” They can’t. They never could. The moment data is touched by an ETL job, a script, an integration, a migration, or a future rewrite, every application‑level rule evaporates. The database is the only place where integrity actually persists. Datatypes, keys, foreign keys, checks, and assertions aren’t optional features — they’re the backbone of reliable systems. If the data matters, the constraints belong in the database. Full stop.
Everyone has a pattern or writing style when it come to SQL I’ve noticed. Some people use uppercase for keywords other people don’t, some start a column on a new line others don’t, people format differently, leaving commas at the end of the line vs at the beginning and many more niche ways people write.
BACKGROUND Consider this post from stackoverflow. As seen above, Nesting of with clauses is not possible in oracle and while the examples in the answer are simple and straightforward allowing you to define multiple different subqueries, sometimes (and I know because I’ve been there) you actually need to or at least wish you would nest
Services or Service names in Oracle specify one or more names that clients can use to connect to an Oracle Database. It is a logical representation of a database. When an instance starts, it registers itself with the listener using one or more service names. They are a good way of separating various application connections
With the announcement of 26ai on premises, many people (myself included) using 19c will be looking to migrate their existing databases over to 26ai. As announced earlier, 19c is the last release in which the option to use a non-CDB is allowed; from 21c onwards, it’s mandatory to change your database architecture to multitenant. Therefore, anyone
Create a pluggable database(19c) from a non-container database. Read More »
In this post, I explain with examples the new oracle database 26ai feature “Group by Alias and group by All” I also discuss using the Having clause and Rollup as well as what to watch out for.
Group by Alias and Group by All in Oracle Database 26ai Read More »
A quick guide to Oracle 26ai’s extended VALUES constructor, showing how to insert, update, and merge multiple rows with cleaner, simpler SQL.
Table Values Constructor In Oracle Database 26ai Read More »
BACKGROUND Consider this problem from LeetCode: leetcode_db_1158, about figuring out for each user, the number of orders they made in 2019. The code below can create the sample data set needed for the question. Here’s an Oracle SQL solution to this problem Notice the left join to a subquery highlighted above! This is done to
Conditional Filter For Aggregates In Oracle Database 26ai Read More »
