I am trying to user SQL*Loader from the new Oracle 11g XE to import from a txt file into a table that has a large field (can use CLOB or LONG RAW) - open to suggestions for this as well.
Am having problems. Searched around on the Internet for solutions - found that I have to edit the Control File - which got me a little closer; I just don't know the syntax to use. Here is my code from the control file (very basic):
LOAD DATA
INFILE 'C:\MAUDE\UPDATES\TXTFILES\FOITEXT\foitext.txt'
BADFILE 'C:\MAUDE\UPDATES\TXTFILES\FOITEXT\foitext.bad'
DISCARDFILE 'C:\MAUDE\UPDATES\TXTFILES\FOITEXT\foitext.dsc'
INTO TABLE "MAUDE"."FOITEXT"
APPEND
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(MDR_REPORT_KEY,
MDR_TEXT_KEY,
TEXT_TYPE_CODE,
PATIENT_SEQ_NO,
DATE_REPORT,
*TEXT VARRAW (1000000)*
Where I am lacking and getting warnings like
_"Record 1: Warning on table "MAUDE"."FOITEXT", column TEXT
Variable length field was truncated."_ It doesn't appear to be a problem; but..wondering if there is a way around it
Also, getting one error message:
_"end of logical record found when reading length of varying length field"_
I foung this one to have in column 6 of my data instead of being null after the final "|" it has a asterisk symbol. Is there a way to stop this from happening?
Last question relating to this is if I have someone querying this data after it is successfully imported, what does the querier(data miner) do with the hex field (BLOB). Does it convert easily into excel when they are querying?
Any help would be greatly appreciated.
Edited by: davidredden1973 on Sep 15, 2011 10:31 AM