Dot Notation get ORA-40478: output value too large (maximum: ) with JSON Array

(Doc ID 2404562.1)

Last updated on MAY 31, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 12.2.0.1 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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms