Lightnews — Scholar-powered news

10 Common Mistakes Java Developers Make when Writing SQL

SQLDaily @sqldaily.bsky.social · 2d

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)

PL/SQL Iterator Constructs
blogs.oracle.com/sql/better-loops-and-qualified-expressions-array-constructors-in-plsql
Step control (BY)
Iteration sequences (lists of values)
Mutable iterators (MUTABLE)
Skip iteration values (WHEN)
Repeated expressions (REPEAT)

SQLDaily @sqldaily.bsky.social · 3d

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

@lukaseder.bsky.social explains what to do instead & lists 7 more

This article is part of a series. You might also like: 10 More Common Mistakes Java Developers Make when Writing SQLYet Another 10 Common Mistakes Java Developers Make When Writing SQL Java develop…

oracle23ai and python - eliminate RoundTrips.

4 7

SQLDaily @sqldaily.bsky.social · 4d

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.bsky.social tested with Python and batching inserted 150x more rows!

TL;DR: Searching for RoundTrips to eliminate between python and Oracle. In this case, we "collect" the individual inserts to do them in a fe...

2 3

SQLDaily @sqldaily.bsky.social · 5d

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!

Looking Through Holes in Time - Sean D. Stuber

SQLDaily @sqldaily.bsky.social · 8d

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.bsky.social investigates

While testing some date/time functions today I came across an interesting quirk in Oracle's Gregorian calendar and different date representations. In the Gregorian calendar, the day after October 4,…

SQLDaily @sqldaily.bsky.social · 9d

Index scalar values in #JSON arrays stored in Oracle Database with

CREATE MULTIVALUE INDEX ... ON t ( t.json_data.array. )

The optimizer can use these indexes when searching the array with

JSON_EXISTS ( t.json_data, ' $.array? ( @. == ... ) '

$JSON enhancements JSON data type: a new data type for binary JSON representation making JSON reads & writes faster Multi-value index for JSON enables fast searches within JSON arrays Example: CREATE TABLE json_data ( data JSON ); CREATE MULTIVALUE INDEX j_array_i ON json_data j ( j.data.arr.number() ); INSERT INTO json_data VALUES ( JSON ( '{ arr : [1,2,3] }' ) ); SELECT * FROM json_data j WHERE JSON_EXISTS ( j.data, '$.arr?(@.number() == 1)' ); --------------------------------------------------------- | Id | Operation | Name | --------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| JSON_DATA | | 2 | INDEX RANGE SCAN (MULTI VALUE) | J_ARRAY_I | ---------------------------------------------------------$

DETERMINISTIC and RESULT_CACHE PL/SQL functions

SQLDaily @sqldaily.bsky.social · 10d

To cache the results of PL/SQL function calls, declare them

DETERMINISTIC => cache within a statement
RESULT_CACHE => cache across sessions

Ensure the functions have few unique inputs & are deterministic before doing this!

@antikyte.bsky.social explores

Recent events here in the UK have once again emphasised the value of being careful with your “cache”. To this end, I’m going to take a look at how PL/SQL functions defined using t…

Partition Pruning/Elimination on Reference Partitioned Tables

4

SQLDaily @sqldaily.bsky.social · 11d

Filter tables on their partition key to get partition pruning in Oracle #SQL

=> your query is more efficient

But with reference partitioning, this key is in the parent table!

@go-faster.co.uk shows how filtering these by foreign key enables pruning

I discussed locally partitioning the unique index on a reference partitioned table in a previous blog . Having implemented it, I wanted to ...

5

SQLDaily @sqldaily.bsky.social · 12d

Get values from the initial/nth/final row with

FIRST_VALUE => inital
NTH_VALUE => Nth
LAST_VALUE => final

The default window ends at the current value =>

NTH_VALUE ( v, N ) is null for rows before N
LAST_VALUE gives the final value with the same sort key, not the very last row

Visualization of the values returned for the functions

FIRST_VALUE ( shape )
NTH_VALUE ( shape, 4 )
LAST_VALUE ( shape )

Ordered by either number, letter, or letter and number.

SQLDaily @sqldaily.bsky.social · 15d

Oracle Database has added many #SQL features in RUs like

Non-positional INSERT
GROUP BY ALL
TIME_BUCKET function

But how do you find these?

In the New Features Guides!

Some need you to up COMPATIBLE - @mikedietrichde.com the impact this has

buff.ly/5i5aPnu

SQLDaily @sqldaily.bsky.social · 16d

Create blockchain tables in Oracle Database with

CREATE BLOCKCHAIN TABLE ... ( ... )
NO DROP UNTIL n DAYS IDLE
NO DELETE UNTIL n DAYS AFTER INSERT
HASHING USING SHA2_512 VERSION "v1"

These

Allow INSERT
Disallow UPDATE
Disallow DELETE/DROP until you wait N days as defined

Blockchain tables Backported to 19.10
Insert-only tables with hash chain – more secure than immutable tables, but uses more space
CREATE BLOCKCHAIN TABLE trade_ledger (
trade_id NUMBER,
customer_name VARCHAR2(100),
confirmation_date DATE,
value NUMBER(8,2))
NO DROP UNTIL 3 DAYS IDLE
NO DELETE UNTIL 16 DAYS AFTER INSERT
HASHING USING SHA2_512 VERSION "v1";

INSERT INTO trade_ledger VALUES (12341, 'Gerald', '08-SEPT-2025', 1000);

DELETE trade_ledger WHERE trade_id = 1;
ORA-05715: operation not allowed on the blockchain or immutable table

DROP TABLE trade_ledger PURGE;
ORA-05723: drop blockchain or immutable table TRADE_LEDGER not allowed

GraphQL syntax for JSON Relational Duality Views - andersswanson.dev

SQLDaily @sqldaily.bsky.social · 17d

Define #JSON Relational Duality Views in Oracle Database with GraphQL syntax

CREATE JSON DUALITY VIEW ...
AS student @insert { -- student table, allowing inserts
_id : id -- student.id
...
}

@anders-swanson.bsky.social gets into the details andersswanson.dev buff.ly/fhqMxZt

Working with JSON Relational Duality Views, you can use the SQL or GraphQL DDL syntax. I like the GraphQL format for its simple, expressive syntax: in this article, we’ll explore using GraphQL to…

SQLDaily @sqldaily.bsky.social · 18d

The NVL function in Oracle #SQL returns

The 1st argument if it's non-null
Otherwise the 2nd argument

Use it like so

WHERE col = NVL ( :var, col )

And the plan can use an index if :var is non-null & a full table scan if it is

@monikalewandowska.bsky.social demos

SQLDaily @sqldaily.bsky.social · 19d

Combine rows by value in #SQL with

GROUP BY col

And this returns one row for each value in col

You can then use aggregate functions to get totals for the rows in each group, e.g.

COUNT - number of rows
AVG - numeric mean
MIN/MAX - smallest/largest
LISTAGG - list of values

GROUP BY examples by number, letter, and both

1 5

SQLDaily @sqldaily.bsky.social · 22d

With Schema Annotations in Oracle Database, you can document your objects by adding key-value metadata to

Tables
Columns
Indexes
(Materialized) Views

With the clause

ANNOTATIONS ( <key1> <value1>, <key2> <value2>, ... )

Added in release 23ai, this has been backported to 19.28

Schema Annotations (backported to 19.28)
Define annotations as free-text keys or key/value pairs
Add annotations to an object
CREATE TABLE ... ANNOTATIONS example

Schema Annotations (backported to 19.28)
Define annotations as free-text keys or key/value pairs
Add annotations to an attribute like a table column
CREATE TABLE ... ANNOTATIONS example with column annotations

SQL Injections are scary!! (hacking tutorial for beginners)

SQLDaily @sqldaily.bsky.social · 23d

#SQL injections are scary!

If your app is vulnerable, hackers can bypass your security and access your private data

Network Chuck shows how dangerous this can be buff.ly/cbC7PD3

Keep your data safe

Use parameterized queries!

Is your password for sale on the Dark Web? Find out now with Dashlane: https://www.dashlane.com/networkchuck50 (Use code networkchuck50 to get 50% off) In this video, we’re learning one of the…

Troubleshooting External Scheduler Jobs

SQLDaily @sqldaily.bsky.social · 24d

With the Oracle Job Scheduler, you can run OS scripts from the #database

If you're stuck, @connormcd.bsky.social has built a troubleshooting guide to help

1 Arguments not command line
2 Config file
3 Permissions
4 Who runs your script
5 You start with “nothing”

One of the cool but often rarely exploited facilties in the Oracle Job Scheduler is the ability to run tasks outside of the database, for example, shell scripts or other programs on the OS. But it …

Oracle External Tables and really really long lines

SQLDaily @sqldaily.bsky.social · 25d

External tables are a great way to read text files into Oracle Database with #SQL

But what if you have fields longer than 4,000 characters?

@antikyte.bsky.social covers

Mapping CHAR > 4000 in the ACCESS PARAMETERS to CLOB
Increasing the READSIZE
Using the big data driver

One of the things I like about External Tables is that you can use them to read a file on the Database Server OS without having to leave the comfort of the database itself.Provided you have permiss…

Insights & FAQS from speaking to folks re: our MCP Server

SQLDaily @sqldaily.bsky.social · 26d

Take care when filtering the INNER table in OUTER JOINs

e.g. the right table in a LEFT OUTER JOIN

Do this in the WHERE clause => it's an INNER JOIN

To preserve the OUTER JOIN, filter the inner table in the ON clause

The pic shows outer joins ON number col with inner filters

Diagrams of how filtering the inner table in the WHERE or ON clauses in outer joins affects the results

2

SQLDaily @sqldaily.bsky.social · 29d

With Oracle SQLcl 25.2 you can start MCP servers

These enable you to talk to your Oracle Database via AI agents

@thatjeffsmith.com has compiled a FAQ to help you understand it, including

Does this work with 11g or or 12c? Probably
How to make it safe

buff.ly/J34VHtb

We've shared our Oracle Database MCP Server story with thousands of people. Here are some insights and answers to your most FAQs.

1 1

SQLDaily @sqldaily.bsky.social · Sep 11

Convert rows to a comma-separated list of de-duplicated values in Oracle #SQL with

LISTAGG ( DISTINCT val, ', ' ) WITHIN GROUP ( ORDER BY ... )

LISTAGG was introduced in 11g Release 2
The DISTINCT clause was added in 19c

LISTAGG DISTINCT
De-duplicate values in the concatenated list
Examples comparing LISTAGG and LISTAGG DISTINCT for fetching the object types listed in user_objects

Hash Join Algorithm - Vlad Mihalcea

SQLDaily @sqldaily.bsky.social · Sep 10

The #SQL hash join algorithm

1 Builds a hash table on the smaller table's join column(s)
2 Reads the larger table, checking if its join columns hash to the table built in 1

This grows linearly with the size of the tables - O ( n + m )

@vladmihalcea.bsky.social explains

Learn how the Hash Join Algorithm works, and when a relational database system employs for an SQL JOIN query.

Non-Positional INSERT statement – the insider story

SQLDaily @sqldaily.bsky.social · Sep 9

Oracle Database 23.9 added non-positional inserts

This assigns values to columns in the SET clause (like UPDATE)

INSERT INTO employees
SET employee_id = 210, first_name = 'Gerald', ...

@gvenzl.bsky.social dives into the details and gives the backstory in

Oracle Database 23.9 implements the non-positional INSERT statement. In my social media posts, I called out that Oracle Database is the first and only database to support this functionality, and by…

SQLDaily @sqldaily.bsky.social · Sep 8

Get the top-N rows in Oracle #SQL with

FETCH FIRST n ROWS ONLY

You can include all rows with the same sort value as the Nth with

FETCH FIRST n ROWS WITH TIES

Or get a fraction of the rows with

FETCH FIRST n PERCENT ROWS [ ONLY | WITH TIES ]