Channel Avatar

The Magic of SQL @UCWeOtlakw8g01MrR8U4yYtg@youtube.com

28K subscribers - no pronouns :c

Hi, I'm Chris Saxon, part of Gerald Venzl's Oracle Developer

Videos Shorts Posts Playlists

Welcoem to posts!!

in the future - u will be able to do some more stuff here,,,!! like pat catgirl- i mean um yeah... for now u can only see others's posts :c

The Magic of SQL
Posted 4 months ago

Primary keys and unique constraints both stop you from storing duplicate values

So what's the difference between them?

Primary

1 per table
NULLs disallowed

Unique

Many per table
NULLs allowed

23 - 0

The Magic of SQL
Posted 4 months ago

Find rows matching a regular expression in Oracle Database with

WHERE REGEXP_LIKE ( <col>, <regex> )

This condition complies with the POSIX regular expression standard and the Unicode Regular Expression Guidelines

15 - 0

The Magic of SQL
Posted 4 months ago

Convert rows to columns in Oracle Database with

SELECT * FROM ...
PIVOT ( <agg_fn> FOR vals IN ( 'v1', 'v2', ... ) )

The database makes a column for each value in the IN list with the result of <agg_fn>

Any columns from the input table unused in PIVOT form an implicit GROUP BY

22 - 0

The Magic of SQL
Posted 4 months ago

To tune #SQL it's useful to add runtime stats to its execution plan

Get these in Oracle Database with

alter session set statistics_level = all;
set serveroutput off

set feed only
QUERY TO TUNE
set feed on

select * from dbms_xplan.display_cursor ( format => 'ALLSTATS LAST' )

The plan now includes these stats so you can see how much work each operation does:

Starts = how many times the operation ran
E-rows = estimated rows
A-rows = actual rows
A-time = actual time
Buffers = logical I/O

23 - 0

The Magic of SQL
Posted 4 months ago

After changing database passwords the old password is invalid

This often means downtime to update apps to use the new password

Or worse - errors if they attempt to connect between changing the database user and updating the app config

To allow apps to briefly use the old and new passwords from 19.12 and 21c set the database user profile to

CREATE PROFILE <profile> LIMIT password_rollover_time <days>

Set database users to use this profile and apps can connect with both passwords until the rollover time ends

13 - 0

The Magic of SQL
Posted 4 months ago

When writing outer joins in #SQL how often do you use

RIGHT OUTER JOIN

As opposed to LEFT?

If it makes the query easier to read/write (opt 3), please share examples!

6 - 7

The Magic of SQL
Posted 5 months ago

To add a month to a date in Oracle #SQL you can use

ADD_MONTHS ( dt, 1 )
dt + INTERVAL '1' MONTH

These are different on the last day of the month

ADD_MONTHS -> The last day of the new month
INTERVAL -> The same day number for the new month; this date could be invalid!

14 - 0

The Magic of SQL
Posted 5 months ago

Change the format of query results in @OracleSQLDev & @oraclesqlcl with

SET SQLFORMAT <fmt>

Or by adding a format comment after SELECT


SELECT /*<fmt>*/...

Supported formats include

csv, html, xml, json, fixed, insert, loader, delimited, ansiconsole

23 - 2

The Magic of SQL
Posted 5 months ago

From Oracle Database 23ai (23.5) you can convert SYSDATE/SYSTIMESTAMP to the DBTIMEZONE:

ALTER SYSTEM SET time_at_dbtimezone = database

OFF - Functions use OS time
USER_SQL - OS time converted to DBTIMEZEONE
DATABASE - All time-dependent operations run based on DBTIMEZEONE

11 - 0

The Magic of SQL
Posted 5 months ago

Update #JSON in Oracle #SQL with the JSON_transform function

23ai extended this enabling you to set values in arrays using path expression formulas, e.g.

JSON_TRANSFORM ( json_doc,
NESTED PATH '$.items[*]' (
SET '@.totalPrice' = path '@.unitPrice * @.quantity'
)
)

11 - 0