Table T1:

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2                                               VARCHAR2(30)
 COL3                                               NUMBER
 COL4                                               NUMBER
 COL5                                               NUMBER
 COL6                                               NUMBER
TIMEDATE                                           VARCHAR2(20)
    
Table T2:

SQL> desc t2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL4                                               NUMBER
 COL6                                               NUMBER
 TIMEDATE                                           VARCHAR2(20)



DataFile: c:\temp\test.txt

col1|col2|col3|col4|col5|col6
1|row1|12|13|14|15
2|second row2|21|22|23|24
3|row3 in source|31|32|33|34


Control File:

Load data
into table T1
replace
fields terminated by '|'
trailing nullcols
(
col1,
col2,
col3,
col4,
col5,
col6,
timedate "to_char(sysdate,'yyyymmdd')"
)
into table T2
trailing nullcols
(
filler1 FILLER,
filler2 FILLER,
filler3 FILLER,
col4,
filler4 FILLER,
col6,
timedate "to_char(sysdate,'yyyymmdd')"
)


 
 
 
 C:\temp\gics>sqlldr userid=test/test@testdb data=test.txt skip=1
 
 control = load.ctl
 
 SQL*Loader: Release 8.1.6.0.0 - Production on Tue May 20 09:42:09 2003
 
 (c) Copyright 1999 Oracle Corporation.  All rights reserved.
 
 Commit point reached - logical record count 2
 Commit point reached - logical record count 3


 
 SQL> select * from t1;
 
       COL1 COL2                                 COL3       COL4       COL5       COL6 TIMEDATE
 ---------- ------------------------------ ---------- ---------- ---------- ---------- --------------
          3 row3 in source                         31         32         33         34 20030520
          1 row1                                   12         13         14         15 20030520
          2 second row2                            21         22         23         24 20030520
          
 
 SQL> select * from t2;
 
 no rows selected

Log File:


SQL*Loader: Release 8.1.6.0.0 - Production on Tue May 20 09:42:09 2003

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Control File:   load.ctl
Data File:      test.txt
  Bad File:     test.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array:     64 rows, maximum of 65536 bytes
Continuation:    none specified
Path used:      Conventional

Table T1, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL1                                FIRST     *   |       CHARACTER            
COL2                                 NEXT     *   |       CHARACTER            
COL3                                 NEXT     *   |       CHARACTER            
COL4                                 NEXT     *   |       CHARACTER            
COL5                                 NEXT     *   |       CHARACTER            
COL6                                 NEXT     *   |       CHARACTER            
TIMEDATE                             NEXT     *   |       CHARACTER            
    SQL string for column : "to_char(sysdate,'yyyymmdd')"

Table T2, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
FILLER1                              NEXT     1           CHARACTER            
  (FILLER FIELD)
FILLER2                              NEXT     1           CHARACTER            
  (FILLER FIELD)
FILLER3                              NEXT     1           CHARACTER            
  (FILLER FIELD)
COL4                                 NEXT     1           CHARACTER            
FILLER4                              NEXT     1           CHARACTER            
  (FILLER FIELD)
COL6                                 NEXT     1           CHARACTER            
TIMEDATE                             NEXT     1           CHARACTER            
    SQL string for column : "to_char(sysdate,'yyyymmdd')"


Table T1:
  3 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Table T2:
  0 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  3 Rows not loaded because all fields were null.


Space allocated for bind array:                  65448 bytes(36 rows)
Space allocated for memory besides bind array:        0 bytes

Total logical records skipped:          1
Total logical records read:             3
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Tue May 20 09:42:09 2003
Run ended on Tue May 20 09:42:12 2003

Elapsed time was:     00:00:02.61
CPU time was:         00:00:00.09    


Is this possible to load data from single data file to multiple tables, if the data is NOT in the control file
and not in FIXED format? 

I am getting "3 Rows not loaded because all fields were null." message, and I think the problem is with
the position field (specyfing NEXT -- meaning looking for next field and since it is null, rejecting it?)

Your help would be much appreciated. 

Hello Tom,

I am trying to use 4 "filler" column in a user defined function in sql loader. Sqlldr is return ORA-01008 not all variables bound. (on Oracle 8.1.7.2)

Does sqlldr support using a filler column in a user defined function?

Thanks in advanced... ;-)

CREATE TABLE CMD_EMAIL_USAGE_TED
(
ACCT_ID VARCHAR2(15) NULL,
CID NUMBER(10) NULL,
CMD_PROMO_ID NUMBER(11) NOT NULL,
VENDOR_ID VARCHAR2(25) NOT NULL,
URL_ID NUMBER(10) NOT NULL,
URL_NAME VARCHAR2(100) NOT NULL,
URL_ADDR VARCHAR2(255) NOT NULL,
URL_REQ_DTM DATE NOT NULL)

ctl file--
load data
TRUNCATE
into table CMD_EMAIL_USAGE_TED fields terminated by '~'
(
ACCT_ID
,CID
,MST_CMPN_ID filler integer
,CMPN_ID filler integer
,PROMO_ID filler char
,SEG_ID filler char
,VENDOR_ID
,URL_ID
,URL_NAME
,URL_ADDR
,URL_REQ_DTM date "yyyy-mm-dd"
,CMD_PROMO_ID expression "GET_CMD_PROMO_ID(:MST_CMPN_ID,:CMPN_ID,:PROMO_ID,:SEG_ID)"
)

Function --
CREATE OR REPLACE FUNCTION GET_CMD_PROMO_ID (i_MST_CMPN_ID in number, i_CMPN_ID in number, i_PROMO_ID in varchar2, i_SEG_ID in varchar2)
RETURN number IS
v_CMD_PROMO_ID number;
BEGIN
-- Get id
select CMD_PROMO_ID into v_CMD_PROMO_ID
from CMD_PROMO
where
MST_CMPN_ID = i_MST_CMPN_ID and
CMPN_ID = i_CMPN_ID and
PROMO_ID = i_PROMO_ID and
SEG_ID = i_SEG_ID and
rownum < 2;

return v_CMD_PROMO_ID;

EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END;

-- cut from sqlldr output --
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ACCT_ID FIRST * ~ CHARACTER
CID NEXT * ~ CHARACTER
MST_CMPN_ID NEXT 4 INTEGER
(FILLER FIELD)
CMPN_ID NEXT 4 INTEGER
(FILLER FIELD)
PROMO_ID NEXT * ~ CHARACTER
(FILLER FIELD)
SEG_ID NEXT * ~ CHARACTER
(FILLER FIELD)
VENDOR_ID NEXT * ~ CHARACTER
URL_ID NEXT * ~ CHARACTER
URL_NAME NEXT * ~ CHARACTER
URL_ADDR NEXT * ~ CHARACTER
URL_REQ_DTM NEXT * ~ DATE yyyy-mm-dd
CMD_PROMO_ID EXPRESSION
SQL string for column : "GET_CMD_PROMO_ID(:MST_CMPN_ID,:CMPN_ID,:PROMO_ID,:SEG_ID)"

Record 1: Rejected - Error on table CMD_EMAIL_USAGE_TED.
ORA-01008: not all variables bound

Function works ok called from sqlplus and if hard code in the control file.

Tom,

is there anyway if we can skip a column from a query, 
if that columns has null for all the rows like below:

SQL> create table t (id number, col1 varchar2(10), col2 varchar2(5) default null);

Table created


SQL> insert into t(id,col1) values(1,'a');

1 row inserted

SQL> insert into t(id,col1) values(2,'b');

1 row inserted

SQL> insert into t(id,col1) values(3,'c');

1 row inserted

SQL> insert into t(id,col1) values(4,'d');

1 row inserted

SQL> select * from t;

ID COL1       COL2
-- ---------- -----
 1 a          
 2 b          
 3 c          
 4 d      

Here, can we stop displaying the column col2, as it has null values for all the rows

thanks in advance 

I have one input file to load 2 different tables. Columns differ with the exception of one column which uses a function to load values. The function uses a column that is present in the "first" table but a FILLER column in the "second" table. Can you show me an example of using delimted.word to skip a column and pass that skipped column to a function ? Thanks.

CREATE TABLE FMP4
(
MASTER_ID VARCHAR2(9),
DOB DATE,
COMP_BY CHAR(3),
CLINIC_ID VARCHAR2(15),
FORMSTAT_ID NUMBER(6) NOT NULL

)

CREATE TABLE FMP4_GRAFT
(
GRFT_MA1 VARCHAR2(25),
GRFT_MO1 VARCHAR2(200),
FORMSTAT_ID NUMBER(6) NOT NULL,
GRFT_SER1 VARCHAR2(25)
)

CREATE OR REPLACE function get_formstat (ps_form_id in char, ps_master_id varchar2)
return varchar2
as
ls_formstat_id formstatus.master_id%TYPE;
begin
select formstat_id
INTO ls_formstat_id
from formstatus
where master_id = ps_master_id
and form_id = ps_form_id;
return ls_formstat_id;
exception when no_data_found then return null;
end;
/

BEGIN DATA

012345678,04/28/1927,TAA,321-01M-O,SAM,JONES,ACME
098765432,01/28/1919,AAB,321-02F-S,BOB,SMITH,BLUE
066666666,03/25/1933,CCC,321-03XYZ,JAN,CLARK,YELLOW

Hi Tom,

We have flat files which contains denormalised data.

Example:
"10","Administration","1","200","1700"
"200","Jennifer","Whalen","JWHALEN","515.123.4444","9/17/1987","2","AD_ASST","4400.00","","101","10"
"20","Marketing","1","201","1800"
"201","Michael","Hartstein","MHARTSTE","515.123.5555","2/17/1996","2","MK_MAN","13000.00","","100","20"
"202","Pat","Fay","PFAY","603.123.6666","8/17/1997","2","MK_REP","6000.00","","201","20"
"30","Purchasing","1","114","1700"
"114","Den","Raphaely","DRAPHEAL","515.127.4561","12/7/1994","2","PU_MAN","11000.00","","100","30"
"115","Alexander","Khoo","AKHOO","515.127.4562","5/18/1995","2","PU_CLERK","3100.00","","114","30"
"116","Shelli","Baida","SBAIDA","515.127.4563","12/24/1997","2","PU_CLERK","2900.00","","114","30"
"119","Karen","Colmenares","KCOLMENA","515.127.4566","8/10/1999","2","PU_CLERK","2500.00","","114","30"
"118","Guy","Himuro","GHIMURO","515.127.4565","11/15/1998","2","PU_CLERK","2600.00","","114","30"
"117","Sigal","Tobias","STOBIAS","515.127.4564","7/24/1997","2","PU_CLERK","2800.00","","114","30"
"40","Human Resources","1","203","2400"
"203","Susan","Mavris","SMAVRIS","515.123.7777","6/7/1994","2","HR_REP","6500.00","","101","40"

Where the first column is from a departments table and the second row is from a employees table.
Now to distinguish these rows we have indicators 1 and 2.

For departments row indicator 1 is at 3rd column and for employees row indicator 2 is at 7th column.

Have written a control file:

OPTIONS ( ERRORS=50, BINDSIZE=50000, ROWS=200, READSIZE=65536)
LOAD DATA
CHARACTERSET WE8MSWIN1252
INFILE 'E:\emp.csv'

INTO TABLE "DBMIG"."DEPARTMENTS"
APPEND
REENABLE DISABLED_CONSTRAINTS
WHEN
FILLER_INDICATOR_ID='1'
FIELDS
TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'

(
"DEPARTMENT_ID" INTEGER EXTERNAL ,
"DEPARTMENT_NAME" CHAR ,
FILLER_INDICATOR_ID FILLER INTEGER EXTERNAL,
"MANAGER_ID" CHAR ,
"LOCATION_ID" INTEGER EXTERNAL
)

INTO TABLE "DBMIG"."EMPLOYEES"
APPEND
REENABLE DISABLED_CONSTRAINTS
WHEN
FILLER_INDICATOR_ID='2'
FIELDS
TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'

(
"EMPLOYEE_ID" INTEGER EXTERNAL ,
"FIRST_NAME" CHAR ,
"LAST_NAME" CHAR ,
"EMAIL" CHAR ,
"PHONE_NUMBER" CHAR ,
"HIRE_DATE" Date 'MM/DD/YYYY' ,
FILLER_INDICATOR_ID FILLER POSITION(7) INTEGER EXTERNAL,
"JOB_ID" CHAR ,
"SALARY" CHAR ,
"COMMISSION_PCT" CHAR ,
"MANAGER_ID" CHAR ,
"DEPARTMENT_ID" CHAR
)

Now the data for departments table gets inserted successfully but throws error

Record 2: Discarded - failed all WHEN clauses.
Record 4: Discarded - failed all WHEN clauses.
Record 5: Discarded - failed all WHEN clauses.
Record 7: Discarded - failed all WHEN clauses.
Record 8: Discarded - failed all WHEN clauses.

If the indicator columns for both are at the same position say 1 then the load goes successfully but when the position changes as here to 3 and 7 respectively for departments and employees it fails.

Is there any way this can be done ?

Regards

Yes definitly, in fact i was wondering how to use external tables for this one ? May be im too dumb...

Regards

Ok i figured it out... create two external tables on the same csv file... and eureka, its working :)

-------------------------------------------------------
CREATE TABLE "EXT_DEPARTMENTS"
(
"DEPARTMENT_ID" NUMBER(4),
"DEPARTMENT_NAME" VARCHAR2(30),
"FILLER_INDICATOR_ID" VARCHAR2(255),
"MANAGER_ID" NUMBER(6),
"LOCATION_ID" NUMBER(4)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY admin_dat_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
LOAD WHEN ("FILLER_INDICATOR_ID" = "1")
BADFILE admin_bad_dir:'dept.bad'
LOGFILE admin_log_dir:'dept.log'
READSIZE 65536
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"DEPARTMENT_ID" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"DEPARTMENT_NAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"FILLER_INDICATOR_ID" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"MANAGER_ID" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"LOCATION_ID" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'emp.csv'
)
)REJECT LIMIT UNLIMITED

---------------------------------------------------------

CREATE TABLE "EXT_EMPLOYEES"
(
"EMPLOYEE_ID" NUMBER(6),
"FIRST_NAME" VARCHAR2(20),
"LAST_NAME" VARCHAR2(25),
"EMAIL" VARCHAR2(25),
"PHONE_NUMBER" VARCHAR2(20),
"HIRE_DATE" DATE,
"FILLER_INDICATOR_ID" VARCHAR2(255),
"JOB_ID" VARCHAR2(10),
"SALARY" NUMBER(8,2),
"COMMISSION_PCT" NUMBER(2,2),
"MANAGER_ID" NUMBER(6),
"DEPARTMENT_ID" NUMBER(4)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY ADMIN_DAT_DIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
LOAD WHEN ("FILLER_INDICATOR_ID" = "2")
BADFILE admin_bad_dir:'emp.bad'
LOGFILE admin_log_dir:'emp.log'
READSIZE 65536
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"EMPLOYEE_ID" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"FIRST_NAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"LAST_NAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"EMAIL" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"PHONE_NUMBER" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"HIRE_DATE" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
DATE_FORMAT DATE MASK "MM/DD/YYYY",
"FILLER_INDICATOR_ID" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"JOB_ID" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SALARY" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"COMMISSION_PCT" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"MANAGER_ID" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"DEPARTMENT_ID" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'emp.csv'
)
)REJECT LIMIT UNLIMITED

--------------------------------------------------------

Thanks for the direction :)

Regards

I have a data record like this.

"1";343;"aaaa;aaaa";"CATEGORY CODE "c" OF EMPLOYEE";

1-My delimiter is the character ';' (i.e. semi colon).

2-Some (not all) fields are enclosed by the '"' character (i.e. double qoute charater).

3-The fields which are enclosed by the double quote character can have a semi colon character in their data.

4-The fields which are enclosed by the double quote character can have a '"' (double quote) in their data.

How can I write a .ctl file for this kind of data.

Thanks

From the manual

"
Since, when using the conventional path method, the bind array is limited by the size of the read buffer, the advantage of a larger read buffer is that more data can be read before a commit is required.

"

Does the bindsize determine the commit point. Does sqlldr
commits each time bindsize amount of data is loaded

Thanks Tom.

Whats the purpose new param "commit_discontinued" in 9i sqlloader.

When it will be useful ?


commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE)

Tom,
I wonder if one can add some constant or expression colum to the control file format that would allow loading a DB column with that value (although it's not in the input data file).
the opposite of Filler option that omits the column from being loaded.

I want to add a "BATCH_ID" tot he data set being loaded, to track further batch processing.

Appreciate your time,
david

Thanks a lot Tom,
sorry for asking a trivial question :)

Here is a working control file from my test:

--Sample table DDL
create table test_batch
(BATCH_ID VARCHAR2(100),
FIELD1 VARCHAR2(100),
FIELD2 VARCHAR2(100));


#Shell script snipet to produce the control file on the fly:
TABLE_NAME=test_batch
TS=`date +%m%d%H%m`

echo "
LOAD DATA
TRUNCATE
INTO TABLE ${TABLE_NAME}
FIELDS TERMINATED BY \",\" OPTIONALLY ENCLOSED BY '\"'
TRAILING NULLCOLS
(batch_id EXPRESSION \"'$$_'||'$TS'\"
,field1
,field2
)
" > ${SQLLDR_CTLFILE}