Solved

SQL Loader Error

Posted on 2011-09-07
13
351 Views
Last Modified: 2012-08-14
Why am I getting this error?  I do not seem to be able to get around it so it loads null fields.

Record 1: Rejected - Error on table "LDCSCHEMA"."CACU_PWPYCLHS", column PY_REST_HST_DIR_PMT_AMT.
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "LDCSCHEMA.CODE_CONV", line 33

Open in new window

Sample row that fails.
05625619860607001168298B02|    |  |  |               |    |  |  |        |    |    | | |        |    |  |  |                                                            |

Open in new window


Table Definition
Column Name	ID	Pk	Null?	Data Type	Default	Histogram	Encryption Alg	Salt

PY_PAYEE_ACCOUNT_ID	1		Y	VARCHAR2 (6 Byte)		Yes		
PY_REST_CRIME_DATE	2		Y	VARCHAR2 (8 Byte)		Yes		
PY_REST_CRIME_DT_TIEBRK_NO	3		Y	VARCHAR2 (3 Byte)		Yes		
PY_RTPMT_CLIENT_DOC_NUMBER	4		Y	VARCHAR2 (6 Byte)		Yes		
PY_CASE_IDENTIFIER_CODE	5		Y	CHAR (1 Byte)		Yes		
PY_CASE_SEQUENCE_NO	6		Y	VARCHAR2 (2 Byte)		Yes		
PY_REST_HST_DOCU_DATE	7		Y	VARCHAR2 (8 Byte)		Yes		
PY_REST_HST_DOC_TYPE	8		Y	VARCHAR2 (15 Byte)		Yes		
PY_REST_HST_CHANGE_DATE	9		Y	VARCHAR2 (8 Byte)		Yes		
PY_REST_HIST_REST_AMT	10		Y	NUMBER (9,2)		Yes		
PY_REST_HIST_INTRST_PCT	11		Y	NUMBER (4,2)		Yes		
PY_REST_HIST_SURCHG_PCT	12		Y	VARCHAR2 (4 Byte)		Yes		
PY_REST_HST_JT_SEV_IND	13		Y	CHAR (1 Byte)		Yes		
PY_REST_HST_JT_JUV_IND	14		Y	CHAR (1 Byte)		Yes		
PY_REST_HST_DIR_PMT_AMT	15		Y	NUMBER (9,2)		Yes		
PY_REST_HST_DIR_PMT_DATE	16		Y	VARCHAR2 (8 Byte)		Yes		
PY_REST_HIST_COMENT_TXT	17		Y	VARCHAR2 (60 Byte)		Yes		
CACU_PWPYCLHS_SK	18	1	N	NUMBER (10)		Yes		

Open in new window


Here is control statement.

INTO TABLE "LDCSCHEMA"."CACU_PWPYCLHS"
TRUNCATE
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
--00001219781003001141849A01|1986|02|28|AMENDED ORDER  |1986|03|07|00763632|0000|0000|N|N|00000000|0000|00|00|ALL RESTITUTION ON "A"CASE
(
 field1                   	BOUNDFILLER,
 field2                   	BOUNDFILLER,
 field3                   	BOUNDFILLER,
 field4                   	BOUNDFILLER,
 field5                   	BOUNDFILLER,
 field6                   	BOUNDFILLER,
 field7                   	BOUNDFILLER,
 field8                   	BOUNDFILLER,
 field9                   	BOUNDFILLER,
 field10                   	BOUNDFILLER,
 field11                   	BOUNDFILLER,
 field12                   	BOUNDFILLER,
 field13                   	BOUNDFILLER,
 field14                   	BOUNDFILLER,
 field15                   	BOUNDFILLER,
 field16                   	BOUNDFILLER,
 field17                   	BOUNDFILLER,
 PY_REST_HIST_COMENT_TXT	"ltrim(rtrim(:PY_REST_HIST_COMENT_TXT))",
 PY_PAYEE_ACCOUNT_ID		"ltrim(rtrim(substr(:field1,1,6)))",
 PY_REST_CRIME_DATE		"substr(:field1,7,4)||substr(:field1,11,2)||substr(:field1,13,2)",
 PY_REST_CRIME_DT_TIEBRK_NO	"ltrim(rtrim(substr(:field1,15,3)))",
 PY_RTPMT_CLIENT_DOC_NUMBER	"ltrim(rtrim(substr(:field1,18,6)))",
 PY_CASE_IDENTIFIER_CODE	"ltrim(rtrim(substr(:field1,24,1)))",
 PY_CASE_SEQUENCE_NO		"ltrim(rtrim(substr(:field1,25,2)))",
 PY_REST_HST_DOCU_DATE		"ltrim(rtrim(:field2))||ltrim(rtrim(:field3))||ltrim(rtrim(:field4))",
 PY_REST_HST_DOC_TYPE		"ltrim(rtrim(:field5))",
 PY_REST_HST_CHANGE_DATE	"ltrim(rtrim(:field6))||ltrim(rtrim(:field7))||ltrim(rtrim(:field8))",
 PY_REST_HIST_REST_AMT		"ldcschema.CODE_CONV(:field9)",
 PY_REST_HIST_INTRST_PCT	"ldcschema.CODE_CONV(:field10)",
 PY_REST_HIST_SURCHG_PCT	"ltrim(rtrim(:field11))",
 PY_REST_HST_JT_SEV_IND		"ltrim(rtrim(:field12))",
 PY_REST_HST_JT_JUV_IND		"ltrim(rtrim(:field13))",
 PY_REST_HST_DIR_PMT_AMT	"ldcschema.CODE_CONV(nvl(ltrim(rtrim(:field14)),'00000000'))",
 PY_REST_HST_DIR_PMT_DATE	"ltrim(rtrim(:field15))||ltrim(rtrim(:field16))||ltrim(rtrim(:field17))"
) 

Open in new window

0
Comment
Question by:bobcaputo
  • 6
  • 6
13 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36496227
>>ORA-06512: at "LDCSCHEMA.CODE_CONV", line 33

You need to look at line 33 of your CODE_CONV procedure.  My guess is it was not set up to receive spaces or null values.
0
 

Author Comment

by:bobcaputo
ID: 36496239
But I am using NVL in loader. Here is code, sorry forgot to add it.

CREATE OR REPLACE function LDCSCHEMA.code_conv(last_digit IN VARCHAR2) RETURN number
IS
answer varchar2(15);
answer1 varchar2(15);

BEGIN
    IF substr(ltrim(rtrim(last_digit)),-1,1) NOT IN ('0','1','2','3','4','5','6','7','8','9') THEN
        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;         
        SELECT  
               substr(ltrim(rtrim(answer)),1,length(ltrim(rtrim(answer)))-2) ||  '.' || substr(answer,-2,2)   
                INTO answer
                FROM DUAL;
    ELSE
        SELECT  
               substr(ltrim(rtrim(last_digit)),1,length(ltrim(rtrim(last_digit)))-2) ||  '.' || substr(last_digit,-2,2)   
                INTO answer
                FROM DUAL;  
    END IF;
                RETURN answer;
END;
/

Open in new window

0
 
LVL 31

Expert Comment

by:awking00
ID: 36496258
What does the CODE_CONV function look like? It seems that it may not be handling blanks.
0
 

Author Comment

by:bobcaputo
ID: 36496271
Note that the CTL file uses NVL so a null is never sent to CODE_CONV.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36496279
>>But I am using NVL in loader

But it still has to call CODE_CONV before it applies NVL.  CODE_CONV is throwing the error.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36496292
>>Note that the CTL file uses NVL so a null is never sent to CODE_CONV.

No on:  PY_REST_HIST_INTRST_PCT      "ldcschema.CODE_CONV(:field10)",

Also, '        ' in the sample row is not NULL.  You need to TRIM it as well.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:bobcaputo
ID: 36496316
Let me shore up these loose ends and retry. However I still believe that the NVL is done before CODE_CONV is called, so therefore no nulls are sent for PY_REST_HST_DIR_PMT_AMT
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36496387
>>However I still believe that the NVL is done before

Now that I look at the code again, NVL is inside the CODE_CONV call so it will be called first.

The error is likely from one of the calls without an NVL call.


Also an FYI:  you can replace LTRIM and RTRIM with just a TRIM.  If you do not specify the trim character on LTRIM and RTRIM it assumes spaces.  TRIM does spaces in a single call.
0
 

Author Comment

by:bobcaputo
ID: 36496427
I have updated CTL but now get this error

Record 1: Rejected - Error on table "LDCSCHEMA"."CACU_PWPYCLHS", column PY_REST_HST_DIR_PMT_AMT.
ORA-00909: invalid number of arguments

Here is updated CTL code.

LOAD DATA
INFILE 'data\docetl600_PWPYCLHS.dat'
BADFILE 'log\docetl601_PWPYCLHS.bad'
DISCARDFILE 'log\docetl601_PWPYCLHS.dsc'
INTO TABLE "LDCSCHEMA"."CACU_PWPYCLHS"
TRUNCATE
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
--00001219781003001141849A01|1986|02|28|AMENDED ORDER  |1986|03|07|00763632|0000|0000|N|N|00000000|0000|00|00|ALL RESTITUTION ON "A"CASE
(
 field1                   	BOUNDFILLER,
 field2                   	BOUNDFILLER,
 field3                   	BOUNDFILLER,
 field4                   	BOUNDFILLER,
 field5                   	BOUNDFILLER,
 field6                   	BOUNDFILLER,
 field7                   	BOUNDFILLER,
 field8                   	BOUNDFILLER,
 field9                   	BOUNDFILLER,
 field10                   	BOUNDFILLER,
 field11                   	BOUNDFILLER,
 field12                   	BOUNDFILLER,
 field13                   	BOUNDFILLER,
 field14                   	BOUNDFILLER,
 field15                   	BOUNDFILLER,
 field16                   	BOUNDFILLER,
 field17                   	BOUNDFILLER,
 PY_REST_HIST_COMENT_TXT	"ltrim(rtrim(:PY_REST_HIST_COMENT_TXT))",
 PY_PAYEE_ACCOUNT_ID		"ltrim(rtrim(substr(:field1,1,6)))",
 PY_REST_CRIME_DATE		"substr(:field1,7,4)||substr(:field1,11,2)||substr(:field1,13,2)",
 PY_REST_CRIME_DT_TIEBRK_NO	"ltrim(rtrim(substr(:field1,15,3)))",
 PY_RTPMT_CLIENT_DOC_NUMBER	"ltrim(rtrim(substr(:field1,18,6)))",
 PY_CASE_IDENTIFIER_CODE	"ltrim(rtrim(substr(:field1,24,1)))",
 PY_CASE_SEQUENCE_NO		"ltrim(rtrim(substr(:field1,25,2)))",
 PY_REST_HST_DOCU_DATE		"ltrim(rtrim(:field2))||ltrim(rtrim(:field3))||ltrim(rtrim(:field4))",
 PY_REST_HST_DOC_TYPE		"ltrim(rtrim(:field5))",
 PY_REST_HST_CHANGE_DATE	"ltrim(rtrim(:field6))||ltrim(rtrim(:field7))||ltrim(rtrim(:field8))",
 PY_REST_HIST_REST_AMT		"ldcschema.CODE_CONV(nvl(trim(:field9)),'000')",
 PY_REST_HIST_INTRST_PCT	"ldcschema.CODE_CONV(nvl(trim(:field10)),'000')",
 PY_REST_HIST_SURCHG_PCT	"ltrim(rtrim(:field11))",
 PY_REST_HST_JT_SEV_IND		"ltrim(rtrim(:field12))",
 PY_REST_HST_JT_JUV_IND		"ltrim(rtrim(:field13))",
 PY_REST_HST_DIR_PMT_AMT	"ldcschema.CODE_CONV(nvl(trim(:field14),'00000000'))",
 PY_REST_HST_DIR_PMT_DATE	"ltrim(rtrim(:field15))||ltrim(rtrim(:field16))||ltrim(rtrim(:field17))"
) 

Open in new window

0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 36496464
Check your parans.  NVL takes two parameters.

change it to:
PY_REST_HIST_REST_AMT  "ldcschema.CODE_CONV(nvl(trim(:field9),'000'))",
0
 

Author Comment

by:bobcaputo
ID: 36496864
Well it seems I have forgotten the old adage "just because Oracle points to an error, doesn't mean thats where the error really is"

Can anyone suggest how to rearrange the CTL file to do direct load? It doesn't support BOUNDFILLER.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36496986
>>It doesn't support BOUNDFILLER.

I take it you found something in the docs on this?

I don't think the rearrange will help.  You need BOUNDFILLER because of the way to manipulate the fields.

If the file is located on the database server I'm thinking External Tables might be better for this?

If you need to use DIRECT path loading and there is a BOUNDFILLER restriction you might need to use a staging table and a trigger to pull this off.
0
 

Author Closing Comment

by:bobcaputo
ID: 36497041
Thank you!!!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now