How to load an extracted ORACLE CLOB into only 1 TEXT column in Postgres?

Question

I'm currently looking at migrating CLOB data from ORACLE into Postgres from an external file. I have created my table in Postgres and the data type I'm using is TEXT which will replicate using a CLOB in ORACLE and now I just need to get my data in.

So far what I've done is extract a CLOB column from ORACLE into a file as per the below, it is only 1 CLOB from 1 COLUMN so I’m trying to load the contents of this entire CLOB into 1 column in Postgres..

CREATE TABLE clob_test (
id number,
clob_col CLOB);

DECLARE
c CLOB;
CURSOR scur IS
SELECT text
FROM dba_source
WHERE rownum < 200001;
BEGIN
EXECUTE IMMEDIATE 'truncate table clob_test';
FOR srec IN scur LOOP
c := c || srec.text;
END LOOP;
INSERT INTO clob_test VALUES (1, c);
COMMIT;
END;
/

DECLARE
buf CLOB;
BEGIN
SELECT clob_col
INTO buf
FROM clob_test
WHERE id = 1;
dbms_advisor.create_file(buf, 'TEST_DIR', 'clob_1.txt');
END;
/

This works fine and generates the clob_1.txt file containing all the contents of the ORACLE CLOB column CLOB_COL. Below is an example of the file output, it seems to contain every possible character you can think of including "~"...


/********** Types and subtypes, do not reorder **********/
type BOOLEAN is (FALSE, TRUE);
type DATE is DATE_BASE;
type NUMBER is NUMBER_BASE;
subtype FLOAT is NUMBER; -- NUMBER(126)
subtype REAL is FLOAT; -- FLOAT(63)
...
...
...
END;
/

My problem now is how do I get the entire contents of this 1 file into 1 record in Postgres so it simulates exactly how the data was originally stored in 1 record in ORACLE?

Effectively what I'm trying to achieve is similar to this, it works but the formatting is awful and doesn't really mirror how the data was originally stored.

POSTGRES> insert into clob_test select pg_read_file('/home/oracle/clob_1.txt');

I have tried using the COPY command but I'm having 2 issues. Firstly if there is a carriage return it will see that as another record and split the file up and the second issue is I can't find a delimiter which isn't being used in the file. Is there some way I can bypass the delimiter and just tell Postgres to COPY everything from this file without delimiters as it's only 1 column?

Any help would be great

AdamKG · Answer 1 · 2020-12-23T18:58:27.270

Note for other answerers: This is incomplete and will still put the data into multiple records; the question also wants all the data in a single field.

Use COPY ... FROM ... CSV DELIMITER e'\x01' QUOTE e'\x02'. The only thing this can't handle is actual binary blobs, which, as I understand it, is not permitted in CLOB (I have never used Oracle myself). This only avoids the delimiter issue; it will still insert the data into one row per line of the input.

I'm not sure how to go about fixing that issue, but you should be aware that it's probably not possible to do this correctly in all cases. The largest field value PG supports is 1gb, while CLOB supports up to 4GB. If you need to correctly import >1GB CLOBs, the only route available is PG's large object interface.

How to load an extracted ORACLE CLOB into only 1 TEXT column in Postgres?

1 Answers1