Home » RDBMS Server » Server Utilities » Trimming spaces in SQL Loader
Trimming spaces in SQL Loader [message #130876] |
Wed, 03 August 2005 00:56 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
misha603
Messages: 20 Registered: July 2005 Location: India
|
Junior Member |
![misha603%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
Hi,
Can you please tell me how to trim the trailing blank spcaes in the dat file. Eg. My dept.dat file is generated by a program and I don't have any control in that program. The output for select * from dept will be.
10,ACCOUNTING ,NEW YORK
20,RESEARCH ,DALLAS
30,SALES ,CHICAGO
40,OPERATIONS ,BOSTON
50,FINANCE ,CHENNAI
55,new ,new
25,all ,all
80,TEST ,BANGALORE
My problem is if I use the above output as the dat file then the dname gets loaded with training blan spaces. Coul you please tell me how to remove the trailing blank spaces in dname before loading into dept table using SQL loader.
Thanks in Advance
Raja
|
|
|
Re: Trimming spaces in SQL Loader [message #130878 is a reply to message #130876] |
Wed, 03 August 2005 01:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
tarundua
Messages: 1080 Registered: June 2005 Location: India
|
Senior Member |
|
|
Hi raja,
Can't u enclose the fields with double quotes, like this
Quote: | LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC)
BEGINDATA
12,RESEARCH,"SARATOGA"
10,"ACCOUNTING",CLEVELAND
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"
|
This is an example from Oracle itself. U can find several examples in ur system at location.
--> ORACLE_HOME/rdbms/demo
File names -- Ulcase1 , Ulcase2 ..... Ulcasen
Go through these examples, it might help u.
regards,
tarun
|
|
|
|
|
|
Re: Trimming spaces in SQL Loader [message #132509 is a reply to message #132464] |
Sat, 13 August 2005 12:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I have not encountered any such limitation on the number of times that trim can be used in Oracle 9i, so I suspect that you have encountered something else. What version of Oracle are you using? Are you referring to trimming more than 127 columns or nesting trim clauses? If nesting trim clauses, then you do not need to :
RTRIM (RTRIM (:col1, 'a'), 'b')
you can just:
RTRIM (:col1, 'ab')
If you copy and run the following script, it will create a test.dat file, a test.ctl file with 129 trims, a test_tab table, and load one record into the test_tab table, using the control file with 129 trims. I tested it on 9i with no problems.
store set saved_settings replace
set echo off feedback off heading off pagesize 0 verify off linesize 300
spool test.dat
select stragg (rn)|| ','
from (select 1 rn
from dual
connect by level < 130)
/
spool off
spool test.ctl
prompt load data
prompt infile 'test.dat'
prompt into table test_tab
prompt fields terminated by ','
prompt (col1 "trim(:col1)"
select ',col' || rn || ' "trim(:col' || rn || ')"'
from (select rownum + 1 rn
from dual
connect by level < 129)
/
prompt )
spool off
start saved_settings
declare
v_sql varchar2(32767);
begin
v_sql := 'create table test_tab (col1 varchar2(1)';
for i in 2 .. 129 loop
v_sql := v_sql || ',col' || i || ' varchar2(1)';
end loop;
v_sql := v_sql || ')';
execute immediate v_sql;
end;
/
desc test_tab
host sqlldr scott/tiger control=test.ctl log=test.log
select * from test_tab
/
drop table test_tab
/
|
|
|
Re: Trimming spaces in SQL Loader [message #133148 is a reply to message #132509] |
Wed, 17 August 2005 18:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
vshukla17
Messages: 3 Registered: August 2005 Location: California
|
Junior Member |
|
|
Hi Barbara,
I used trim for columns.
Version: SQL*Loader: Release 9.2.0.1.0
My control file looked like:
-------------------
load data
into table bby_cmgt_exc
replace
FIELDS TERMINATED BY ","
optionally enclosed by '"'
trailing nullcols
(
FIPSSTATE "TRIM(:FIPSSTATE )",
OEDISTNO "TRIM(:OEDISTNO )",
OEBLDGNO "TRIM(:OEBLDGNO )",
PIN "TRIM(:PIN )",
PARDISTFIP "TRIM(:PARDISTFIP)",
PARDISTNUM "TRIM(:PARDISTNUM)",
IPED_RMCNO "TRIM(:IPED_RMCNO)",
ACT_FICE "TRIM(:ACT_FICE )",
INSTITUT "TRIM(:INSTITUT )",
MAILADDRES "TRIM(:MAILADDRES)",
MAILCITY "TRIM(:MAILCITY )",
MAILSTATE "TRIM(:MAILSTATE )",
.
.
.
FILLER_2 "TRIM(:MAILSTATE )"
)
Around 250 columns. Data loader was able to load data from a .csv file for 127 columns. A column extra would fail it.
Exception: ORA-26095: unprocessed stream data exists
Search on google suggested trim might be the culprit...refer -
http://www.orafaq.com/forum/t/26343/0/
I reduced the trim usuage to less than 127 and it works fine now.
Thanks
Vivek Shukla
|
|
|
Re: Trimming spaces in SQL Loader [message #133151 is a reply to message #133148] |
Wed, 17 August 2005 19:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I am using the same version of Oracle, 9.2.0.1.0. I wonder if it also depends on the operating system or some other additional variable. I am running it on Windows XP Home Edition Service Pack 2. What are you running it on?
|
|
|
Goto Forum:
Current Time: Fri Sep 06 17:57:59 CDT 2024
|