SQLLDR with Function does not load.

I have the following sqlldr control file.
LOAD DATA
INFILE 'data\docetl800_tsupv_refund.dat'
BADFILE 'data\docetl801_tsupv.bad'
DISCARDFILE 'data\docetl801_tsupv.dsc'

INTO TABLE "LDCSCHEMA"."CACU_TSUPV_REFUND"  
TRUNCATE
FIELDS TERMINATED BY '|' 
TRAILING NULLCOLS
(
  CACU_CLIENT_DOC_NUMBER "ltrim(rtrim(:CACU_CLIENT_DOC_NUMBER))", 
  cacuCheckDate  "SUBSTR(:cacuCheckDate,7,4) || SUBSTR(:cacuCheckDate,1,2) || SUBSTR(:cacuCheckDate,4,2)",
  cacuCheckNumber "ltrim(rtrim(:cacuCheckNumber))", 
  cacuRefundReasonCode "ltrim(rtrim(:cacuRefundReasonCode))", 
  cacuRefundAmt "ldcschema.code_conv(ltrim(rtrim(:cacuRefundAmt)))",   
  cacuAppropriationCode "ltrim(rtrim(:cacuAppropriationCode))", 
  cacuPayeeName "ltrim(rtrim(:cacuPayeeName))", 
  cacuPayeeStreet1 "ltrim(rtrim(:cacuPayeeStreet1))", 
  cacuPayeeStreet2 "ltrim(rtrim(:cacuPayeeStreet2))", 
  cacuPayeeCity "ltrim(rtrim(:cacuPayeeCity))", 
  cacuPayeeState "ltrim(rtrim(:cacuPayeeState))", 
  cacuPayeeZipCode "ltrim(rtrim(:cacuPayeeZipCode))", 
  cacuTSupvRefundStatusCode "ltrim(rtrim(:cacuTSupvRefundStatusCode))", 
  cacuSupvRefundStatusDate "SUBSTR(:cacuSupvRefundStatusDate,7,4) || SUBSTR(:cacuSupvRefundStatusDate,1,2) || SUBSTR(:cacuSupvRefundStatusDate,4,2)", 
  cacuRecordedDate "SUBSTR(:cacuRecordedDate,7,4) || SUBSTR(:cacuRecordedDate,1,2) || SUBSTR(:cacuRecordedDate,4,2)", 
  cacuLastMaintDt "SUBSTR(:cacuLastMaintDt,7,4) || SUBSTR(:cacuLastMaintDt,1,2) || SUBSTR(:cacuLastMaintDt,4,2)",
  cacuLastMaintOpId "ltrim(rtrim(:cacuLastMaintOpId))"
)

Open in new window


Here is the error msg I get.
Record 1: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Open in new window


I wrote the function ldcschema.code_conv() to convert some EBCDIC codes.

Thank you,

Bob
bobcaputoAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
It appears the issue is with the cacuappropriationcode value.

Is that the only error message?  It should also say why it failed.

If you can post a table definition and sample data I can take a look.

Or post th entire sqlldr log file.
0
 
bobcaputoAuthor Commented:
Here is the output file.
SQL*Loader: Release 8.1.7.0.0 - Production on Mon Apr 18 14:51:08 2011

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

Control File:   \\APWMAD0A0773\DEV$\wicsv3\jobs\DOCETL801_TSUPREF.ctl
Data File:      data\docetl800_tsupv_refund.dat
  Bad File:     data\docetl801_tsupv.bad
  Discard File: data\docetl801_tsupv.dsc 
 (Allow all discards)

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

Table "LDCSCHEMA"."CACU_TSUPV_REFUND", loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CACU_CLIENT_DOC_NUMBER              FIRST     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:CACU_CLIENT_DOC_NUMBER))"
CACUCHECKDATE                        NEXT     *   |       CHARACTER            
    SQL string for column : "SUBSTR(:cacuCheckDate,7,4) || SUBSTR(:cacuCheckDate,1,2) || SUBSTR(:cacuCheckDate,4,2)"
CACUCHECKNUMBER                      NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuCheckNumber))"
CACUREFUNDREASONCODE                 NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuRefundReasonCode))"
CACUREFUNDAMT                        NEXT     *   |       CHARACTER            
    SQL string for column : "ldcschema.code_conv(ltrim(rtrim(:cacuRefundAmt)))"
CACUAPPROPRIATIONCODE                NEXT     *   |       CHARACTER            
    SQL string for column : ":cacuAppropriationCode"
CACUPAYEENAME                        NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuPayeeName))"
CACUPAYEESTREET1                     NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuPayeeStreet1))"
CACUPAYEESTREET2                     NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuPayeeStreet2))"
CACUPAYEECITY                        NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuPayeeCity))"
CACUPAYEESTATE                       NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuPayeeState))"
CACUPAYEEZIPCODE                     NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuPayeeZipCode))"
CACUTSUPVREFUNDSTATUSCODE            NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuTSupvRefundStatusCode))"
CACUSUPVREFUNDSTATUSDATE             NEXT     *   |       CHARACTER            
    SQL string for column : "SUBSTR(:cacuSupvRefundStatusDate,7,4) || SUBSTR(:cacuSupvRefundStatusDate,1,2) || SUBSTR(:cacuSupvRefundStatusDate,4,2)"
CACURECORDEDDATE                     NEXT     *   |       CHARACTER            
    SQL string for column : "SUBSTR(:cacuRecordedDate,7,4) || SUBSTR(:cacuRecordedDate,1,2) || SUBSTR(:cacuRecordedDate,4,2)"
CACULASTMAINTDT                      NEXT     *   |       CHARACTER            
    SQL string for column : "SUBSTR(:cacuLastMaintDt,7,4) || SUBSTR(:cacuLastMaintDt,1,2) || SUBSTR(:cacuLastMaintDt,4,2)"
CACULASTMAINTOPID                    NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuLastMaintOpId))"

Record 1: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 2: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 3: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 4: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 5: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 6: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 7: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 8: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 9: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 10: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 11: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 12: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 13: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 14: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 15: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 16: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 17: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 18: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 19: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 20: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 21: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 22: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 23: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 24: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 25: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 26: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 27: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 28: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 29: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 30: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 31: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 32: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 33: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 34: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 35: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 36: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 37: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 38: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 39: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 40: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 41: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 42: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 43: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 44: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 45: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 46: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 47: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 48: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 49: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 50: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier


MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table "LDCSCHEMA"."CACU_TSUPV_REFUND":
  0 Rows successfully loaded.
  51 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.


Space allocated for bind array:                  61404 bytes(14 rows)
Space allocated for memory besides bind array:        0 bytes

Total logical records skipped:          0
Total logical records read:            56
Total logical records rejected:        51
Total logical records discarded:        0

Run began on Mon Apr 18 14:51:08 2011
Run ended on Mon Apr 18 14:51:08 2011

Elapsed time was:     00:00:00.27
CPU time was:         00:00:00.04    

Open in new window


Here is the table def:
CREATE TABLE LDCSCHEMA.CACU_TSUPV_REFUND
(
  CACU_CLIENT_DOC_NUMBER     VARCHAR2(6 BYTE)   NOT NULL,
  CACUCHECKDATE              VARCHAR2(8 BYTE)   NOT NULL,
  CACUCHECKNUMBER            NUMBER(8)          NOT NULL,
  CACUREFUNDREASONCODE       VARCHAR2(2 BYTE),
  CACUREFUNDAMT              NUMBER(9,2),
  CACUAPPROPRIATIONCODE      CHAR(1 BYTE),
  CACUPAYEENAME              VARCHAR2(37 BYTE),
  CACUPAYEESTREET1           VARCHAR2(25 BYTE),
  CACUPAYEESTREET2           VARCHAR2(25 BYTE),
  CACUPAYEECITY              VARCHAR2(20 BYTE),
  CACUPAYEESTATE             CHAR(2 BYTE),
  CACUPAYEEZIPCODE           VARCHAR2(9 BYTE),
  CACUTSUPVREFUNDSTATUSCODE  VARCHAR2(2 BYTE),
  CACUSUPVREFUNDSTATUSDATE   VARCHAR2(8 BYTE),
  CACURECORDEDDATE           VARCHAR2(8 BYTE),
  CACULASTMAINTDT            VARCHAR2(10 BYTE),
  CACULASTMAINTOPID          VARCHAR2(8 BYTE),
  OFFENDERID                 VARCHAR2(7 BYTE),
  ADDRESSID                  VARCHAR2(7 BYTE),
  STREETNUMBER               VARCHAR2(10 BYTE),
  STREETDIRECTION            VARCHAR2(2 BYTE),
  STREETNAME                 VARCHAR2(30 BYTE),
  STREETTYPE                 VARCHAR2(4 BYTE),
  SUITENUMBER                VARCHAR2(6 BYTE),
  APARTMENTNUM               VARCHAR2(6 BYTE),
  POBOX                      VARCHAR2(6 BYTE),
  RURALROUTENUMBER           VARCHAR2(6 BYTE),
  RURALBOXNUM                VARCHAR2(6 BYTE),
  ADDRESSOTHER               VARCHAR2(30 BYTE),
  STREETDIRECTIONSUFFIX      VARCHAR2(2 BYTE),
  COUNTRY                    VARCHAR2(2 BYTE),
  COUNTY                     VARCHAR2(3 BYTE),
  ADDRESSEXISTFLAG           CHAR(1 BYTE),
  ADDRESSRELEXISTFLG         CHAR(1 BYTE),
  ADDRESSVALIDFLG            CHAR(1 BYTE)
)

Open in new window


And here is some sample data.
I04372|01/10/1998|A8069642|OT|0004130{|A|GREAT LAKES HIGHER EDUCATION CORP    |DRAWER 480               |                         |MILWAUKEE           |WI|53293    |  |02/03/1998|02/03/1998|02/03/1998|DOC058
I04372|06/02/1997|A7500074|OT|0003540{|A|DEPT OF REVENUE                      |CASHIERS UNIT J JONES    |4638 UNIVERSITY AVE      |MADISON             |WI|53707    |  |06/03/1997|06/03/1997|06/03/1997|DOC058
I06887|04/09/1998|A8248134|OT|0001000{|A|UW MADISON                           |750 UNIV AVE  RM 149     |                         |MADISON             |WI|53706    |  |04/10/1998|04/10/1998|04/10/1998|DOC058
I08926|10/26/2000|A0337613|CV|0000100{|A|ANDREA RUENGER                       |12 SUNSHINE              |                         |ROSWELL             |NM|88201    |  |11/01/2000|11/01/2000|11/01/2000|DOCI57
I09344|02/06/2001|A0549502|IC|0000100{|A|JULIE JADIN                          |3151 W GIRAD AVENUE      |                         |ENGLEWOOD           |CO|80110    |  |02/13/2001|02/13/2001|02/13/2001|DOCI57
I09354|10/12/2000|A0309014|OT|0000600{|A|SCOTT R BYLCZYNSKI                   |855 E LAKE STREET        |                         |LAKE MILLS          |WI|53551    |  |10/18/2000|10/18/2000|10/18/2000|DOCI57
I09357|05/14/2002|A1492895|OT|0000300{|A|DARVIN GREEN                         |4755 N 29TH STREET       |                         |MILWAUKEE           |WI|53209    |  |05/21/2002|05/21/2002|05/21/2002|DOCI57
I09360|11/21/2002|A1897185|OT|0000400{|A|JAMES JOHNSON JR                     |5578 BAYRIDGE DR         |                         |HILLIARD            |OH|43026    |  |11/22/2002|11/22/2002|11/22/2002|DOCBIG
I09363|11/05/2003|A2590430|OT|0000200{|A|WAYNE A CORDTS                       |10547  1/2  E COUNTY RD B|                         |LAKE NEBAGAMON      |WI|54849    |  |11/06/2003|11/06/2003|11/06/2003|DOC523
I09369|04/09/2004|A2906910|OT|0001000{|A|DUSTIN STAUSKE                       |54175 INDIAN LAKE ROAD   |                         |MASON               |WI|54856    |  |04/13/2004|04/13/2004|04/13/2004|DOC523

Open in new window


That should be about it, let me know what else you may need.
0
 
HainKurtSr. System AnalystCommented:
CACUAPPROPRIATIONCODE is this really one char in the file you are trying to load?
is there any trigger involved in this table?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bobcaputoAuthor Commented:
Yes it is one char as you can see in the sample data. It is the sixth data field in.

There are no triggers on this table.

This one has me scratching my head, and wondering about the function in the statement directly before this column.

Any help is appreciated.
0
 
HainKurtSr. System AnalystCommented:
if you use

cacuAppropriationCode "ltrim(rtrim(:cacuAppropriationCode))",

-->

cacuAppropriationCode "A",

or

cacuAppropriationCode "'A'",

does it work (I mean hard code it to "A" to test

0
 
bobcaputoAuthor Commented:
It seems to have gone past that and now gotten

Record 1: Rejected - Error on table "LDCSCHEMA"."CACU_TSUPV_REFUND", column CACUPAYEESTREET1.
ORA-00984: column not allowed here

I will have to look at some things. Very odd problem, but great to substitute hard value. My head is numb today from this.
0
 
slightwv (䄆 Netminder) Commented:
I'm unable to reproduce the exact error you are receiving.  I was able to locate a couple more:

cacuCheckNumber starts with an 'A' so I trimmed that.

cacuRefundAmt  ends with a '{'.

I was able to load one row with the following


--drop TABLE CACU_TSUPV_REFUND purge;
CREATE TABLE CACU_TSUPV_REFUND
(
  CACU_CLIENT_DOC_NUMBER     VARCHAR2(6 BYTE)   NOT NULL,
  CACUCHECKDATE              VARCHAR2(8 BYTE)   NOT NULL,
  CACUCHECKNUMBER            varchar2(20),
  CACUREFUNDREASONCODE       VARCHAR2(2 BYTE),
  CACUREFUNDAMT              NUMBER(9,2),
  CACUAPPROPRIATIONCODE      CHAR(1 BYTE),
  CACUPAYEENAME              VARCHAR2(37 BYTE),
  CACUPAYEESTREET1           VARCHAR2(25 BYTE),
  CACUPAYEESTREET2           VARCHAR2(25 BYTE),
  CACUPAYEECITY              VARCHAR2(20 BYTE),
  CACUPAYEESTATE             CHAR(2 BYTE),
  CACUPAYEEZIPCODE           VARCHAR2(9 BYTE),
  CACUTSUPVREFUNDSTATUSCODE  VARCHAR2(2 BYTE),
  CACUSUPVREFUNDSTATUSDATE   VARCHAR2(8 BYTE),
  CACURECORDEDDATE           VARCHAR2(8 BYTE),
  CACULASTMAINTDT            VARCHAR2(10 BYTE),
  CACULASTMAINTOPID          VARCHAR2(8 BYTE),
  OFFENDERID                 VARCHAR2(7 BYTE),
  ADDRESSID                  VARCHAR2(7 BYTE),
  STREETNUMBER               VARCHAR2(10 BYTE),
  STREETDIRECTION            VARCHAR2(2 BYTE),
  STREETNAME                 VARCHAR2(30 BYTE),
  STREETTYPE                 VARCHAR2(4 BYTE),
  SUITENUMBER                VARCHAR2(6 BYTE),
  APARTMENTNUM               VARCHAR2(6 BYTE),
  POBOX                      VARCHAR2(6 BYTE),
  RURALROUTENUMBER           VARCHAR2(6 BYTE),
  RURALBOXNUM                VARCHAR2(6 BYTE),
  ADDRESSOTHER               VARCHAR2(30 BYTE),
  STREETDIRECTIONSUFFIX      VARCHAR2(2 BYTE),
  COUNTRY                    VARCHAR2(2 BYTE),
  COUNTY                     VARCHAR2(3 BYTE),
  ADDRESSEXISTFLAG           CHAR(1 BYTE),
  ADDRESSRELEXISTFLG         CHAR(1 BYTE),
  ADDRESSVALIDFLG            CHAR(1 BYTE)
)
/

create or replace function code_conv(inStr in varchar2) return varchar2
is
begin
	return inStr;
end;
/

show errors

Open in new window

LOAD DATA
INFILE *
INTO TABLE CACU_TSUPV_REFUND  
TRUNCATE
FIELDS TERMINATED BY '|' 
TRAILING NULLCOLS
(
  CACU_CLIENT_DOC_NUMBER "ltrim(rtrim(:CACU_CLIENT_DOC_NUMBER))", 
  cacuCheckDate  "SUBSTR(:cacuCheckDate,7,4) || SUBSTR(:cacuCheckDate,1,2) || SUBSTR(:cacuCheckDate,4,2)",
  cacuCheckNumber "ltrim(rtrim(:cacuCheckNumber),'A')", 
  cacuRefundReasonCode "ltrim(rtrim(:cacuRefundReasonCode))", 
  cacuRefundAmt "code_conv(ltrim(rtrim(:cacuRefundAmt,'{')))",   
  cacuAppropriationCode "ltrim(rtrim(:cacuAppropriationCode))", 
  cacuPayeeName "ltrim(rtrim(:cacuPayeeName))", 
  cacuPayeeStreet1 "ltrim(rtrim(:cacuPayeeStreet1))", 
  cacuPayeeStreet2 "ltrim(rtrim(:cacuPayeeStreet2))", 
  cacuPayeeCity "ltrim(rtrim(:cacuPayeeCity))", 
  cacuPayeeState "ltrim(rtrim(:cacuPayeeState))", 
  cacuPayeeZipCode "ltrim(rtrim(:cacuPayeeZipCode))", 
  cacuTSupvRefundStatusCode "ltrim(rtrim(:cacuTSupvRefundStatusCode))", 
  cacuSupvRefundStatusDate "SUBSTR(:cacuSupvRefundStatusDate,7,4) || SUBSTR(:cacuSupvRefundStatusDate,1,2) || SUBSTR(:cacuSupvRefundStatusDate,4,2)", 
  cacuRecordedDate "SUBSTR(:cacuRecordedDate,7,4) || SUBSTR(:cacuRecordedDate,1,2) || SUBSTR(:cacuRecordedDate,4,2)", 
  cacuLastMaintDt "SUBSTR(:cacuLastMaintDt,7,4) || SUBSTR(:cacuLastMaintDt,1,2) || SUBSTR(:cacuLastMaintDt,4,2)",
  cacuLastMaintOpId "ltrim(rtrim(:cacuLastMaintOpId))"
)
begindata
I04372|01/10/1998|A8069642|OT|0004130{|A|GREAT LAKES HIGHER EDUCATION CORP    |DRAWER 480               |                         |MILWAUKEE           |WI|53293    |  |02/03/1998|02/03/1998|02/03/1998|DOC058

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Notice, I removed the hard-coded schema names and double quotes on object names.
0
 
bobcaputoAuthor Commented:
The } is an EBCDIC code which the function turns into a number and adds pos or neg.

I am going to have to take some time and look at this.

Thank you, and I will get back to this tomorrow.
0
 
bobcaputoAuthor Commented:
I am also wondering if the curly bracket is doing something to the loader.


Hhmmmmmmmmmmmmmm......will have to test some more.
0
 
slightwv (䄆 Netminder) Commented:
I didn't have your function so it errored for me.  If your code handles it then it should be fine.

This doesn't address the first column though.

I went back with the code I posted and all 10 of the rows posted loaded.
0
 
OP_ZaharinCommented:
hi bobcaputo,
- i've also try to simulate your error.
- i've created the table, the .dat file and the control file based on your given sample.

- the only change that i made is:
: taking out the ldcschema.code_conv() from the control file. what does code_conv() does? you might want to look at that function again. i assume it convert the '0004130{' to '0004130' or to number?
: since CACUREFUNDAMT is NUMBER(9,2) , i modify the .dat file and remove the { character - save the file and run the loader again. all the 10 data is loaded successfully.
- i didn't receive any other error and no other changes made to the table schema, dat and ctl file.
0
 
slightwv (䄆 Netminder) Commented:
OP_Zaharin,

The function and brace have been explained.
0
 
bobcaputoAuthor Commented:
BTW here is my function. It converts the char at end of field to value. It is a EBCDIC coded number.

I will look at all of these ideas and get back here later today.

create or replace function ldcschema.code_conv(last_digit IN VARCHAR2) RETURN NUMBER

IS

answer NUMBER;

BEGIN

SELECT (
        CASE 
           WHEN substr(ltrim(rtrim(last_digit)),-1,1) IN ('{','A','B','C','D','E','F','G','H','I') THEN
                substr(ltrim(rtrim(last_digit)),1,length(ltrim(rtrim(last_digit)))-1) ||
                decode(substr(ltrim(rtrim(last_digit)),-1,1),'{',0,'A',1,'B',2,'C',3,'D',4,'E',5,'F',6,'G',7,'H',8,'I',9)
           ELSE
                '-' ||  substr(ltrim(rtrim(last_digit)),1,length(ltrim(rtrim(last_digit)))-1) ||
                decode(substr(ltrim(rtrim(last_digit)),-1,1),'}',0,'J',1,'K',2,'L',3,'M',4,'N',5,'O',6,'P',7,'Q',8,'R',9)  
        END
        
       ) INTO answer
        FROM DUAL;
        
RETURN answer;

END;

Open in new window

0
 
bobcaputoAuthor Commented:
I can get everything to run except when I include my function. When I include my function I get the following.

SQL*Loader: Release 8.1.7.0.0 - Production on Tue Apr 19 07:52:48 2011

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

Control File:   \\APWMAD0A0773\DEV$\wicsv3\jobs\DOCETL801_TSUPREF.ctl
Data File:      data\docetl800_tsupv_refund.dat
  Bad File:     data\docetl801_tsupv.bad
  Discard File: data\docetl801_tsupv.dsc 
 (Allow all discards)

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

Table LDCDEVELOPER.CACU_TSUPV_REFUND, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CACU_CLIENT_DOC_NUMBER              FIRST     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:CACU_CLIENT_DOC_NUMBER))"
CACUCHECKDATE                        NEXT     *   |       CHARACTER            
    SQL string for column : "SUBSTR(:cacuCheckDate,7,4) || SUBSTR(:cacuCheckDate,1,2) || SUBSTR(:cacuCheckDate,4,2)"
CACUCHECKNUMBER                      NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuCheckNumber),'A')"
CACUREFUNDREASONCODE                 NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuRefundReasonCode))"
CACUREFUNDAMT                        NEXT     *   |       CHARACTER            
    SQL string for column : "ldcdeveloper.code_conv(ltrim(rtrim(:cacuRefundAmt)))"
CACUAPPROPRIATIONCODE                NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuAppropriationCode))"
CACUPAYEENAME                        NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuPayeeName))"
CACUPAYEESTREET1                     NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuPayeeStreet1))"
CACUPAYEESTREET2                     NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuPayeeStreet2))"
CACUPAYEECITY                        NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuPayeeCity))"
CACUPAYEESTATE                       NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuPayeeState))"
CACUPAYEEZIPCODE                     NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuPayeeZipCode))"
CACUTSUPVREFUNDSTATUSCODE            NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuTSupvRefundStatusCode))"
CACUSUPVREFUNDSTATUSDATE             NEXT     *   |       CHARACTER            
    SQL string for column : "SUBSTR(:cacuSupvRefundStatusDate,7,4) || SUBSTR(:cacuSupvRefundStatusDate,1,2) || SUBSTR(:cacuSupvRefundStatusDate,4,2)"
CACURECORDEDDATE                     NEXT     *   |       CHARACTER            
    SQL string for column : "SUBSTR(:cacuRecordedDate,7,4) || SUBSTR(:cacuRecordedDate,1,2) || SUBSTR(:cacuRecordedDate,4,2)"
CACULASTMAINTDT                      NEXT     *   |       CHARACTER            
    SQL string for column : "SUBSTR(:cacuLastMaintDt,7,4) || SUBSTR(:cacuLastMaintDt,1,2) || SUBSTR(:cacuLastMaintDt,4,2)"
CACULASTMAINTOPID                    NEXT     *   |       CHARACTER            
    SQL string for column : "ltrim(rtrim(:cacuLastMaintOpId))"

Record 1: Rejected - Error on table LDCDEVELOPER.CACU_TSUPV_REFUND, column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 2: Rejected - Error on table LDCDEVELOPER.CACU_TSUPV_REFUND, column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 3: Rejected - Error on table LDCDEVELOPER.CACU_TSUPV_REFUND, column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 4: Rejected - Error on table LDCDEVELOPER.CACU_TSUPV_REFUND, column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 5: Rejected - Error on table LDCDEVELOPER.CACU_TSUPV_REFUND, column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 6: Rejected - Error on table LDCDEVELOPER.CACU_TSUPV_REFUND, column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 7: Rejected - Error on table LDCDEVELOPER.CACU_TSUPV_REFUND, column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 8: Rejected - Error on table LDCDEVELOPER.CACU_TSUPV_REFUND, column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier

Record 9: Rejected - Error on table LDCDEVELOPER.CACU_TSUPV_REFUND, column CACUAPPROPRIATIONCODE.
ORA-00904: : invalid identifier


Table LDCDEVELOPER.CACU_TSUPV_REFUND:
  0 Rows successfully loaded.
  9 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.


Space allocated for bind array:                  61404 bytes(14 rows)
Space allocated for memory besides bind array:        0 bytes

Total logical records skipped:          0
Total logical records read:             9
Total logical records rejected:         9
Total logical records discarded:        0

Run began on Tue Apr 19 07:52:48 2011
Run ended on Tue Apr 19 07:52:48 2011

Elapsed time was:     00:00:00.17
CPU time was:         00:00:00.00    

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
What username are you running sqlldr with?  Does it have access to execute the convu9ode function?

The only difference I see is you are specifying the function owner in the control file.

If I get time later, I'll add your function to my test above.
0
 
bobcaputoAuthor Commented:
I believe the permissions are correct but will double check.

Thanks for all your help!!!!!!
0
 
bobcaputoAuthor Commented:
Yup!!

It was permissions. But the Oracle message was misleading as usual.

Thank you for all your time and help!!!

Bob
0
 
slightwv (䄆 Netminder) Commented:
No problem.  The ora-904 is typically permissions when you fully qualify object names.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.