Re: blob insertion

From: Mike Carter <mike_at_delriotech.com>
Date: Thu, 16 Dec 1999 16:15:37 -0600
Message-ID: <2td64.134888$y45.2758721@news4.giganews.com>

Kenneth,
Here's an exaple procedure I wrote in an Oracle 8.1.5 database to import images (jpegs, bmps, gifs, etc.). The table inserted into is called IMGADDENDSTORE and is defined as:

    ITEMID NUMBER(10)
    BLOB_ITEM ORDSYS.ORDImage

Here's the code from the procedure:
PROCEDURE "SP_LOADIMGADDENDSTORE" (item IN NUMBER,  filename IN CHAR,
 itemtype IN CHAR)
AS

img ORDSYS.ORDImage;
ctx RAW(4000) := NULL;

BEGIN
  INSERT INTO IMGADDENDSTORE
    VALUES(item,

           ORDSYS.ORDImage(ORDSYS.ORDSource(empty_blob(),
                           NULL, NULL, NULL, SYSDATE, 1),
                           NULL, NULL, NULL, NULL, NULL,
                           NULL, NULL));

  COMMIT;
  SELECT BLOB_ITEM INTO img FROM IMGADDENDSTORE WHERE ITEMID = item FOR UPDATE;
  BEGIN
    IF (itemtype = 'A') THEN
      img.setSource('FILE', 'APS_ADDENDUM', filename);     ELSIF (itemtype = 'I') THEN
      img.setSource('FILE', 'APS_IMAGES', filename);     ELSE
      img.setSource('FILE', 'APS_IMAGES', filename);     END IF;     img.import(ctx);
    img.setProperties();
  END;   UPDATE IMGADDENDSTORE SET BLOB_ITEM = img WHERE ITEMID = item; END; To extract the image use:
SELECT I.BLOB_ITEM.GETCONTENT()
FROM IMGADDENDSTORE I; This query returns the actual image type which you can determine by the following query:
Select I.Blob_Item.GetMimeType()
From IMGADDENDSTORE I;

Kenneth Lewin <lewin_kenneth_at_bah.com> wrote in message news:3858FFFB.64606D8_at_bah.com...
> I am attempting to store a 1-2Meg file within a blob field in my
> database.
> How can the file be inserted into and extracted from the database via
> SQL*Plus?????
> (Oracle 8.1.5 database)
>
> thanks,
> -kl
Received on Thu Dec 16 1999 - 16:15:37 CST