Hi,
I am trying to read data from JSON and store it in Oracle DB.
I am using below code to read JSON data, but got "ORA-01704: string literal too long" error.
with json as (select '[
{
"Identifier": "999999999999999999999",
"Item_Document": "<item> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> <creationDateTime>2017-12-17T09:30:47Z</creationDateTime> ",
"Number": "1"
}
]' as doc From Dual)
Select Item_Identification ,
Item_Document,
Doc_Seqno
From json_table( (Select Doc From Json) , '$[*]'
Columns ( Item_Identification Path '$.Item_Identification',
Item_Document Path '$.Item_Document',
Doc_Seqno Path '$.Doc_Seqno'
)
);
I am getting below error.
ORA-01704: string literal too long
01704. 00000 - "string literal too long"
*Cause: The string literal is longer than 4000 characters.
*Action: Use a string literal of at most 4000 characters.
Longer values may only be entered using bind variables.
How do i read JSON data so that i can store in Oracle DB tables?