Forum Stats

  • 3,839,722 Users
  • 2,262,530 Discussions
  • 7,901,048 Comments

Discussions

How can we load data into CLOB datatype column using SQL Loader?

Nirvan
Nirvan Member Posts: 23 Red Ribbon

Hi Experts-

We have a tab delimiter file which will be received on timely basis, apart from other column values there is one column which contains huge data which I am trying to load into a clob column, and we are not sure how big the data will be in that column as it is not specified. Currently I am trying to load the data by extending the size of the column in control file every time which may not be good practice. Could you please let me know if there is any other possibility to load the data without specifying the size. I tried searching for a solution everything I found is about lobfile which may not help here as we are not receiving the data in a separately alone for the specific column.

--For example the table structure looks like this.

CREATE TABLE TMP_LOAD_CLOB_DATA

 (

  CUSTOMER_ID VARCHAR2(255),

  FIRSTNAME  VARCHAR2(255),

  LASTNAME  VARCHAR2(255),

  CUSTOMER_DETAILS CLOB

 );

--Assuming the data will look like shown below.

CUSTOMER_ID FIRSTNAME LASTNAME CUSTOMER_DETAILS 

123 ABC XYZ Lot Of Information about the customer which should be loaded into clob

--The control file will look like this

OPTIONS (DIRECT=TRUE, MULTITHREADING=TRUE, PARALLEL=TRUE)              

load data           

infile '/test.txt'                                                                                                                                                                  

into table TMP_LOAD_CLOB_DATA         

APPEND                                                                                                                                                                                       

fields terminated by X'09'      

trailing nullcols  

(CUSTOMER_ID,

FIRSTNAME,

LASTNAME,

CUSTOMER_DETAILS char(50000))


How can I avoid adding the column size like char(50000) and still load the data into clob column?

Thank you,

Nirvan

Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,610 Red Diamond
    edited Feb 5, 2021 12:35PM Answer ✓

    Well, since CLOB in general is multi-line text each clob needs to be stored as a separate file. So we prepare CTL file:

    LOAD DATA
    INFILE 'C:\TEMP\TMP_LOAD_CLOB_DATA.TXT'
      INTO TABLE TMP_LOAD_CLOB_DATA
      FIELDS TERMINATED BY X'09'
      (
       CUSTOMER_ID,
       FIRSTNAME,
       LASTNAME,
       CUSTOMER_DETAILS_CLOB_FILE FILLER,
       CUSTOMER_DETAILS LOBFILE(CUSTOMER_DETAILS_CLOB_FILE) TERMINATED BY EOF
      )
    

    Data file C:\TEMP\TMP_LOAD_CLOB_DATA.TXT:

    123	ABC	XYZ	C:\TEMP\TMP_LOAD_CLOB1.TXT
    456	DEF	ZZZ	C:\TEMP\TMP_LOAD_CLOB2.TXT
    

    Clob file C:\TEMP\TMP_LOAD_CLOB1.TXT:

    Lot Of Information 
    about customer ABC XYZ
    which should be loaded
    into a clob
    

    Clob file C:\TEMP\TMP_LOAD_CLOB2.TXT:

    Lot Of Information 
    about customer DEF ZZZ
    which should be loaded
    into a clob
    

    Now:

    SQL> SELECT  *
      2    FROM  TMP_LOAD_CLOB_DATA
      3  /
    
    no rows selected
    
    SQL> host
    Microsoft Windows [Version 10.0.17763.1637]
    (c) 2018 Microsoft Corporation. All rights reserved.
    
    I:\>sqlldr [email protected]/tiger control=C:\TEMP\TMP_LOAD_CLOB_DATA.ctl
    
    SQL*Loader: Release 12.2.0.1.0 - Production on Fri Feb 5 07:25:12 2021
    
    Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
    
    Path used:      Conventional
    Commit point reached - logical record count 2
    
    Table TMP_LOAD_CLOB_DATA:
      2 Rows successfully loaded.
    
    Check the log file:
      TMP_LOAD_CLOB_DATA.log
    for more information about the load.
    
    I:\>exit
    
    SQL> SELECT  *
      2    FROM  TMP_LOAD_CLOB_DATA
      3  /
    
    
    CUSTOMER_ID FIRSTNAME LASTNAME CUSTOMER_DETAILS
    ----------- --------- -------- ------------------------------
    123         ABC       XYZ      Lot Of Information
                                   about customer ABC XYZ
                                   which should be loaded
                                   into a clob
    
    456         DEF       ZZZ      Lot Of Information
                                   about customer DEF ZZZ
                                   which should be loaded
                                   into a clob
    
    SQL>
    

    SY.

    User_ZZAIV

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,610 Red Diamond
    edited Feb 5, 2021 12:35PM Answer ✓

    Well, since CLOB in general is multi-line text each clob needs to be stored as a separate file. So we prepare CTL file:

    LOAD DATA
    INFILE 'C:\TEMP\TMP_LOAD_CLOB_DATA.TXT'
      INTO TABLE TMP_LOAD_CLOB_DATA
      FIELDS TERMINATED BY X'09'
      (
       CUSTOMER_ID,
       FIRSTNAME,
       LASTNAME,
       CUSTOMER_DETAILS_CLOB_FILE FILLER,
       CUSTOMER_DETAILS LOBFILE(CUSTOMER_DETAILS_CLOB_FILE) TERMINATED BY EOF
      )
    

    Data file C:\TEMP\TMP_LOAD_CLOB_DATA.TXT:

    123	ABC	XYZ	C:\TEMP\TMP_LOAD_CLOB1.TXT
    456	DEF	ZZZ	C:\TEMP\TMP_LOAD_CLOB2.TXT
    

    Clob file C:\TEMP\TMP_LOAD_CLOB1.TXT:

    Lot Of Information 
    about customer ABC XYZ
    which should be loaded
    into a clob
    

    Clob file C:\TEMP\TMP_LOAD_CLOB2.TXT:

    Lot Of Information 
    about customer DEF ZZZ
    which should be loaded
    into a clob
    

    Now:

    SQL> SELECT  *
      2    FROM  TMP_LOAD_CLOB_DATA
      3  /
    
    no rows selected
    
    SQL> host
    Microsoft Windows [Version 10.0.17763.1637]
    (c) 2018 Microsoft Corporation. All rights reserved.
    
    I:\>sqlldr [email protected]/tiger control=C:\TEMP\TMP_LOAD_CLOB_DATA.ctl
    
    SQL*Loader: Release 12.2.0.1.0 - Production on Fri Feb 5 07:25:12 2021
    
    Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
    
    Path used:      Conventional
    Commit point reached - logical record count 2
    
    Table TMP_LOAD_CLOB_DATA:
      2 Rows successfully loaded.
    
    Check the log file:
      TMP_LOAD_CLOB_DATA.log
    for more information about the load.
    
    I:\>exit
    
    SQL> SELECT  *
      2    FROM  TMP_LOAD_CLOB_DATA
      3  /
    
    
    CUSTOMER_ID FIRSTNAME LASTNAME CUSTOMER_DETAILS
    ----------- --------- -------- ------------------------------
    123         ABC       XYZ      Lot Of Information
                                   about customer ABC XYZ
                                   which should be loaded
                                   into a clob
    
    456         DEF       ZZZ      Lot Of Information
                                   about customer DEF ZZZ
                                   which should be loaded
                                   into a clob
    
    SQL>
    

    SY.

    User_ZZAIV
  • Nirvan
    Nirvan Member Posts: 23 Red Ribbon

    Hi Solomon, Thank you for your response..

  • User_ZZAIV
    User_ZZAIV Member Posts: 1 Green Ribbon

    Hi,

    For above is there any way to load CLOB data without having separate files ??

  • User_48D4R
    User_48D4R Member Posts: 1 Green Ribbon

    What to do when a large text is part of a single file and needs to be inserted with an SQLLDR.

    eg: it is 498,780 characters long.

    ID | VALUE | DATA

    1 | Dr4wQQ | it is 498,780 characters long......

    2 | WQWQ3 | it is 925,342 characters long......

    3 | DERRQQ | it is 478,998 characters long......

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,610 Red Diamond
    edited Aug 12, 2022 11:07AM

    I'll assume CLOB doesn't start with new line. I will also assume, based on your data sample, vertical pipe can't appear in CLOB - otherwise you simply can't use it as field separator. If so, control file TMP_LOAD_CLOB_DATA.CTL:

    LOAD DATA
      INFILE 'C:\MY_TEMP\TMP_LOAD_CLOB_DATA.TXT'
      "str X'7C0D0A'"
      INTO TABLE TMP_LOAD_CLOB_DATA
      FIELDS TERMINATED BY "|"
      (
       ID,
       VALUE,
       DATA
      )
    
    

    7C is hex ascii code for vertical pipe. 0D0A is new line (CRLF - carriage return, line feed). Now we need to add vertical pipe at the end of each line in data file TMP_LOAD_CLOB_DATA.TXT:

    1 | Dr4wQQ | it
     is 498,780
     characters
     long......|
    2 | WQWQ3 | it 
    is 
    925,342
     characters long......|
    3 | DERRQQ | it 
    is 
    478,998
     characters
     long
     line1
     line2
     many
     more
     lines|
    

    Now we load:

    I:\>sqlldr [email protected]/yyy control=c:\my_temp\tmp_load_clob_data.ctl log=c:\my_temp\tmp_load_clob_data.log
    
    SQL*Loader: Release 12.2.0.1.0 - Production on Fri Aug 12 06:55:18 2022
    
    Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
    
    Path used:      Conventional
    Commit point reached - logical record count 2
    Commit point reached - logical record count 3
    
    Table TMP_LOAD_CLOB_DATA:
      3 Rows successfully loaded.
    
    Check the log file:
      c:\my_temp\tmp_load_clob_data.log
    for more information about the load.
    
    I:\>
    

    Now we check what was loaded:

    SQL> select * from tmp_load_clob_data;
    
            ID VALUE      DATA
    ---------- ---------- ----------------------------------------
             1  Dr4wQQ     it
                           is 498,780
                           characters
                           long......
    
             2  WQWQ3      it
                          is
                          925,342
                           characters long......
    
             3  DERRQQ     it
                          is
                          478,998
                           characters
                           long
                           line1
                           line2
                           many
                           more
                           lines
    
    SQL>
    

    SY.