SQLDaily

JSON support for PL/SQL; new in 12.2 Example using JSON_OBJECT_T to add an attribute to a JSON object calculated from existing attributes

Manipulate #JSON in PL/SQL with the object types added in 12.2

JSON_ELEMENT_T
JSON_SCALAR_T
JSON_OBJECT_T
JSON_ARRAY_T

With functions to get, put, and remove you can fetch, add, and delete values from JSON objects and arrays

Multifactor Authentication for users in Oracle Database is here in the July 2025 RU for 19c and 23ai

Enable it when logging in with a username/password via tools like SQLcl with

ALTER USER <user> ADD FACTOR 'oma_push' AS '<email>'

Alan Williams explains buff.ly/nzQYFpr

From JSON to tables: Oracle Database JSON Relational Duality Views Discover Oracle Database's JSON Relational Duality Views for flexible, efficient access to both relational and document-oriented data.

#JSON relational duality views in Oracle Database 23ai are a flexible, performant, and consistent way to access data as

Documents
Relational tables
Both!

@anders-swanson.bsky.social shows you how to build basic duality views to do this

The #database is free to run parts of a SQL WHERE clause in any order

So how do you stop errors if you know an expression may throw one

e.g. the square root of negative numbers?

Use CASE to only run it on valid values

WHERE CASE WHEN col > 0 THEN sqrt ( col ) ELSE ... END ...

SQL Injection as a Feature Looking at old applications, we always wonder who in their right mind thought of building them so badly. But every repository has its story and every effort has noble origins. I encountered such an ap

Users often want tailored reports from their database; requests to customize these can morph the system into

#SQL Injection As A Service

@dialloibu.bsky.social charts the story of one such system leading to a user deleting key data

buff.ly/tRkOsUR

Keep your data safe folks!

Bitwise Aggregation Functions Perform bit AND, OR, & XOR operations on numbers; return the numeric value the bit operations Example running these operations over the numbers 7, 5, and 8; constructing the numbers from binary

Run bit AND, OR, and XOR operations across numbers in Oracle #SQL using

BIT_AND_AGG
BIT_AND_AND
BIT_AND_OR

functions from 21c

These convert numbers to binary, then do bitwise comparisons across rows

3 => 011
5 => 101

3 AND 5 => 001 => 1
3 XOR 5 => 110 => 6
3 OR 5 => 111 => 7

Oracle Data Pump is the screwdriver in your toolbox for data migration

A flexible, easy-to-use tool to export and import data between databases

@dohdatabase.com runs through the basics of how this works

buff.ly/XKrgxEO

Deadlock within DML statements Oracle maintain a very detailed note Troubleshooting "ORA-00060 Deadlock Detected" Errors (Doc ID 62365.1) .   This document includes an exa...

Deadlock happens when two or more transactions try to lock the same rows in a different order

Usually this involves different tables

@go-faster.co.uk shows it's possible in Oracle #SQL when two UPDATEs use different indexes to change the same rows

You can insert rows into updatable views...

...but it's possible to add data contradicting the WHERE clause

So the values seem to vanish!

Stop this by adding

CREATE VIEW ... AS SELECT ... WHERE ...
WITH CHECK OPTION

And you can only insert rows that satisfy the WHERE clause

“Is That Column Indexed?” There may be times when the DBA wants, or needs, to know: What indexes are created against a given table How those indexes are built Such information is critical to performance tuning or discoverin…

When tuning a #SQL query, a key question is

Which columns are indexed?

You can check this in Oracle Database by looking at the *_IND_COLUMNS views

@ddfdba.bsky.social has built a script to check this and pull in other useful tuning information for a table

(Geo)JSON support for Spatial - new in 18c SDO_UTIL.FROM_GEOJSON(), converts GeoJSON to a Spatial geometry object SDO_UTIL.TO_GEOJSON(), convert a Spatial geometry object to GeoJSON Example using SDO_UTIL.FROM_GEOJSON to find the distance between points in Marseille and Paris in GeoJSON

Convert GeoJSON to Oracle Spatial objects and back with

SDO_UTIL.FROM_GEOJSON
SDO_UTIL.TO_GEOJSON

This enables you to use spatial functions on JSON data

e.g. to find the distance between two points

SQL Notebooks in Oracle SQL Developer for VS Code Introducing SQL Notebooks, a new way to work with Oracle Database SQL and PL/SQL scripts in the SQL Developer Extension for VS Code.

Tell #SQL stories with SQL Notebooks in Oracle SQL Developer for VS Code

Show statements with their results
Along with markdown blocks to describe the data

@thatjeffsmith.com shows

To find rows matching a #SQL query, the Oracle optimizer can use

ACCESS - get all of a subset of rows (usually via an index)
FILTER - check a set of rows, discarding unwanted data

But how does it pick which to use?

@connormcd.bsky.social investigates buff.ly/WjsrFe8

The difference between two dates in Oracle Database is the number of days between them

To see a breakdown of the units, use interval expressions:

( d1 - d2 ) day to second -> days, hours, minutes, seconds
( d1 - d2 ) year to month -> years, months

What’s new for Developers in Oracle Database 23.7 For more information on what’s new in Oracle Database 23.6, see: What’s new for Developers in Oracle Database 23.6

Oracle Database has added many #SQL features in each RU for 23ai

@gvenzl.bsky.social runs through what's new in 23.7

TIME_BUCKET Function
Materialized columns
DBMS_DEVELOPER.GET_METADATA
Call PL/SQL from JavaScript stored procedures
Smallfile Tablespace Shrink

Hybrid Partitioned Tables Internal and external data access in one table Each partition is either internal or external Read and write to internal partitions External partitions are read only Range and list partitioning only Example of CREATE TABLE for a hybrid partitioned table

Access internal and external data with hybrid partitioned tables in Oracle Database 19c

Internal partitions use regular rows

External partitions use files on the database server or object stores in a wide variety of formats, including CSV, Parquet, and ORC

PL/SQL Function Results Cache - A Smart Memory for Your Database This week's throwback feature of the week is the PL/SQL Function Results Cache. This feature lets you easily cache commonly calculated function results. Using the PL/SQL Function Results Cache can…

Cache the results of PL/SQL functions by marking them

CREATE FUNCTION ... RETURN ... RESULT_CACHE

Ideal for functions run many times with the same inputs on static data

Killian Lynch shows you how to use and monitor it

To group data by year/day/hour/... with Oracle #SQL TRUNC it:

TRUNC ( <dt>, '<fmt>' )

But what about multiples like 4 hours or 2 days?

This was fiddly. Laurent Schneider shows how 23.7 simplifies it with

TIME_BUCKET ( <dt>, <interval>, <start_date> )

Ever deleted the wrong data?

...and committed it?

...in production? 😱

With Oracle Flashback Query, you can view data as it existed at a time in the past

SELECT ... FROM ... AS OF TIMESTAMP <time>

Use this to find rows no longer in the table and re-insert them

Fed up with other developers overwriting your changes when working in a shared #database?

Prevent this with Oracle Loki

This #oracleapex app enables you to lock objects, so only one person can change them at a time

Rafal Grzegorczyk demos buff.ly/dH4J97G

Stop long-running #SQL statements from Oracle Database 18c with

ALTER SYSTEM CANCEL SQL '<sid>, <serial#>'

To ensure the next statement runs, you can pass the SQL ID of the statement to stop - in case it finishes first!

ALTER SYSTEM CANCEL SQL '<sid>, <serial#>, <sql_id>'

How SQL DISTINCT and ORDER BY are Related One of the things that confuse SQL users all the time is how DISTINCT and ORDER BY are related in a SQL query. The Basics Running some queries against the Sakila database, most people quickly under…

You can ORDER BY columns not in the SELECT list of a query

...unless you have

SELECT DISTINCT this FROM t ORDER BY that <-- invalid!

Why?

DISTINCT deduplicates the selected values and forms a new table, so only these are accessible

@lukaseder.bsky.social explains

Real Time Materialized Views Today's throwback feature of the week is the Real-Time Materialized View.What are Materialized Views and how are they different from Real-Time Materialized Views? A Materialized View stores the…

Materialized views (MVs) store the results of a query

The challenge is keeping them up-to-date

Killian Lynch shows how real-time (ON QUERY COMPUTATION) MVs in Oracle Database can give current results by applying changes to out-of-date MVs

Follow parent-child relationships in Oracle #SQL with CONNECT BY

SELECT ...
FROM ...
START WITH <root>
CONNECT BY PRIOR parent_row = child_row

Helper functions include

LEVEL - depth
CONNECT_BY_ISLEAF - does the row have children
SYS_CONNECT_BY_PATH - path from root -> current row

Speed SQL: Guess the missing keyword (????) in: ALTER ???? users ADD ( photo BLOB NOT NULL, email VARCHAR2 (255) NOT NULL ) From the four options shown TO_DATE, TABLE, TEMPORARY, STATEMENT_ID

Test your Oracle #SQL knowledge against the clock in Speed SQL

You have 60 seconds to guess the missing keywords (????) in 10 SQL statements

Can you get them all?

buff.ly/gayEdYm

Come back every day for a new batch to solve

Or explore thousands of database quizzes on Oracle Dev Gym

Enhanced Analytic Functions (ISO SQL Standard) Window functions now support GROUPS & EXCLUDE in the SQL standard window frame clause SQL statements now support the window_clause from the SQL standard Example using a named window with GROUPS and EXCLUDE frames.

Oracle Database 21c enhanced analytic functions to support GROUPS, EXCLUDE, & the WINDOW clause:

fn ( ... ) OVER ( <w> GROUPS ... EXCLUDE ... )
...
WINDOW <w> AS ( ... )

GROUPS => rows with the same sort key are in the same group
EXCLUDE => omit the current or adjacent rows

How to round a date? Wait! What? The ROUND function returns a number rounded to the decimal places specified by the function's parameter. If the function is called without ...

You can round datetime values in Oracle Database with

ROUND ( <dt>, <fmt> )

This rounds up at

Year - 1st July
Quarter - 16th of second month in quarter
Month - 16th of month
Week - 3.5 days into week

‪@monikalewandowska.bsky.social‬ demos

Oracle Edition-Based Redefinition allows many copies of PL/SQL objects in the database

‪@jasminfluri.bsky.social‬ covers two use cases for this

Zero-downtime deployments
Multiple applications using the same database

And covers what you need to know to use it buff.ly/ziuvE45

Delete data fast with a filtered table move from Oracle Database 12.2

ALTER TABLE ...
MOVE
INCLUDING ROWS WHERE <condition>

This discards rows where the condition is not true

This can remove lots of data faster than DELETE

Note this is DDL, so you can't roll it back!

ORAdiff screenshot of version selectors to generate difference report

Want to know all the changes to

Dictionary views
Parameters
Privileges
Fixes
and more

between two releases of Oracle Database?

Check out ORAdiff buff.ly/uz9whaH

Select the source and target releases, and things you want to compare

Then get a report of all the diffs