Oracle-L: loading photos into Oracle BLOB

From: Jack van Zanen <JACK_at_QUANTSYSTEMS.NL>
Date: Tue, 13 May 2003 07:41:07 -0800
Message-ID: <F001.005978FC.20030513074107@fatcity.com>

Hi All,

I'm loading pictures into an oracle database by means of the details below.
(they work fine)

But what I want to do is not only fill the BLOB field with data, but at the same time load the photodescription field with the full path name.

I have tried many varieties of these scripts but do not seem to be able to get it right.
Anybody any ideas???

TIA Jack

CREATE TABLE ALBUM
(

  PHOTOID           NUMBER,
  PHOTO             BLOB,
  PHOTODESCRIPTION  VARCHAR2(200 BYTE),
  PHOTODATE         DATE

)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (

            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )

LOGGING
LOB (PHOTO) STORE AS

      ( TABLESPACE  USERS 
        ENABLE      STORAGE IN ROW
        CHUNK       8192
        PCTVERSION  10
        NOCACHE
        STORAGE    (
                    INITIAL          64K
                    MINEXTENTS       1
                    MAXEXTENTS       2147483645
                    PCTINCREASE      0
                    BUFFER_POOL      DEFAULT
                   )
      )

NOCACHE
NOPARALLEL; CREATE UNIQUE INDEX PHOTOID_PK ON ALBUM
(PHOTOID)

LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (

            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )

NOPARALLEL; CREATE OR REPLACE TRIGGER IDPHOTO
BEFORE INSERT ON ALBUM
FOR EACH ROW
BEGIN
SELECT PHOTONUM.NEXTVAL
INTO :NEW.PHOTOID FROM DUAL;
END;
/
SHOW ERRORS; ALTER TABLE ALBUM ADD (
  CONSTRAINT PHOTOID_PK PRIMARY KEY (PHOTOID)     USING INDEX
    TABLESPACE USERS
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE (

                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
               ));


*************************************************

SQL LOADER Controlfile

LOAD DATA
INFILE PHOTOS.LST
REPLACE
INTO TABLE ALBUM
(EXT_FNAME FILLER CHAR(200),

PHOTO LOBFILE(EXT_FNAME) TERMINATED BY EOF, PHOTODESCRIPTION FILLER CHAR(200),
PHOTODATE SYSDATE)

PHOTOS.LST

D:\MyDocuments\P4290002.JPG
D:\MyDocuments\P5030005.JPG
D:\MyDocuments\P5030006.JPG
D:\MyDocuments\P5030007.JPG
D:\MyDocuments\P5030008.JPG

****************************************************************************
**************************************************************

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jack van Zanen
  INET: JACK_at_QUANTSYSTEMS.NL

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Tue May 13 2003 - 10:41:07 CDT