Re: SQL*Loader: How do I load more than 300 characters long fields

From: Peter Hanlon <pete_at_hanlon.co.uk>
Date: Sun, 03 Oct 1999 12:54:24 +0100
Message-ID: <37F743F0.D9A28324@hanlon.co.uk>

If you are still having problems with SQL*Ldr, take a look at http://www.hanlon.co.uk. DataMill is a native Oracle loader that correctly handles large columns and loads faster than SQL*Ldr in conventional mode. In addition DataMill can run updates, deletes, PL/SQL and server side Java at load time. You can download a 30 day trial from the web site mentioned above.

I have attached a DataMill control file for the example you posted. To run DataMill from the command line enter "DataMill control=Test.ctl infile=<your data file>", the arguments can be placed in the control file if you wish.

Hope this helps.

<---- Test.ctl ----->
ARGUMENTS
{
  CONNECT=scott/tiger;
}

DECLARE SECTION
{
  FIELDS TERMINATED BY '|';

  vDEPNO char(2);
  vDNAME char(500);
  vLOC   char(13);

}

START LOAD
{
  TRUNCATE TABLE DEPT REUSE STORAGE;
}

TRANSACTION BODY
{

   INSERT INTO DEPT (

       DEPNO,
       DNAME,
       LOC)
   VALUES (
      :vDEPNO,
       :vDNAME,
       :vLOC);

}

<---- Test.ctl ----->

Tim Shute wrote:

> You will also need to be careful what version of Oracle you are running.
>
> We tried the same with Oracle 7.3.4.0.0 and it caused problems with
> char(1000) spilling over into the next logical field in a comma-delimited
> file.
>
> We upgraded to Oracle 7.3.4.4.1 and all was well.
>
> HTH
>
> --
> Tim Shute
> Software Engineering Manager
>
> NiSoft (UK) Limited
> NiSoft House
> Ravenhill Business Park
> Ravenhill Road
> Belfast BT6 8AW
> Internet: www.nisoft.co.uk
> Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
> news:37d05c50.54641199_at_newshost.us.oracle.com...
> > A copy of this was sent to chi-on wong <john.wong_at_sun.com>
> > (if that email address didn't require changing)
> > On Wed, 01 Sep 1999 23:22:46 -0700, you wrote:
> >
> > >Hi,
> > >
> > >In a .csv file I have something like this:
> > >
> > >John,Then something very very very long about 300
> >
> >characters.................................................................
> .....
> >
> >...........................................................................
> .....
> > >......................................................,99
> > >
> > >I am trying to load it into a table with
> > >
> > >create table test(
> > >name varchar2(10),
> > >comments varchar2(500),
> > >age varchar2(10))
> > >
> > >
> > >Although the comments field is varchar2(500), SQL*Loader will reject
> > >anything
> > >longer than 255 characters with this error:
> > >
> > >
> > In your .ctl file put a char(300) for example:
> >
> > LOAD DATA
> > INFILE *
> > INTO TABLE dept
> > REPLACE
> > FIELDS TERMINATED BY '|'
> > (
> > deptno
> > ,dname char(500)
> > ,loc
> > )
> >
> >
> > lets dname be upto 500 bytes.
> >
> >
> > >Record 1: Rejected - Error on table TEST, column COMMENTS.
> > >Field in data file exceeds maximum length
> > >
> > >
> > > What can I do to load the 300 characters field into
> > >the comments field?
> >
> >
> > --
> > See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to
> Oracle8i'...
> > Current article is "Part I of V, Autonomous Transactions" updated June
> 21'st
> >
> > Thomas Kyte tkyte_at_us.oracle.com
> > Oracle Service Industries Reston, VA USA
> >
> > Opinions are mine and do not necessarily reflect those of Oracle
> Corporation
Received on Sun Oct 03 1999 - 06:54:24 CDT