RE: Creating tab-delimited txt file using sqlplus

From: Manning, Stephen (calanais) <Stephen.Manning_at_calanais.com>
Date: Fri, 29 Sep 2000 20:47:45 +0100
Message-Id: <10634.118261@fatcity.com>

Tina

Try removing the chr(13) from your select, its been appended to the value of your INDUSTRY_TEXT column. SQLLDR is trying to insert 41 characters into the VARCHAR2(40) column.

HTH
Stephen

-----Original Message-----
From: Tina Ridgley [mailto:tlridgley_at_yahoo.com] Sent: 29 September 2000 19:31
To: Multiple recipients of list ORACLE-L Subject: Creating tab-delimited txt file using sqlplus

All,

I am attempting to create a tab delimited txt file of data using sqlplus. I am able to generate the file but am not able to get sqlldr to load the file.

My table looks like this:

SQL> desc prov;

 Name                            Null?    Type
 ------------------------------- -------- ----
 PID                                      NUMBER
 PROVIDERNAME                             VARCHAR2(55)
 STREETADDRESS1                           VARCHAR2(55)
 STREETADDRESS2                           VARCHAR2(55)
 CITY                                     VARCHAR2(25)
 COUNTY                                   VARCHAR2(25)
 STATEPROVINCE                            VARCHAR2(25)
 COUNTRY                                  VARCHAR2(25)
 ZIPCODE                                  VARCHAR2(25)
 PHONE1                                   VARCHAR2(25)
 PHONE2                                   VARCHAR2(25)
 FAX                                      VARCHAR2(25)
 CELLPHONE                                VARCHAR2(25)
 PAGER                                    VARCHAR2(25)
 URL                                     
VARCHAR2(125)
 ICONID                                   NUMBER
 TYPEID                                   NUMBER
 LATITUDE                                 NUMBER
 LONGITUDE                                NUMBER
 GEOCODEQUALITY                           VARCHAR2(25)
 PROVIDERTYPE                             VARCHAR2(20)
 F9                                       VARCHAR2(55)
 LAST_NAME                                VARCHAR2(50)
 FIRST_NAME                               VARCHAR2(50)
 YP_CODE                                  NUMBER
 PRIMARY_SIC                              NUMBER(6)
 FRANCHISE_CODE                           VARCHAR2(7)
 INDUSTRY_CODE                            CHAR(1)
 ABI_NUMBER                               NUMBER(9)
 SIC_TEXT                                 VARCHAR2(50)
 FRANCHISE_TEXT                           VARCHAR2(40)
 INDUSTRY_TEXT                            VARCHAR2(40)

SQL> spool off;

My script to create the data file looks like this:

SET PAGESIZE 0
SET LINESIZE 256
SET PAUSE off
SET HEADING off
SET FEEDBACK off

SPOOL providers.lis
SELECT
pid||chr(9)||providername||chr(9)||streetaddress1||chr(9)||streetaddress2||c hr(9)||city||chr(9)||county||chr(9)||stateprovince||chr(9)||country||chr(9)|
|zipcode||chr(9)||phone1||chr(9)||phone2||chr(9)||fax||chr(9)||cellphone||ch

r(9)||pager||chr(9)||url||chr(9)||iconid||chr(9)||typeid||chr(9)||latitude||
chr(9)||longitude||chr(9)||geocodequality||chr(9)||providertype||chr(9)||f9|


|chr(9)||last_name||chr(9)||first_name||chr(9)||yp_code||chr(9)||primary_sic

||chr(9)||franchise_code||chr(9)||industry_code||chr(9)||abi_number||chr(9)|
|sic_text||chr(9)||franchise_text||chr(9)||industry_text||chr(13)
FROM providers where rownum <= 150;

My sqlldr ctl file looks like this:

LOAD DATA
INFILE 'providers.lis'
INTO TABLE PROV
FIELDS TERMINATED BY X'9'
TRAILING NULLCOLS
( PID ,
    PROVIDERNAME,
    StreetAddress1,
    StreetAddress2,
    City,
    County,
    StateProvince,
    Country,
    Zipcode,
    Phone1,
    Phone2,
    Fax,
    CellPhone,
    Pager,
    URL,
    IconID,
    TypeID,
    Latitude,
    Longitude,
    GeocodeQuality,
    ProviderType,
    F9,
    Last_name,
    First_name,
    Yp_code,
    Primary_sic,
    franchise_code,
    industry_code,
    abi_number,
    sic_text,
    franchise_text,
    industry_text
)

All rows are being rejected. The message sqlldr generates is:

Record 1: Rejected - Error on table PROV. ORA-01401: inserted value too large for column

I'm not seeing the problem. Anyone have any suggestions on what might be wrong?

Thanks in advance,

Tina

Do You Yahoo!?
Yahoo! Photos - 35mm Quality Prints, Now Get 15 Free! http://photos.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tina Ridgley
  INET: tlridgley_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L

Received on Fri Sep 29 2000 - 14:47:45 CDT