3 Replies Latest reply: Jun 21, 2013 2:24 PM by Barbara Boehmer RSS

    SQL Loader NCLOB

    J.Limousin

      Hi everybody !

       

      This is my first post on the forum, and I don't speak english very very well so please be cool with me.

       

      I actually encounter an annoying problem ! Let's explain the situation

       

      I have, in the compay in which I work, 3 different DBMS. SQL Server, Access and Oracle. The company is planning to migrate from Access to Oracle, and I'm in charge of that job. So I'm working on the migration of an Access Database, and I encounter a problem !

       

      I have on the Oracle side the structure; but not the data. And I need to move the data on Access to Oracle. I'm doing this by hand with some CSV exportation, and SQL Loader importation. But there is still a table that makes a problem. In fact, on the Oracle side, that table has a CLOB column, to store large amount of text data. And on Access it's stored in text (I just totally don't know Access xD)

       

      The fact is that when I'm trying to move data from a CSV file exported on Access, to Oracle, I get this error message :

       

      "Record 1: Rejected - Error on table XXX.XXX, column XXX.

      Field in data file exceeds maximum length"

       

      Do someone could help me to resolve this problem? I spent hours trying to find something on the internet but without success. :/

       

      Thanks in advance !!

       

      Cordially,

       

      J.Limousin

        • 1. Re: SQL Loader NCLOB
          DK2010

          Hi,

          if i understand you are using the  table having clob column.

          can you share the dumy  table structure and control file.. here.. can help to solve the issue

          • 2. Re: SQL Loader NCLOB
            J.Limousin

            Yep of course !

             

            Structure :

             

            SQL> desc d_data_text;

            Name   Null?    Type

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

            ID_D_DATA_TEXT   NOT NULL NUMBER(38)

            SVALUE   NOT NULL NCLOB

            ID_S_ATTRIBUTE    NUMBER(38)

            ID_S_OBJECT    NUMBER(38)

            SSEARCH_VALUE    NCLOB

             

            Control file :

             

            LOAD DATA

            CHARACTERSET UTF8

            INFILE 'DDataText.txt'

            INTO TABLE TXM_ACCESS.d_data_text

            FIELDS TERMINATED BY ',' optionally enclosed by '"'

            (ID_D_Data_Text,

            sValue,

            ID_S_Attribute,

            ID_S_Object,

            sSearch_Value)

             

            Thank you for your attention.

            • 3. Re: SQL Loader NCLOB
              Barbara Boehmer

              If you do not specify a length for your fields, then it defaults to 255 and anything larger than that is rejected.  So, you need to figure out what the maximum length of each field is and add those.  For example, CHAR(5000) is used for each of the clob fields below, assuming that the maximum length is 5000.


              LOAD DATA

              CHARACTERSET UTF8

              INFILE 'DDataText.txt'

              INTO TABLE d_data_text

              FIELDS TERMINATED BY ',' optionally enclosed by '"'

                (ID_D_Data_Text,

                 sValue         CHAR(5000),

                 ID_S_Attribute,

                 ID_S_Object,

                 sSearch_Value  CHAR(5000))