Re: How to load ascii file in oracle table

From: Daniel St-Jacques <daniel.st-jacques_at_ca.kontron.com>
Date: 1 Nov 2001 06:48:31 -0800
Message-ID: <2355c0e9.0111010648.1129f3f7@posting.google.com>

Hello everyone I found the solution of that problem. I will share it with you.

The solution is a mixte between a SQL loader and SQL PLUS.

1. In this example, the ascii file is a fix field length format. CUSTOMER.TXT 12345678901234NAMEXXXXXXXXXXXXXXXXX50XXXXXXXXXXXXXXXXXXXXXXXXXXZADDRESSXXXXXXX30 XXXXXXXXXXXXXZSECONDADDRESSXXX30XXXXXXXXXXXZTHIRDADDRESSXXX30XXXXXXXXXXXXZFOURTH ADDRESSXXXX30XXXXXXXXXXZCITYXXXXXXXXXX30XXXXXXXXXXXXXZXXXSTATZIPPOSTXXZ123456
2. Prepare the control file of the SQL loader LOADFILE.ctl

INFILE 'CUSTOMER.TXT' BADFILE 'outfile.bad' DISCARDFILE 'outfile.dsc' INTO TABLE tmptable_load

 (cust_id           POSITION (   1 :  14 ) CHAR,
  name              POSITION (  15 :  64 ) CHAR,
  address           POSITION (  65 :  94 ) CHAR,
  second_address    POSITION (  95 : 124 ) CHAR,
  third_address     POSITION ( 125 : 154 ) CHAR,
  fourth_address    POSITION ( 155 : 184 ) CHAR,
  city              POSITION ( 185 : 214 ) CHAR,
  country           POSITION ( 215 : 217 ) CHAR,
  state_prov        POSITION ( 218 : 221 ) CHAR,
  zippost           POSITION ( 222 : 231 ) CHAR,
  user_def_1        POSITION ( 232 : 237 ) CHAR

 )

3. Create the SQL scriptCREATE TABLE tmptemp_SAP

--LOADFILE.sql
CREATE TABLE tmptable_load

 (cust_id           VARCHAR2(14) CONSTRAINT pk_cust PRIMARY KEY,
  name              VARCHAR2(50),
  address           VARCHAR2(30),
  second_address    VARCHAR2(30),
  third_address     VARCHAR2(30),
  fourth_address    VARCHAR2(30),
  city              VARCHAR2(30),
  country           VARCHAR2(3),
  state_prov        VARCHAR2(4),
  zippost           VARCHAR2(10),
  user_def_1        VARCHAR(6)

 ) ;

HOST sqlldr userid=user\/password control=LOADFILE.ctl log=LOADFILE.log discard=LOADFILE.dsc bad=LOADFILE.bad silent=\(HEADER, FEEDBACK\);

DECLARE
   missing_record NUMBER := 0 ;
   cursor cust_cursor is select * from tmptable_load ; BEGIN
   for tmp_rec in cust_cursor loop

     BEGIN 
        update customer set   name                 = tmp_rec.name,
                              address              = tmp_rec.address,
                              second_address       =
tmp_rec.second_address,
                              third_address        =
tmp_rec.third_address,
                              fourth_address       =
tmp_rec.fourth_address,
                              city                 = tmp_rec.city,
                              country              = tmp_rec.country,
                              state_prov           =
tmp_rec.state_prov,
                              zippost              = tmp_rec.zippost,
                              user_def_1           =
tmp_rec.user_def_1
                where cust_id = tmp_rec.cust_id ;
      EXCEPTION
          when NO_DATA_FOUND then
            missing_record := missing_record + 1 ;
      END ;

   end loop ;
END ;
/
drop table tmptable_load ;
quit ;

With that You will load ASCII file to a temp table (tmptable_load) and then to the oracle table (customer).

I hope that will help people like me that have to work nearly a week to find that.

daniel.st-jacques_at_ca.kontron.com (Daniel St-Jacques) wrote in message news:<2355c0e9.0110260501.47ddd62e_at_posting.google.com>...
> Ban Spam <ban-spam_at_operamail.com> wrote in message news:<Xns9145B437E7F5FSunnySD_at_24.0.3.73>...
> > daniel.st-jacques_at_ca.kontron.com (Daniel St-Jacques) wrote in
> > news:2355c0e9.0110250942.36963614_at_posting.google.com:
> >
> > > Hello all
> > >
> > > I am new in the Oracle / SQL world.
> > >
> > > I need help.
> > >
> > > Here is my problem.
> > >
> > > I need to create a program that will load an ASCII file into an Orcale
> > > Table.
> > >
> > > ASCII FILE: file.txt
> > > Contents: 123,Joe Blow,123 lost,somewhere,nowhere
> > > 124,Mary Jains,324 where,city,country
> > >
> > > Oracle Table: CUST-NUM
> > > NAME
> > > ADDRESS
> > > CITY
> > > ZIP
> > > COUNTRY
> > >
> > > I need to do an SQL program that will check if the customer exist.
> > > If it exist, the program has to update the oracle table with the
> > > information from the ascii file.
> > > If it is not exist, the program has to create the customer into the
> > > oracle table.
> > >
> > > Is someone can help me PLEASE.
> > >
> > > Thank you
> > >
> > > You can send my your suggestion at
> > > daniel.st-jacques_at_ca.kontron.com
> > > or
> > > stjacqd_at_hotmail.com
> > >
> > > Thank you again
> > >
> >
> > SQLLDR utility supplied by Oracle will load your data.
> > Load it into a TEMP/WORK table & then use SQL to INSERT
> > the new records.
> >
> > What happens when the names match but the rest of the fields
> > are different?
>
>
> Thank you for the answer but I don't know how to use the SQLLDR.
>
> Should I start a SQL to create a table ( CREATE TABLE X ... )
> Than launch the SQLLDR ? How the SQLLDR works. I try to understand the
> SQLLDR section in the Utilities manual but it is look like that I am
> totally dump. I don't understand how to make that stuff working for
> me.
> I create an ascii file with the LOAD INFILE customer.txt INTO TABLE X
> ...
> but the system still give me errors. I never used Oracle before. So
> for me it is a bit complex to understand.
>
> Please if possible can you give by a step by step solution
> THANK YOU
Received on Thu Nov 01 2001 - 08:48:31 CST