"SQL Loader has generated errors and will be closed by windows"

From: Bob Metelsky <bmetelsky_at_cps92.com>
Date: Tue, 30 Apr 2002 08:44:03 -0800
Message-ID: <F001.00453928.20020430084403@fatcity.com>

Im running sql loader with the following parameters

sqlldr userid=user/password_at_instance control=C:\filename.ctl

Run this from a command window and in about 10 seconds a window pops up to the effect
"SQL Loader has generated errors and will be closed by windows and error log is being created"

The problem is the log file is empty! How can I debug my control file?

fwiw Im using a script that creates a ctl file (I havent used it an a year or so... a little rusty) see in line

apparetly sql loader needs to have columns which have a "-" totally enclosed in ""

here is a sample of my ctl file

##################################################

LOAD DATA INFILE 'src.txt'
BADFILE 'C:\bad.txt'

INTO TABLE SRCTABLE FIELDS TERMINATED BY ' ' TRAILING NULLCOLS

(" V-FLAG"                           CHAR NULLIF("V-FLAG"=BLANKS)

 , NAMEID                           CHAR NULLIF(NAMEID=BLANKS)



 , "PRIMARY-ID"                       CHAR NULLIF("PRIMARY-ID"=BLANKS)




 , "SEQ-NO"                          CHAR NULLIF("SEQ-NO"=BLANKS)




 , "LAST-NAME"                        CHAR NULLIF("LAST-NAME"=BLANKS)




 , "FIRST-NAME"                      CHAR NULLIF("FIRST-NAME"=BLANKS)




 , "MIDDLE-INITIAL"                   CHAR

NULLIF("MIDDLE-INITIAL"=BLANKS)                         
##################################################

thanks
bob

#########################

REM control.sql script
SET ECHO off
REM REQUIREMENTS
REM SELECT privileges on the table
REM

---

• ---- REM PURPOSE: REM Prepares a SQL*Loader control file for a table already existing in the REM database. The script accepts the table name and automatically creates REM a file with the table name and extension 'ctl'. REM This is especially useful if you have the DDL statement to create a REM particular table and have a free-format ASCII-delimited file but have REM not yet created a SQL*Loader control file for the loading operation. REM REM Default choices for the file are as follows (alter to your needs): REM Delimiter: comma (',') REM INFILE file extension: .dat REM DATE format: 'MM/DD/YY' REM REM You may define the Loader Data Types of the other Data Types by REM revising the decode function pertaining to them. REM REM

  ---

  --- REM EXAMPLE: REM SQL> start control.sql emp REM REM LOAD DATA REM INFILE 'EMP.dat' REM INTO TABLE EMP REM FIELDS TERMINATED BY ',' REM ( REM REM EMPNO REM , ENAME REM , JOB REM , MGR REM , HIREDATE DATE "MM/DD/YY" REM , SAL REM , COMM REM , DEPTNO REM REM ) REM REM

  ---

  --- REM DISCLAIMER: REM This script is provided for educational purposes only. It is NOT

REM supported by Oracle World Wide Technical Support. REM The script has been tested and appears to work as intended. REM You should always run new scripts on a test instance initially. REM

---

-- 
REM Main text of script follows: 
 
set heading off 
set verify off 
set feedback off 
set show off 
set trim off 
set pages 0 
set concat on 
spool &1..ctl 
SELECT  
'LOAD DATA'||chr(10) 
||'INFILE '''||lower(table_name)||'.dat'' '||chr(10) 
||'INTO TABLE '||table_name||chr(10) 
||'FIELDS TERMINATED BY '','' '||chr(10) 
||'TRAILING NULLCOLS'||chr(10) 
||'(' 
FROM user_tables 
WHERE TABLE_NAME = UPPER('&1'); 
SELECT decode(rownum,1,'   ',' , ')||rpad(column_name,33,' ') 
|| decode(data_type, 
               'VARCHAR2','CHAR NULLIF('|| column_name ||'=BLANKS)', 
            'FLOAT',   'DECIMAL EXTERNAL
NULLIF('||column_name||'=BLANKS)', 
           'NUMBER', 
                     decode(data_precision, 
                0, 'INTEGER EXTERNAL NULLIF ('||column_name  
||'=BLANKS)', 
                          decode(data_scale,0, 
        'INTEGER EXTERNAL NULLIF  
('||column_name ||'=BLANKS)', 
                         'DECIMAL EXTERNAL NULLIF  
('||column_name ||'=BLANKS)'  
                                     ) 
                           ), 
     'DATE',    'DATE "MM/DD/YY"  NULLIF ('||column_name  
||'=BLANKS)',NULL) 
FROM user_tab_columns 
WHERE TABLE_NAME = UPPER('&1') 
ORDER BY COLUMN_ID; 
SELECT ')'  
FROM sys.dual; 
spool off 
 
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Bob Metelsky
  INET: bmetelsky_at_cps92.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
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Apr 30 2002 - 11:44:03 CDT