CODEWITHSUNDEEP
javaoracle-databasejooq
and
and

Reputation: 127

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

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?

Upvotes: 2

Views: 529

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220952

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

Upvotes: 1

Related Questions