Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: SQLLDR loading BLOBS

Re: SQLLDR loading BLOBS

From: Mads S. Larsen <mads_at_syrak.dknospam>
Date: Thu, 31 Aug 2000 23:01:19 GMT
Message-ID: <3tBr5.6127$uy1.101580@news000.worldonline.dk>

Hi!

You must use a filler for the filename and a LOBFILE for the BLOB, as explained in the doc below:

This was taken from the Oracle8i interMedia Text Reference, appendix D:



SQL*Loader Example

The following example shows how to use SQL*Loader to load mixed format documents from the operating system to a BLOB column. The example has two steps:
create the table
issue the SQL*Loader command that reads control file and loads data into table

Creating the Table
This example loads to a table articles_formatted created as follows: CREATE TABLE articles_formatted

  ARTICLE_ID NUMBER PRIMARY KEY ,

  AUTHOR       VARCHAR2(30),
  FORMAT       VARCHAR2(30),
  PUB_DATE     DATE,
  TITLE        VARCHAR2(256),
  TEXT         BLOB

);

The article_id column is the primary key, which is required in a Text table. Documents are loaded in the text column, which is of type BLOB. Issuing the SQL*Loader Command
The following command starts the loader, which reads the control file LOADER1.DAT:
sqlldr userid=demo/demo control=loader1.dat log=loader.log

Example Control File: LOADER1.DAT
This SQL*Loader control file defines the columns to be loaded and instructs the loader to load the data line by line from LOADER2.DAT into the articles_formatted table. Each line in LOADER2.DAT holds a comma separated list of fields to be loaded.
-- load file example
LOAD INFILE 'loader2.dat'
INTO TABLE articles_formatted
APPEND
FIELDS TERMINATED BY ','
(article_id SEQUENCE (MAX,1),
 author CHAR(30),
 format,
 pub_date SYSDATE,
 title,
 ext_fname FILLER CHAR(80),
 text LOBFILE(ext_fname) TERMINATED BY EOF)

This control file instructs the loader to load data from LOADER2.DAT to the articles_formatted table in the following way: The ordinal position of the line describing the document fields in LOADER2.DAT is written to the article_id column. The first field on the line is written to author column. The second field on the line is written to the format column. The current date given by SYSDATE is written to the pub_date column. The title of the document, which is the third field on the line, is written to the title column.
The name of each document to be loaded is read into the ext_fname temporary variable, and the actual document is loaded in the text BLOB column: Example Data File: LOADER2.DAT
This file contains the data to be loaded into each row of the table, articles_formatted.
Each line contains a comma separated list of the fields to be loaded in articles_formatted. The last field of every line names the file to be loaded in to the text column:
Ben Kanobi, plaintext,Kawasaki news article,../sample_docs/kawasaki.txt, Joe Bloggs, plaintext,Java plug-in,../sample_docs/javaplugin.txt, John Hancock, plaintext,Declaration of
Independence,../sample_docs/indep.txt,

M. S. Developer, Word7,Newsletter example,../sample_docs/newsletter.doc,
M. S. Developer, Word7,Resume example,../sample_docs/resume.doc,
X. L. Developer, Excel7,Common example,../sample_docs/common.xls,
X. L. Developer, Excel7,Complex example,../sample_docs/solvsamp.xls,
Pow R. Point, Powerpoint7,Generic presentation,../sample_docs/generic.ppt, Pow R. Point, Powerpoint7,Meeting presentation,../sample_docs/meeting.ppt,
Java Man, PDF,Java Beans paper,../sample_docs/j_bean.pdf,
Java Man, PDF,Java on the server paper,../sample_docs/j_svr.pdf,
Ora Webmaster, HTML,Oracle home page,../sample_docs/oramnu97.html,
Ora Webmaster, HTML,Oracle Company Overview,../sample_docs/oraoverview.html,
John Constable, GIF,Laurence J. Ellison : portrait,../sample_docs/larry.gif,
Alan Greenspan, GIF,Oracle revenues : Graph,../sample_docs/oragraph97.gif, Giorgio Armani, GIF,Oracle Revenues : Trend,../sample_docs/oratrend.gif,

<don_leclaire_at_my-deja.com> wrote in message news:8oeu10$gds$1_at_nnrp1.deja.com...
> I am trying to load a variable length JPEG's
> into an Oracle 8i database using SQLLDR, these are
> coming from a flat file and I need to know if anyone has
> completed this..
>
> I need to know how to do this or if the JPEG format has a
> reserve character that I can delimit with
>
> HELP HELP HELP!!!!!!!
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Aug 31 2000 - 18:01:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US