I apparently do not have a clear understanding of utl_file. I created a directory object called DocumentDir, I issue the following commands in Sql Developer when logged on as Sys:
create or replace directory DocumentDir as 'C:\DATA';
grant read, write on directory DocumentDir to PUBLIC;
When I execute the line “utl_file.fopen('DocumentDir', pFilename, 'wb')”, I get the error “ORA-29280: invalid directory object” in a procedure I'm testing. The procedure is:
create or replace PROCEDURE SAVETOFLIE(pParticipantID IN Number, pDocumentID IN Number, pFileName IN Varchar2) AS
BEGIN
declare
lob Blob ;
vExists boolean;
vFileLength number;
vBlockSize number;
hfile utl_file.file_type; -- file handle of destination file
loboffset number := 1; -- we start reading at the 1st byte in the blob
lobsize number; -- size of the blob
bytesread number := 32767;
rawbuffer raw(32767);
amount number;
begin
Select Document INTO LOB FROM Documents where ParticipantID = pParticipantID and DocumentID = pDocumentID;
lobsize := dbms_lob.getlength(lob);
hfile := utl_file.fopen('DocumentDir', pFilename, 'wb');
begin
if lobsize < bytesread and lobsize > 0 then
bytesread := lobsize;
ELSE
LogError('PL/SQL Function SAVETOFLIE - ERROR: File size larger than allowed or = zero. File name: ' || pFileName || ' Participant ID: ' || to_char(pParticipant) || ' DocumentID: ' || to_char(pDocumentID) || ' Document Size: ' || to_char(lobsize));
end if;
if lobsize > 0 and lobsize < bytesread then
while loboffset < lobsize
loop
dbms_lob.read(lob_loc => lob
, amount => bytesread
, offset => loboffset
, buffer => rawbuffer);
loboffset := loboffset + bytesread;
utl_file.put_raw(hfile, rawbuffer);
end loop;
end if;
utl_file.fclose(hfile);
exception
when OTHERS then
if utl_file.is_open(hfile) then
utl_file.fclose(hfile);
end if;
raise;
end;
end;
END SAVETOFLIE;
What am I missing?