Problem with CLOB data type when running ogr2ogr command

Question

Using ogr2ogr, I transferred data from the Oracle database to the PostgreSQL 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 PostgreSQL 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"

Answer 1

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

• Stefan Milošević: Thanks for your reply. I have already tried the approach that involves using -sql syntax when using the ogr2ogr command, but the problem is that the contents of a COLB cell are more than 4000 characters. I need a way to transfer data using an ogr2ogr command that would automatically create an attribute that matches the data type text. Or, probably by using the appropriate sql syntax within the -sql parameter.
• Albert Godfrind: From my tests it could be that the driver that reads from Oracle just ignores certain data types. BLOB and CLOB, but also maybe XMLTYPE. And probably also any object column. This may be also because the core OGR does not understand those types.
• Albert Godfrind: You say the CLOBs are more than 4000 bytes. But are they longer than 32767 bytes ? If not then you could set your database to extended (See: docs.oracle.com/en/database/oracle/oracle-database/19/refrn/…).