Forum Stats

  • 3,853,805 Users
  • 2,264,277 Discussions
  • 7,905,450 Comments

Discussions

Excel Import

user929252
user929252 Member Posts: 2
edited Dec 10, 2009 4:54PM in SQL Developer
Hi

We are trying to load an excel file using Sqldeveloper.
The message we are getting is this

SQL Error:ORA-12899: value too large for column "APPS"."ENTERED_PSO_DETAILS"."FORM_STATUS" (actual: 4, maximum: 3)

The field is VARCHAR2(3 BYTE)

Is there a way of stopping sqldeveloper from putting in the word NULL into that field.

Cheers
Tagged:

Answers

  • Dermot ONeill-Oracle
    Dermot ONeill-Oracle Member Posts: 618 Employee
    Hi <Please Supply Your Name>,

    The import doesn't yet support manipulation of data.
    A solution would be to search and replace the word NULL with nothing in your excel sheet before import. I assume you don't want to keep the actual word "NULL".

    I double checked to see how we export null values , and these are exported as nothing. So there shouldn't be an issue exporting and the importing using SQL Developer.

    In the latest internal build
    Our imported catches this at the valid stage of the wizard, where is notes that the value is too large for the column.

    Regards,
    Dermot
    SQL Developer Team
  • Hi

    Thanks for that

    IN our excel spreadsheet the value for that column is empty, we aren't populating it at all, its sql developer that is then converting the empty cell to 'NULL'
    Just need to work out if there is a way of stopping developer from doing this, I presume it would work if it was NULL and not 'NULL'

    Cheers
  • Dermot ONeill-Oracle
    Dermot ONeill-Oracle Member Posts: 618 Employee
    edited Dec 10, 2009 4:54PM
    Hi,

    I went through this again as I wasn't able to replicate the issue.
    But ... in RC1 there was an issue where the data and columns got mixed up when empty(null) values where present.

    Ex Excel:

    COL1 | COL2
    --------------------
    _____| abcd

    Where col1 value is empty.

    SQL Developer RC1 was reading this as
    COL1 | COL2
    --------------------
    abcd | _____

    This was logged and fixed
    Bug 9179010 - RC1: ISSUES WITH IMPORT XLS HANDLING OF NULL CELLS

    This would cause a similar issue as to the one you experienced, because a value in COL2 in your excel sheet may have been 4 chars in length. But SQL Developer tried to insert the value into COL1 in your table.

    This is fixed now and will be available in the next release.
    I haven't been able to replicate SQL Developer changing the data from empty(null) to the string 'NULL' though.
    You can check the exact syntax used by selecting "Send to worksheet". In RC1 it will be in the wrong order, but you could check if 'NULL' strings are being created or not.

    Regards,
    Dermot
    SQL Developer Team

    Edited by: Dermot ONeill on Dec 10, 2009 1:53 PM
    Dermot ONeill-Oracle
This discussion has been closed.