[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQLLDR with Function does not load.

Posted on 2011-04-18
19
Medium Priority
?
1,008 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
0
Comment
Question by:bobcaputo
  • 9
  • 7
  • 2
  • +1
19 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 35419112
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
 

Author Comment

by:bobcaputo
ID: 35419152
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
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 1000 total points
ID: 35419317
CACUAPPROPRIATIONCODE is this really one char in the file you are trying to load?
is there any trigger involved in this table?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:bobcaputo
ID: 35419331
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 35419400
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
 

Author Comment

by:bobcaputo
ID: 35419487
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35419534
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35419541
Notice, I removed the hard-coded schema names and double quotes on object names.
0
 

Author Comment

by:bobcaputo
ID: 35419580
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
 

Author Comment

by:bobcaputo
ID: 35419598
I am also wondering if the curly bracket is doing something to the loader.


Hhmmmmmmmmmmmmmm......will have to test some more.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35419644
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35421301
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35423572
OP_Zaharin,

The function and brace have been explained.
0
 

Author Comment

by:bobcaputo
ID: 35423950
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
 

Author Comment

by:bobcaputo
ID: 35424122
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35424244
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
 

Author Comment

by:bobcaputo
ID: 35424261
I believe the permissions are correct but will double check.

Thanks for all your help!!!!!!
0
 

Author Comment

by:bobcaputo
ID: 35424454
Yup!!

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

Thank you for all your time and help!!!

Bob
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35425990
No problem.  The ora-904 is typically permissions when you fully qualify object names.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month17 days, 23 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question