SQL Daily

Want to learn a new skill in 2024? Make it #SQL! Oracle Dev Gym has a series of FREE classes to help you do this Start with Databases for Developers: Foundations Join at devgym.oracle.com/pls/apex/dg/cl…

Edition-based redefinition enables zero-downtime app upgrades for Oracle AI Database @DBoriented discusses why it's worth using when offline upgrades are OK: Any time or length upgrades Separate database & app upgrades Flexible release of new versions db-oriented.com/2024/12/30/ebr…

Database deployments can fail But rolling back schema changes is tricky - so plan it @jasminfluri runs through options Versioned Migrations with Reversible Scripts Backups before Changes Immutable Migrations & Forward Fixes Restore Points & Flashbacks medium.com/@jasminfluri/d…

SYSDATE in Oracle AI Database is evaluated once per Statement in #SQL Call in PL/SQL Wrap SYSDATE in a DETERMINISTIC function to trick the compiler into evaluating it once/statement But beware: this can lead to unwanted behaviour @phsalvisberg explores salvis.com/blog/2025/10/1…

Daylight saving is ending across Europe and North America But they change on different dates! To see when the continents are an hour closer, Kim Berg Hansen built a pattern-matching query comparing the TIMEZONE_HOUR for dates across the year kibeha.dk/2025/10/dst-di…

Query Apache Iceberg tables from Oracle AI Database using external tables ACCESS PARAMETERS ( com.oracle.bigdata.fileformat=parquet com.oracle.bigdata.credential.name=... .bigdata.access_protocol=iceberg ) Bryan Grenn demos bryangrenn.blogspot.com/2025/09/db23ai…

Oracle Database 23ai becomes Oracle AI Database 26ai when you apply the Oct 2025 Release Update This adds a GRAPHQL table function that Accepts #GraphQL queries Maps fields to database tables and columns Returns JSON e.g. SELECT * FROM GRAPHQL ( ' tab { col1 col2 } ' )

Use Oracle Data Redaction to mask sensitive data in query results Kajal Singh runs through the enhancements added to this in 23.6 for Mathematical & set functions Sorting & grouping Views with expressions Virtual columns in function-based indexes blogs.oracle.com/database/post/…

Create new list partitions when you insert new partition key values in Oracle #SQL by defining the table PARTITION BY LIST ( ... ) AUTOMATIC Or convert existing tables with ALTER TABLE ... SET PARTITIONING AUTOMATIC Mike Smithers demos mikesmithers.wordpress.com/2025/10/02/get…

Apply the Oct 2025 Release Update and Oracle Database 23ai becomes Oracle AI Database 26ai This adds the QUALIFY clause QUALIFY filters window functions after grouping Like the HAVING clause does for aggregate functions SELECT ... FROM ... QUALIFY fn OVER ( ... ) > ...

Instead of removing rows via DELETE, many applications mark rows as deleted by setting a flag With Oracle #SQL, you can partition on this deleted flag to separate active and inactive data But there are nuances to this - @connor_mc_d covers the details connor-mcdonald.com/2025/10/03/sof…

Got unaggregated columns you want to include in the SELECT list, but not the GROUP BY? You can use ANY_VALUE, e.g.: SELECT c1, ANY_VALUE ( c2 ), COUNT ... FROM ... GROUP BY c1 This returns the value from a random row (it's optimized to return the first) => non-deterministic

Database triggers often contain auditing or other security logic So stopping bad actors from disabling triggers is vital @petefinnigan shows how in Oracle Database: Create an AFTER DDL ON DATABASE trigger Check ALTER TRIGGER statements for DISABLE petefinnigan.com/weblog/archive…

Define which prior rows to include in window functions with ORDER BY ... [ frame ] ... PRECEDING The frame states which sort keys to include (UNBOUNDED = all) ROWS - Strict row count RANGE - Logical value offset; only valid for numbers & datetimes GROUPS - Unique values count

Use Heap Maps in Oracle Database to see: Which tables are actively used vs. rarely/never used Whether data is being read by full table scans or index lookups Opportunities for compression, archiving, or other ILM policies Matt DeMarco shows how to use it oramatt.com/2025/09/30/usi…

Convert #JSON to rows & columns with JSON_Table But if there are errors extracting a field, by default, Oracle Database sets its value to null @MartinDBA shows how to force an error instead in 23ai with ALTER SESSION SET JSON_BEHAVIOR="ON_ERROR:ERROR" martincarstenbach.com/2025/08/07/why…

Oracle Database 21c enhanced FOR LOOPs in PL/SQL to enable you to use Custom increments (BY) Lists of values to loop through Conditions to skip the loop body (WHEN) Expressions to set the next value (REPEAT WHILE) Mutable iterands to set their value inside the loop (MUTABLE)

Are you making any of these common #SQL mistakes? Forgetting about NULL Processing data in Java memory Using JDBC Pagination to paginate large results Using aggregate instead of window functions Lukas Eder explains what to do instead & lists 7 more blog.jooq.org/10-common-mist…

Processing one row at a time leads to lots of round-trips to the #database This is SLOOOOOOOW It's faster to batch records and send many in one call How much faster? @pdevisser tested with Python and batching inserted 150x more rows! simpleoracledba.blogspot.com/2025/07/oracle…

You can subtract one year from a date in Oracle #SQL with either ADD_MONTHS ( dt, -12 ) - INTERVAL '1' YEAR But take care - these handle 29th Feb differently: ADD_MONTHS => 29th Feb -> 28th Feb in previous year INTERVAL => 29th Feb -> 29th Feb in previous year => error!

The Gregorian calendar was adopted in 1582 In this year 4 Oct was followed by 15 Oct The dates between (5-14 Oct) don't exist in Oracle Database This can lead to surprising results, e.g. SELECT DATE'1582-10-10' => 15 Oct @sdstuber investigates seanstuber.com/2018/12/18/loo…