| Using TRIM in sql loader control file [message #352894] |
Fri, 10 October 2008 02:03  |
risk_sly
Messages: 2
Registered: October 2008
|
Junior Member |
|
|
Hi,
I need to use TRIM function in my sql loader control file to make sure all the needed records will be loaded in the table but I always encounter error. When TRIM is removed from the code below, sql loader works fine. Can anyone show the correct syntax of using TRIM in cases like this? thank you.
Load Data
APPEND
INTO TABLE GLOBAL_ONE_FEE_REBATE WHEN TRIM(ACT_TYPE) = 'SR'
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
RPT_YEAR,
RPT_MONTH,
....
....
....
|
|
|
|
|
|
| Re: Using TRIM in sql loader control file [message #352904 is a reply to message #352894] |
Fri, 10 October 2008 03:10  |
 |
Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Can't wait for Barbara to see this question; meanwhile, it appears that WHEN TRIM(ACT_TYPE) = 'SR'
As I don't know better, I have two options on mind:
- load the whole file into a temporary table. Once it is done, use SQL query to insert only "SR" records into the target table; something like
INSERT INTO global_one_fee_rebate
(rpt_year, rpt_month, ...)
(SELECT rpt_year, rpt_month, ...
FROM temporary_table
WHERE trim(act_type) = 'SR'
)
- use the external tables feature (if available in your Oracle database version (which you didn't mention)); it would save you the SQL*Loader step, and enable you to insert only records whose "trim(act_type)" equals "SR" using the above INSERT statement.
|
|
|
|
|
|
| Re: Using TRIM in sql loader control file [message #353066 is a reply to message #352906] |
Fri, 10 October 2008 17:49 |
 |
Barbara Boehmer
Messages: 9092
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If you use LRTRIM and a WHEN clause in your external table creation, you can load only the desired rows in one pass. However, if this is for an existing table with data already in it, then you will stil need to insert, but at least you will already have just the correct rows with the spaces trimmed. Please see the demo below.
-- test.dat:
2008|10|SR|
2008|09|AB|
2008|08| SR |
SCOTT@orcl_11g> CREATE TABLE global_one_fee_rebate
2 (rpt_year NUMBER,
3 rpt_month NUMBER,
4 act_type VARCHAR2(8))
5 ORGANIZATION EXTERNAL
6 (TYPE ORACLE_LOADER
7 DEFAULT DIRECTORY extfiles
8 ACCESS PARAMETERS
9 (RECORDS DELIMITED BY NEWLINE
10 LOAD WHEN (act_type = "SR")
11 FIELDS TERMINATED BY "|" LRTRIM
12 MISSING FIELD VALUES ARE NULL
13 REJECT ROWS WITH ALL NULL FIELDS
14 (rpt_year,
15 rpt_month,
16 act_type))
17 LOCATION ('test.dat'))
18 /
Table created.
SCOTT@orcl_11g> SELECT * FROM global_one_fee_rebate
2 /
RPT_YEAR RPT_MONTH ACT_TYPE
---------- ---------- --------
2008 10 SR
2008 8 SR
SCOTT@orcl_11g> SELECT LENGTH (act_type) FROM global_one_fee_rebate
2 /
LENGTH(ACT_TYPE)
----------------
2
2
SCOTT@orcl_11g>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
