Oracle-L: Re: Try Again: Loading Data with "Return" key from SQL Loader

From: WLSH <wlsh1961_at_yahoo.com>
Date: Wed, 09 Apr 2003 09:25:42 -0800
Message-ID: <F001.0057E111.20030409092542@fatcity.com>

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,

SERV_END_TIME,
SERV_LONG_DESC TERMINATED BY '}' OPTIONALLY ENCLOSED BY '"' "SUBSTR(:SERV_LONG_DESC, 1, 1000)" )

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'                --- incorrect

INTO TABLE C_AHD_FEED
WHEN TICKET_NUM != ' '
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ..... Thanks again and cheers
Steven

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

REQUESTING_DEPARTMENT= DEPT
PROGRAM_NAME=
CATALOG_ID=
SR_DESCRIPTION_TEXT= SURPLUS THIS ASSET
SR_COMMENTS_TEXT=DO THE PREFIELD ))
A complete one record will be like:
{"AA3861111","2342123","RC","AA","LGA", "01", "FRONT OFFC","20010101". "011100","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-Y! ! ! 5
FROM_LOCATION= DV-Y5-Y5-22-NA-Unkno

REQUESTING_DEPARTMENT= DEPT
PROGRAM_NAME=
CATALOG_ID=
SR_DESCRIPTION_TEXT= SURPLUS THIS ASSET
SR_COMMENTS_TEXT=DO THE PREFIELD"}
My current control file:

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: WLSH
  INET: wlsh1961_at_yahoo.com

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 - 12:25:42 CDT