0

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 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"

Improve this question

1 Answer 1

Reset to default
0

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
Improve this answer
3
  • 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.
    – Stefan Milošević
    Commented Apr 12, 2020 at 11:47
  • 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
    Commented Apr 13, 2020 at 10:05
  • 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/…).
    – Albert Godfrind
    Commented Apr 13, 2020 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.