0

Problem is that when i have a file in a mediocre size (4+ chars) but below some 10k'ish.
Then the driver will default my String into a LONG, which results in this error:
ORA-01461: can bind a LONG value only for insert into a LONG column

Found out the error occurs with string sizes between 4k and 40k.

I've tried many many things to change this, but nothing seems to work. I really hope someone can help me.

Here is the controller class:

@RequestMapping(value = "/uploadFile/{companiesId}/{day}/{month}/{year}/{userId}/{orgId}",
            method = RequestMethod.POST, headers = "Accept=*")
    public void uploadFile(@PathVariable("companiesId") int companiesId,
            @PathVariable("day") int day, @PathVariable("month") int month,
            @PathVariable("year") int year, @PathVariable("userId") int userId,
            @RequestParam(value = "file") MultipartFile multipartFile,
            Model model, HttpServletRequest request, HttpServletResponse response, @PathVariable("orgId") Integer orgId) {
        Calendar date = Calendar.getInstance();
        date.set(Calendar.YEAR, year);
        date.set(year, month - 1, day);
        System.out.println("date: " + date.getTime());
        String fileName = multipartFile.getOriginalFilename();
        String fileContent = null;
        try {
            InputStream is = multipartFile.getInputStream();
            byte[] bytes = IOUtils.toByteArray(is);
            String fileData = new String(bytes);
            fileContent = fileData ;

        } catch (IOException ex) {
            java.util.logging.Logger.getLogger(HomeController.class.getName()).log(Level.SEVERE, null, ex);
            throw new RuntimeException(ex);
        }
        Session session = sessionFactory.openSession();
        long insertedFileId = -1;
        Transaction t = session.beginTransaction();
        t.begin();
        if (fileContent != null) {
            ImportFile file = new ImportFile();
            file.setFileDate(date.getTime());
            file.setFileName(fileName);
            file.setContent(fileContent);
            file.setChangedBy(userId);
            file.setCreatedBy(userId);
            file.setCompaniesId(companiesId);
            file.setOrgId(orgId);
            session.persist(file);
            insertedFileId = file.getId();
        }
        t.commit();
}

Here is Entity class:

    @Id
    @Column(name = "IMPORT_COST_FILES_ID")
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "WHMOD_SEQ")
    @SequenceGenerator(name = "WHMOD_SEQ", sequenceName = "warehouse.WHMOD_SEQ")
    private Long id;

    @Column(name = "FILE_NAME")
    private String fileName;
    @Column(name = "COMPANIES_ID")
    private Integer companiesId;
    @Column(name = "CHANGED_BY")
    private Integer changedBy;
    @Column(name = "CREATED_BY")
    private Integer createdBy;
    @Column(name = "FILE_CONTENT")
    @Lob
    private String content;
    @Column(name = "File_date")
    @Temporal(javax.persistence.TemporalType.DATE)
    private Date fileDate;
    @Column(name = "ORG_ID")
    private Integer orgId;
}

Here is relevant POM.xml info:

    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-entitymanager</artifactId>
        <version>3.6.0.Final</version>
        <scope>compile</scope>
    </dependency>

    <dependency>
        <groupId>hsqldb</groupId>
        <artifactId>hsqldb</artifactId>
        <version>1.8.0.10</version>
    </dependency>

    <dependency>
        <groupId>org.hibernate.javax.persistence</groupId>
        <artifactId>hibernate-jpa-2.0-api</artifactId>
        <version>1.0.0.Final</version>
        <scope>compile</scope>
    </dependency>

    <!-- Persistence Dependencies -->
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>3.3.2.GA</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-annotations</artifactId>
        <version>3.3.1.GA</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-commons-annotations</artifactId>
        <version>3.3.0.ga</version>
    </dependency>

Here is my DB settings xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:jee="http://www.springframework.org/schema/jee"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:jdbc="http://www.springframework.org/schema/jdbc"
       xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd
        http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.0.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">

    <description>Example configuration to get you started.</description>
    <jee:jndi-lookup id="datasource" jndi-name="jdbc/LogisticsUserCoreDS"/> 
    <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>

    <bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
        <property name="dataSource" ref="datasource" />
        <property name="packagesToScan" value="dk.org.importcost.domain" />

        <property name="schemaUpdate" value="false" />


        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory
                </prop>
                <prop key="hibernate.auto-import">true</prop>
                <prop key="hibernate.format_sql">true</prop>
                <prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.query.factory_class">org.hibernate.hql.classic.ClassicQueryTranslatorFactory</prop>
            </props>
        </property>
    </bean>

    <tx:annotation-driven transaction-manager="transactionManager"/>
    <context:component-scan base-package="dk.org.importcost.domain"/>
</beans>

Table DLL

 CREATE TABLE "WAREHOUSE"."IMPORT_COST_FILES" 
   (    "IMPORT_COST_FILES_ID" NUMBER(10,0) NOT NULL ENABLE, 
    "FILE_NAME" VARCHAR2(80 BYTE) NOT NULL ENABLE, 
    "FILE_DATE" DATE NOT NULL ENABLE, 
    "FILE_CONTENT" CLOB, 
    "STATUS_TYPES_ID" NUMBER(10,0), 
    "COMPANIES_ID" NUMBER(10,0) NOT NULL ENABLE, 
    "DELETED" VARCHAR2(1 BYTE) DEFAULT 'N', 
    "CREATED_BY" NUMBER(10,0) NOT NULL ENABLE, 
    "CREATED_DATE" DATE NOT NULL ENABLE, 
    "CHANGED_BY" NUMBER(10,0), 
    "CHANGED_DATE" DATE, 
    "ERROR_MSG" VARCHAR2(100 BYTE), 
    "ORG_ID" NUMBER(10,0), 
     CONSTRAINT "IMPORT_COST_FILES_PK" PRIMARY KEY ("IMPORT_COST_FILES_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "INDX"  ENABLE, 
     CONSTRAINT "WHICF_USERS_FK_1" FOREIGN KEY ("CREATED_BY")
      REFERENCES "SYSMODULE"."USERS" ("USERS_ID") ENABLE, 
     CONSTRAINT "WHICF_COMP_FK" FOREIGN KEY ("COMPANIES_ID")
      REFERENCES "SYSMODULE"."COMPANIES" ("COMPANIES_ID") ENABLE, 
     CONSTRAINT "WHICF_USERS_FK_2" FOREIGN KEY ("CHANGED_BY")
      REFERENCES "SYSMODULE"."USERS" ("USERS_ID") ENABLE, 
     CONSTRAINT "WHICF_STATYP_FK" FOREIGN KEY ("STATUS_TYPES_ID")
      REFERENCES "LOGISTICS"."STATUS_TYPES" ("STATUS_TYPES_ID") ENABLE, 
     CONSTRAINT "IMPCOST_COMP_FK" FOREIGN KEY ("ORG_ID")
      REFERENCES "SYSMODULE"."COMPANIES" ("COMPANIES_ID") ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA" 
 LOB ("FILE_CONTENT") STORE AS BASICFILE (
  TABLESPACE "DATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

DB driver version: DatabaseMetaData metadata = session.connection().getMetaData(); = version:10.2.0.3.0

Anders Metnik
  • 6,096
  • 7
  • 40
  • 79
  • Which (exact) Oracle version are you using? And what is the version of the JDBC driver? (the number in the filename is **not** the driver version, it's the intended **Java** version). And what is the `create table` statement of the table in question? –  Jan 13 '15 at 07:39
  • How do I see the driver version then? (create table statement will be up in a sec) – Anders Metnik Jan 13 '15 at 07:51
  • either call `DatabaseMetaData.getDriverVersion()` or look in the `MANIFEST.MF` (inside the jar) - newer versions have the driver version in there. –  Jan 13 '15 at 07:53
  • I've tried that, results in same error (actually I've reverted back to that). – Anders Metnik Jan 13 '15 at 07:58
  • In this answer this seems to work: http://stackoverflow.com/a/1645780/330315 –  Jan 13 '15 at 08:00
  • DatabaseMetaData does not have an static method. – Anders Metnik Jan 13 '15 at 08:00
  • 1
    AFAIK `@Lob` should be placed on a `byte[]` or `Byte[]` for binary data or `char[]` or `String` for character data. It shouldn't be a `Clob` type. – M. Deinum Jan 13 '15 at 08:01
  • Another thing you are mixing hibernate versions, don't do that. When using `hibernate-entitymanager` as of `3.6` the `hibernate-core` version should match the version. You shold be able to remove the dependency completely. – M. Deinum Jan 13 '15 at 08:04
  • @M.Deinum updated Q to reflect me using string instead of clob – Anders Metnik Jan 13 '15 at 08:04
  • `10.2.0.3.0` is a pretty old driver. Are you still using Oracle 10 as well? (which is no longer supported) –  Jan 13 '15 at 08:24
  • No we are using OracleDB 11g, but the driver afaik, comes is the one placed on the apache Tomcat, and it's being used by a lot of other programs, so can't be changed. – Anders Metnik Jan 13 '15 at 10:18

0 Answers0