Hello
I've got a json file that's structured as per the attached
json_array_objects.txt (125 Bytes)So there are individual objects within an array. Using the external table syntax that's shared in numerous examples, it can't identify the separate rows - which I completely get. It's simply looking for a line feed as a delimiter.
create or replace directory example_dir as 'E:\'
CREATE TABLE json_array_objects
( JSON_DOCUMENT CLOB
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY example_dir
ACCESS PARAMETERS
( RECORDS DELIMITED BY 0x'0A'
DISABLE_DIRECTORY_LINK_CHECK
FIELDS (json_document CHAR(5000)))
LOCATION
( example_dir:'json_array_objects.txt'
)
)
REJECT LIMIT UNLIMITED
PARALLEL;
select * from json_array_objects

If I reformat the json file as per the other attachment - so put the json on a single line, remove the commas etc, this external table specification works as expected.
<a href="https://objectstorage.us-phoenix-1.oraclecloud.com/p/BqK85Rn1zA5MP0vYiqbAdPgs7Z6OmMxw8SD3WCFVm5kY8uReidZ1KPIKkgJ1hCkG/n/axciphqpnohg/b/forums-legacy/o/uploads/A5OWWN22TWL3/json-nonarray-objects.txt" alt="json_nonarray_objects.txt type="text/plain">json_nonarray_objects.txt</a> (46 Bytes)CREATE TABLE json_nonarray_objects
( JSON_DOCUMENT CLOB
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY example_dir
ACCESS PARAMETERS
( RECORDS DELIMITED BY 0x'0A'
DISABLE_DIRECTORY_LINK_CHECK
FIELDS (json_document CHAR(5000)))
LOCATION
( example_dir:'json_nonarray_objects.txt'
)
)
REJECT LIMIT UNLIMITED
select JSON_DOCUMENT from json_nonarray_objects
The thing I'm struggling to understand is how I can set up the sql*loader syntax to load the first file such that it looks like the output from the second file.
I'm guessing this might involve some pre-process which is doable, but I'd rather avoid it if I can - the files are large and there's going to be lots of them. Unfortunately I don't have a great deal of control over the source format.
Any thoughts/suggestions?
Cheers
David