search

darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
978 stars 341 forks source link

FATAL: ERROR: coulmn #1751

Closed avmindestroy closed 3 months ago

avmindestroy commented 3 months ago

Hello, what is he swearing at, how can I solve it, I have a solution, it is interrupted on this pole image

darold commented 3 months ago

Can you run Ora2Pg in debug mode (-d) for this table only and reports the query used to extract the data?

avmindestroy commented 3 months ago

How to make a correct query to a table? Couldn't you write an example command? Thank you

avmindestroy commented 3 months ago

hello, image run ORA2pg -d the result was like this in the screenshot

darold commented 3 months ago

You can use -d -a TBLNAME to only export the table concerned by the error. What I need is not the error but the query used by Ora2Pg to collect data from Oracle, it is given before the start of the dump. Please also post the DDL of the table creation. Does this table is partitioned or have autogenerated column?

avmindestroy commented 3 months ago

ora2pg -c config/ora2pg.conf -t INSERT -d -a sntmessageinchats [2024-03-18 09:57:58] Ora2Pg version: 24.1 [2024-03-18 09:57:58] Export type: INSERT [2024-03-18 09:57:58] Geometry export type: INTERNAL [2024-03-18 09:57:58] ORACLE_HOME = /u01/app/oracle/product/19.0.0/client_1 [2024-03-18 09:57:58] NLS_LANG = AMERICAN_AMERICA.AL32UTF8 [2024-03-18 09:57:58] NLS_NCHAR = AL32UTF8 [2024-03-18 09:57:58] Trying to connect to database: dbi:Oracle:host=localhost;sid=database;port=1521 [2024-03-18 09:57:58] Isolation level: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE [2024-03-18 09:57:58] DEBUG: executing initial command to Oracle: commit [2024-03-18 09:57:58] Force Oracle to compile schema DLV3733 before code extraction [2024-03-18 09:58:01] Retrieving table information... [2024-03-18 09:58:01] Collecting 1 tables in DBA_OBJECTS took: 0 wallclock secs ( 0.00 usr + 0.00 sys = 0.00 CPU) [2024-03-18 09:58:01] Collecting 1 tables information in DBA_TABLES took: 0 wallclock secs ( 0.00 usr + 0.00 sys = 0.00 CPU) [2024-03-18 09:58:01] Retrieving index information... [2024-03-18 09:58:02] Collecting 9 indexes in DBA_INDEXES took: 1 wallclock secs ( 0.01 usr + 0.00 sys = 0.01 CPU) [2024-03-18 09:58:02] Retrieving columns information... [2024-03-18 09:58:02] Collecting column information for table ... [2024-03-18 09:58:02] DEBUG, SELECT A.COLUMN_NAME, A.DATA_TYPE, A.DATA_LENGTH, A.NULLABLE, A.DATA_DEFAULT, A.DATA_PRECISION, A.DATA_SCALE, A.CHAR_LENGTH, A.TABLE_NAME, A.OWNER FROM DBA_TAB_COLUMNS A WHERE A.TABLE_NAME NOT LIKE 'BIN$%' AND A.OWNER='DLV3733' AND (REGEXP_LIKE(UPPER(A.TABLE_NAME), ?)) ORDER BY A.COLUMN_ID [2024-03-18 09:58:02] Collecting 20 columns in DBA_INDEXES took: 0 wallclock secs ( 0.01 usr + 0.00 sys = 0.01 CPU) [2024-03-18 09:58:02] Retrieving foreign keys information... [2024-03-18 09:58:02] Retrieving unique keys information... [2024-03-18 09:58:02] Retrieving check constraints information... [2024-03-18 09:58:03] [1] Scanning table SntMessageInChats (605 rows)... [2024-03-18 09:58:03] ORACLE_HOME = /u01/app/oracle/product/19.0.0/client_1 [2024-03-18 09:58:03] NLS_LANG = AMERICAN_AMERICA.AL32UTF8 [2024-03-18 09:58:03] NLS_NCHAR = AL32UTF8 [2024-03-18 09:58:03] Trying to connect to database: dbi:Oracle:host=localhost;sid=database;port=1521 [2024-03-18 09:58:03] Isolation level: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE [2024-03-18 09:58:03] DEBUG: executing initial command to Oracle: commit [2024-03-18 09:58:03] Retrieving partitions information... [2024-03-18 09:58:03] Collecting 9 indexes in DBA_INDEXES took: 0 wallclock secs ( 0.00 usr + 0.01 sys = 0.01 CPU) [2024-03-18 09:58:03] Dropping foreign keys of table SntMessageInChats... NOTICE: constraint "fk0vpljo5oduagapo2ftmbnlw0iq" of relation "sntmessageinchats" does not exist, skipping NOTICE: constraint "fkbqbs9ei33djerzkhrdaoia4qe3e" of relation "sntmessageinchats" does not exist, skipping NOTICE: constraint "fklticnqatnh250rjilay2voqu" of relation "sntmessageinchats" does not exist, skipping NOTICE: constraint "fkwbixxftyddghzh4sillbg9c1ooy" of relation "sntmessageinchats" does not exist, skipping [2024-03-18 09:58:03] Disabling user triggers... [2024-03-18 09:58:03] Exporting data of table SntMessageInChats... [2024-03-18 09:58:03] Setting search_path using: SET search_path = orapost,public; ... [2024-03-18 09:58:03] Truncating table SntMessageInChats... [2024-03-18 09:58:03] Looking how to retrieve data from SntMessageInChats... [2024-03-18 09:58:03] DEGUG: Query sent to Oracle: SELECT "Id","CreatedOn","CreatedById","ModifiedOn","ModifiedById","ProcessListeners","SntContactId","SntOperatorContactId","SntChatId","SntOperatorId",CASE WHEN dbms_lob.getlength("SntMessage_Old") = 0 THEN NULL ELSE "SntMessage_Old" END,"SntSendByOperator","SntTypeMsg","SntMessageId","SntIsNotRead","SntClientName","IsPrompt",CASE WHEN dbms_lob.getlength("COLnd30jVj7makKp8CdwqlNBeEtO") = 0 THEN NULL ELSE "COLnd30jVj7makKp8CdwqlNBeEtO" END,"SNTMESSAGE","SntMessage" FROM "DLV3733"."SntMessageInChats" a [2024-03-18 09:58:03] Fetching all data from SntMessageInChats tuples... [2024-03-18 09:58:03] Dumping data from table SntMessageInChats into PostgreSQL table SntMessageInChats... [2024-03-18 09:58:03] Setting client_encoding to UTF8... [2024-03-18 09:58:03] Disabling synchronous commit when writing to PostgreSQL... [2024-03-18 09:58:03] Executing pre command to PostgreSQL: SET search_path = orapost,public;

[2024-03-18 09:58:03] DEBUG: Creating output for 150000 tuples [2024-03-18 09:58:03] DEBUG: Sending INSERT bulk output directly to PostgreSQL backend [2024-03-18 09:58:03] ERROR (log error enabled): ERROR: column "id" of relation "sntmessageinchats" does not exist LINE 1: INSERT INTO sntmessageinchats (id,createdon,createdbyid,modi... ^ Wide character in print at /usr/lib64/perl5/IO/Handle.pm line 417. [2024-03-18 09:58:03] ERROR (log error enabled): ERROR: column "id" of relation "sntmessageinchats" does not exist LINE 1: INSERT INTO sntmessageinchats (id,createdon,createdbyid,modi...

darold commented 3 months ago

Ok, Ora2Pg also extract data from the two column in Oracle: "SNTMESSAGE" and "SntMessage"

Does your Oracle table have these two columns? If this is the case and it is not an error (I think that logically it is a problem at Oracle side, how can you have 2 columns named identically except the case?) that mean that you have to switch to case sensitivity in Ora2Pg if you want to reproduce the same in PostgreSQL, see PRESERVE_CASE in ora2pg.conf.

avmindestroy commented 3 months ago

Thank you, it worked Question: I imported other tables earlier, it crashed with errors, now we have fixed the error and I need to re-run the COPY command?

darold commented 3 months ago

I depends, if there was no errors on other tables data migration, you can simply run the COPY action on the tables that had an error. See ALLOW configuration directive or the -a command line option