We help IT Professionals succeed at work.

SQLLDR with Function does not load.

bobcaputo
bobcaputo asked
on
Medium Priority
1,358 Views
Last Modified: 2012-06-27
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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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.
HainKurtSr. System Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
if you use

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

-->

cacuAppropriationCode "A",

or

cacuAppropriationCode "'A'",

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

Author

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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Notice, I removed the hard-coded schema names and double quotes on object names.

Author

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.

Author

Commented:
I am also wondering if the curly bracket is doing something to the loader.


Hhmmmmmmmmmmmmmm......will have to test some more.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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.
Top Expert 2011

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
OP_Zaharin,

The function and brace have been explained.

Author

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

Author

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

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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.

Author

Commented:
I believe the permissions are correct but will double check.

Thanks for all your help!!!!!!

Author

Commented:
Yup!!

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

Thank you for all your time and help!!!

Bob
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
No problem.  The ora-904 is typically permissions when you fully qualify object names.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.