Excel Import

user929252

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

-K-

LOL!

Dermot ONeill-Oracle

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

user929252

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

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