Hello, I am using dbms_cloud.create_external_table and it is creating the external table correctly using the script below. However, there are a few records that are showing up as blank/null even though there are values in the actual file.
The conversionerrors syntax below stores those as null because if I take out that clause, the records do not get added to the external table.
The thing about the records that are conversion errors, is that they are incredibly long (50,000+ characters). I would think a CLOB would be able to handle this length. Is there a maximum limit?
If I take the same file, and delete the majority of those records to about 200 characters, it works correctly. This makes me think all of the issues pertain to the long length of these records.
BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
table_name => 'PT_JSON',
credential_name => 'mycredential',
file_uri_list => 'filename.json',
format =>
json_object(
'recorddelimiter' VALUE 'X''A''',
'rejectlimit' value 'unlimited',
'trimspaces' value 'lrtrim',
'conversionerrors' value 'store_null'),
column_list => 'NPI clob'
);
END;
/