Re: Loading LOBs into Oracle with SQLLDR - query

In article <6284dd3.0208160035.1fe32739_at_posting.google.com>, mmontreaux_at_hotmail.com says...
>
>Hello, just a bit of a query on syntax for SQLLDR and loading
>CLOBs/BLOBs/LOBs.
>
>I have created a table in SQL+ as shown below
>
>CREATE TABLE tblLobTest (OriginalFileName VARCHAR2(100),
> UploadedFile CLOB);
>
>
>Then I am attempting to load some files into this table using SQLLDR.
>What I would like is to have the filename in the table column
>'OriginalFileName' and the file itself in the CLOB field
>'UploadedFile'. I can get the file populating the CLOB using the
>control file below.
>
>LOAD DATA
>INFILE *
>INTO TABLE tblLobTest
>REPLACE
>FIELDS TERMINATED BY ','
>( fillerfile FILLER CHAR,
> "uploadedfile" LOBFILE (fillerfile) TERMINATED BY EOF NULLIF
>fillerfile = 'NONE'
>)
>BEGINDATA
>O:\lobtest\ZY6743000049540174_2001111200201.dat
>O:\lobtest\ZY6743000049540185_2001111200202.dat
>O:\lobtest\ZY6743000049540194_2001111200203.dat
>O:\lobtest\ZY6743000043273313_2001111200204.dat
>O:\lobtest\ZY6743000041264392_2001111200205.dat
>O:\lobtest\ZY6743000049540169_2001111200206.dat
>O:\lobtest\ZY6743000049540193_2001111200207.dat
>O:\lobtest\ZY6743000049540196_2001111200208.dat
>O:\lobtest\ZY6743000049540177_2001111200209.dat
>O:\lobtest\ZY6743000049540190_2001111200210.dat
>O:\lobtest\ZY6743000043233704_2001111200211.dat
>O:\lobtest\ZY6743000049540148_2001111200212.dat
>O:\lobtest\ZY6743000049540142_2001111200213.dat
>O:\lobtest\ZY6743000043255705_2001111200214.dat
>
>What it doesn't do it put the filename into 'OriginalFileName' as well
>(without duplicating the filename & path in the control file that is)
>and this is what I also need/want.
>
>Here is the version information for the version of Oracle I have
>installed.
>
>Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
>PL/SQL Release 8.1.7.0.0 - Production
>CORE 8.1.7.0.0 Production
>TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
>NLSRTL Version 3.4.1.0.0 - Production
>
>Although I'm sure the information is in the Oracle docs somewhere,
>having scoured much information I can't seem to find anything.
>Hopefully I've just been looking at this far to long ! Many thanks in
>advance to those people that think about my query.
>Monty

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from tbllobtest   2 /

ORIGINALFILENAME UPLOADEDFILE

------------------------- --------------------------------------------------
/export/home/tkyte/t.ctl  LOAD DATA
                          INFILE *
                          INTO TABLE tblLobTest
                          REPLACE
                          FIELDS TERMINATED BY ','
                          ( originalFileName  position(1:100),
                            uploadedfile LOBFILE (originalFileName) TERMINAT
                          ED BY EOF NULLIF originalFileName = 'NONE'
                          )
                          BEGINDATA
                          /export/home/tkyte/t.ctl

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>

sort of a recursive answer -- the answer to the query is the answer to the question.

Note: do NOT put quotes about uploadedfile -- that would be a quoted identifier.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp