Problem with clob data type when running ogr2ogr command

Question

Using ogr2ogr, I transferred data from the oracle database to the postgres database. However, the table "table" contains attributes that have a "clob" as a data type, which were not created. Is there any way of casting the "clob" data type into the data type that supports postgres at the og2ogr command. Example ogr2ogr command:

ogr2ogr -f "PostgreSQL" "PG:dbname=xx host=xx.xx.xx.xx port=xxxx user=xxxx password=xxxx" "OCI:xxxx/xxxx@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = xxxx)))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = xx))):sema.table" -nln table1 -overwrite -progress -a_srs EPSG:32634 -lco "SCHEMA="sema1"

Albert Godfrind · Answer

If you want the CLOB column in your PostgreSQL database, then try one of the following approaches:

Define a view over the spatial table that casts the CLOB column to a VARCHAR, like this (assuming it is called DESCRIPTION

CREATE VIEW ... AS 
SELECT ...,CAST(DESCRIPTION AS VARCHAR2(nnn)) AS DESCRIPTION 
FROM ...

Use the -sql option to specify a complete SELECT ... FROM statement, like this:

ogr2ogr ... -sql "SELECT ...,CAST(DESCRIPTION AS VARCHAR2(nnn)) AS DESCRIPTION FROM ..."

Note that if the SELECT statement is very long, then  you can save it into a file, and use that file instead:

ogr2ogr ... -sql @my_select.sql

The casting will work as long as the CLOB content is less than 4000 bytes (or less than 32767 bytes if you are on database 12.2 or later and you have configured it to use long strings).

As an aside, you do not need to use the full SQL*NET syntax for the database connection. You can use the simpler:

OCI:user/pass@server-ip:1521/service_name:table_name

Problem with clob data type when running ogr2ogr command

One Answer

Add your own answers!

Ask a Question