Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Try Again: Loading Data with "Return" key from SQL Loader - is it possible ?

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

From: Yechiel Adar <adar76_at_inter.net.il>
Date: Wed, 09 Apr 2003 11:08:51 -0800
Message-ID: <F001.0057E418.20030409110851@fatcity.com> 





Why not use the format:



SERV_LONG_DESC varchar2(1000) TERMINATED BY '}' OPTIONALLY ENCLOSED BY '"' 



Yechiel Adar


Mehish





  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





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




------------------------------------------------------------------------------


  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 - 14:08:51 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US