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
1.03k stars 341 forks source link

replace_query any question.... #856

Open hataeheung opened 4 years ago

hataeheung commented 4 years ago

Hi..

I am using ora2pg to export oracle table in postgresql. Created table should contains the fields of join to two different tables of Oracle.

example

REPLACE_QUERY   NC_PROD_DLV_INFO[SELECT a.*, b.prod_no FROM NC_PROD_DLV_INFO a JOIN NC_PROD b ON (a.prod_dlv_info_seq = b.prod_dlv_info_seq)

error

[2020-03-13 09:30:56] Looking how to retrieve data from NC_PROD_DLV_INFO...
[2020-03-13 09:30:56] DEGUG: Query sent to Oracle: SELECT "PROD_DLV_INFO_SEQ","ACNT_NO","DLV_MTH_TP_CD","QUICKSVC_DLV_AREA_CONT","VST_RCPT_ADRBK_SEQ","DLV_BDL_GRP_SEQ","DLVFEE_CL_CD","BASE_DLVFEE","CONDL_FRE_AMT","REPT_CARG_BSS_QTY","SECN_BSS_QTY1","SECN_BSS_QTY2","QTYBY_EXTRAFEE1","QTYBY_EXTRAFEE2","PAY_CL_CD","DLV_AREA_TP_CD","AREA2_EXTRAFEE","AREA3_EXTRAFEE","RTN_DLVSVC_CO_SEQ","RTN_DLVFEE","EXCG_DLVFEE","SHMTPL_ADRBK_SEQ","RTNPL_ADRBK_SEQ","PCIN_GTH_YN","AREABY_DIFF_DLVFEE_INFO","ADD_ISTLFEE_YN","DLV_ATTR_TP_CD","ORDR_DLV_EXPCT_PRD_TP_CD","ORDR_DLV_EXPCT_PRD_DRT_INPT","TDAY_STCK_QTY","CUST_PROD_AFT_ORDER_YN","DLV_HOPE_GRP_SEQ" FROM "NCPAPPO"."NC_PROD_DLV_INFO" a
[2020-03-13 09:30:56] Fetching all data from NC_PROD_DLV_INFO tuples...
[2020-03-13 09:30:57] DEBUG: number of rows 99 extracted from table NC_PROD_DLV_INFO
[2020-03-13 09:30:57] Dumping data from table NC_PROD_DLV_INFO into PostgreSQL table NC_PROD_DLV_INFO...
[2020-03-13 09:30:57] Setting client_encoding to UTF8...
[2020-03-13 09:30:57] Disabling synchronous commit when writing to PostgreSQL...
[2020-03-13 09:30:57] DEBUG: Creating output for 10000 tuples
[2020-03-13 09:30:57] DEBUG: Sending INSERT bulk output directly to PostgreSQL backend
DBD::Pg::st execute failed: called with 33 bind variables when 32 are needed at /usr/local/share/perl5/Ora2Pg.pm line 14581.
[2020-03-13 09:30:57] FATAL: called with 33 bind variables when 32 are needed
Aborting export...
darold commented 4 years ago

You can not add an additional column from an other table in the target list. Here b.prod_no must be removed or the corresponding column must be added to NC_PROD_DLV_INFO. I understand that adding a column to Oracle table is something very intrusive and should be avoid, I will add a configuration directive to be able to add a list of column to handle this particular case.

hataeheung commented 4 years ago

You can not add an additional column from an other table in the target list. Here b.prod_no must be removed or the corresponding column must be added to NC_PROD_DLV_INFO. I understand that adding a column to Oracle table is something very intrusive and should be avoid, I will add a configuration directive to be able to add a list of column to handle this particular case. 👍 💯

I hope that the function is completed quickly. How long does it take? This is a really necessary function.