Martin Giffy D'SouzaProtalkapex.com·Jan 8, 2024Data Integrity for Fast Refresh Materialized Views with Aggregates and Updates/DeletesUpdate: thanks to Connor McDonald for pointing out how to fix this issue (see "The Solution" and subsequent explanation below). Connor wrote a followup post based on this article discussing count(*) vs count(1) differences. The Problem I recently had...Discuss·1 like·615 readsOracle
Martin Giffy D'SouzaProtalkapex.com·Jan 5, 2024How to Get a Non-Aggregate value in an Aggregate QuerySuppose you want to find the maximum salary for each job in the emp table. This is very easy using using the max aggregate: select job, max(sal) max_sal from emp group by job order by job ; JOB MAX_SAL --------- ------- ANALYST 3000 CL...Discuss·4 likes·462 readsOracle
Martin Giffy D'SouzaProtalkapex.com·Jan 3, 2024Dot Notation for JSON in Oracle SQLWhen dealing with Oracle SQL statements and the need to parse JSON arises, you've likely encountered the json_value function. However, Oracle offers a more straightforward approach through simple dot-notation access to JSON data. This feature can sim...Discuss·3 likes·419 readsoracle-sql
Martin Giffy D'SouzaProtalkapex.com·Nov 26, 2023APEX_STRING.SPLIT, Pipelined Functions, and Pickler FetchesWell that's a loaded title! Most developers may not know what each term means and as such this article will break down each item then group it all together. apex_string.split apex_string.split allows you to split a delimited string into rows (similar...Discuss·1 like·950 readsoracle-sql
Martin Giffy D'SouzaProtalkapex.com·Nov 25, 2023Oracle SQL Listagg for ClobsIn Oracle the listagg function is limited to 4000 characters. If the resulting string exceeds 4000 characters then an error is raised. Example: with data as ( -- Generates: -- - Numbers each 20 digits in length -- - Alph...Discuss·2 likes·884 readsoracle-sql
Martin Giffy D'SouzaProtalkapex.com·Nov 25, 2023How to merge JSON objects in SQLWhen working with APEX, handling JSON data structures is a common task, particularly when interacting with REST APIs. There may be instances where you need to merge or layer one JSON object onto another, such as when using default configurations in a...Discuss·429 readsSQL
Pavan Potharajpavan5.hashnode.dev·Oct 12, 2023SQL DataTypesData Types:In SQL (Structured Query Language), a datatype specifies the type of data that can be stored in a column of a table. It defines the kind of values that a column can hold, such as integers, strings, dates, etc. Each column in a table must h...Discuss·1 likeoracle-sql
Jochen Van den Bosscheblog.jochenvandenbossche.dev·Jan 8, 2023So... So you think you can sort?Like with many articles, this one found its origin in an application bug. A developer came to me saying that the ORDER BY in his SQL gave incorrect results. After some investigation, this article was the result. ORDER BY is so easy to add at the end ...Discussoracle-sql
Martin Giffy D'SouzaProtalkapex.com·Jan 31, 2021The Biggest Hidden Bug in Your APEX ApplicationLet's start with a little quiz. Without checking, what do you think happens/returns running the following query: -- Note the two digit year select to_date('24-Jan-21', 'DD-MON-YYYY') from dual; I recently asked this same question on Twitter and the ...Discuss·1 like·226 reads#oracle-apex
