An error is reported for data synchronization Caused by: java.sql.BatchUpdateException: ORA-01461

[mtzcsc]

Search before asking

• [X] I had searched in the issues and found no similar issues.

What happened

An error is reported for data synchronization Caused by: java.sql.BatchUpdateException: ORA-01461: 仅能绑定要插入 LONG 列的 LONG 值

SeaTunnel Version

2.3.4

SeaTunnel Config

# Defining the runtime environment
env {
  parallelism = 2
  job.mode = "BATCH"
}
source{
    Jdbc {
        url = "jdbc:oracle:thin:@ip:1521/ORCL_1"
        driver = "oracle.jdbc.OracleDriver"
        user = "c"
        password = ""
        query = "select ORG_CODE  JGBM  , PERSON_ID HZWYBS  ,'1' DATA_SOURCE, '1' OPERATIONFLAG ,'' JKDABM , NVL(substr(replace(INS_PROP_CODE,'无',''),0,2),'9') HZYBLXBM ,IDCARD KH ,'' KLX  ,'' FKDQ ,'0' HZLX  ,NVL(IDCARD_TYPE_CODE,'01') SFZJLBBM ,NVL(trim(IDCARD),'000000000000000000') SFZJHM ,substr(NAME,0,10) HZXM  ,replace(SEX_CODE,'无','')  XBBM  ,DATE_OF_BIRTH CSRQ  ,'' NLDW ,TEL DHHM  ,TEL SJHM  , '-' DZYJDZ  ,'-' XLBM  ,replace(CITIZENSHIP_CODE,'无','')  GJBM  ,replace(NATION_CODE,'无','') MZBM  ,replace(MARITAL_CODE,'无','')  HYZKBM  , CONTACT_NAME  LXRXM    from caching2.TM_OUT_PERSON_INFO p  where rksj>=trunc(sysdate-3)"
    }
}

transform {
    # If you would like to get more information about how to configure seatunnel and see full list of transform plugins,
    # please go to https://seatunnel.apache.org/docs/transform-v2/sql
}

sink {
    Jdbc {
        url = "jdbc:oracle:thin:@ip:1521/ORCL_1"
        driver = "oracle.jdbc.OracleDriver"
        user = ""
        password = ""

        generate_sink_sql = "true"
        database = "ORCL"
        table = "EHR2.HZJBXXB"
        enable_upsert = "true"
        primary_keys = [JGBM, HZWYBS, DATA_SOURCE, OPERATIONFLAG]
        #schema_save_mode = "CREATE_SCHEMA_WHEN_NOT_EXIST"
    }
}

Running Command

Invoked via DS

Error Exception

Caused by: java.sql.BatchUpdateException: ORA-01461: 仅能绑定要插入 LONG 列的 LONG 值

Zeta or Flink or Spark Version

No response

Java or Scala Version

No response

Screenshots

Are you willing to submit PR?

• [ ] Yes I am willing to submit a PR!

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

source:

create table TM_OUT_PERSON_INFO ( org_code VARCHAR2(50) not null, org_name VARCHAR2(400) not null, person_id VARCHAR2(50) not null, name VARCHAR2(800) not null, name_phonetic VARCHAR2(100), sex_code VARCHAR2(20) not null, date_of_birth DATE, age VARCHAR2(50), tel VARCHAR2(50), idcard_type_code VARCHAR2(50), idcard VARCHAR2(50), insurance_no VARCHAR2(200), computer_no VARCHAR2(200), health_card_no VARCHAR2(400), contact_name VARCHAR2(400), ref_name VARCHAR2(100), contact_idcard_type_code VARCHAR2(50), contact_idcard VARCHAR2(50), contact_tel VARCHAR2(400), present_addr VARCHAR2(255), workunit_name VARCHAR2(255), citizenship_code VARCHAR2(50), nation_code VARCHAR2(50), marital_code VARCHAR2(50), native_place_code VARCHAR2(50), career_code VARCHAR2(50), career_name VARCHAR2(255), org_internal_id VARCHAR2(50), last_edited_time DATE not null, secret_level VARCHAR2(8), cancel_flag VARCHAR2(1) not null, reserve1 VARCHAR2(255), reserve2 VARCHAR2(255), reserve3 VARCHAR2(255), reserve4 VARCHAR2(255), birth_place VARCHAR2(300), birth_prov_code VARCHAR2(50), birth_prov_name VARCHAR2(50), birth_city_code VARCHAR2(50), birth_city_name VARCHAR2(100), birth_county_code VARCHAR2(50), birth_county_name VARCHAR2(400), birth_town_code VARCHAR2(50), birth_town_name VARCHAR2(200), degree VARCHAR2(30), blood_type_abo VARCHAR2(8), blood_type_rh VARCHAR2(8), health_card_issue_area VARCHAR2(10), person_live_type VARCHAR2(10), ins_type_code VARCHAR2(10), ins_type_name VARCHAR2(30), ins_prop_code VARCHAR2(10), ins_prop_name VARCHAR2(30), permanent_addr VARCHAR2(300), perm_prov_code VARCHAR2(50), perm_prov_name VARCHAR2(50), perm_city_code VARCHAR2(50), perm_city_name VARCHAR2(280), perm_county_code VARCHAR2(50), perm_county_name VARCHAR2(400), perm_town_code VARCHAR2(50), perm_town_name VARCHAR2(200), dead_tag VARCHAR2(10), dead_date DATE, create_date DATE, created_by_id VARCHAR2(50), created_by_name VARCHAR2(50), ehr_flag VARCHAR2(1) default 0, upload_time DATE default sysdate, empi_flag VARCHAR2(2) default '0', cda_flag VARCHAR2(1) default '0', rksj DATE default sysdate )

sink:

create table EHR2.HZJBXXB ( jgbm VARCHAR2(50) not null, hzwybs VARCHAR2(64) not null, operationflag CHAR(1) not null, jkdabm VARCHAR2(32), hzyblxbm VARCHAR2(100) not null, kh VARCHAR2(32), klx VARCHAR2(5), fkdq VARCHAR2(100), hzlx VARCHAR2(10) not null, sfzjlbbm VARCHAR2(2) not null, sfzjhm VARCHAR2(50) not null, hzxm VARCHAR2(50) not null, xbbm CHAR(1) not null, csrq DATE, nl VARCHAR2(100), nldw VARCHAR2(10), dhhm VARCHAR2(50), sjhm VARCHAR2(100), dzyjdz VARCHAR2(50), xlbm VARCHAR2(2), gjbm VARCHAR2(60), mzbm VARCHAR2(20), hyzkbm VARCHAR2(2), lxrxm VARCHAR2(200), lxrxzqhdm VARCHAR2(6), lxrs VARCHAR2(20), lxrshi VARCHAR2(20), lxrxq VARCHAR2(20), lxrx VARCHAR2(20), lxrc VARCHAR2(20), lxrmphm VARCHAR2(128), lxrdh VARCHAR2(200), lxryb VARCHAR2(30), lxrgx VARCHAR2(50), czdhjbz CHAR(1), csdxzqhdm VARCHAR2(100), csds VARCHAR2(20), csdshi VARCHAR2(20), csdxq VARCHAR2(20), csdx VARCHAR2(20), csdc VARCHAR2(20), csdmphm VARCHAR2(528), csdyb VARCHAR2(6), jzdxzqhdm VARCHAR2(6), jzds VARCHAR2(20), jzdshi VARCHAR2(20), jzdxq VARCHAR2(20), jzdx VARCHAR2(20), jzdc VARCHAR2(20), jzdmphm VARCHAR2(500), jzdyb VARCHAR2(6), hjdxzqhdm VARCHAR2(100), hjds VARCHAR2(20), hjdshi VARCHAR2(20), hjdxq VARCHAR2(20), hjdx VARCHAR2(20), hjdc VARCHAR2(20), hjdmphm VARCHAR2(500), hjdyb VARCHAR2(6), jgs VARCHAR2(20), jgshi VARCHAR2(20), gzdwmc VARCHAR2(200), gzdwdz VARCHAR2(128), gzdwdh VARCHAR2(20), gzdwyb VARCHAR2(6), createtime DATE default sysdate not null, updatetime DATE default sysdate, uploadtime DATE default sysdate, updateflag CHAR(1) default '0', handleflag CHAR(1) default '0', data_source CHAR(1) not null, rksj DATE default sysdate, zylb VARCHAR2(100), aboxx VARCHAR2(50), rhxx VARCHAR2(50), ywgms VARCHAR2(100), ywgmsqt VARCHAR2(200), bls VARCHAR2(200), ycs VARCHAR2(200), cjqk VARCHAR2(200), cjqkqt VARCHAR2(200), jzsfq VARCHAR2(100), jzsfqqt VARCHAR2(200), jzsmq VARCHAR2(100), jzsmqqt VARCHAR2(200), jzsxdjm VARCHAR2(100), jzsxdjmqt VARCHAR2(200), jzszn VARCHAR2(200), jzsznqt VARCHAR2(200), shhjcfpf VARCHAR2(100), shhjrllx VARCHAR2(100), shhjrllxqt VARCHAR2(200), shhjys VARCHAR2(100), shhjysqt VARCHAR2(100), shhjcs VARCHAR2(100), shhjqxl VARCHAR2(100), phr_created_date DATE, phr_create_doctor_code VARCHAR2(100), phr_create_doctor_name VARCHAR2(100), phr_create_org_code VARCHAR2(100), phr_create_org_name VARCHAR2(100), phr_create_org_phone VARCHAR2(100), phr_health_management_org_code VARCHAR2(100), phr_health_management_org_name VARCHAR2(100), phr_responsible_doctor_code VARCHAR2(100), phr_responsible_doctor_name VARCHAR2(100), degree VARCHAR2(50) )

[hailin0]

@mtzcsc please try schema_save_mode is RECREATE_SCHEMA

sink {
    Jdbc {

        schema_save_mode = "RECREATE_SCHEMA"
    }
}

[mtzcsc]

@hailin0 该表无法无法支持重建 ，不过有自动新建的表也出现相同的问题

[hailin0]

@mtzcsc Please add source table create table sql into this issue

[hailin0]

source table datatype

[mtzcsc]

Originally, the jar of the ojdbc8-12 version was adjusted, and the adjustment to the ojdbc8-19 version will not cause an error

[Carl-Zhou-CN]

Originally, the jar of the ojdbc8-12 version was adjusted, and the adjustment to the ojdbc8-19 version will not cause an error

Issue resolved, close this issue