Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55

    Unanswered: Preserve blanks in Sql*Loader

    Hi,
    I read a data file with a lot of fields which contain whitespace, and i would like to preserve these in my database.
    I found in the Database_utilities doc of oracle the option PRESERVE BLANKS, but i don't know where i have to put it. I try many place in the control file, but it doesn't work. I've got errors everytime.
    Can u help me ?

    Code:
    OPTIONS (SILENT=ALL,DIRECT=true)
    LOAD DATA
    PRESERVE BLANKS
    APPEND
    -----------------------------------------------
    -- CHARGEMENT Initial Table dqaqt1C          --
    -----------------------------------------------
    into table dqa.dqaqt1C
    REENABLE DISABLED_CONSTRAINTS
    (
    VIN				POSITION(01:17) CHAR(17), 
    TITRE_24			POSITION(18:41) CHAR(24), 
    TITRE_10			POSITION(42:51) CHAR(10), 
    CODE_FAMILLE			POSITION(52:55) CHAR(4), 
    DATE_EMON			POSITION(56:74) DATE "DD/MM/YYYY HH24:MI:SS",
    ANNEE_EMON			POSITION(75:78) INTEGER EXTERNAL,
    TRIMESTRE_EMON			POSITION(79:79) INTEGER EXTERNAL,
    MOIS_EMON			POSITION(80:81) INTEGER EXTERNAL,
    DATE_EFER			POSITION(82:100) DATE "DD/MM/YYYY HH24:MI:SS",
    DATE_EPEI			POSITION(101:119) DATE "DD/MM/YYYY HH24:MI:SS",
    DATE_SMON			POSITION(120:138) DATE "DD/MM/YYYY HH24:MI:SS",
    DATE_ECOM			POSITION(139:157) DATE "DD/MM/YYYY HH24:MI:SS", 
    ANNEE_ECOM			POSITION(158:161) INTEGER EXTERNAL,
    TRIMESTRE_ECOM			POSITION(162:162) INTEGER EXTERNAL,
    MOIS_ECOM			POSITION(163:164) INTEGER EXTERNAL,
    DATE_ENTREE_GARANTIE		POSITION(165:183) DATE "DD/MM/YYYY HH24:MI:SS",
    ANNEE_ENTREE_GARANTIE		POSITION(184:187) INTEGER EXTERNAL,
    TRIMESTRE_ENTREE_GARANTIE	POSITION(188:188) INTEGER EXTERNAL,
    MOIS_ENTREE_GARANTIE		POSITION(189:190) INTEGER EXTERNAL,
    CODE_CENTRE_PRODUCTION		POSITION(191:192) CHAR(2), 
    CODE_POINT_VENTE		POSITION(193:201) CHAR(9), 
    CODE_PAYS_POINT_VENTE		POSITION(202:203) CHAR(2), 
    NO_APV_PR			POSITION(204:215) CHAR(12), 
    NO_OPR				POSITION(216:220) CHAR(5), 
    NB_VEHICULES			POSITION(221:221) INTEGER EXTERNAL,
    ZONE_ATTRIBUTS_B		POSITION(222:261) CHAR(40), 
    ZONE_ATTRIBUTS_T		POSITION(262:661) CHAR(400), 
    ZONE_ATTRIBUTS_G		POSITION(662:861) CHAR(200), 
    ZONE_ATTRIBUTS_F		POSITION(862:1061) CHAR(200)
    )

  2. #2
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    This worked for me

    Code:
    LOAD DATA 
    
    APPEND
    PRESERVE BLANKS
    INTO TABLE "EMP_DET"
    
    
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"' AND '"'
    TRAILING NULLCOLS
    (ENO 
    , ENAME 
    , PHPRE 
    , PHNO 
    , EXTN 
    , JOIN_DATE
    )
    Try using preserve blanks before INTO table_name.
    Oracle can do wonders !

  3. #3
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55
    Yes, i already tried, but whith no success.
    Just one thing, i have no separator as ','. Just positionned string.
    ...

    I want to preserve my whitespace with few fields (CHAR) but trim whitespace with date fields...

    I edit one more time :
    i found my problem.
    I didn't put the NULLIF (56:74)=BLANKS, on the date fields. So, he tried to insert a strin with 19 whitespace as a date because of the PRESERVE BLANKS option.
    It's okay now.
    Thx for your contribution.
    Last edited by venderic; 01-30-04 at 10:31.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •