Failed to write output data to ORACLE date type field

[vicochan]

I have an example of writing output data to an ORALCE table,

the table structure is as follows

create table GZ_MIGRATE_HISTORICAL_30_TEST
(
snap_time DATE,
city VARCHAR2(20),
population_total NUMBER,
migrate_total NUMBER,
createon DATE default sysdate
)
The following table describes the YAML configuration：

pipeline:
  processors:
    - bloblang: |
        root.arr = content().split(",")
        root.snap_time = root.arr.index(0).string().ts_parse("20060102").ts_format("2006-01-02")
        root.city = root.arr.index(1).string()
        root.population_total = root.arr.index(2).int32()
        root.migrate_total = root.arr.index(3).int32()
        root.createon = now()
    - catch:
      - log:
          message: "Processing failed due to: ${!error()}"
output:
  sql_insert:
    driver: oracle
    dsn: oracle://user:[email protected]:1521/dbname
    table: GZ_MIGRATE_HISTORICAL_30_TEST
    columns:
      - snap_time
      - city
      - population_total
      - migrate_total
      - createon
    args_mapping:
      root = [ this.createon.ts_strftime("%Y-%b-%d %H:%M:%S"),this.city,this.population_total,this.migrate_total,this.createon.ts_strftime("%Y-%b-%d %H:%M:%S")] 

The error message is as follows:

level=error msg="Failed to send message to sql_insert: ORA-01861: literal does not match format string\n" @service=stc label="" path=root.output

Another way to write it

output:
  sql_raw:
    driver: oracle
    dsn: oracle://user:[email protected]:1521/dbname
    query: |
      INSERT INTO GZ_MIGRATE_HISTORICAL_30_TEST1 (snap_time,city,population_total,migrate_total,createon) VALUES(to_date($1,'yyyy-mm-dd'),$2,$3,$4,to_date($5,'yyyy-mm-dd'));
    args_mapping: |
      root = [
      this.createon,
      this.city,
      this.population_total,
      this.migrate_total,
      this.createon,
      ]

I also want to use the same mistake in another way, what is the syntax for writing the date type field of ORALCE?

[Jeffail]

Converting to a discussion as per #2026

[mihaitodor]

Hey @vicochan, the following works for me:

input:
  generate:
    count: 1
    mapping: |
      root.createon = "2024-03-06"

output:
  sql_raw:
    driver: oracle
    dsn: oracle://system:testpass@localhost:1521/FREEPDB1
    query: |
      INSERT INTO TEST (snap_time) VALUES(to_date(:1,'yyyy-mm-dd'))
    args_mapping: |
      root = [
        this.createon
      ]
    init_statement: create table TEST(snap_time DATE)

I used docker run --rm -it -p 1521:1521 -e ORACLE_PASSWORD=testpass gvenzl/oracle-free:slim-faststart to start the Oracle Docker container.

I guess you might have some error in your data. Please try adding some log processors after your bloblang with message: ${! error() } and message: ${! content() } to see if there are any issues.

[vicochan]

Thank you for answers