Solved

SQL Loader Error

Posted on 2011-09-07
13
361 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 77

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 32

Expert Comment

by:awking00
ID: 36496258
What does the CODE_CONV function look like? It seems that it may not be handling blanks.
0
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!

 

Author Comment

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

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 77

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
 

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 77

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 77

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 77

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

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

734 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