Re: Function returning BLOB

DBMS_LOB.CREATETEMPORARY actually creates a BLOB in your default tablespace. In which case it will have to allocate space in which case it will violate the pragma reference WNDS. Where you go from there I dunno...Maybe remove the pragma? Metalink Note 74155.1 has a related pdf describing the storage of a temporary LOB.

TD

Randi W wrote:
> 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 Wed Jun 16 2004 - 23:29:43 CDT