Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Location
    India
    Posts
    217

    Unanswered: sql loader and CLOB

    My control file looks like below,

    Code:
    LOAD DATA 
    INFILE data.TXT
    INTO TABLE rl_test
    REPLACE
    fields terminated by "" TRAILING NULLCOLS
    (c "REPLACE(:c,'a','b')")

    When I use the sql loader i get the following error,

    Code:
    SQL*Loader-309: No SQL string allowed as part of C field specification
    The column C is of type CLOB. (My data is only text, not text files.)

    But i am able to use the following command in sql plus,

    Code:
    update <table_name> set <column_name> = replace (.....);
    Does this mean that I can not use REPLACE function in sql loader?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Yes, you CAN use REPLACE function in SQL*Loader. However, SQL Strings are not supported for LOBs, BFILEs, object columns, nested tables, or varrays, therefore, you cannot specify SQL Strings as part of a filler field specification.

    Read more about DDL behavior and restrictions here.

  3. #3
    Join Date
    Feb 2005
    Location
    India
    Posts
    217
    what is SQL String?
    Is it built in functions like replace, substr, length?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    In your example, SQL String is this: "REPLACE(:c,'a','b')"

  5. #5
    Join Date
    Feb 2005
    Location
    India
    Posts
    217
    Thank you. Littlefoot.

Posting Permissions

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