Home » RDBMS Server » Server Utilities » Using TRIM in sql loader control file
Using TRIM in sql loader control file [message #352894] Fri, 10 October 2008 02:03 Go to next message
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 #352899 is a reply to message #352894] Fri, 10 October 2008 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about using the Search command above?
http://www.orafaq.com/forum/?SQ=4db3368a1b7d219cf0683f345437c81c&t=search&srch=trim&btn_submit=Search&field=all&f orum_limiter=10&search_logic=AND&sort_order=DESC&author=

Regards
Michel
Re: Using TRIM in sql loader control file [message #352904 is a reply to message #352894] Fri, 10 October 2008 03:10 Go to previous messageGo to next message
Littlefoot
Messages: 20900
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'
results in an syntax error.

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 #352906 is a reply to message #352904] Fri, 10 October 2008 03:19 Go to previous messageGo to next message
risk_sly
Messages: 2
Registered: October 2008
Junior Member
Thanks so much for the replies. I'll try this one. Other options/solutions will be highly appreciated. Thank you!
Re: Using TRIM in sql loader control file [message #353066 is a reply to message #352906] Fri, 10 October 2008 17:49 Go to previous message
Barbara Boehmer
Messages: 8635
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>

Previous Topic: update using sqlldr
Next Topic: Error inserting images into table
Goto Forum:
  


Current Time: Wed Dec 07 20:36:44 CST 2016

Total time taken to generate the page: 0.17197 seconds