Dot Notation get ORA-40478: output value too large (maximum: ) with JSON Array
(Doc ID 2404562.1)
Last updated on JANUARY 13, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 12.2.0.1 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
Dot Notation is not working for Oracle JSON Array.
select p.id,p.policy_js.policyOptions[*].calculationMethods[*].inputs[*].key from pi_policy_template p where p.id='RegularPay'
select p.id,p.policy_js.policyOptions[*] from pi_policy_template p where p.id='RegularPay'
Able to retrieve the data using JSON_TABLE
SELECT jt.*
FROM PI_POLICY_TEMPLATE p
, JSON_TABLE(p.policy_js, '$.policyOptions[*].calculationMethods[*]'
COLUMNS ( RATE_TYPE VARCHAR2(20) PATH '$.calculationMethodID') ) as jt
WHERE p.ID = 'RegularPay';
CREATE TABLE "SKIPVPD"."PI_POLICY_TEMPLATE"
( "VPD_KEY" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"OID" VARCHAR2(32 BYTE) NOT NULL ENABLE,
"VERSION" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"CATEGORY" VARCHAR2(50 BYTE),
"TYPE" VARCHAR2(50 BYTE),
"POLICY_JS" VARCHAR2(32767 BYTE),
"ID" VARCHAR2(100 BYTE),
"NAME" VARCHAR2(100 BYTE),
"DESCSCRIPTION" VARCHAR2(4000 BYTE),
CONSTRAINT "CK_PI_POLICY_TEMPLATE" CHECK ( POLICY_JS IS JSON WITH UNIQUE KEYS STRICT) ENABLE
) SEGMENT CREATION IMMEDIATE;
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |