My Oracle Support Banner

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 later
Oracle 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


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.