SQL*Load query

I have a situation like the one below in that I need to split up a character field into multiple fields where they are longer than the field size.

It's harder to describe than to example, so:

abs1 position (*) char(5),
abs2 position (*) char(5),
abs3 position (*) char(5),
abs4 position (*) char(5))

TT*1*abcde*
TT*2*abcdeABCDE*
TT*37*"abcdeAB*DEZYXWV"*
TT*4*"abcdeAB""DEzyxwvZYXWV"*


------------------------------

FKEY	RECID	ABS1	ABS2	ABS3	ABS4
-------	-------	-------	-------	-------	-------
TT	1	abcde
TT	2	abcde	ABCDE
TT	37	abcde	AB*DE	ZYXWV
TT	4	abcde	AB"DE	zyxwv	ZYXWV

Instead I get (in log file):

Record 2: Rejected - Error on table TEMPLOAD, column ABS1. Field in data file exceeded maximum specified length Record 3: Rejected - Error on table TEMPLOAD, column ABS1. Field in data file exceeded maximum specified length Record 4: Rejected - Error on table TEMPLOAD, column ABS1. Field in data file exceeded maximum specified length

• WHY DO I WANT TO DO THIS ? I actually need to split LONG data into varchar2 columns so that a pre insert trigger on TEMPLOAD can move it into a LONG column in another table. This may be insert or update depending on whether the recid exists in the destination table. The field is up to 6000 characters in size - too large for a single varchar2 column. :new cannot use LONG columns in triggers (I've tried and Oracle7 Server SQL Reference confirms it). 4 x 5-char examples was easier to test than 3 x 2000 char !!!

Any assistance would be greatly appreciated.

Thanks,
Tim. Received on Wed Jan 14 1998 - 00:00:00 CET