Ora2pg is adding new columns to some tables when exporting using type INSERT OR COPY

[graddaoui]

Congrats for massive work on ora2pg . ora2pg version : 21 database : oracle 11c postgresql : v12 i'm only needed to extract data from tables but i encoutred this issue : when i tried to import data to postgresql i get the error message "insert has more target columns than expressions" when i checked the error i saw that :

• multiple tables had an extra column added
• some columns are renamed : example "stagingid" ==> "tradesstagingid" / "historyref" ==> "snapshotid" while i applied renaming only for indexes . i really need help @darold

[darold]

Well if you have made some columns renaming then you should consider using configuration directives REPLACE_COLS in ora2pg.conf. About error message "insert has more target columns than expressions" I'm surprised because the target columns list is taken from the Oracle table definition so we must have the same number of expressions. Have you set MODIFY_STRUCT or REPLACE_QUERY in ora2pg.conf?

[graddaoui]

1/ i didn't use columns renaming only the directive reserved_words 2/ and also i didn't set modify_struct or replace_query . ora2pg gets me the insert statement but changing the column name sometimes . i didn't know why actually

[graddaoui]

This is my configuration file : https://products.groupdocs.app/editor/wysiwyg/WordProcessing/699603b1-d9f2-4daf-99df-cc08027d8665/conf.txt

[darold]

ora2pg gets me the insert statement but changing the column name sometimes

This is not possible unless you instruct Ora2Pg to do so but I've not heard for such a bug.

some columns are renamed : example "stagingid" ==> "tradesstagingid" / "historyref" ==> "snapshotid"

Please post the Oracle description of this table (text output of "DESC thetable") where column are renamed and also the DDL that was generated by Ora2Pg and that you have imported to PG.

[graddaoui]

this is the insert statement generated:

INSERT INTO colstatementhistory (id,historyid,ts,agreementid,counterpartyid,relationship,reportingcur,notiftime,timezone,principalia,counterpartyia,mtmexposure,totalexposure,mktvalassetprinc,adjprinccolval,mktvalassetctpy,adjctpycolval,portfoliochangedperc,specialreminder,agreementtype,valuationfreqperiod,valuationfreqday,reciprocity,princmintransamnt,princroundingamnt,margindelvprinc,marginreturnprinc,lockupdelvprinc,lockupretunprinc,ctpymintransamnt,ctpyroundingamnt,margindelvctpy,marginreturnctpy,lockupdelvctpy,lockupreturnctpy,marginmtmexposure,marginprincipalia,marginctpyia,margintotalexpamnt,princthreshold,ctpythreshold,adjexposureamnt,valueiacolheld,valueiacoldelivered,netmarginreq,deliveryamnt,recallamnt,lockupmarginreqprinc,lockupmarginreqcpty,curlockupmarginprinc,curlockupmargincpty,lockupmargincallprinc,lockupmargincallcpty,notes,resolutiontime,sameday,principalname,confirmedcolheld,pendingcolheld,confirmedcoldelivered,pendingcoldelivered,counterpartyextia,principalextia,extiacurrencyid,principalcalextia,counterpartycalextia,systemcalcprincipalextia,systemcalccounterpartyextia,cptyextiamodifytime,princextiamodifytime,overwriteiawithextia,securitysnapshotid,eod,itmexposure,otmexposure,itmmarginreq,otmmarginreq,adjitmexposureamnt,adjotmexposureamnt,intotalexposure,outtotalexposure,iadeliveryamnt,iarecallamnt,totalnotional,ratingcontigentidprin,ratingcontigentidcpty,ccpflag,conditionamount,vmcashbalance,vmsecuritiescol,totalsettlementamt,variationmargin,accruedcoupon,vmed,imcashbalance,imsecuritiescol,imrequirement,imed,cashavailable,neted,vmmktcashbalance,vmmktsecuritiescol,immktcashbalance,immktsecuritiescol,excludecollateral,unsettledupfrontfee,princimmintransamnt,princimroundingamnt,ctpyimmintransamnt,ctpyimroundingamnt,datemissmatchfxrate,datemissmatchsecurity,valuationdate,netsecurityvalue,netcashvalue,netnonsyscalvalue,wrongpriceexp,agreementexposureadj,multimodel,tradeineligible,tradeconflict,iaconflict,tsaconflict,settledimcashbalance,settledimsecuritiescol,settledvmcashbalance,settledvmsecuritiescol,pendingimcashbalance,pendingimsecuritiescol,pendingvmcashbalance,pendingvmsecuritiescol,tradescount,princimthreshold,ctpyimthreshold,vmimthresholdseparated,adjmtmexpamt,adjmtmitmexpamt,adjmtmotmexpamt,adjiaexpamt,physicaltsa,tsapassthrough,physicaltsacol,eligrulestemplateid,pricesource,pricemissed,pricemissedrepotrade,imvaluationfreqperiod,imsameday,imnotiftime,imresolutiontime,nettingmtmandia,ratingcontigentidprinim,ratingcontigentidcptyim,prinpayscash,ctpypayscash,confirmedcashbalance,pendingcashbalance,prinpaysfee,ctpypaysfee,confirmedfeebalance,pendingfeebalance,cashreq,feereq,conditionamountreceive,virtualadjprinccolval,virtualadjctpycolval,virtualmargindelvprinc,virtualmargindelvctpy,virtualmarginreturnprinc,virtualmarginreturnctpy,virtualdeliveryamnt,virtualrecallamnt,virtualiadeliveryamnt,virtualiarecallamnt,ecrule,heldvmec,heldimec,pledgedvmec,pledgedimec,clbreachadjustment,princladjcolval,cptycladjcolval,prinvmcladjcolval,prinimcladjcolval,cptyvmcladjcolval,cptyimcladjcolval,prinvmpostcladjcolval,prinimpostcladjcolval,cptyvmpostcladjcolval,cptyimpostcladjcolval,prcvmcladjusted,prcimcladjusted,cptyvmcladjusted,cptyimcladjusted,prcvmclmktval,prcimclmktval,cptyvmclmktval,cptyimclmktval,princfinraexempt,cptyfinraexempt,princorgfinraexempt,cptyorgfinraexempt,repotradesfinraexempt,revrepotradesfinraexempt,princfinrabuf,cptyfinrabuf,prinrqv,cptyrqv,prinimrqv,cptyimrqv,preprinrqv,precptyrqv,preprinimrqv,precptyimrqv,prinrqvon,prinimrqvon,cptyrqvon,cptyimrqvon,genlock,accountcashbalance,cashamounts,currented,currentedglobalccy,endingbalance,imcashdeposit,imcashed,imcollateral,imdcvm,imr,imsecuritiesdeposit,imsecuritiesed,lastbalancefeedtime,mmr,netedglobalccy,netplcleared,nlv,nov,openingbalance,ote,pendinged,pendingmovesglobalccy,regmoves,todaysettledvmcash,todaysettledimcash,todaysettledimsec,totalequity,totalnetpl,pendingedglobalccy,virtualimed,virtualpendingimcashbalance,virtualpendingimsecuritiescol,virtualpendingvmcashbalance,virtualimcashed,virtualimsecuritiesed,virtualpendinged,virtualpendingedglobalccy,virtualpendingmovesglobalccy,custodyfee,appliedbuffer,prebufferneted,prebufferpendinged,prebuffervirtualpendinged,dfr,dfsecuritiesbalance,dfcashbalance,dfcollateral,dfed,dfedglobalccy,dfpendinged,virtualdfpendinged,dfpendingedglobalccy,virtualdfpendingedglobalccy,dfpendingmovesglobalccy,virtualdfpendingmovesglobalccy,dfappliedbuffer,dfprebufferneted,dfprebufferpendinged,dfprebuffervirtualpendinged,pendingdfcashbalance,virtualpendingdfcashbalance,pendingdfsecuritiescol,virtualpendingdfsecuritiescol,todaysettleddfcash,todaysettleddfsec,dfmktcashbalance,dfmktsecuritiescol,etdimcashbalance,etdimsecuritiescol,etddfcashbalance,etddfsecuritiescol,settleddfcashbalance,settleddfsecuritiescol,adjprincdfcolval,adjctpydfcolval,prcdfclmktval,cptydfclmktval,principalngr,cptyngr,missingiapercentage)  VALUES ...

the securitysnapshotid column doesn't exist in my oracle table : i didn't understand why it's added and this is an example where it's been renamed, the insert statement generated by ora2pg

INSERT INTO feed_staging_bad_record (tradestagingid,jobexecutionid,recorddata,rejectreason,rownumber)  VALUES ...

while my table's columns description is (stagingid,jobexecutionid,recorddata,rejectreason,rownumber) , this was changed in multiple table that made manual correction too hard and time costly

[darold]

Pleas post the result of DESC COLLINE_MIGRATION.feed_staging_bad_record from your Oracle database.

[graddaoui]

this is the output :

Name           Null?    Type       
-------------- -------- ---------- 
STAGINGID      NOT NULL NUMBER(19) 
JOBEXECUTIONID             NUMBER(19) 
RECORDDATA                   CLOB       
REJECTREASON                CLOB       
ROWNUMBER                  NUMBER(19) 

[darold]

Not reproducible, please use latest development code and give it a test. If you still have the problem let me know.

[graddaoui]

okay thanks