Hi. I'm using Oracle 19.13 and am trying to create a definition for an external table that allows me to view each record in a text file as a one row which has a single clob column. The text files I have are all SQL scripts that contain generated INSERT statements like this:
INSERT INTO case_import (process_name,process_id,case_id,date_started,date_completed,date_due,strict,subscribable,aggregate_data,aggregate_data_url,case_status,published,date_published,version,citizen_id,view_group_id,citizen_name,originator,case_summary,citizen_email,category,product,date_due_warn,is_anonymised) VALUES (…
where the strings are enclosed in single-quotes and the numerics aren't. Added complications are that the CLOB columns both contain JSON, with escaped double-quotes and escaped single-quotes, and file sizes range between about 850MB and 3GB. In a ten line sample file I'm playing with the length of the longest line is over 150,000 characters.
Ideally I'd simply run the scripts into a table defined like this:
CREATE TABLE case_import (
process_name VARCHAR2(250),
process_id VARCHAR2(250),
case_id VARCHAR2(250),
date_started VARCHAR2(250),
date_completed VARCHAR2(250),
date_due VARCHAR2(250),
strict NUMBER(16),
subscribable NUMBER(16),
aggregate_data CLOB,
aggregate_data_url VARCHAR2(250),
case_status NUMBER(16),
published NUMBER(16),
date_published VARCHAR2(250),
version VARCHAR2(250),
citizen_id VARCHAR2(250),
view_group_id VARCHAR2(250),
citizen_name VARCHAR2(250),
originator VARCHAR2(250),
case_summary CLOB,
citizen_email VARCHAR2(250),
category VARCHAR2(250),
product VARCHAR2(250),
date_due_warn VARCHAR2(250),
is_anonymised NUMBER(16)
);
but I can't because even ignoring the errors caused by the escaped characters in the JSON the values for the CLOB columns are over 4000 characters anyway so I get ORA-01704.
The only plan I have is to create a table with a single CLOB column, putting one line from the script file into one row in the table, i.e. one INSERT statement into one CLOB, so that I can run some PL/SQL on each CLOB to extract the data we need. I'm convinced it ought to be possible to define an external table like that so I can pull the data into the database in some form or other, but I can't work out a way to do that.
Any help you can offer would be appreciated.
Thanks.