SQL and PL/SQL articles

Articles about Oracle SQL

JSON_MERGEPATCH is sneaky (8 Feb 2023)
DEFAULT ON NULL, and ORA-01451: column to be modified to NULL cannot be modified to NULL (10 Jan 2023)
Oracle’s Numbers (9 Jul 2022)
Comparing Timestamps with Time Zone (5 Mar 2021)
DBMS_MVIEW.explain_mview with no MV_CAPABILITIES_TABLE (2 Sep 2020)
Loading large GeoJSON objects (4 Mar 2020)
Many happy birthdays (3 Sep 2018)
Report query with optional parameters (15 Feb 2018)
Compiling views: when the FORCE fails you (3 Apr 2017)
A nice Descending Index Range Scan (10 Mar 2017)
Restriction when column default is sequence.nextval (3 Oct 2016)
SQL Problem Solved: Merge History Tables (15 Oct 2015)
SQL Problem: Merge record history tables (14 Oct 2015)
Trivia question: Why does the 2nd Drop Work? (21 May 2013)
Workaround for ORA-04063: view “x” has errors (31 Oct 2012)
Non-Overlapping Dates Constraint (30 Aug 2012)
Fabian Pascal is back and Debunking again (29 Jul 2012)
DATE is not a date! (22 Jun 2012)
Constrain a table to only 1 row (21 Mar 2012)
DEFAULT NULL NULL (13 Feb 2012)
3 Reasons to Hate Hibernate (25 Nov 2011)
MERGE to table with Virtual Columns + Error Log Table = ORA-03113 (1 Sep 2011)
Quick and simple frequency analysis (11 Aug 2011)
SPOD for a query (27 May 2011)
Psychology and Large Tables (5 May 2011)
Infinite Query (16 Apr 2011)
Question: why can’t the optimizer do better with these? (14 Apr 2011)
Question: Deferred Unique Constraint using Function-based Index (28 Mar 2011)
Handling unique constraint violations by Hibernate (11 Mar 2011)
Data dictionary quiz question (3 Mar 2011)
What’s this table? SYS_IOT_OVER_152769 (23 Feb 2011)
A simple data ETL method – nothin’ but SQL (3 Feb 2011)
“It works, just not for the reason you think” (4 Nov 2010)
Count All Rows in a Schema (21 Oct 2010)
Difference Between Two Timestamps (20 Oct 2010)
Generate DML/DDL/QUERY from SQL – the easy way (26 Aug 2010)
Never satisfied (18 Aug 2010)
Purge all Recyclebins without SYSDBA (9 Aug 2010)
A fascinating SQL problem (29 Jul 2010)
Negative, Captain (22 Jul 2010)
If at first you don’t succeed… it’s impossible. (12 Jul 2010)
Querying for Contiguous Series (25 Jun 2010)
My favourite 11g thing (22 Jun 2010)
ORA-01481 Invalid number format model (25 May 2010)
Weird SQL Results (29 Apr 2010)
A Good Bad Example (22 Apr 2010)
“Not possible” is rarely correct (7 Apr 2010)
Truncated Mean in Oracle (6 Apr 2010)
SQL Injection License Plate (23 Mar 2010)
A good entry-level interview question (17 Mar 2010)
How to Change the ORDER BY When You Can’t Modify the SQL (23 Dec 2009)
Two questions about stored outlines (29 Aug 2009)
Create Excel file from SQL query using Python (25 Jul 2009)
Great number formatting trick (24 Jul 2009)
I Am Wizard: A Short Story (27 Mar 2009)
Prefix searches, Wildcard characters and LIKE (14 Jul 2008)
WITH With an IN; or, A Reason to Refactor (2 May 2008)
Learn more about Oracle by examining obscure SQL (21 Apr 2008)
LIKE with wildcard (%) at start CAN use an index (17 Jan 2008)
Will Oracle use my regexp function-based index? (22 Nov 2007)
Easy way to crash a Oracle 10.2 session (22 Nov 2007)
Do you know the difference between USER_TAB_COLS & USER_TAB_COLUMNS? (17 Jul 2007)
Generic Audit (7 Nov 2006)
Generating test data that matches existing data (12 Apr 2006)
“BULK COLLECT and FORALL are faster than cursor processing” (18 Mar 2006)
Quick Quiz – Sequence NEXTVAL and CURRVAL (20 Feb 2006)
Oracle 101: The (Very) Basics (15 Feb 2006)
And you thought OR was commutative… (11 Feb 2006)
” = empty string and/or NULL… (9 Feb 2006)
Simplify your SQL – Avoid Unnecessary Joins (24 Jan 2006)
Small SAMPLE bug in Oracle 9i (ORA-30561) (4 Dec 2005)
Avoiding Lost Updates #3: The Solution (24 Oct 2005)
Avoiding Lost Updates #2: Updateable Views (21 Oct 2005)
Avoiding Lost Updates: Protecting Data in a Multi-user environment (21 Oct 2005)
SQL problem (12 Oct 2005)
Instead-of-Delete trigger problem (10 Oct 2005)

Articles about Oracle PL/SQL

JSON_MERGEPATCH is sneaky (8 Feb 2023)
DEFAULT ON NULL, and ORA-01451: column to be modified to NULL cannot be modified to NULL (10 Jan 2023)
Oracle’s Numbers (9 Jul 2022)
Comparing Timestamps with Time Zone (5 Mar 2021)
DBMS_MVIEW.explain_mview with no MV_CAPABILITIES_TABLE (2 Sep 2020)
Loading large GeoJSON objects (4 Mar 2020)
Many happy birthdays (3 Sep 2018)
Report query with optional parameters (15 Feb 2018)
Compiling views: when the FORCE fails you (3 Apr 2017)
A nice Descending Index Range Scan (10 Mar 2017)
Restriction when column default is sequence.nextval (3 Oct 2016)
SQL Problem Solved: Merge History Tables (15 Oct 2015)
SQL Problem: Merge record history tables (14 Oct 2015)
Trivia question: Why does the 2nd Drop Work? (21 May 2013)
Workaround for ORA-04063: view “x” has errors (31 Oct 2012)
Non-Overlapping Dates Constraint (30 Aug 2012)
Fabian Pascal is back and Debunking again (29 Jul 2012)
DATE is not a date! (22 Jun 2012)
Constrain a table to only 1 row (21 Mar 2012)
DEFAULT NULL NULL (13 Feb 2012)
3 Reasons to Hate Hibernate (25 Nov 2011)
MERGE to table with Virtual Columns + Error Log Table = ORA-03113 (1 Sep 2011)
Quick and simple frequency analysis (11 Aug 2011)
SPOD for a query (27 May 2011)
Psychology and Large Tables (5 May 2011)
Infinite Query (16 Apr 2011)
Question: why can’t the optimizer do better with these? (14 Apr 2011)
Question: Deferred Unique Constraint using Function-based Index (28 Mar 2011)
Handling unique constraint violations by Hibernate (11 Mar 2011)
Data dictionary quiz question (3 Mar 2011)
What’s this table? SYS_IOT_OVER_152769 (23 Feb 2011)
A simple data ETL method – nothin’ but SQL (3 Feb 2011)
“It works, just not for the reason you think” (4 Nov 2010)
Count All Rows in a Schema (21 Oct 2010)
Difference Between Two Timestamps (20 Oct 2010)
Generate DML/DDL/QUERY from SQL – the easy way (26 Aug 2010)
Never satisfied (18 Aug 2010)
Purge all Recyclebins without SYSDBA (9 Aug 2010)
A fascinating SQL problem (29 Jul 2010)
Negative, Captain (22 Jul 2010)
If at first you don’t succeed… it’s impossible. (12 Jul 2010)
Querying for Contiguous Series (25 Jun 2010)
My favourite 11g thing (22 Jun 2010)
ORA-01481 Invalid number format model (25 May 2010)
Weird SQL Results (29 Apr 2010)
A Good Bad Example (22 Apr 2010)
“Not possible” is rarely correct (7 Apr 2010)
Truncated Mean in Oracle (6 Apr 2010)
SQL Injection License Plate (23 Mar 2010)
A good entry-level interview question (17 Mar 2010)
How to Change the ORDER BY When You Can’t Modify the SQL (23 Dec 2009)
Two questions about stored outlines (29 Aug 2009)
Create Excel file from SQL query using Python (25 Jul 2009)
Great number formatting trick (24 Jul 2009)
I Am Wizard: A Short Story (27 Mar 2009)
Prefix searches, Wildcard characters and LIKE (14 Jul 2008)
WITH With an IN; or, A Reason to Refactor (2 May 2008)
Learn more about Oracle by examining obscure SQL (21 Apr 2008)
LIKE with wildcard (%) at start CAN use an index (17 Jan 2008)
Will Oracle use my regexp function-based index? (22 Nov 2007)
Easy way to crash a Oracle 10.2 session (22 Nov 2007)
Do you know the difference between USER_TAB_COLS & USER_TAB_COLUMNS? (17 Jul 2007)
Generic Audit (7 Nov 2006)
Generating test data that matches existing data (12 Apr 2006)
“BULK COLLECT and FORALL are faster than cursor processing” (18 Mar 2006)
Quick Quiz – Sequence NEXTVAL and CURRVAL (20 Feb 2006)
Oracle 101: The (Very) Basics (15 Feb 2006)
And you thought OR was commutative… (11 Feb 2006)
” = empty string and/or NULL… (9 Feb 2006)
Simplify your SQL – Avoid Unnecessary Joins (24 Jan 2006)
Small SAMPLE bug in Oracle 9i (ORA-30561) (4 Dec 2005)
Avoiding Lost Updates #3: The Solution (24 Oct 2005)
Avoiding Lost Updates #2: Updateable Views (21 Oct 2005)
Avoiding Lost Updates: Protecting Data in a Multi-user environment (21 Oct 2005)
SQL problem (12 Oct 2005)
Instead-of-Delete trigger problem (10 Oct 2005)