Re: SQL Loader problem
Tim, Sorry for late reply. I was out for a few days. You are right. CHAR(4000) fixed the problem. I really appreciate your help and all the help from other people who answered my question. Thanks. Anna From: Tim Gorman [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: SQL Loader problem Date: Tue, 29 Jul 2003 15:09:29 -0800 Sliced bread or not, the syntax problem will be the same since external tables are modeled on SQL*Loader syntax... :-) Anna, I suspect that there is confusion on the SQL*Loader concepts of external datatypes and internal datatypes, which incidentally is shared by external tables currently. SQL*Loader's internal datatypes match the standard Oracle RDBMS datatypes, but the external datatypes are something else altgoether... I don't have docs available, but specifying an external datatype of VARCHAR or VARCHAR2 probably doesn't mean what you want it to mean. If memory serves, I think it is supposed to be a two-byte binary integer (which is the data length) to be followed by the data itself. Or something like that... Try CHAR(4000), perhaps? The Server Utilities manual from the standard Oracle doc-set on http://otn.oracle.com; has all this stuff in it... Hope this helps... -Tim on 7/29/03 3:19 PM, Mladen Gogala at [EMAIL PROTECTED] wrote: Try with external tables. The best thing since sliced bread. On 2003.07.29 17:39, Anna Li wrote: Hi All, I'm trying to use SQL Loader to load data from a text file into a table. However, I always get error as column SKILL_DESCRIPTION field in data file exceeds maximum length where the column SKILL_DESCRIPTION is declared as varchar2(4000). I know the data is 4000 characters, but 1000 characters. It was inserted successfully when I execute insert statement in TOAD as: insert into skills_tmp values(..); Is there any option in SQL Loader I have to set in order to load a long string? Thanks for all the inputs in advance. Anna _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anna Li INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anna Li INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Loader problem
Try with external tables. The best thing since sliced bread. On 2003.07.29 17:39, Anna Li wrote: Hi All, I'm trying to use SQL Loader to load data from a text file into a table. However, I always get error as column SKILL_DESCRIPTION field in data file exceeds maximum length where the column SKILL_DESCRIPTION is declared as varchar2(4000). I know the data is 4000 characters, but 1000 characters. It was inserted successfully when I execute insert statement in TOAD as: insert into skills_tmp values(..); Is there any option in SQL Loader I have to set in order to load a long string? Thanks for all the inputs in advance. Anna _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anna Li INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Loader problem
Sliced bread or not, the syntax problem will be the same since external tables are modeled on SQL*Loader syntax... :-) Anna, I suspect that there is confusion on the SQL*Loader concepts of external datatypes and internal datatypes, which incidentally is shared by external tables currently. SQL*Loader's internal datatypes match the standard Oracle RDBMS datatypes, but the external datatypes are something else altgoether... I don't have docs available, but specifying an external datatype of VARCHAR or VARCHAR2 probably doesn't mean what you want it to mean. If memory serves, I think it is supposed to be a two-byte binary integer (which is the data length) to be followed by the data itself. Or something like that... Try CHAR(4000), perhaps? The Server Utilities manual from the standard Oracle doc-set on http://otn.oracle.com; has all this stuff in it... Hope this helps... -Tim on 7/29/03 3:19 PM, Mladen Gogala at [EMAIL PROTECTED] wrote: Try with external tables. The best thing since sliced bread. On 2003.07.29 17:39, Anna Li wrote: Hi All, I'm trying to use SQL Loader to load data from a text file into a table. However, I always get error as column SKILL_DESCRIPTION field in data file exceeds maximum length where the column SKILL_DESCRIPTION is declared as varchar2(4000). I know the data is 4000 characters, but 1000 characters. It was inserted successfully when I execute insert statement in TOAD as: insert into skills_tmp values(..); Is there any option in SQL Loader I have to set in order to load a long string? Thanks for all the inputs in advance. Anna _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anna Li INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL*Loader problem - constraint violation
After reading some archives on google, I came up with this solution: Drop the index. Use sqlldr to append to the table - for the id column, use: (lc_rid sequence (max,1) ...) Recreate the index. However, when I did this, I received an error message saying that I can't have duplicates in the index (of course not because the index is to be unique). But I checked lc_rid for duplicates and didn't find any. So what am I missing here? Thanks, Saira -Original Message- Saira Somani-Mendelin Sent: July 7, 2003 3:59 PM To: Multiple recipients of list ORACLE-L I'm new to sqlldr and here is my control file: LOAD DATA INFILE '/home/oracle/116.csv' BADFILE '/home/oracle/116.bad' DISCARDFILE '/home/oracle/116.rej' APPEND INTO TABLE LC_F FIELDS TERMINATED BY ',' TRAILING NULLCOLS (lc_rid,loc,loc_type,loc_size,sku,pkg,lot,uc1,uc2,uc3,zone,area,loc_stt, cycc_stat,size_load_cap,store_seq,cmd_seq,hgt,wid,dpth,wgt,pal_req,ck_di git,repl_batch_qty,repl_dynam_qty,repl_uom,trig_batch_qty,trig_dynam_qty ,trig_uom,eq_class,tag_track,cap_by_load,cycc_date,whse,route,carrier,ca rrier_grp,trailer,carrier_trailer,seal,pgmmod,usrmod,modcnt,dtimecre,dti memod,empty,linehaul_carrier,max_fp_cap,search,aisle_grp) lc_rid is a unique index primary key and I'm not sure how to handle that. Do I include it as a field in my INFILE? Do I define it as NEXTVAL somehow? Here is a snippet from the log file: Record 1: Rejected - Error on table LC_F. ORA-1: unique constraint (TEST65W.LCI_LOC) violated LCI_LOC looks like this: CREATE UNIQUE INDEX LCI_LOC ON LC_F(LOC) If anyone can help, it would be very appreciated. Thank you, Saira -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL*Loader problem - constraint violation
Saira It looks like your index is being created on the LOC column, right? CREATE UNIQUE INDEX LCI_LOC ON LC_F(LOC) So your change to the lc_rid column did not fix this problem. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, July 08, 2003 9:29 AM To: Multiple recipients of list ORACLE-L After reading some archives on google, I came up with this solution: Drop the index. Use sqlldr to append to the table - for the id column, use: (lc_rid sequence (max,1) ...) Recreate the index. However, when I did this, I received an error message saying that I can't have duplicates in the index (of course not because the index is to be unique). But I checked lc_rid for duplicates and didn't find any. So what am I missing here? Thanks, Saira -Original Message- Saira Somani-Mendelin Sent: July 7, 2003 3:59 PM To: Multiple recipients of list ORACLE-L I'm new to sqlldr and here is my control file: LOAD DATA INFILE '/home/oracle/116.csv' BADFILE '/home/oracle/116.bad' DISCARDFILE '/home/oracle/116.rej' APPEND INTO TABLE LC_F FIELDS TERMINATED BY ',' TRAILING NULLCOLS (lc_rid,loc,loc_type,loc_size,sku,pkg,lot,uc1,uc2,uc3,zone,area,loc_stt, cycc_stat,size_load_cap,store_seq,cmd_seq,hgt,wid,dpth,wgt,pal_req,ck_di git,repl_batch_qty,repl_dynam_qty,repl_uom,trig_batch_qty,trig_dynam_qty ,trig_uom,eq_class,tag_track,cap_by_load,cycc_date,whse,route,carrier,ca rrier_grp,trailer,carrier_trailer,seal,pgmmod,usrmod,modcnt,dtimecre,dti memod,empty,linehaul_carrier,max_fp_cap,search,aisle_grp) lc_rid is a unique index primary key and I'm not sure how to handle that. Do I include it as a field in my INFILE? Do I define it as NEXTVAL somehow? Here is a snippet from the log file: Record 1: Rejected - Error on table LC_F. ORA-1: unique constraint (TEST65W.LCI_LOC) violated LCI_LOC looks like this: CREATE UNIQUE INDEX LCI_LOC ON LC_F(LOC) If anyone can help, it would be very appreciated. Thank you, Saira -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL*Loader problem - constraint violation
Ok. I must be legally blind :) Can this happen to anyone or just me? I will try this again with my bifocals on. Thank you for pointing it out kindly!! Saira -Original Message- Sent: July 8, 2003 8:46 AM To: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Saira It looks like your index is being created on the LOC column, right? CREATE UNIQUE INDEX LCI_LOC ON LC_F(LOC) So your change to the lc_rid column did not fix this problem. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, July 08, 2003 9:29 AM To: Multiple recipients of list ORACLE-L After reading some archives on google, I came up with this solution: Drop the index. Use sqlldr to append to the table - for the id column, use: (lc_rid sequence (max,1) ...) Recreate the index. However, when I did this, I received an error message saying that I can't have duplicates in the index (of course not because the index is to be unique). But I checked lc_rid for duplicates and didn't find any. So what am I missing here? Thanks, Saira -Original Message- Saira Somani-Mendelin Sent: July 7, 2003 3:59 PM To: Multiple recipients of list ORACLE-L I'm new to sqlldr and here is my control file: LOAD DATA INFILE '/home/oracle/116.csv' BADFILE '/home/oracle/116.bad' DISCARDFILE '/home/oracle/116.rej' APPEND INTO TABLE LC_F FIELDS TERMINATED BY ',' TRAILING NULLCOLS (lc_rid,loc,loc_type,loc_size,sku,pkg,lot,uc1,uc2,uc3,zone,area,loc_stt, cycc_stat,size_load_cap,store_seq,cmd_seq,hgt,wid,dpth,wgt,pal_req,ck_di git,repl_batch_qty,repl_dynam_qty,repl_uom,trig_batch_qty,trig_dynam_qty ,trig_uom,eq_class,tag_track,cap_by_load,cycc_date,whse,route,carrier,ca rrier_grp,trailer,carrier_trailer,seal,pgmmod,usrmod,modcnt,dtimecre,dti memod,empty,linehaul_carrier,max_fp_cap,search,aisle_grp) lc_rid is a unique index primary key and I'm not sure how to handle that. Do I include it as a field in my INFILE? Do I define it as NEXTVAL somehow? Here is a snippet from the log file: Record 1: Rejected - Error on table LC_F. ORA-1: unique constraint (TEST65W.LCI_LOC) violated LCI_LOC looks like this: CREATE UNIQUE INDEX LCI_LOC ON LC_F(LOC) If anyone can help, it would be very appreciated. Thank you, Saira -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani-Mendelin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Sql*Loader problem...
JL, When you describe the field type in the SQLLoader file set the CHAR(4000) as the field description. You are correct in saying that the default is CHAR(255). As an example; col1 terminated by , , col2 CHAR(4000) terminated by , ... Ron [EMAIL PROTECTED] 03/13/03 01:44PM Hi... I would like to receive a bit of your help (I've been looking at Metalink with no luck). I'm having a sql*loader problem when I try to insert records in a table field. The problem is: my data file has a column with MORE THAN 255 characters long, and sql*loader rejects the records that exceed that limit! The data are generated by an app called NetCache. And, for auditing, we need to load the data into the oracle database. I'm having problems with just one column: URL_VISITED varchar2(4000) Any ideas, help would be appreciated!. I need to load a string like this: (A workaround could be SUBSTRac just the first 255 chars ) 'http://www.hotmail.msn.com/cgi-bin/sbox?did=1t=4zO *OzYH5DI!K5JDg2lw3GV*Z17S74coVgj5bSbi3Idhx6CPvVsK3Eh bHes!GVxjYRKdxmfIAmoF1SvuqW0Vd5rQ$$p=4wcx4xWmyGq6jd OZdt9566bmc4SziYsk6XKA60n*bkWwcK3UuKf8dzl1ykTjzYsAhK YN8wfQ6ay2rrR1FSRXj*djcyllOvUfV5H8drR6iESnR6PEig6lqH Cv7iH*opeUYbDd4IlZxxwRC6pqrKos5CLOW2iSBE5fp4L2g71b7E LgER0F!Eiw0XdUZsVKN!a17yI*bRtievO7lRyJefsNcjcA$$js=yes' Regards! JL __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL*Loader Problem
Bill: I also found that I have to specify the size of the column even if the data field is being skipped using filler! Ken -Original Message- Sent: Friday, January 11, 2002 10:55 AM To: Multiple recipients of list ORACLE-L Subject:Re: SQL*Loader Problem Where's what note 113211.1 on metalink says about this. I hope it helps. Problem Description --- Using SQL*Loader to load data into a database table and rows fail to load with the message: Record X: Rejected - Error on table ., column XX. Field in data file exceeds maximum length The data to be loaded will be longer than 255 characters and the database column is set to larger than 255 characters. Solution Description In the control file specify the length of the column if the columns in the database are longer than 255 characters. eg LOAD DATA INFILE * INTO TABLE TEST.TEST1 APPEND FIELDS TERMINATED BY ',' (COL1, COL2 char(4000), COL3) BEGINDATA 333, data data data longer than 255 characters data data etc,this will work Explanation --- As a default, if the column length is not specified, it will default to a maximum of 255 characters. If the database column is longer than 255 characters and the data to be loaded exceeds 255 characters, then it exceeds the maximum length. Specifying the maximum length in the control file overrides the default setting. References -- Oracle8 Server Utilities Additional Search Words --- sqlloader loader . Bill Gentry DBA Allina Health System Minneapolis, MN 55403 612-775-1190 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 11, 2002 9:55 AM 8.1.7 on W2000 I am loading data and keep getting an error. The fields are text about 400+ characters. The column I am loading into is varchar2(4000). I keep getting rejects with this error: Field in data file exceeds maximum length. Any ideas as to why this is happening? Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient Systems, Inc. Minneapolis, MN -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Gentry INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL*LOADER problem
Thanks Diana for letting me know about the replace function. A little more research and my problem was solved. Satish IyerDBACCSS Team 684-3016 [EMAIL PROTECTED] 05/30/01 07:50PM Satish,You can do a couple of things...in the query you could to areplace(column1, chr(10), '~') (or some other unlikely character orstring), then do a replace again in the SQL*Loader script to get thecarriage returns back in there. Or you could use the query to put acharacter in the front of each true record, something likeselect '#' || col1, col2, col3, and then use the SQL*Loader commands for concatenating records. Can'tremember offhand how that one works, though...HTH,Diana DuncanTITAN Technology PartnersOne Copley Parkway, Ste 540Morrisville, NC 27560VM: 919.466.7337 x 316F: 919.466.7427E: [EMAIL PROTECTED] "Satish Iyer" [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] tle.wa.us cc: Sent by: Fax to: [EMAIL PROTECTED] Subject: SQL*LOADER problem 05/30/2001 08:05 PM Please respond to ORACLE-L Hi everyone.Having this typical problem with sql*loader. I am extracting data out froma table and this table has a field in which users have put in a new linefeed character. Now when I extract the data out using SQL*PLUS itobviously has a problemeg.1 First line of stt 2333232 Second line2 Full line 23232323 Again a partial 2323232 line.Now if such a table was extracted to a txt file, the sqlldr runs into aproblem of getting the second half of the line as expected. Any ideas howthis could be avoided ?Hope I explained that okay.Satish IyerDBACCSS Team 684-3016-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: SQL*LOADER problem
Satish, You can do a couple of things...in the query you could to a replace(column1, chr(10), '~') (or some other unlikely character or string), then do a replace again in the SQL*Loader script to get the carriage returns back in there. Or you could use the query to put a character in the front of each true record, something like select '#' || col1, col2, col3, and then use the SQL*Loader commands for concatenating records. Can't remember offhand how that one works, though... HTH, Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Satish Iyer [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] tle.wa.us cc: Sent by:Fax to: [EMAIL PROTECTED]Subject: SQL*LOADER problem 05/30/2001 08:05 PM Please respond to ORACLE-L Hi everyone. Having this typical problem with sql*loader. I am extracting data out from a table and this table has a field in which users have put in a new line feed character. Now when I extract the data out using SQL*PLUS it obviously has a problem eg. 1 First line of stt 2333232 Second line 2Full line 2323232 3 Again a partial 2323232 line. Now if such a table was extracted to a txt file, the sqlldr runs into a problem of getting the second half of the line as expected. Any ideas how this could be avoided ? Hope I explained that okay. Satish Iyer DBA CCSS Team 684-3016 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
