Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

SQL Loader Error

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
bobcaputo
Asked:
bobcaputo
  • 6
  • 6
1 Solution
 
slightwv (䄆 Netminder) Commented:
>>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
 
bobcaputoAuthor Commented:
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
 
awking00Commented:
What does the CODE_CONV function look like? It seems that it may not be handling blanks.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
bobcaputoAuthor Commented:
Note that the CTL file uses NVL so a null is never sent to CODE_CONV.
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
slightwv (䄆 Netminder) Commented:
>>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
 
bobcaputoAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
bobcaputoAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
Check your parans.  NVL takes two parameters.

change it to:
PY_REST_HIST_REST_AMT  "ldcschema.CODE_CONV(nvl(trim(:field9),'000'))",
0
 
bobcaputoAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
bobcaputoAuthor Commented:
Thank you!!!
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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now