Function returning BLOB

From: Randi W <randiwolner_at_hotmail.com>
Date: Tue, 15 Jun 2004 10:38:31 +0200
Message-ID: <camci8$dvb$1@services.kq.no>

My version of Oracle is 8.1.7 for NT (and AIX). I want is to store pictures as files on the database server. In Oracle I would like to have a table containing information about persons, and one of the columns will contain the file name of the picture. From some client application I will show all information about a person, and I would like to call an Oracle function that returns the content of the file.

I thought that creating a function that opens the file and returns content as a BLOB would be a good idea. The function that I include works when I use it in a Oracle PL/SQL block. BUT - I would like to use this in SQL, and then I think I have to put a PRAGMA on it.
The problem is that when I put a pragma on (remove the comment in the code) I get this error message:
PLS-00452: Subprogram 'BLOB_TEST' violates its associated pragma

create or replace directory my_files as 'D:\'
/

CREATE OR REPLACE PACKAGE test_blob IS
  function blob_test return BLOB;

• PRAGMA RESTRICT_REFERENCES(blob_test, WNDS, WNPS); END test_blob;
  /
  

CREATE OR REPLACE PACKAGE BODY test_blob IS function blob_test return blob is

        l_blob  blob;
        l_bfile bfile;
begin
       DBMS_LOB.CREATETEMPORARY(l_blob, TRUE);

      l_bfile := bfilename( 'MY_FILES', 'vin.pcx' );
      dbms_lob.fileopen( l_bfile );

      dbms_lob.loadfromfile( l_blob, l_bfile,
                               dbms_lob.getlength( l_bfile ) );
      dbms_lob.fileclose( l_bfile );
        return l_blob;

end blob_test;

END test_blob;
/

Is there any better way to do what I want? Thanks for any help on this!

Best regards,
Randi W Received on Tue Jun 15 2004 - 03:38:31 CDT