Everything From how to write simple SQL and PL/SQL statements to new features of the language, Creating functions, Stored procedures, complex queries, solutions to real world challenges and much more. In this Category I will post about the Language that has been around for a while and is still going strong Oracle SQL and it’s procedural extension PL/SQL
Introduction LATERAL is a join type that allows a subquery to use columns from tables that appear earlier in the FROM clause of the query. It lets us build a mental model of exploring data from the inside out. This feature lets us work with subqueries in a unique way, allowing them to reference columns
What is the High Water Mark (HWM)? The high water mark in oracle refers to a point in a segment beyond which data blocks are unformatted and have never been used. If we imagine a data block as a Rectangular block of data that fills up from left to right, then the high water mark
Introduction As a DBA, data modeling is part of the job; working with developers, reviewing table design, implementing proper constraints and much more. In an earlier post about performance tuning, I talk about some practical steps that can be taken to fix performance problems. However, sometimes those problems could be “self-inflicted” and I ended that
Introduction For a 30+ year old language, SQL is doing pretty good in terms of its relevance and usefulness. It can solve a lot of problems, even a lot of the problems people believe require a traditional programming language. The legendary Tom Kyte, when asked in an interview about the most underutilized feature of the
Introduction Calendar functions, introduced in 23.26.1, provide us with the ability to format dates into standard year/quarter/month/week/day text for these calendars: Each formatted calendar function returns a VARCHAR2 value representing the input date at a particular level of the calendar hierarchy. In this post, I will discuss the Calendar type functions and how to use
Calendar Functions and Date Dims in Oracle Database 26ai Read More »
With the release of Oracle Database 23.26.2.0.0, A new join syntax JOIN TO ONE has been introduced. It’s meant to simplify the FROM clause and prevent duplicates that may arise from join conditions. This post explores this new feature and explains how it can be of help when writing SQL queries.
Background One of the best resources for anyone who’s into oracle tech is this site called the devgym. It contains games, quizzes, classes and workouts all designed to help build developers’ Oracle Database, SQL and PL/SQL muscles for FREE! I visit this site often to play the speed SQL and SQUIZL games and sometimes the
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
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 »
