| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Try Again: Loading Data with "Return" key from SQL Loader
Steven,
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 ","
...
All of the research through my books that I have, I only found this information in one book. I had this same problem in the past with SQLLoader. The book is "Expert one on one" by Thomas Kyte Pg 407. A good book that has a lot of answers if you dig deep enough. Ron
>>> wlsh1961_at_yahoo.com 04/09/03 01:25PM >>>
Nice Idea! Thanks Yechiel. It's start working now. I use (not equal to)
TERMINATOR instead of those whitespaces.
Just one more confusion here. It looks like SQL Loader will only allow
to load 250 text long with those whitespace in it. Any text longer than
that will be dropped even the table designs as Varchar2(2000), and
SUBSTR doesn't seem handle it. Is it true?
LOAD DATA
INFILE AHD_BILLING.DAT "str '\n'"
REPLACE
CONTINUEIF LAST != '}'
INTO TABLE C_AHD_FEED
WHEN TICKET_NUM != ' '
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (
TICKET_NUM position (*+1) char, ASSET_NUM NULLIF ASSET_NUM = BLANKS, ...... TIMEZONE,
Thanks again
Steven
Yechiel Adar <adar76_at_inter.net.il> wrote:Hello Steven 0D0A is end of
record - carriage return + line feed. The 20 is probably a blank at the
start of the next line. I think that you need to check "continue if last
not equal ')' ". Yechiel Adar
Mehish----- Original Message ----- From: WLSH To: Multiple recipients
of list ORACLE-L Sent: Wednesday, April 09, 2003 12:03 AMSubject: Re:
Try Again: Loading Data with "Return" key from SQL Loader - is it
possible ?
Thanks, Igor and all !
I got the hex number out '0D0A20' alright, now it comes the new
problem. It looks like there are 3 characters combined. But the compare
text in CONTINUEIF LAST can only be one character. Therefore it always
generate error. Any other ideas?
LOAD DATA
INFILE AHD_BILLING.DAT
REPLACE
CONTINUEIF LAST = X'0D0A20' --- incorrectINTO TABLE C_AHD_FEED
Igor Neyman <ineyman_at_perceptron.com> wrote: select rawtohex(<your_column>) from <your_table> Igor ----- Original Message ----- From: WLSH To: Multiple recipients of list ORACLE-L Sent: Tuesday, April 08, 2003 12:53 PMSubject: Re: Try Again: Loading Data with "Return" key from SQL Loader - is it possible ?
Thanks Jared and ALL:
I found that I can Use "CONTINUEIF". (the incoming file does not have a
fix position format.) Now the difficult is WHAT EXACTLY 'hex-str' IS
which represents the special character from the incoming text (it looks
like a carriage return, but really not)? This special character is
stored in my database table. Is there a way that I can convert it into a
hex string ?
Thanks again for any help
Steven
Jared.Still_at_radisys.com wrote: see:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch07.htm#1006472
WLSH
Sent by: root_at_fatcity.com
04/07/2003 09:33 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
cc:
?
WLSH wrote:
Date: Mon, 7 Apr 2003 08:10:18 -0700 (PDT)
To: ORACLE-L_at_fatcity.com
Hello, List:
I'm doing a load from SQL loader to DB Table. One field of data
(SERV_LONG_DESC) contains "RETURN" data. Is it possible to load it into
table? I'm not sure if SQL Loader can load from different lines. If
not,
any other options I can do this ? The pure SERV_LONG_DESC field alone
will be like:
(( REQUESTOR_NAME= Janet Abell
REQUESTOR_PHONE_NUMBER= 215.234.9852
FROM_ASSET_ID= CSC272002
hERE IS A COMMENT
MANUFACTURER= DELL
MODEL_NUMBER= DELLATLP
NEEDED_DATE=
NEEDED_TIME=
FROM_USER_NAME= Mary Abbott FROM_ASSET_DESCRIPTION_TEXT= Default system type FROM_FACILITY= DV-Y5-Y5 FROM_LOCATION= DV-Y5-Y5-22-NA-Unkno
FROM_USER_NAME= Mary Abbott FROM_ASSET_DESCRIPTION_TEXT= Default system type FROM_FACILITY= DV-Y5-Y! ! ! 5 FROM_LOCATION= DV-Y5-Y5-22-NA-Unkno
LOAD DATA
INFILE AHD_BILLING.DAT
REPLACE
INTO TABLE C_AHD_FEED
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( TICKET_NUM position (*+1) char,
ASSET_NUM NULLIF ASSET_NUM = BLANKS,
ADJUSTMENT_IND CONSTANT 'N',
BILL_FLAG CONSTANT 'N',
DIVISION,
PLANT ,
BUILDING,
FLOOR ,
AREA ,
SERV_START_DATE "to_date(:SERV_START_DATE||:SERV_START_TIME,'yyyymmdd
HH24:MI:SS')",
SERV_START_TIME,
SERV_LONG_DESC TERMINATED BY '}' OPTIONALLY ENCLOSED BY '"'
)
Thanks a lot for any idea!
Steven
Do y! ! ou! Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: RROGERS_at_galottery.org 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: ListGuru_at_fatcity.com (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).Received on Wed Apr 09 2003 - 14:24:02 CDT
 |
 |