Im running sql loader with the following parameters
sqlldr userid=user/password@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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
