Issue to use REPLACE or TRANSLATE function in SQL*LOADER control file
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Issue to use REPLACE or TRANSLATE function in SQL*LOADER control file

  1. #1
    Join Date
    Jan 2006
    Posts
    39

    Issue to use REPLACE or TRANSLATE function in SQL*LOADER control file

    I need to use sqlloader to load a few flat file into staging tables in Oracle 10g. One column in the flat file contains - " "- in the string. I want to use REPLACE or REGEXP_REPLACE or TRANSLATE function in sqlloader control file to remove " " out. The basic code are like these:

    1. "translate(: column_name, 'A"', 'A')",

    2. "replace(:column_name, '"', null)", or " replace(: column_name, '"')",

    These functions are working for sure.

    However, when I put these function into control file. It will not work with the error message like: SQL*LOADER -350 syntax error token longer than max allowable length of 258 chars for RELACE, or syntax error expecting "," or ">", found "A" for TRANSLATE.

    Here is my sqlldr control file

    LOAD DATA
    INFILE 'C:\file_1.DEL'
    BADFILE 'C:\file_1.BAD'
    DISCARDFILE 'C:\file_1.dsc'
    TRUNCATE
    INTO TABLE "SCHEMA"."MY_TABLE"
    FIELDS TERMINATED BY '^' OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (
    COLUMN_1 CHAR(15) "LPAD(:INVOICEDISTRIBUTIONID, 15, '0')",
    COLUMN_2 CHAR(15),
    COLUMN_3 CHAR(150) "REPLACE(:COLUMN_3,'"')"
    )

    This is my flat file string. It is terminated by ^ and enclosed by " I need to take double quote around "BOARD DIRECTOR" off through data loading by sqlldr

    "65129478""51386316""PROVIDE PROFESSIONAL SERVICES "BOARD DIRECTOR" AS DEVELOPED AND CUSTOMIZED THROUGH FEBRUARY 08, 1995."^

    Is anyone familiar with this function or you dealed with the same sqlloader job before? Please give me some advice where is the problem in syntax. Thanks.

  2. #2
    Join Date
    Apr 2006
    Posts
    377
    If COLUMN_1 & COLUMN_2 are consistent lengths, the following would work:

    Code:
    COLUMN_1 POSITION(2:9) "LPAD(:COLUMN_1, 15, '0')",
    COLUMN_2 POSITION(12:19),
    COLUMN_3 POSITION(22) "REPLACE(:COLUMN_3,chr(34))"

  3. #3
    Join Date
    Jan 2006
    Posts
    39
    ebrian:

    You are right. This may be the final solution. because this sqlldr control file contain more than 80 columns. if using position, I have to set up all columns in position and need to count so many spaces. This is why I didn't use fixed position and want to use function to remove the double quote during the loading process. Thanks for your advice.

Posting Permissions

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



Click Here to Expand Forum to Full Width