OraFAQ Forum: Server Utilities » LOAD CHAR TEXT to CLOB

Home » RDBMS Server » Server Utilities » LOAD CHAR TEXT to CLOB

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

LOAD CHAR TEXT to CLOB [message #122823]

Wed, 08 June 2005 12:32

pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville

Senior Member

I am using SQLloader to load the text data to into a clob field. It does everything fine except it truncates the begining spaces in
the text data.

Here is my control file.

LOAD DATA
INFILE '/orabase/rls100/migration/lc_final/LOADER/DATA/CORRESP_DATA.dat' "str '\
n'"
INTO TABLE CORRESP_DATA APPEND
REENABLE
TRAILING NULLCOLS
(CORRESP_DATA_ID FLOAT EXTERNAL TERMINATED BY "|"
,CORRESPONDENCE_ID FLOAT EXTERNAL TERMINATED BY "|"
,CORRESP_ELEMENT_NO FLOAT EXTERNAL TERMINATED BY "|"
,TEXT CHAR(4000) TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
)

TEXT field is defined as CHAR(4000) in the control file where as the database the field is defined as CLOB.

Here is the sample TEXT field data of the unload record.

12594377|8829361|240| XXXXXXXXX $.00 XXXXXXXXX 0 4.8750% $.00 $.00 $.00 $.00 $.00 0 0.0000% $.00 $.00 $.00 $.00 57120 9.9999% $99.99 $99.99 YYYYYYY ZZZZZZZZZZ |

Please note there are 3 spaces in the above record before the XXX

RESULT : - After Load the data looks like this.

there are no spaces before XXXX.....

12594377|8829361|240|XXXXXXXXX $.00 XXXXXXXXX 0 4.8750% $.00 $.00 $.00 $.00 $.00 0 0.0000% $.00 $.00 $.00 $.00 57120 9.9999% $99.99 $99.99 YYYYYYY ZZZZZZZZZZ |

I need the data should be as is after the load.
Can someone shed some light as to what should I do.

Thanks much.

Report message to a moderator

Re: LOAD CHAR TEXT to CLOB [message #122893 is a reply to message #122823]

Thu, 09 June 2005 01:27

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

Remove the part that says:

OPTIONALLY ENCLOSED BY '"'

Report message to a moderator

Re: LOAD CHAR TEXT to CLOB [message #123070 is a reply to message #122823]

Thu, 09 June 2005 14:54

pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville

Senior Member

Thanks Barbara. It worked.

However I had to change the datatype in table to VARCHAR2 before loading it. Then changing to CLOB datatype.

Got question on this.
Is it possible to change the datatype of a field from VARCHAR2(4000) to CLOB without losing the data in a table?
What is the syntax for it.

Thanks in advance

Report message to a moderator

Re: LOAD CHAR TEXT to CLOB [message #123078 is a reply to message #123070]

Thu, 09 June 2005 16:04

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

scott@ORA92> DESCRIBE corresp_data
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 CORRESP_DATA_ID                                                NUMBER
 CORRESPONDENCE_ID                                              NUMBER
 CORRESP_ELEMENT_NO                                             NUMBER
 TEXT                                                           VARCHAR2(4000)

scott@ORA92> SELECT * FROM corresp_data
  2  /

CORRESP_DATA_ID CORRESPONDENCE_ID CORRESP_ELEMENT_NO
--------------- ----------------- ------------------
TEXT
----------------------------------------------------------------------------------------------------
       12594377           8829361                240
   XXXXXXXXX $.00 XXXXXXXXX 0 4.8750% $.00 $.00 $.00 $.00 $.00 0 0.0000% $.00 $.00 $.00 $.00 57120 9
.9999% $99.99 $99.99 YYYYYYY ZZZZZZZZZZ


scott@ORA92> ALTER TABLE corresp_data RENAME COLUMN text TO text_varchar2
  2  /

Table altered.

scott@ORA92> ALTER TABLE corresp_data ADD text CLOB
  2  /

Table altered.

scott@ORA92> UPDATE corresp_data SET text = text_varchar2
  2  /

1 row updated.

scott@ORA92> ALTER TABLE corresp_data DROP COLUMN text_varchar2
  2  /

Table altered.

scott@ORA92> DESCRIBE corresp_data
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 CORRESP_DATA_ID                                                NUMBER
 CORRESPONDENCE_ID                                              NUMBER
 CORRESP_ELEMENT_NO                                             NUMBER
 TEXT                                                           CLOB

scott@ORA92> SELECT * FROM corresp_data
  2  /

CORRESP_DATA_ID CORRESPONDENCE_ID CORRESP_ELEMENT_NO
--------------- ----------------- ------------------
TEXT
--------------------------------------------------------------------------------
       12594377           8829361                240
   XXXXXXXXX $.00 XXXXXXXXX 0 4.8750% $.00 $.00 $.00 $.00 $.00 0 0.0000% $.00 $.
00 $.00 $.00 57120 9.9999% $99.99 $99.99 YYYYYYY ZZZZZZZZZZ


scott@ORA92>

Report message to a moderator

Re: LOAD CHAR TEXT to CLOB [message #123089 is a reply to message #122823]

Thu, 09 June 2005 16:55

pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville

Senior Member

Barbara.

It is working great. Thats a million.

You are genius.

Best regards

Report message to a moderator

Previous Topic:	Scheduled Jobs and Database Export
Next Topic:	Error importing converted datafile

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 04 05:53:29 CDT 2024