Blog
The Café Database Blog
SQL Assertions
Writing this article is something I have wanted to do for decades. Since the SQL-92 standard, “Assertions” have existed, but no database engine had implemented them until now. We could define a SQL Assertion as an advanced constraint that allows cross-checking across...
Not by AI, nor without.
Note: If this article seems too long to you, ask an AI to summarize it. Then, if you want, read it and you'll see what you missed.II have a friend who worked for many years at one of Spain’s leading publishing houses. As she told me, there isn’t a book that doesn’t go...
FLIGHTS open dataset.
In 2013, I published the book SQL Optimization in Oracle. Until then, when I taught SQL or PL/SQL training, the “universal” tables EMP and DEPT were enough for me to demonstrate practically everything. The problem came when I started needing examples with a bit more...
OWNER vs JOB_CREATOR in DBMS_SCHEDULER
A few days ago I received an email from a subscriber. The question looked simple… but it came with a twist: Good morning Javier, I have two database users: 1. OWNER 2. DBA DBA creates a job where the owner is user OWNER. The job runs without issues. The user DBA is...
Insufficient privileges and PL/SQL
If you have been working with Oracle for some time, you have most likely seen this scenario: From SQL, an operation works. From an anonymous PL/SQL block, it also works. From a stored procedure… ORA-01031: insufficient privileges. The usual way to try to fix it...
Multilingual sorts in Oracle
Oracle allows text to be sorted according to the linguistic rules of each language. It’s important to understand that each writing system follows different collation rules. For example, in Norwegian, there are three characters that don’t exist in the standard Latin...
Bring your impdp some love
Imagine you're planning a database migration between two servers. You're performing a FULL import into a brand-new DATABASE. You have plenty of good reasons to "reorganize" the entire DATABASE: a SYSTEM tablespace over 60GB, a SYSAUX tablespace exceeding 40GB... you...
How to build a chain of jobs with DBMS_SCHEDULER
A few days ago, in the Café Database Discord Server Administration channel, someone asked how a chain of job executions could be implemented with DBMS_SCHEDULER. The idea is that when a task finishes, it waits two minutes and runs again. The problem is that the...
SPOUG2024 chronicle
I’m writing these lines on my way back to Valencia after attending the annual SPOUG event in Madrid. The Oracle Users Group of Spain has grown strong, and the event was centered around six conference rooms where, simultaneously, a series of talks and presentations on...
Upgrade your database DST… or not?
In 2019 they decided in Chile that the winter time adjustment would be 5 months, instead of the usual 3 months. Morocco also decided to move its time with respect to GMT from +00 to +01. These changes that you may think do not affect you in the least, unless you live...
Leap years in the Oracle database
Leap years have been a headache throughout history. Also for computer systems and databases.
Discover the origin of these problems and let’s take a look at date bugs that are still present in Oracle23c
A chain of synonyms #JoelKallmanDay
This one is about bad practices. The use of synonyms. If you have felt a cold sweat, you probably experienced something similar. Many users want to access application tables "transparently." Note that I put it in quotes because calling "transparent" "without prefixing...