OraFAQ Forum: Server Utilities » Loading the CLOB data into table

Home » RDBMS Server » Server Utilities » Loading the CLOB data into table (11.1.0.6.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Loading the CLOB data into table [message #564108]

Tue, 21 August 2012 07:39

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

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

Any help?

Report message to a moderator

Re: Loading the CLOB data into table [message #564114 is a reply to message #564113]

Tue, 21 August 2012 08:12

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Only invisible ones.

Regards
Michel

Report message to a moderator

Re: Loading the CLOB data into table [message #564115 is a reply to message #564114]

Tue, 21 August 2012 08:14

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

i am not clear.. what you are trying to say?

Report message to a moderator

Re: Loading the CLOB data into table [message #564119 is a reply to message #564115]

Tue, 21 August 2012 08:41

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Loading the CLOB data into table [message #564120 is a reply to message #564115]

Tue, 21 August 2012 08:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

MY car it's not working
tell me how to make my car go.

Report message to a moderator

Re: Loading the CLOB data into table [message #564122 is a reply to message #564120]

Tue, 21 August 2012 08:47

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

Report message to a moderator

Re: Loading the CLOB data into table [message #564196 is a reply to message #564122]

Wed, 22 August 2012 00:42

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

Do you have any suggestions?

Report message to a moderator

Re: Loading the CLOB data into table [message #564197 is a reply to message #564196]

Wed, 22 August 2012 00:52

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

Report message to a moderator

Re: Loading the CLOB data into table [message #564200 is a reply to message #564197]

Wed, 22 August 2012 01:17

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

every record went to bad?

Report message to a moderator

Re: Loading the CLOB data into table [message #564326 is a reply to message #564196]

Wed, 22 August 2012 11:13

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.

Report message to a moderator

Re: Loading the CLOB data into table [message #564353 is a reply to message #564326]

Thu, 23 August 2012 01:11

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

Thanks koteswar.
Please provide the sqlloader script....

Report message to a moderator

Re: Loading the CLOB data into table [message #568520 is a reply to message #564353]

Sat, 13 October 2012 01:47

Barbara Boehmer
Messages: 9105
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))

Report message to a moderator

Previous Topic:	REMAP_DATAFILES parameter
Next Topic:	trace DDL SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 21 22:55:59 CDT 2025