SQLLDR syntax question
in [Oracle Help]
|
Prev: paypal wholesale all brand(UGGBOOTS,SHOES,CLOTHES,HANDBAG,WATCH,JEANS,JERSEY,T-SHIRT,SHIRTS,HOODY,EYEGLASS,CAP,SHAWL,WALLT) and so on.
Next: Materialized Views Vs. Streams Replication
From: Martin Frodderrer on 7 Mar 2010 09:17 Hello. This is my first post to this group so if the question is too simple, be gentle with me. I have created a table called test like this "create table test(id1 number, id2 number)" in Oracle 10g.2 Standard Edition on a Windows XP home computer. I am trying to import data into my "test" table. My SQLLDR control file looks like this. It is representative of my test data but not my test data. LOAD DATA INFILE * INTO TABLE test REPLACE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ( id1 integer external, id2 integer external ) BEGINDATA 1+9, 400 "2*10", 401 3+8, 402 4, 403 5, 404 When I run SQLLDR, all I get imported into my "test" table is the rows with 4,403 and 5,404. I want the additional rows containing 10, 400 20, 401 11, 402 included in the import too. I can't figure out how to do it. What I have tried is, in my SQLLDR control file, changing id1 integer external, to id1 integer expression "to_number(:id)", this doesn't work. I have also tried id1 integer expression "select :id1 from dual", but this doesn't work either. I am new to this. Can someone help? Thank you Martin
From: Gerard H. Pille on 7 Mar 2010 13:54 Martin Frodderrer wrote: > Hello. This is my first post to this group so if the question is too > simple, be gentle with me. > > I have created a table called test like this "create table test(id1 > number, id2 number)" in Oracle 10g.2 Standard Edition on a Windows XP > home computer. > > I am trying to import data into my "test" table. > > My SQLLDR control file looks like this. It is representative of my > test data but not my test data. > > LOAD DATA > INFILE * > INTO TABLE test > REPLACE > FIELDS TERMINATED BY ',' > OPTIONALLY ENCLOSED BY '"' > ( > id1 integer external, > id2 integer external > ) > BEGINDATA > 1+9, 400 > "2*10", 401 > 3+8, 402 > 4, 403 > 5, 404 > > > When I run SQLLDR, all I get imported into my "test" table is the rows > with 4,403 and 5,404. I want the additional rows containing > 10, 400 > 20, 401 > 11, 402 > included in the import too. I can't figure out how to do it. > > What I have tried is, in my SQLLDR control file, changing > > id1 integer external, > > to > > id1 integer expression "to_number(:id)", > > this doesn't work. > > I have also tried > > id1 integer expression "select :id1 from dual", > > but this doesn't work either. > > I am new to this. Can someone help? > > Thank you > > Martin > I think you may have more luck with another tool, but sql*loader is not really suited for this task.
From: ddf on 7 Mar 2010 18:02 On Mar 7, 9:17 am, Martin Frodderrer <martinfridder...(a)googlemail.com> wrote: > Hello. This is my first post to this group so if the question is too > simple, be gentle with me. > > I have created a table called test like this "create table test(id1 > number, id2 number)" in Oracle 10g.2 Standard Edition on a Windows XP > home computer. > > I am trying to import data into my "test" table. > > My SQLLDR control file looks like this. It is representative of my > test data but not my test data. > > LOAD DATA > INFILE * > INTO TABLE test > REPLACE > FIELDS TERMINATED BY ',' > OPTIONALLY ENCLOSED BY '"' > ( > id1 integer external, > id2 integer external > ) > BEGINDATA > 1+9, 400 > "2*10", 401 > 3+8, 402 > 4, 403 > 5, 404 > > When I run SQLLDR, all I get imported into my "test" table is the rows > with 4,403 and 5,404. I want the additional rows containing > 10, 400 > 20, 401 > 11, 402 > included in the import too. I can't figure out how to do it. > > What I have tried is, in my SQLLDR control file, changing > > id1 integer external, > > to > > id1 integer expression "to_number(:id)", > > this doesn't work. > > I have also tried > > id1 integer expression "select :id1 from dual", > > but this doesn't work either. > > I am new to this. Can someone help? > > Thank you > > Martin You should do the obvious and rewrite your control file in this manner: LOAD DATA INFILE * INTO TABLE test REPLACE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ( id1 integer external, id2 integer external ) BEGINDATA 10, 400 20, 401 11, 402 4, 403 5, 404 Expressions such as 1+9, "2*10" and 3+8 are not integers to Oracle, they are strings. Provide integers, as shown above, and you will have all five rows loaded. David Fitzjarrell
From: Carlos on 8 Mar 2010 03:25 On Mar 8, 12:02 am, ddf <orat...(a)msn.com> wrote: > On Mar 7, 9:17 am, Martin Frodderrer <martinfridder...(a)googlemail.com> > wrote: > > > > > Hello. This is my first post to this group so if the question is too > > simple, be gentle with me. > > > I have created a table called test like this "create table test(id1 > > number, id2 number)" in Oracle 10g.2 Standard Edition on a Windows XP > > home computer. > > > I am trying to import data into my "test" table. > > > My SQLLDR control file looks like this. It is representative of my > > test data but not my test data. > > > LOAD DATA > > INFILE * > > INTO TABLE test > > REPLACE > > FIELDS TERMINATED BY ',' > > OPTIONALLY ENCLOSED BY '"' > > ( > > id1 integer external, > > id2 integer external > > ) > > BEGINDATA > > 1+9, 400 > > "2*10", 401 > > 3+8, 402 > > 4, 403 > > 5, 404 > > > When I run SQLLDR, all I get imported into my "test" table is the rows > > with 4,403 and 5,404. I want the additional rows containing > > 10, 400 > > 20, 401 > > 11, 402 > > included in the import too. I can't figure out how to do it. > > > What I have tried is, in my SQLLDR control file, changing > > > id1 integer external, > > > to > > > id1 integer expression "to_number(:id)", > > > this doesn't work. > > > I have also tried > > > id1 integer expression "select :id1 from dual", > > > but this doesn't work either. > > > I am new to this. Can someone help? > > > Thank you > > > Martin > > You should do the obvious and rewrite your control file in this > manner: > > LOAD DATA > INFILE * > INTO TABLE test > REPLACE > FIELDS TERMINATED BY ',' > OPTIONALLY ENCLOSED BY '"' > ( > id1 integer external, > id2 integer external > ) > BEGINDATA > 10, 400 > 20, 401 > 11, 402 > 4, 403 > 5, 404 > > Expressions such as 1+9, "2*10" and 3+8 are not integers to Oracle, > they are strings. Provide integers, as shown above, and you will have > all five rows loaded. > > David Fitzjarrell >>"It is representative of my test data but not my test data. " A shoot in the dark but... Maybe BOUNDFILLER + EXPRESSION is what you are looking for: http://carlosal.wordpress.com/2007/09/24/sqlloader-y-when-la-palabra-clave-es-boundfiller/ HTH Cheers. Carlos.
From: Kay Kanekowski on 8 Mar 2010 09:56
Hi Martin, you need a function that calculate your "string" data. That's my try: create or replace function fn_kk_calc ( string_in IN varchar2 ) return number is erg number; begin execute immediate 'select ' || string_in || ' from dual' into erg; return erg; end; / LOAD DATA INFILE math.txt INTO TABLE kk_math REPLACE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ( id1 integer external "fn_kk_calc(:id1)", id2 integer external ) hth Kay |