How to avoid inserting spaces with sql loader

kirankota79 · Post by **kirankota79** » Tue Jul 22, 2008 11:13 am

Hi.i am loading an oracle table with sqlloader. I have a column varchar(11), but the real data consists of 6 digits in length. I have written this table to a dataset using PRESERVE_BLANKS as true. Now i want to load back to the table without blanks for that particular column. When i use trim function in the transformer before oracle stage for truncate and load, it is not removing the spaces. It is loading 6 digits plus 5 spaces. What i need to set, in order to avoid loading extra space? This is urgent for me!

Thanks

chulett · Post by **chulett** » Tue Jul 22, 2008 12:13 pm

Isn't this just a matter of setting PRESERVE_BLANKS to false during the load? Also assuming the target field in Oracle is a VARCHAR2(11) and not a CHAR(11), yes?

kirankota79 · Post by **kirankota79** » Tue Jul 22, 2008 12:23 pm

I am setting PRESERVE_BLANKS to false and yes it is also a varchar.
Actually PRESERVE_BLANKS is set to true at project level but it is set to false at job level which i use to truncate and load. Is that OK?

chulett wrote:Isn't this just a matter of setting PRESERVE_BLANKS to false during the load? Also assuming the target field in Oracle is a VARCHAR2(11) and not a CHAR(11), yes?

chulett · Post by **chulett** » Tue Jul 22, 2008 12:26 pm

Sounds fine to me but obviously we're missing something if it isn't working for you.

kirankota79 · Post by **kirankota79** » Tue Jul 22, 2008 12:42 pm

This is happening to all the varchar columns in the table. If the decalred length is more than the actual value then it is including the spaces at the end.
Is there anything i can do? i also tried trim before loading, but it is not use.

And one more thing is i am not able to see the control file, i am not able to figure out where it is stored . iam able to see only the log file and it is as follows:

SQL*Loader: Release 9.2.0.4.0 - Production on Tue Jul 22 13:06:09 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Control File: ora.4696.781000.0.ctl
Character Set WE8ISO8859P1 specified for all input.

Data File: \\.\pipe\ora.4696.781000.fifo.0
File processing option string: "FIX 389"
Bad File: ora.4696.781000.0.log.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 25000
Bind array: 1000000 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table SYSADM.PS_B_ADV_RECON_TBL, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COMPANY 1:3 3 CHARACTER
NULL if COMPANY = BLANKS
PAYGROUP 4:6 3 CHARACTER
NULL if PAYGROUP = BLANKS
PAY_END_DT 7:25 19 DATE YYYY/MM/DD HH24:MI:SS
NULL if PAY_END_DT = BLANKS
OFF_CYCLE 26:26 1 CHARACTER
NULL if OFF_CYCLE = BLANKS
PAGE_NUM 27:66 40 CHARACTER
NULL if PAGE_NUM = BLANKS
LINE_NUM 67:106 40 CHARACTER
NULL if LINE_NUM = BLANKS
SEPCHK 107:146 40 CHARACTER
NULL if SEPCHK = BLANKS
PRIORITY 147:186 40 CHARACTER
NULL if PRIORITY = BLANKS
FORM_ID 187:192 6 CHARACTER
NULL if FORM_ID = BLANKS
PAYCHECK_NBR 193:209 17 CHARACTER
NULL if PAYCHECK_NBR = BLANKS
CHECK_DT 210:228 19 DATE YYYY/MM/DD HH24:MI:SS
NULL if CHECK_DT = BLANKS
EMPLID 229:239 11 CHARACTER
NULL if EMPLID = BLANKS
EMPL_RCD 240:279 40 CHARACTER
NULL if EMPL_RCD = BLANKS
NAME 280:329 50 CHARACTER
NULL if NAME = BLANKS
ACCOUNT_TYPE 330:330 1 CHARACTER
NULL if ACCOUNT_TYPE = BLANKS
ACCOUNT_NUM 331:347 17 CHARACTER
NULL if ACCOUNT_NUM = BLANKS
BANK_CD 348:358 11 CHARACTER
NULL if BANK_CD = BLANKS
BRANCH_EC_CD 359:375 17 CHARACTER
NULL if BRANCH_EC_CD = BLANKS
DEPOSIT_AMOUNT 376:387 12 CHARACTER
NULL if DEPOSIT_AMOUNT = BLANKS
B_ADV_STATUS 388:388 1 CHARACTER
NULL if B_ADV_STATUS = BLANKS

Table SYSADM.PS_B_ADV_RECON_TBL:
18 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Space allocated for bind array: 255640 bytes(581 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 18
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Tue Jul 22 13:06:09 2008
Run ended on Tue Jul 22 13:06:10 2008

Elapsed time was: 00:00:00.66
CPU time was: 00:00:00.04

ray.wurlod · Post by **ray.wurlod** » Tue Jul 22, 2008 3:25 pm

You're using a fixed-width dat file - these positions have to be filled with something. What value do you have for APT_STRING_PADCHAR ?

kirankota79 · Post by **kirankota79** » Tue Jul 22, 2008 3:32 pm

i am not using variable APT_STRING_PADCHAR at job level but in the administrator it is 0x0. Do i need to change to any value?