For a given grouping, LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values into a string using the delimiter specified. A common problem people hi…| Paulzip's Oracle Blog
2 posts published by paulzipblog during April 2020| Paulzip's Oracle Blog
A question I’ve seen asked time and again in forums and from DB developers is “What’s the difference between a unique constraint and a unique index?”. So I thought I’d do a quick write up explaining the differences. Foreign Keys Allows Nulls Also Creates… Unique Constraint ✔️ ✔️ Unique Index Unique Index ❌ ❌ … Continue reading Unique Index Versus Unique Constraint| Paulzip's Oracle Blog
Imagine you’re working with a table full of data – say, employee bonuses stored across five columns: bonus1, bonus2, bonus3, bonus4, and bonus5. Each row represents an employee, and the bonuses are scattered across these columns, with some entries missing (i.e., NULL). Your boss walks in and says, “I want all the actual bonus values … Continue reading Taming the Nulls: Column Shifting Values Left in SQL| Paulzip's Oracle Blog
Access Control Entries (ACEs) control which principals (users or roles) can access different resources, primarily network services, but also Oracle Wallets – which store credentials like private keys, certificates, and other sensitive data needed for secure communication and authentication. ACEs were introduced in Oracle Database 12c and supersede the older Access Control Lists (ACLs) by … Continue reading Script for Exporting Access Control Entries (ACEs)| Paulzip's Oracle Blog
If you try to declare a SQL macro in a package and make use of it in another function / procedure in the package, you’ll get an error.ORA-62565: The SQL Macro method failed with error(s). ORA-04067: not executed, package body <package body name> does not existWhy and how can you fix it? Example: To show … Continue reading ORA-62565 When Compiling Package Which Both Declares and Calls SQL Macro.| Paulzip's Oracle Blog
Oracle’s JSON_TABLE provides a facility to un-nest arrays using the nested path clause, but if you try to use that with sibling arrays you can end up with unexpected results. In my previous post JSON Sibling Arrays – Without Ordinal Association, I covered the situation where sibling arrays have no connection through ordinal position, they … Continue reading JSON Sibling Arrays – With Ordinal Association| Paulzip's Oracle Blog
URL escaping (encoding), is a method to encode arbitrary data in a Uniform Resource Identifier (URI) using only the limited US-ASCII characters legal within a URI. Certain characters are reserved in URLs, so are replaced with percent encoding, which is a % + two hex digits which represent the character’s ASCII code. For multibyte characters … Continue reading URLEscape and URLUnescape for CLOBs| Paulzip's Oracle Blog
Oracle’s JSON_TABLE provides a facility to un-nest arrays using the nested path clause, but if you try to use that with sibling arrays you can end up with unexpected results. Sibling arrays here mean separate arrays, but at the same level under the same parent object. There are two types of sibling array scenarios: 1. … Continue reading JSON Sibling Arrays – Without Ordinal Association| Paulzip's Oracle Blog
Oracle’s TO_CHAR function can convert a decimal into Roman Numerals, for example : The format model ‘FMRN‘ is made up of two parts, FM = Fill Mode, this modifier suppresses whitespace padding in the return value.RN = Roman numerals (upper case). You can use rn for lower case Roman Numerals. However, Oracle doesn’t have a … Continue reading Converting Roman Numerals to Decimal| Paulzip's Oracle Blog
The addition of SQL Macros in Oracle 21c (and some aspects back ported to 19c versions) were an excellent addition to DB developer’s tool sets. They allow us to factor out common SQL expressions and statements into reusable, parameterized constructs that can be used in other SQL statements. Giving capabilities like parameterized views or better … Continue reading SQL MACROs + WITH Clauses = Prohibited!!| Paulzip's Oracle Blog
Someone on the Oracle Community forum asked “Is there a way to parse unknown depth level of nested json?”. In other words, if you had a JSON structure which kept nesting data to an unkn…| Paulzip's Oracle Blog
A question cropped up on the Oracle SQL and PL/SQL community forums regarding generating a JSON nested level structure that is hierarchical in nature, from hierarchical data – that typically …| Paulzip's Oracle Blog