Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Loader Error

Posted on 2011-09-07
13
Medium Priority
?
368 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

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 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

664 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