Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Craig.

Asked: December 09, 2010 - 10:10 am UTC

Last updated: December 10, 2010 - 11:24 am UTC

Version: 6.2.1

Viewed 10K+ times! This question is

You Asked

I've been struggling with a dataload from an outside datasource and have to get the previous 2 years worth of data files loaded by Christmas. Normally that wouldn't be a problem because once I figure out my initial load process, there are only minor tweaks from there. I fear though, that I'm missing something obvious or am going about solving this problem the wrong way. I have been unable to get my initail load to work. First the first 5 lines of the data file:

074851 ,KEYS OF KINGDOM (INTERLUDES) ,$10.00, 1 , $5.00
073448 ,"GOSPEL PIANIST, THE ",$13.00, - , $-
088100 ,KEYS OF KINGDOM (COMMUNION)) ,$10.00, - , $-
018781 ,KEYS OF KINGDOM (CHRISTMAS) ,$10.00, 3 , $15.00
018773 ,KEYS OF KINGDOM (GENERAL) ,$10.00, - , $-

They are sending us a list of every product of ours they sell, regardless of if they sold any of that product this month(?!?). It appears as if ' - ' means 0 quantity sold. Tom, I do dataloads all the time and have just finished my first Oracle 11G class, but this one is kicking my tail. Here is my control file I've written(still doesn't work yet, and as you can see, I've been working on this one as I have time for almost a week)

-- Written by Craig Buchanan 12/03/10
Load DATA
INFILE '/xfers/oracle/dw/data/presser_sales.dat'
BADFILE '/xfers/oracle/dw/data/presser_sales.bad'
APPEND INTO TABLE STAGE.PRESSER_SALES
WHEN (ITEM_QTY != '0')
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS

(ITEM_NUM CHAR
"TRIM(:ITEM_NUM)",
TITLE CHAR
"TRIM(:TITLE)",
LIST_PRICE CHAR
"NVL(translate(TRIM(:LIST_PRICE), '0123456789.ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~!#$%^&*()_+=-`{}[]|\?><,/''','0123456789.'),0)",
ITEM_QTY CHAR
"NVL(translate(TRIM(:ITEM_QTY), '0123456789.ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~!#$%^&*()_+=-`{}[]|\?><,/''','0123456789.'),0)",
PAYMENT_AMOUNT CHAR
"NVL(translate(TRIM(:PAYMENT_AMOUNT), '0123456789.-ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~!#$%^&*()_+=`{}[]|\?><,/''','0123456789.-'),0)",
INSERT_USER CONSTANT "SYSTEM",
INSERT_USER_DATE SYSDATE,
INSERT_STATUS_CODE CONSTANT "I")

...and here is my table I need to load it into...

CREATE TABLE presser_sales
(presser_sales_id NUMBER ,
item_num VARCHAR2(20 BYTE),
title VARCHAR2(200 BYTE),
list_price NUMBER,
item_qty NUMBER,
payment_amount NUMBER,
insert_user VARCHAR2(20 BYTE),
insert_user_date DATE,
insert_status_code CHAR(1 BYTE))
/

This seems so simple, just a few fields, map em, move on with your life. If my boss would let me load everything into STAGE as Varchar2, I would handle data cleanup on my transfer to our PROD table, but he has drawn a line in the sand on this. So I'm trying to handle ugly data that I have no control over, being passed across to me in wrong formats. I'd really apreciate you talking me through the thought process of a solution that I could use. Thanks for your time.

and Tom said...

I think it was all mostly due to "when (item_qty!='0')", you meant to say "-".

I didn't like your functions for the numbers, I just stripped the $ and to_numbered it.

ops$tkyte%ORA11GR2> !cat test.ctl
Load DATA 
INFILE *
REPLACE
INTO TABLE PRESSER_SALES 
WHEN (ITEM_QTY != '-') 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS 
(ITEM_NUM     CHAR,
 TITLE        CHAR "TRIM(:TITLE)", 
 LIST_PRICE   CHAR "to_number(substr(:list_price,2))",
 ITEM_QTY     CHAR, 
 PAYMENT_AMOUNT CHAR "to_number(substr(:payment_amount,2))",
 INSERT_USER      CONSTANT "SYSTEM", 
 INSERT_USER_DATE    SYSDATE, 
 INSERT_STATUS_CODE    CONSTANT "I"
) 
begindata
074851    ,KEYS OF KINGDOM (INTERLUDES) ,$10.00, 1 , $5.00 
073448    ,"GOSPEL PIANIST, THE      ",$13.00, -  , $-  
088100    ,KEYS OF KINGDOM (COMMUNION)) ,$10.00, -  , $-  
018781    ,KEYS OF KINGDOM (CHRISTMAS)  ,$10.00, 3 , $15.00 
018773    ,KEYS OF KINGDOM (GENERAL)  ,$10.00, -  , $-  

ops$tkyte%ORA11GR2> !sqlldr / test

SQL*Loader: Release 11.2.0.2.0 - Production on Thu Dec 9 13:34:36 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 5

ops$tkyte%ORA11GR2> select * from presser_sales;

PRESSER_SALES_ID ITEM_NUM             TITLE                          LIST_PRICE   ITEM_QTY PAYMENT_AMOUNT INSERT_USE INSERT_USER_DATE     I
---------------- -------------------- ------------------------------ ---------- ---------- -------------- ---------- -------------------- -
                 074851               KEYS OF KINGDOM (INTERLUDES)           10          1              5 SYSTEM     09-dec-2010 13:34:36 I
                 018781               KEYS OF KINGDOM (CHRISTMAS)            10          3             15 SYSTEM     09-dec-2010 13:34:36 I

ops$tkyte%ORA11GR2> !cat test.log

SQL*Loader: Release 11.2.0.2.0 - Production on Thu Dec 9 13:34:36 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

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

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

Table PRESSER_SALES, loaded when ITEM_QTY != 0X2d(character '-')
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ITEM_NUM                            FIRST     *   ,  O(") CHARACTER            
TITLE                                NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "TRIM(:TITLE)"
LIST_PRICE                           NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "to_number(substr(trim(:list_price),2))"
ITEM_QTY                             NEXT     *   ,  O(") CHARACTER            
PAYMENT_AMOUNT                       NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "to_number(substr(trim(:payment_amount),2))"
INSERT_USER                                               CONSTANT
    Value is 'SYSTEM'
INSERT_USER_DATE                                          SYSDATE
INSERT_STATUS_CODE                                        CONSTANT
    Value is 'I'

Record 2: Discarded - failed all WHEN clauses.
Record 3: Discarded - failed all WHEN clauses.
Record 5: Discarded - failed all WHEN clauses.

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


Space allocated for bind array:                  83136 bytes(64 rows)
Read   buffer bytes: 1048576

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

Run began on Thu Dec 09 13:34:36 2010
Run ended on Thu Dec 09 13:34:36 2010

Elapsed time was:     00:00:00.04
CPU time was:         00:00:00.00


Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Followup. Records still not loaded

Craig Buchanan, December 09, 2010 - 1:04 pm UTC

Still no records loaded after trying the new version of the control file. From the presser_sales.ctl.log

Record 2: Discarded - failed all WHEN clauses.
Record 3: Discarded - failed all WHEN clauses.
Record 5: Discarded - failed all WHEN clauses.
Record 1: Rejected - Error on table STAGE.PRESSER_SALES, column PAYMENT_AMOUNT.
ORA-01722: invalid number
Record 4: Rejected - Error on table STAGE.PRESSER_SALES, column PAYMENT_AMOUNT.
ORA-01722: invalid number

...my new control file as suggested earlier...

-- Written by Craig Buchanan/ Tom!  12/03/10
Load DATA
INFILE '/xfers/oracle/dw/data/presser_sales.dat'
BADFILE '/xfers/oracle/dw/data/presser_sales.bad'
APPEND INTO TABLE STAGE.PRESSER_SALES
WHEN (ITEM_QTY != '-')
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS 
(ITEM_NUM     CHAR,
 TITLE        CHAR "TRIM(:TITLE)", 
 LIST_PRICE   CHAR "to_number(substr(:list_price,2))",
 ITEM_QTY     CHAR, 
 PAYMENT_AMOUNT CHAR "to_number(substr(:payment_amount,2))",
 INSERT_USER      CONSTANT "SYSTEM", 
 INSERT_USER_DATE    SYSDATE, 
 INSERT_STATUS_CODE    CONSTANT "I"
) 
...and again, the table I am attempting to load into...

CREATE TABLE presser_sales
    (presser_sales_id               NUMBER ,
    item_num                       VARCHAR2(20 BYTE),
    title                          VARCHAR2(200 BYTE),
    list_price                     NUMBER,
    item_qty                       NUMBER,
    payment_amount                 NUMBER,
    insert_user                    VARCHAR2(20 BYTE),
    insert_user_date               DATE,
    insert_status_code             CHAR(1 BYTE))
/

...and finally, the procedure I'm calling sqlldr from...

PROCEDURE PRESSER_SALES
   as
   c_program_name              VARCHAR2(100)    := 'STAGE.LOAD_PKG.PRESSER_SALES';
   str                         varchar2(1350);
   sbj                         varchar2(1350);

  BEGIN

     dw.log(C_PROGRAM_NAME,'*** Procedure Start ***');
     COMMIT;
     execute immediate '    DELETE FROM STAGE.PRESSER_SALES WHERE INSERT_STATUS_CODE = ''I''';
     COMMIT;

     DW.SQLLOADER('presser_sales.ctl','STAGE','PRESSER_SALES');
     FILE_PKG.SOURCE_FILE_MTH_BACKUP('presser_sales');

     dw.log(C_PROGRAM_NAME,'    File presser_sales.dat has been loaded into STAGE and backup has been performed.');

     execute immediate 'UPDATE STAGE.PRESSER_SALES SET INSERT_STATUS_CODE = ''C'' WHERE INSERT_STATUS_CODE = ''I''';
     COMMIT;

     dw.log(C_PROGRAM_NAME,'*** Procedure End ***');
     COMMIT;

  EXCEPTION
    WHEN OTHERS THEN

         dw.log(C_PROGRAM_NAME,'An error has occurred and processing has been terminated. Notification sent.');
         COMMIT;

       sbj := 'PRESSER_SALES stored procedure, LOAD_PKG package ERROR';
       dw.send_email('dwgroup@umpublishing.org','dwgroup@umpublishing.org',
                   sbj, sqlerrm);
       raise_application_error(-20001,'The following error has occured: ' || sqlerrm);
  END PRESSER_SALES;

Tom Kyte
December 10, 2010 - 8:30 am UTC

but you don't give me the offending data - I cannot reproduce this.

any reason you are not using an external table? since the file must exist on the database server (you are calling sqlldr from plsql I see - that is happening on the server)

you could use an external table which would be mapped as all varchar2's and then just use an insert as select from it - and work with the full power of SQL.

PROCEDURE PRESSER_SALES looks light a nightmare, doesn't it ?

A reader, December 09, 2010 - 2:16 pm UTC

why execute immediate when doing dml from pl/sql ?
when others then commit; ?
...
Tom Kyte
December 10, 2010 - 9:52 am UTC

I didn't even really read it, but yes, it has some interesting 'properties'

TRIM missing from control file?

Jonathan Taylor, December 09, 2010 - 5:24 pm UTC

There is no TRIM() in the control file for LIST_PRICE and PAYMENT_AMOUNT (although strangely it appears in the SQLLDR output in Tom's example).

Maybe this is the problem - but I don't access to a database to try it.
Tom Kyte
December 10, 2010 - 9:54 am UTC

there needs to be no trim, that was a cut and paste error on my part - sorry about that.

sqlldr already trims the whitespace for us.

Why not external tables?

Mike, December 10, 2010 - 6:11 am UTC

Is your version really 6.2.1???

My approach on a problem like this would be to define an external table, with simple VARCHAR2(255) columns delimited by commas. Then write a procedure to read the table and do what you want. This helps simplify things by:
- keeping the logic in one place
- having the ability to make the logic as complex as you need
- having all of your data available as a table - not some left behind in a BADFILE

Work within SQL

Maverick, December 10, 2010 - 9:11 am UTC

Why don't just dump the data as is [all characters] in a temp table in database and then use scripts to modify as needed and write [insert] in original table? ofcourse it's a two step process but since you need to finish this by christmas, why to struggle....

@Mike from Cleveland

Duke Ganote, December 10, 2010 - 9:29 am UTC

Thank you all

Craig Buchanan, December 10, 2010 - 10:39 am UTC

I was tasked with doing this load solely using sqlldr. If I would have been allowed to use a temp table with all varchar2 fields, I would have been done within just a day or two. I just submitted the first 5 rows of my file as sample data earlier for the purpose of my question, but my real initail file had thousands of rows.

074851      ,KEYS OF KINGDOM (INTERLUDES)  ,$10.00, 1 , $5.00 
073448      ,"GOSPEL PIANIST, THE           ",$13.00, -   , $-   
088100      ,KEYS OF KINGDOM (COMMUNION))  ,$10.00, -   , $-   
018781      ,KEYS OF KINGDOM (CHRISTMAS)   ,$10.00, 3 , $15.00 
018773      ,KEYS OF KINGDOM (GENERAL)     ,$10.00, -   , $-   

This morning I sucessfully loaded my file, and while I am convinced that it isn't the cleanest way to write the load, I am very gratefull to finally get that fist pump moment.

-- Written by Craig Buchanan/ Tom!  12/03/10
Load DATA
INFILE '/xfers/oracle/dw/data/presser_sales.dat'
BADFILE '/xfers/oracle/dw/data/presser_sales.bad'
APPEND INTO TABLE STAGE.PRESSER_SALES
WHEN (ITEM_QTY != '-')
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS 
(ITEM_NUM     CHAR,
 TITLE        CHAR "TRIM(:TITLE)", 
 LIST_PRICE   CHAR "to_number(REPLACE(TRIM(:LIST_PRICE),'$'))", 
 ITEM_QTY     CHAR "to_number(REPLACE(REPLACE(TRIM(:ITEM_QTY),'(','-'),')'))", 
 PAYMENT_AMOUNT CHAR TERMINATED BY X'0D0A' "to_number(REPLACE(REPLACE(REPLACE(TRIM(:ITEM_QTY),'$'),'(','-'),')'))", 
 INSERT_USER      CONSTANT "SYSTEM", 
 INSERT_USER_DATE    SYSDATE, 
 INSERT_STATUS_CODE    CONSTANT "I"
) 

I was unable to get the TRANSLATE function to work properly, thus the ugly nested replaces. I had to account for numbers wrapped in parens '(val)' that mean the number is negative. I was told that this is some sort of accounting practice, so I now have one more reason to not like accountants.

Thank you all again for your input and your help in getting me un-stuck. It is greatly appreciated.

Tom Kyte
December 10, 2010 - 11:24 am UTC

... I was tasked with doing this load solely using sqlldr. ...

I'd be glad to get on the phone with your manager and explain that dictating the tool and approach to be used means THEY should be writing the code. They should be giving you a GOAL to accomplish and allow you to use the most appropriate tool. sqlldr is wrong in this case - shouldn't be part of the plan (unless you haven't read any of the documentation since 2000 that is...)


Typo

Craig Buchanan, December 10, 2010 - 10:52 am UTC

sorry for the double post, but I noticed my error. Here is the corrected version of the working control file.

-- Written by Craig Buchanan/ Tom! 12/03/10
Load DATA
INFILE '/xfers/oracle/dw/data/presser_sales.dat'
BADFILE '/xfers/oracle/dw/data/presser_sales.bad'
APPEND INTO TABLE STAGE.PRESSER_SALES
WHEN (ITEM_QTY != '-')
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(ITEM_NUM CHAR,
TITLE CHAR "TRIM(:TITLE)",
LIST_PRICE CHAR "to_number(REPLACE(TRIM(:LIST_PRICE),'$'))",
ITEM_QTY CHAR "to_number(REPLACE(REPLACE(TRIM(:ITEM_QTY),'(','-'),')'))",
PAYMENT_AMOUNT CHAR TERMINATED BY X'0D0A' "to_number(REPLACE(REPLACE(REPLACE(TRIM(:PAYMENT_AMOUNT),'$'),'(','-'),')'))",
INSERT_USER CONSTANT "SYSTEM",
INSERT_USER_DATE SYSDATE,
INSERT_STATUS_CODE CONSTANT "I"
)