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 unknown number of levels, how would you extract the data? This kind of JSON structure is sometimes called Hierarchical JSON or Recursive Structure JSON. If you’re not entirely sure what that means, my previous blog post Hierarchical JSON Structures, might give you some better understanding, but I’ll give some background here anyway. In this post, I explore how would you extract such data…
Continue readingConvert Values to and From Any Base
If you want to convert to and from any base e.g. binary, octal, hexadecimal, base 64, or even using your own base digits – including Unicode ones, you might find my generic base conversion functions useful.
Continue readingJSON – Duplicate Keys
A question on the Oracle Communities SQL / PLSQL page posed an interesting problem regarding the generation of a non standard JSON structure. In this case, it had repeated siblings with duplicate key names within the same object.
Required Output :
{
"companyid" : "101010",
"invoice" :
{
"refNo" : "A1",
"cheques" : [{"cheque_no" : "10"}],
"refNo" : "A2",
"cheques" :[{"cheque_no" : "20"}],
"refNo" : "A3",
"cheques" :[{"cheque_no" : "30"}]
}
}
Blob to Hex and Hex to Blob
Oracle has routines RawToHex and HexToRaw which convert raw bytes into hex string representation and vice versa, but these are limited to 32k. I needed the equivalent but for a blob (Maximum size: (4 GB – 1) * DB_BLOCK_SIZE) for something I was working on. These would convert blobs to hex clobs and vice versa.
Anyway, here’s the two routines I came up with :
Continue readingSQL*Plus Automatic Column Sizing
If you’ve ever run a query in SQL*Plus, you’ve probably seen columns truncate the display of data or take up far too much width given the size of the data type.
select *
from all_objects
where rownum <= 30;
OWNER |OBJECT_NAME |SUBOBJECT_NAME | OBJECT_ID|DATA_OBJECT_ID|OBJECT_TYPE |CREATED |LAST_DDL_TIME |TIMESTAMP |STATUS |T|G|S| NAMESPACE|EDITION_NAME |SHARING |E|O|A|DEFAULT_COLLATION |D|S|CREATED_APPID|CREATED_VSNID|MODIFIED_APPID|MODIFIED_VSNID
--------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------|----------|--------------|-----------------------|-------------------|-------------------|-------------------|-------|-|-|-|----------|--------------------------------------------------------------------------------------------------------------------------------|------------------|-|-|-|----------------------------------------------------------------------------------------------------|-|-|-------------|-------------|--------------|--------------
SYS |I_FILE#_BLOCK# | | 9| 9|INDEX |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 4| |NONE | |Y|N| |N|N| | | |
SYS |I_OBJ3 | | 38| 38|INDEX |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 4| |NONE | |Y|N| |N|N| | | |
SYS |I_TS1 | | 45| 45|INDEX |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 4| |NONE | |Y|N| |N|N| | | |
SYS |I_CON1 | | 51| 51|INDEX |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 4| |NONE | |Y|N| |N|N| | | |
SYS |IND$ | | 19| 2|TABLE |04/02/2019 04:45:20|04/02/2019 06:05:43|2019-02-04:04:45:20|VALID |N|N|N| 1| |METADATA LINK | |Y|N|USING_NLS_COMP |N|N| | | |
SYS |CDEF$ | | 31| 29|TABLE |04/02/2019 04:45:20|04/02/2019 06:05:44|2019-02-04:04:45:20|VALID |N|N|N| 1| |METADATA LINK | |Y|N|USING_NLS_COMP |N|N| | | |
SYS |C_TS# | | 6| 6|CLUSTER |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 5| |METADATA LINK | |Y|N| |N|N| | | |
SYS |I_CCOL2 | | 58| 58|INDEX |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 4| |NONE | |Y|N| |N|N| | | |
SYS |I_PROXY_DATA$ | | 24| 24|INDEX |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 4| |NONE | |Y|N| |N|N| | | |
SYS |I_CDEF4 | | 56| 56|INDEX |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 4| |NONE | |Y|N| |N|N| | | |
SYS |I_TAB1 | | 33| 33|INDEX |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 4| |NONE | |Y|N| |N|N| | | |
SYS |CLU$ | | 5| 2|TABLE |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 1| |METADATA LINK | |Y|N|USING_NLS_COMP |N|N| | | |
SYS |I_PROXY_ROLE_DATA$_1 | | 26| 26|INDEX |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 4| |NONE | |Y|N| |N|N| | | |
SYS |I_OBJ1 | | 36| 36|INDEX |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 4| |NONE | |Y|N| |N|N| | | |
SYS |UNDO$ | | 15| 15|TABLE |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 1| |METADATA LINK | |Y|N|USING_NLS_COMP |N|N| | | |
SYS |I_UNDO2 | | 35| 35|INDEX |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 4| |NONE | |Y|N| |N|N| | | |
SYS |I_TS# | | 7| 7|INDEX |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 4| |NONE | |Y|N| |N|N| | | |
SYS |I_FILE1 | | 43| 43|INDEX |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 4| |NONE | |Y|N| |N|N| | | |
SYS |I_COL2 | | 49| 49|INDEX |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 4| |NONE | |Y|N| |N|N| | | |
SYS |I_OBJ# | | 3| 3|INDEX |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 4| |NONE | |Y|N| |N|N| | | |
SYS |C_OBJ# | | 2| 2|CLUSTER |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 5| |METADATA LINK | |Y|N| |N|N| | | |
SYS |I_CDEF3 | | 55| 55|INDEX |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 4| |NONE | |Y|N| |N|N| | | |
SYS |C_COBJ# | | 29| 29|CLUSTER |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 5| |METADATA LINK | |Y|N| |N|N| | | |
SYS |CCOL$ | | 32| 29|TABLE |04/02/2019 04:45:20|04/02/2019 06:05:44|2019-02-04:04:45:20|VALID |N|N|N| 1| |METADATA LINK | |Y|N|USING_NLS_COMP |N|N| | | |
SYS |I_OBJ5 | | 40| 40|INDEX |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 4| |NONE | |Y|N| |N|N| | | |
SYS |PROXY_ROLE_DATA$ | | 25| 25|TABLE |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 1| |METADATA LINK | |Y|N|USING_NLS_COMP |N|N| | | |
SYS |I_CDEF1 | | 53| 53|INDEX |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 4| |NONE | |Y|N| |N|N| | | |
SYS |C_USER# | | 10| 10|CLUSTER |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 5| |METADATA LINK | |Y|N| |N|N| | | |
SYS |C_FILE#_BLOCK# | | 8| 8|CLUSTER |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 5| |METADATA LINK | |Y|N| |N|N| | | |
SYS |FET$ | | 12| 6|TABLE |04/02/2019 04:45:20|04/02/2019 04:45:20|2019-02-04:04:45:20|VALID |N|N|N| 1| |METADATA LINK | |Y|N|USING_NLS_COMP |N|N| | | |
30 rows selected.
So you have to specify the size of the columns to try to mitigate this :
column owner format a30
column object_name format a30
-- etc etc
and try to size the column so the data and column name don’t get truncated. This can be a pain. Wouldn’t it be nice if there was some way to do this automatically based on the data (or a sample of the data)? Well I thought so too and so decided to write a function which takes a query and gives you the optimal column size clause for string based columns. It’ll return the larger of column data and column name length.
Continue readingJSON and Booleans
JSON supports boolean values true and false, however, these are not supported by Oracle SQL. So it’s tempting to cast to a number (0 or 1). Here I’m running as 18c and casting to number :
select version_full
from product_component_version;
VERSION_FULL
-----------------
18.4.0.0.0
select *
from json_table(
'{"IsFalse":false,"IsTrue":true}'
, '$'
columns (
IsFalse number path '$.IsFalse'
, IsTrue number path '$.IsTrue'
)
);
ISFALSE| ISTRUE
----------|----------
0| 1
However, this is an unwise decision if you want to make your code extensible. In some versions of Oracle 19c, if you run the same statement, you might be surprised at the result…
select version_full
from product_component_version;
VERSION_FULL
-----------------
19.11.0.0.0
select *
from json_table(
'{"IsFalse":false,"IsTrue":true}'
, '$'
columns (
IsFalse number path '$.IsFalse'
, IsTrue number path '$.IsTrue'
)
);
ISFALSE| ISTRUE
----------|----------
{null} | {null}
Null??!!! What’s going on in 19c? Well by default JSON_TABLE returns “null on error”, so if we change to “error on error” we can understand why.
select *
from json_table(
'{"IsFalse":false,"IsTrue":true}'
, '$'
columns (
IsFalse number path '$.IsFalse'
, IsTrue number path '$.IsTrue'
error on error
)
)
*
Error at line 0
ORA-40799: cannot convert Boolean value to number
In 19c (not all versions) Oracle removed the ability to typecast a boolean to number in JSON functions, even the Oracle documents no longer mention the ability to cast to number. I found this out the hard way, when production code I had written, relying on casting JSON booleans to number, stopped functioning correctly.
What should you cast to instead? Varchar2! It’ll always work
select *
from json_table(
'{"IsFalse":false,"IsTrue":true}'
, '$'
columns (
IsFalse varchar2(5) path '$.IsFalse'
, IsTrue varchar2(5) path '$.IsTrue'
error on error
)
);
ISFALSE| ISTRUE
----------|----------
'false' | 'true'
You can then compare to the string representations of JSON true and false. In PL/SQL I simply declare constants :
declare
subtype TJSONBool is varchar2(5);
JSON_FALSE constant TJSONBool := 'false';
JSON_TRUE constant TJSONBool := 'true';
vSomeFlag TJSONBool;
begin
-- [snip...]
select SomeFlag
into vSomeFlag
from json_table(
'{"SomeFlag":false}'
, '$'
columns (
SomeFlag varchar2(5) path '$.SomeFlag'
error on error
)
);
if vSomeFlag = JSON_FALSE then
--[snip...]
end if;
end;
/
JSON – Extracting Key Names
In some circumstances you might have a JSON source document that is non standard, where a key name itself contains information that you want to extract.
For example, consider this sample stock JSON. The stock numbers 75s8n10Bmw, and AAA1234123w are actually a key name rather than a key value in the data.
{
"stock": {
"75s8n10Bmw": {
"name": "Super Washing machine",
"introduced":"2020-05-21",
},
"AAA1234123w": {
"name": "Gas Cooker",
"introduced":"2018-02-21",
}
}
}
Unlike XML Path which has name() and local-name() functions for extracting a key (node) name, JSON path doesn’t seem to have an equivalent. So how do you extract the key name as a value and return it in the output? Here’s two solutions to the problem :
JSON Array to Delimited String
Someone posed a question on OTN asking how to get the string representation of a JSON Array as a column from a JSON_Table call.
E.g. Turning...
{"a":400,"b":500,"c":["Jim","Fred","Jason","Al-\"E\"-Boy"]}
into...
A| B|C
----------|----------|------------------------------
400| 500|Jim,Fred,Jason,Al-"E"-Boy
Note : Al-"E"-Boy was added as a special case with embedded quotes
Flashback Data Archive – Auditing Table Data Changes, a Better Approach.
If you’ve ever wanted to audit insert / update / delete changes to data in a table, the classic approach would be to use DML (Data Manipulation Language) Triggers. This is creating an audit history table and add a trigger to the table(s) in question which populates that audit history table. Whilst this approach works, it has many pitfalls the main ones being :
- Performance Cost – DML Triggers adds a significant performance cost to DML operations, slowing your DB down, not just the extra inserts to the audit table, but the context switches between the SQL of the DML and the PL/SQL of the trigger.
- Maintenance Headaches – They introduce maintenance issues as they don’t automatically evolve with table DDL (Data Definition Language). e.g. If someone adds / removes / changes columns, someone also has to remember to change the triggers.
- Retention Size – The size of the audit table will often continue to grow and grow without any consideration to retention window (how long you’re likely to keep your data for).
- Direct Path Issues – You cannot use
Insert /*+ APPEND */ ...direct path inserts as this prohibits trigger usage andSQL*Loader DIRECT PATH LOADsilently disables triggers, so you won’t capture those changes, in which case you’ll lose audit information.
So is there a better option? Fortunately, yes there is and one which doesn’t suffer from the problems listed above Flashback Data Archive.
Continue readingNumber of Interval Units in Interval – DSIntervalToNum and YMIntervalToNum
Oracle has two routines NumToDSInterval and NumToYMInterval which return an interval from an input number of interval units. (Note : For those not aware, an interval is the difference between two timestamps. DS stands for intervals in the range Days to Seconds; YM stands for intervals in the range Years to Months.)
E.g.
select NumToDSInterval(12.34, 'hour') DSInterval
, NumToYMInterval(23.45, 'month') YMInterval
from dual
/
DSINTERVAL |YMINTERVAL
------------------------------|----------
+000000000 12:20:24.000000000 |+01-11
But Oracle don’t provide functions for doing the inverse of this, that is, extracting the number of interval units from an interval, which was something I needed the other day. For example you want to know the number of hours between two timestamps. Here’s my solutions :
create or replace function DSIntervalToNum(pInterval in interval day to second,
pUnits in varchar2) return number deterministic is
-- Converts a Day to Second interval into a number of the units specified
ILLEGAL_ARGUMENT EXCEPTION;
pragma exception_init(ILLEGAL_ARGUMENT, -1760);
vUnitsPerDay pls_integer;
begin
case upper(pUnits)
when 'DAY' then vUnitsPerDay := 1;
when 'HOUR' then vUnitsPerDay := 24;
when 'MINUTE' then vUnitsPerDay := 1440;
when 'SECOND' then vUnitsPerDay := 86400;
else raise ILLEGAL_ARGUMENT;
end case;
return extract(day from pInterval) * vUnitsPerDay
+ extract(hour from pInterval) * (vUnitsPerDay / 24)
+ extract(minute from pInterval) * (vUnitsPerDay / 1440)
+ extract(second from pInterval) * (vUnitsPerDay / 86400);
end;
/
create or replace function YMIntervalToNum(pInterval in interval year to month,
pUnits in varchar2) return number deterministic is
-- Converts a Year to Month interval into a number of the units specified
ILLEGAL_ARGUMENT EXCEPTION;
pragma exception_init(ILLEGAL_ARGUMENT, -1760);
vResult number;
begin
case upper(pUnits)
when 'YEAR' then vResult := extract(year from pInterval) + extract(month from pInterval) / 12;
when 'MONTH' then vResult := extract(year from pInterval) * 12 + extract(month from pInterval);
else raise ILLEGAL_ARGUMENT;
end case;
return vResult;
end;
/
Oracle’s NumToDSInterval, NumToYMInterval raise ORA-01760 Illegal Argument exception if you pass something invalid, so I’ve done the same.
Let’s see some examples of their use :
select DSIntervalToNum('+000000008 05:21:26.647200000', 'second') total_seconds
, DSIntervalToNum('-000000763 08:53:54.528000000', 'minute') total_minutes
, DSIntervalToNum('+000000518 20:32:25.440000000', 'hour') total_hours
, DSIntervalToNum(systimestamp - timestamp '1997-01-31 09:26:56.66 +02:00', 'hour') total_days
, YMIntervalToNum('12-6', 'month') total_months
, YMIntervalToNum('+167-06', 'year') total_years
from dual
/
TOTAL_SECONDS|TOTAL_MINUTES|TOTAL_HOURS|TOTAL_DAYS|TOTAL_MONTHS|TOTAL_YEARS
-------------|-------------|-----------|----------|------------|-----------
710486.647| -1099253.9| 12452.5404|212595.623| 150| 167.5
Paulzip's Oracle Blog 