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

From: Yechiel Adar <adar76_at_inter.net.il>
Date: Wed, 09 Apr 2003 01:58:53 -0800
Message-ID: <F001.0057DB49.20030409015853@fatcity.com>

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 AM Subject: 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

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 PM Subject: 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: 
        Subject: Try Again: Loading Data with "Return" key from SQL Loader - is it possible 
        ?



        WLSH wrote: 
        Date: Mon, 7 Apr 2003 08:10:18 -0700 (PDT)
        From: WLSH 
        Subject: Loading Data with "Return" key from SQL Loader - is it possible ?
        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






--------------------------------------------------------------------------
      Do you 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: Yechiel Adar
  INET: adar76_at_inter.net.il

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 - 04:58:53 CDT

Re: Try Again: Loading Data with "Return" key from SQL Loader - is it possible ?