Home » RDBMS Server » Server Utilities » Loading the CLOB data into table (11.1.0.6.0)
Loading the CLOB data into table [message #564108] Tue, 21 August 2012 07:39 Go to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Hi All,

How to load the CLOB data into table..in the attached file 18 column has clob data it's appear like new line..
Using external table how to load. Please help me..i tried it's not working...

Find the attached external table script and file.
Thanks,
  • Attachment: clobdata.txt
    (Size: 172.93KB, Downloaded 2102 times)

[Updated on: Tue, 21 August 2012 07:49]

Report message to a moderator

Re: Loading the CLOB data into table [message #564113 is a reply to message #564108] Tue, 21 August 2012 08:09 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Any help?
Re: Loading the CLOB data into table [message #564114 is a reply to message #564113] Tue, 21 August 2012 08:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Only invisible ones.

Regards
Michel
Re: Loading the CLOB data into table [message #564115 is a reply to message #564114] Tue, 21 August 2012 08:14 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

i am not clear.. what you are trying to say?
Re: Loading the CLOB data into table [message #564119 is a reply to message #564115] Tue, 21 August 2012 08:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just you have to wait and not ask every half hour.
By the way, I don't know how I could read your file; 792 lines of stuff, some with more than 3K characters.

Regards
Michel
Re: Loading the CLOB data into table [message #564120 is a reply to message #564115] Tue, 21 August 2012 08:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
MY car it's not working
tell me how to make my car go.
Re: Loading the CLOB data into table [message #564122 is a reply to message #564120] Tue, 21 August 2012 08:47 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Thanks quick update...i will wait...
CREATE TABLE test (  Phone_Name varchar2(1000),
          Email_Template_version varchar2(1000),
          Midlit_Code varchar2(1000),
          List_Code varchar2(1000),
          Offer_Type varchar2(1000),
          Headline_Image varchar2(1000),
          Headline_Alt varchar2(1000),
          CTA_Image varchar2(1000),
          CTA_Image_ALT varchar2(1000),
          Phone_Image1 varchar2(1000),
          Phone_Alt1 varchar2(1000),
          Phone_link1 varchar2(1000),
          Offer_Box_Image1 varchar2(1000),
          Offer_Box_Alt1 varchar2(1000),
          Offer_Box_link1 varchar2(1000),
          Copy_below_button_Image varchar2(1000),
          Copy_below_button_ALT  varchar2(1000),
          Body_Copy CLOB,
          Web_Promo_Code_Image  varchar2(1000),
          Web_Promo_Code_ALT varchar2(1000),
          Web_Promo_Code_Link varchar2(1000),
          Store_Map varchar2(1000),
          Store_Map_ALT_Image  varchar2(1000),
          Store_Map_ALT varchar2(1000),
          Sprint_Buy_Back_Image varchar2(1000),
          Sprint_Buy_Back_ALT varchar2(1000),
          Black_Bar_Image1  varchar2(1000),
          Black_Bar_Image_ALT1 varchar2(1000),
          Black_Bar_Link1 varchar2(1000),
          Black_Bar_Image_2 varchar2(1000),
          Black_Bar_Image_ALT2 varchar2(1000),
          Black_Bar_Link2 varchar2(1000),
          Legal varchar2(3000)  )  
          ORGANIZATION external (TYPE oracle_loader DEFAULT DIRECTORY AD_DATA_EXT 
                                ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE  
                                SKIP 1 
FIELDS TERMINATED BY 0X'09' LRTRIM  )  LOCATION ('clobdata.txt'))
 REJECT LIMIT UNLIMITED
Re: Loading the CLOB data into table [message #564196 is a reply to message #564122] Wed, 22 August 2012 00:42 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Do you have any suggestions?
Re: Loading the CLOB data into table [message #564197 is a reply to message #564196] Wed, 22 August 2012 00:52 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I haven't tried this ever, but can you please tell us what you tried, what was the error if any, or what is there in log file and so on?

regards,
Delna
Re: Loading the CLOB data into table [message #564200 is a reply to message #564197] Wed, 22 August 2012 01:17 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

every record went to bad?
Re: Loading the CLOB data into table [message #564326 is a reply to message #564196] Wed, 22 August 2012 11:13 Go to previous messageGo to next message
koteswar
Messages: 4
Registered: August 2012
Location: bangalore
Junior Member

Hello dude,

which method your using for loading the data into table ? suppose your using sql* loader u can fallow like below..

clob_filename FILLER CHAR(100),
clob_content LOBFILE(clob_filename) TERMINATED BY EOF,
blob_filename FILLER CHAR(100),
blob_content LOBFILE(blob_filename) TERMINATED BY EOF)
Notice the filename columns in the datafile are marked as FILLERs, so they are not loaded into the table, but they are used in the LOBFILE definition to identify the loacation of the LOB information.



Thanks,
koteswar.
Re: Loading the CLOB data into table [message #564353 is a reply to message #564326] Thu, 23 August 2012 01:11 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Thanks koteswar.
Please provide the sqlloader script....
Re: Loading the CLOB data into table [message #568520 is a reply to message #564353] Sat, 13 October 2012 01:47 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
options (skip=1)
load data
infile clobdata.txt
into table test
fields terminated by X'09'
(Phone_Name,
Email_Template_version,
Midlit_Code,
List_Code,
Offer_Type,
Headline_Image,
Headline_Alt,
CTA_Image,
CTA_Image_ALT,
Phone_Image1,
Phone_Alt1,
Phone_link1,
Offer_Box_Image1,
Offer_Box_Alt1 CHAR(5000),
Offer_Box_link1,
Copy_below_button_Image,
Copy_below_button_ALT,
Body_Copy CHAR (5000),
Web_Promo_Code_Image,
Web_Promo_Code_ALT,
Web_Promo_Code_Link,
Store_Map,
Store_Map_ALT_Image,
Store_Map_ALT,
Sprint_Buy_Back_Image,
Sprint_Buy_Back_ALT,
Black_Bar_Image1,
Black_Bar_Image_ALT1,
Black_Bar_Link1,
Black_Bar_Image_2,
Black_Bar_Image_ALT2,
Black_Bar_Link2,
Legal CHAR(5000))
Previous Topic: REMAP_DATAFILES parameter
Next Topic: trace DDL SQL
Goto Forum:
  


Current Time: Thu Mar 28 09:25:07 CDT 2024