handling NCLOB columns in fdw_export

[dr-kd]

I'm getting this error for an fdw export for nclob types:

column "definition" of foreign table "mycolumn" cannot be converted to or from Oracle data type

Do you have suggestions of how to resolve this?

The SQL out to create the foreign tables looks like this:

    one(8) NOT NULL,
    my_nclob text,
    .. etc ,
) SERVER orcl OPTIONS(schema 'mychema, table `'mytable',` readonly 'true');

One seemingly obvious thing for these table would be to cast the data type to something that fdw can eat in a vewi and then create the fdw table from that. Is there another approach I could take? Can I resolve this issue with ora2pg's existing functionality?

An alternative is to change the schema in my oracle database so we I don't have this issue at all but I lack knowledge to know how safe that is.

Is there another approach that I haven't thought of?

If I can get some guidance, I'm happy to contribute patches (docs or code) back.

[darold]

The oracle_fdw documentation says "NCLOB is currently not supported because Oracle cannot automatically convert it to the client encoding." so the export is aborted at oracle_fdw side. For this particular table you can use Ora2Pg direct export (not through oracle_fdw), Ora2Pg will translate the data to text. You will have to verify that the encoding is correct.

[dr-kd]

The way I resolved it was to create some views - e.g.

create or replace view vpg_blah (
sometext text ) from select to_clob(sometext) from blah;

then on the pg side:

create foreign table ora2pg_fdw_import.blah (mytext text) server rcl options (schema 'myschema', table 'vpg_blah', readonly 'true');

and then insert into blah select * from ora2pg_fdw_import.blah; which works for me because the column ordering is stable.

It'd be nice to have a feature where the fdw import ddl and insert into statements can be output to a separate file for editing. I hacked into ora2pg.pm to provide part of this for my purposes, but it did not seem clear to me how to unobtrusively add the feature to the code.

[avinashvallarapu]

You can also use oracle_execute() function of Oracle_FDW. Import DBA_TAB_COLUMNS or ALL_TAB_COLUMNS from Oracle using Oracle_FDW and then see which Tables and Columns are impacted by directly querying the Foreign table (DBA_TAB_COLUMNS or ALL_TAB_COLUMNS). We can then use the oracle_execute() function of oracle_fdw and create the view on Oracle. This process can all be handled by a Single Routine in PostgreSQL before initiating migration using Ora2Pg. Ora2Pg can then have necessary mapping to select from the VIEW and migrate to the Target Table.

[SISukharev]

You can create table as select from with convertation columns

CREATE FOREIGN TABLE xxatach.document_attribute ( "Id" varchar(36) OPTIONS(key 'true') NOT NULL, "DocumentId" varchar(36) NOT NULL, "CardFieldValueId" varchar(36) NULL, "StorageKey" varchar(2000) NULL, "Value" varchar(2000) NULL, "SearchableValue" varchar(2000) NULL, "Created" timestamp(6) NULL, "LongSearchableValue" text NULL, "LongValue" text NULL ) SERVER oradb OPTIONS (table '( SELECT "Id", "DocumentId", "CardFieldValueId", "StorageKey", "Value", "SearchableValue", "Created", to_clob("LongSearchableValue") AS "LongSearchableValue", to_clob("LongValue") AS "LongValue" FROM XXATACH."Document_Attribute" )');