SQL Loader Error

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

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;
/

Select allOpen in new window

What does the CODE_CONV function look like? It seems that it may not be handling blanks.

Note that the CTL file uses NVL so a null is never sent to CODE_CONV.

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

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

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

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

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))"
) 

Select allOpen in new window

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.

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

Thank you!!!