How to create a database table containing a RAW field with jooq

Question

Context and Problem

We use files containing metadata to describe data stored in csv-files. The metadata files contain the structure of the table the data was originally exported from. We use jooq (pro version) to generate the create statement for a temporary table in which the data from the csv files ist loaded. The generated ddl is afterwards executed by a pl/sql package.

This works fine in general but there is a problem with oracle raw fields. I cannot figure out how to create a table containing an oracle RAW, as SQLDataType does not contain RAW.

Simplified runnable Example

package ch.and.stackoverflow.questions;

import org.jooq.DataType;
import org.jooq.Field;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import org.jooq.impl.DefaultDataType;

import java.util.List;

public class JooqAndRawDatatypeFieldSimple {

    public static void main(final String[] args) {

        // VARCHAR2
        DataType<?> varchar2DataType = DefaultDataType.getDataType(SQLDialect.ORACLE12C, "VARCHAR2");
        varchar2DataType = varchar2DataType.length(24);
        Field<?> varchar2Field = DSL.field("VARCHAR2_COL", varchar2DataType);

        // NUMBER
        DataType<?> numberDataType = DefaultDataType.getDataType(SQLDialect.ORACLE12C, "NUMBER");
        numberDataType = numberDataType.precision(5).scale(2);
        Field<?> numberField = DSL.field("NUMBER_COL", numberDataType);

        // RAW
        DataType<?> rawDataType = DefaultDataType.getDataType(SQLDialect.ORACLE12C, "RAW");
        rawDataType = rawDataType.length(100);
        Field<?> rawField = DSL.field("RAW_COL", rawDataType);


        String sql = DSL.createTable("TEST_TABLE").columns(List.of(varchar2Field, numberField, rawField)).getSQL();

        System.out.println(sql);
    }
}

This results in following ddl:

CREATE TABLE "TEST_TABLE" (
    VARCHAR2_COL varchar2(24) NULL,
    NUMBER_COL number(5, 2) NULL,
    RAW_COL raw NULL
    )

The statement is invalid because RAW requires a size (https://docs.oracle.com/cd/E11882_01/server.112/e41085/sqlqr06002.htm#SQLQR959).

Question

How can I create a table in an oracle database containing a column with RAW datatype using jooq as generator for the ddl statement?

How did you get the RAW type into your DDL? By referencing OracleDataType.RAW directly? — Lukas Eder
– Lukas Eder, Commented Feb 15, 2021 at 16:06
No, by using DataType<?> dataType = DefaultDataType.getDataType(SQLDialect.ORACLE12C, columnMetaData.dataType); where columnMetaData.dataType is "RAW". And the creation of the field: DSL.field(columnMetaData.getColumnName(), dataType); — and
– and, Commented Feb 15, 2021 at 16:45

Lukas Eder · Accepted Answer · 2021-02-17 15:42:37Z

1

This appears to be a bug in jOOQ: https://github.com/jOOQ/jOOQ/issues/11455

You'll have to work around it by patching the generated SQL string, either via plain SQL templating or using an ExecuteListener

answered Feb 17, 2021 at 15:42

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create a database table containing a RAW field with jooq

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related