1 Reply Latest reply: Jul 22, 2011 2:22 AM by Mkirtley-Oracle RSS

    SQL LOADER

    AnnPricks E
      Hi all,

      Can anyone tell me, what error occured in the sample datafile and control file?

      I have a text data in informix db and i want to load that data into oracle.

      Here is my sample code,

      I have a table,that table structure is,

      create table ins_ann
      (
      id number(5),
      name varchar2(20),
      description clob
      );

      and
      my datafile like
      1,'ann','c:\annfol\ann4.txt'
      2,'pricks','c:\annfol\ann4.txt'
      3,'sahin','c:\annfol\ann4.txt'
      4,'fredin','c:\annfol\ann4.txt'

      then,i have save it as ann1.txt(the location is 'C:\annfol\ann1.txt')

      and then my clob datafile like
      resume<he is a good boy and searching for a job ><qulification<he completed mca in loyola and his mark is 85.4%><he knows .NET very well
      and he has very much interest to work in .Net project
      <he will be able to learn anything hereafter>,
      resume<he is working in tcs and his domain is databse and
      particularly he is working in oracle ><and now he was assingning the project>,
      description<he is working in wipro and his domain is databse and
      particularly he is working in oracle ><and now he was assingning the project>,
      empolyee<he is working in tcs and his domain is databse and
      particularly he is working in oracle ><and now he was assingning the project>,

      then i have it as ann4.txt.(the location is 'C:\annfol\ann4.txt')

      and my control file like
      LOAD DATA
      INFILE 'c:\annfol\ann1.txt'
      BADFILE 'c:\annfol\ann2.txt'
      INTO TABLE ins_ann
      FIELDS TERMINATED BY ","
      (
      ID,
      NAME,
      V_DESC FILLER CHAR(10000),
      "DESCRIPTION" LOBFILE(V_DESC) TERMINATED BY ","
      )

      then save it as ann5.ctl(location 'C:\annfol\ann5.ctl')

      then i am giving the command like
      sqlldr scott@XE control='C:\annfol\ann5.ctl' log='C:\annfol\ann3.txt'
      in cmd

      then one error occured like SYSTEM CANT OPEN THE FILE 'c:\annfol\ann4.txt'

      Can anyone please correct this sample code? this is very useful to my project.Please tell the correction?

      and also

      Can anyone explain how to load clob data within same datafile and that clob data like
      just for example,
      <fvsdvfjsvfsbvjbsbfjsdfbdjsbvfsdnbvdsbvbjkvhuhvjhdfhoeugfenl
      sfhskhfkshfhssv svjvjjvjlewhfkwbefbwefbwebfwbfbwjk>dklnkbqwkdfqwbdfqbcfbhbcbjkbckbkcbbcbksbcvksbkcvsk
      asbsjkbcksbnkjbvksbvkjbsjkbvjksbvjkbskbvksbvkbskvbskhuihwehffbbfbvbbvksbvkbsk><ajdksajkdbabxcbakcbkabckj>

      you assume this is one row.

      Please reply me?