Currently we are doing a massive import of 2002-2010 fiscal year data from Lotus Notes to Oracle. We are using CSV files and importing them into Oracle. The Lotus Notes version was much more lenient on string lengths and with moving to Oracle we have implemented maximum string lengths on most of the fields. One of these is crashing Toad on import. Toad gets to the line and the Abort, Trim, Trim and don’t ask again dialog appears. If Trim or Trim and don’t ask again is chosen, the Toad software crashes. Since the data is confidential I created scripts to recreate the error.
CREATE TABLE RFS_TEST
(
COMMENT VARCHAR2(4000 BYTE)
)
TABLESPACE OSDS_TS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 80K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
Next create a text file with following item:
" aaaaaaaaaaa aaaaaaa aaaaaaa ; aaaaaaa aaaaaaaa; 1. aaaaaaa: aaa aaaaaaaaaaa aaaaaaa aaaaaaa (aaa) aa a aaa-aaaa aaaaaaa aaaaaaaa aa aaaaaaa aaaaaaaaaa aaaaaaaaa aa aaaaaaa aaaaaaaaaaa aaaaaaa, aaaaaaaaaaa, aaa aaaaaaaa, aaa aaa aaaaaaa aaaaaaa aa aaaaaaaaa aaaaaaaaaa aaaaaaa aa aaaaaaaaa aaaaaaaaaaa aaaaaaaaaaaaa aaaa aaa aaaaaaaaaa aaaaaaaaaaa aa aaaaa aaaaa aa aaaaaaaa. aa aaaaaaaa aa aaa aaaaa aaaaaaaaa aa aaaaaaa aaa aaaaaaaaaaa, aaaaaaaaaa, aaa aaaaaaaaa, aaa aaa aa aaaaaaa aa aaa aa aaaa aaaaaa aa aaaaaaaa aaaaaaa (aaa), aaaaaa aa aaa aaa aaa aaaaaaaa (aaaaaaaa aaa $250,000 aaaa), aaa aaaaaaaa aa a aaaaa-aaaaaaa aaaaaaaaa aaaaa. aaaaaa aaa aaaaa aaaa aa aaa aaaaaaa (aa 01) aaa aaaaaaa aaa aaaaaaaaaa aaaaaaaa aa a aaa aaaaaaa aaaaaaaaaa, aaaa aaa aaaaaa aaaaaa aaaa aaaaaa aaaaaaaaaa aaaaaaaaaaaaaaaa aaaa aaaa. aaa aa 92 aaaaa aaa aaa aaaaaaa aaaa aa aaaaaa aa aaa aaaaaa aa aaa aaaaaaa, aaaaaaaa, aaaaaaaaaaaaa, aaa aaaaaaaa aaaaaaaaa, aaa aaa aaaaa aaaaaaa aaaaaaaa aa aaaaa aaaaa.; 2. aaaa aaaaaaaaaaaa: aaa aaaaa aaaaaaaa aa aaa aaa aaaa aa aaaa aaaaaaaaaaaaa aaaaaaa aaa aaa aaaaaaaaaaa aaaaaa aaaaaaaa aaa aaaaaaa aa aaaaaaa aaaaaaaaaaa (aaa aaaaaa/aaaaaaa aaaaaaaaaaaaaa) aaaa aaa aaa aa aaaaa aaa aa aa aaa aaaaaaa. aaaa aaaaaaaaaaaaa aaaaaa aaaa aa aaaaaaaaa aa a aaaaaaaa aaaaaaaaa aaaa a aaaaaa aa aaa aaaaa aaa aaaa aaaaaaaaaaaaa aaaaaaa aaaaaaaa, a aaaaaaaaaa, aaaaaaaa aaaaaaaaaaaa aaaaaaaa, aaaaaaaaaaaa, aaa aaaaaaaa aaaa. aaaaaa aaaaaa aaaa aa 500-800 aaaaaaaaaa aaaaa (aaaa 30-50 aaaaaaaaaa aaaa aaaaaaaa), aa aaaaaaa aaaaaaaaa; aaa aaaaaaaa aaaa aaaa aa aaaaaaaaa aa a aaaa-aaaaaaaaaa aaaaaa aaaaaaaaaa aa aaa aaaaaaaa aaaaaaaa. aaaaaa aaa aaaaa aaaa, aaaa aaaaaaaa aaaaaaaaaa aaaa aaaaa aaa aaaaaaa aaaaaaaaaa aaa aaaaaaa aaaaa aaaa aaaaaaaaa; aaa aaaaaa-aaaaaaaaaa aaaa aaaaa aaaaaaaaa aaa aaaaaaaaa aaaaa aaaaaaaa aaa aaa aaaa aaaa aaaaaaa aaaaa. aaa aaaaaaaa aaaaaaaaa aaaaaaaaa aaa aaaaaaaaaaa aaaaaa aaaaa aaaaa aa aa aaaaa aaa aaa aaaaaaa aaaaaaaaaa aaa aaaaa aaaaaaaa aaa aaaa aaaa.; 2. aaaaaaaaa aaaaaaaaaaaa:; a. aa aaaaa 50 aaaa aaaaaaaaa aaaa aaaaaaaaaaa aaaaaaa aaaaaaaaaaa aaaaaaaa, aa aa aaaaaaaa aa aaaaaa-aaaaaaaaaa aaaaaa aaa aaaaaaa, aa 10 aaa aaaaaa-aaaaaaaaa, aaaa aa aaaaa aaa aaaaa aaaa, aaaaaaaaaaa, aaa aaaaaaaaa aa aaaaa-aaaa, aaaa-aaa aaaaaa aa aaaaaaa aaaaaaaa aa aaaaa 500 aaaaaa, aaa aaa aa aaa aaaaaaa aaaaaaaaaaa aaaaaa aaaaaa. aaaa aaaaaa-aaaaaaaaa aaaaaaaa aa aaaaa aaa aaaaaa aaaa 1 aaa aaaa aaaaaaa 1-2 aaaa aaaa aaaaaaaaaa. aaa aaaaaaaa aaaa aaaaaaaaa aaaa aa aa aaaaaaaaaa aaa aaaaaaaaaaa; aaaaaa aaaaaaa aa aaaa aaaaaaaaaa aaaa aa aaaaaaaaa aa a aaaaaaaa aaaaaaaaaaaaa aaaaaaaa.; a. aaa aaaaaaa aaaa aaaaaaaa aaaaaa aaaa 1 aa a aaaaaaaaa aaaaaaa aaaaaaaaa, aaa aaaaaaa aaaaaaaaa aaaa aaaaaaaaa aa aaa aaaaaaaaaaa aaaaa aaa a aaaaaa aaaaaaa aaa aaaaaaaa aaaa aaaaaaa, aa aa aaaaaaaaa aa a aaaa-aaaaa aaaaaaa aaaaaaaa aaaaaa aa aaaa 2 aaaa aaaaaaa aaaaaaaa aaaaaaaaa aa aaaa 5 aa 6.; a. aaaaaa aaaa 1 aaa aaaaaa-aaaaaaaaaa aaaaaaaaa aaaaaaaa aa aaa aaaaaaa aaaaaa aaaaaaaaaa aaaaaaa aa aaaa aa aaa aaaaaaa aaaaaaaa. aa aaaaaaa aaaaaaaa aaa aaaaaaaa aa aaa aaa aaa aaa aaaaaaaaa aa aaa aaaaaa.; a. aaa aaaaaaaaaaa aaaaaaa aaaaaaaaaa aaa aaaa aa aaa aaa aa aaaa 1 aaa a aaaaaa aaaa aa aaaa aa aaa aaaaaaaaa aa aaaa 6. aaa aaaa, aaa aaaaaa-aaaaaaaaaa aaa aaaaaaaa aa aaaaaaa aaaaaaaaaaa aaaaaaaaaaaaa, aaaa aaa aaaaaaa aa aaa aaaaaaaa aaaaaa aaa aa aaaaa aaaaaaaa aaa aa aaaaaaaaaaa aaaaaaaaaaaaa. aaa aaaaaaaaaaa aa aaa aaaaaaaaa 2001 aaaaaaaaaa aaaa aa aaaaaa aaa aaaaaaaaa aaaaaa aaaa 2 aa aaa aaaaaaa aa a aaaa-aaaaa aaaaaaa aaaaaaaa aaaaaa.; a. aaa aaa aaaaaaaaaa aaaaaaaa aaaaaaaaa aa aaaa aaa aaaaaaaaaa aaaaaa, aaaaaaaaaaa aa aaaaaaaaaa aaaaaaa, aaa aaaaaa aa aaa aaa aaa aaaa aaaaaa aaaa 1 aaaa aaaaaaaa aaaaa aaa aaaaaaaaaaa aa aaa aaaaaaaaa-aaaaaaaaa aaaa aa aa aaaaa. aaa aaaaaa aaaa aaaaaaa aaaaaaaa aaaaaaaa aa aaa aaaaaaa aaa aaaaaa aaaaaa (aaaa aa aaaaaaaaaaa, aaaaaaaa, aaaaaa aaaaaaaa, aaa.) aaa aaaaaaa aaa aaaaaaaaa aaa aaaaaaaa (aaaaaa aaaaaaaaaa, aaaaaaaaaa, aaaaaa, aaaaaaaaa, aaaaaaaaaaa) aa aaaaaa aaaaaaaaa aa aaa aaaaaaaaaaa aaaaaaaaa.; a. aaa aaaaaaa aaaaaaaaa aaaaaaaa aaaaaaaa aaaaaa, aaaaaaaaaaa aaaaaa aaaa 1 aaa aaaaaaa aaaaaaaaa aaaaaaaaa aa aaaaaa-aaaaaaaaaa, aaaa aa aaaaaaaaaa aa aaa aaaaaaaa aaaaaaaaa, aaaa a aaaa aa aaaaaaaaaaaaa aaaaaaa aaaaaaaa aaaaaaaaa aaaaaaaa aaaaaaaa aa aaaa 2. aaaaa aaa aaaaaaa-aaaaaaaaa aaaa aaaaaaaa aaaaaaaaa aa aa aaaaaaaaa, aaa aaaa aaaa aa aaaaaaaaa aa a aaaaaaaa aaaaaaaaaaaa aaaaaaaa.; a. aaaaaaaaa aa aaa aaaaaaaa aaaaaaaaaa, aaa aaaaaaaa aaaaaaaaa aaaa aaaa aaaaaaaa a aaaaaaaaa aaaaa aaaaaaaa, aa aaaaaaa aa aaa aaaaaaaaa, aaaaaaaa aaaaaa aaaaaaaaaa, aaaaaaaa aaaaaa, aaaa aaaaaaaaaa, aaaaaaaaaaa aaaaaaaaa aaaaaaaaaa, aaa. aa aa aaaaaaaaa aa aaa aaa aaaaa aaaaaaaaaaa aa a aaaaaaaa aaaa aa aaa aaaaaaa aaaaaaaaaaa aaaaaaaaa.; a. aaa aaaa aaaaaaaa aa aaaaaaa aaa aaaaaa aaaaaaa aaa aaa aaaaaaa, aa aaaaaaa aaaaaa, aa aaaaaaaaa, aaaaaaaaa, aaaa aaaaa, aaaaaaaaa aaaaaaaaaa, aaaaaaaaaaaa aaaaaaaaaa aaa aaaaaaa aaaaa aaa aaaaaaaa.; a. aaaaaaaaaa aaaaaaaaa aaaa aa aaaaaaaa aaaaaa aaa aaaa aa aaaaaaaaaaa aaaaaa aaaaa aa aaaaaaaaa.; a. aa aaa aaaaa aaaa aa aaa aaaaaaa, aaaaa aaaa aa aaaaaaaa aaa aaaaaaa, aaaaaaaa, aaaa aaaaaaa, aaa aaaaaaaa aaa aaaaaaa.; a. aa aaaaaaaaaaa (aaa aaaaaaaa aa aaa aaaaaaaaa aaaaa), aaaaaaa aaa aa aaaaaaaaaa aa aaaaaa aaaaaaa aaaaaaaaaa aaaaaaaa aa aaaaaa aa aaaaaaaa aaaaaaa aaaaaaaaa.; - aaaaaaa aa: aaa aaaaaaa aa 01/14/2002 10:57:04 aa; aaa aaaaaa aaaaaaaa aaa aaaaaaaaaaaa aa aaaa 419. aaaa aa, aaaa aaaaa aa aaa aaaaaaaa aaaaaa–aaaa aaaaaa aaaa ‘aaaaaa’, aaaaaa aa aaaaa aaaaa aaaaaa aa aaa aaaaaa. - aaaaaaa aa: aaaaaa aaaaaaa aa 01/28/2002 01:58:29 aa; aaaaaa aaaaaaa (01/31/2002) - aaa, aaa $$ aaaa aa aaaa aaa aaaa–aaaaa aaa aaaaa a aaa aa aaa aaaa, aaaaaaa aa aaaa a aaaaaa aaaaaaaa. aa aaa aaaa aaa aaa aaaaaa aaaaaaa aaaaaaa, aaa aaa aaaaaaa aaa # aaa aaa aaaaaaaaa aaaaaaaaa aaaaaaaa. aaaaa aaa ‘aaaa’ aaaaaa aaaa aaaaa aaa aaaaa, aa aaaa aaaaa aaaaaaaaaaa."
Now granted this is over 6k being inserted into a 4k space but it should be trimmed gracefully. To finish recreating the error right click on the newly created table RFS_Test and select Import Data. Click Next. Select the text document with the text above. Hit a few Next buttons and Automap just for good measure. Complete the process by clicking on some more Next buttons and finally Execute. You should receive the Data is too Large for Field error and click on either Trim or Trim and don’t ask again.
I would have posted the files but I don’t think I have enough permissions for that.
I did end up changing the scripts around for this to be handled; however, I think this is a bug in the software and should be addressed.
dave