SQL & PL/SQL

Sample approach:

procedure SaveToFile( lob blob, directory varchar2, fileName varchar2 ) is 
       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 
       lobSize := DBMS_LOB.GetLength( lob ); 
 
       hFile := UTL_FILE.fOpen( directory, fileName, 'wb' ); 
 
       if lobSize < bytesRead then 
               bytesRead := lobSize; 
       end if; 
 
       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; 
 
       UTL_FILE.fClose( hFile ); 
end;

...Neither this! Unfortunately!

Thanks for your response!
Sim

no file created in the oracle directory (p_dir as parameter in the procedure).
Have anyone the slightest idea what may be the reason...?

Are you looking in the right directory? the right server?
Check the path (select * from all_directories)

Yes, there is one specific oracle directory.
When i set , on purpose, the wrong directory then an error message appears!

Thanks, for the response,
Sim

...Neither this! Unfortunately!
"It doesn't work" type replies are next to useless. Use some savvy, we can't guess what your screen shows. How about you post any associated exception message or give us a little information at least?
Have you granted read / write access to the Oracle Directory from SYS (or another elevated user who created the directory) to the user running the code?

"what your screen shows".
The screen does show only the length of the blob - the message "Len: " (according to the dbms_output.put_line in this procedure). No error message, no exception message (although i catch if any appears), nothing else. For a reason, i haven't pinpointed yet, the file is not created, although a not zero-length blob is passed to this procedure!!!
When i search for the filename - given as parameter in the procedure- in the directory then no such file is created.
As regards the access to the oracle directory, the below statement has been issued:
GRANT EXECUTE, READ, WRITE ON DIRECTORY DR TO DD WITH GRANT OPTION;

Thanks,
Sim

Based on the information provided so far, the problem is probably not on the Oracle side.
How are you calling the procedure? Are you sure there's no exception caught and hidden by the calling process?
Are you able to read back the file, right after creation ?

declare
 myFile bfile := bfilename('MY_DIR','myFile.dat');
begin
 dbms_lob.open(myFile, dbms_lob.lob_readonly);
 dbms_output.put_line(dbms_lob.getlength(myFile));
 dbms_lob.close(myFile);
end;
/

I suspect the problem is in how you are calling yours or Billy's procs. You have probably suppressed an exception with when OTHERS then.
Run this code, without changing anything other than the Oracle directory vOracleDir, and report results :

declare
  vExists boolean;
  vFileLength number;
  vBlockSize number;
  vOracleDir varchar2(30) := 'YOUR_ORA_DIR';
  vFilename  varchar2(30) := 'YourFileName.blb';
  procedure savetofile(lob blob, directory varchar2, filename varchar2) is
    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
    lobsize := dbms_lob.getlength(lob);
    hfile   := utl_file.fopen(directory, filename, 'wb');
    begin
      if lobsize < bytesread then
        bytesread := lobsize;
      end if;
      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;
      utl_file.fclose(hfile);
    exception
      when OTHERS then
        if utl_file.is_open(hfile) then
          utl_file.fclose(hfile);
        end if;
        raise;
    end;
  end;
  function GetBlobOfLength(pLen integer) return Blob is
    vChunkLen integer := 1000;
    vChunk raw(1000);
    vCount integer := pLen;
    vResult Blob;
  begin
    if coalesce(pLen, 0) > 0 then
      vChunk := DBMS_Crypto.RandomBytes(vChunkLen);
      dbms_lob.createtemporary(vResult, false, dbms_lob.call);
      loop
        dbms_lob.writeappend(vResult, least(vChunkLen, vCount), vChunk);
        vCount := vCount - vChunkLen;
        exit when vCount <= 0;
      end loop;
    end if;
    return case when pLen = 0 then empty_blob() else vResult end;
  end;
begin
  SaveToFile(GetBlobOfLength(35000), vOracleDir, vFilename);
  UTL_FILE.FGetAttr(vOracleDir, vFileName, vExists, vFileLength, vBlockSize);
  if vExists then
    dbms_output.put_line('File '|| vFilename || ' written successfully to oracle dir ' || vOracleDir ||', File Length = ' || vFileLength);
  else
    dbms_output.put_line('File '|| vFilename || ' does not exist');
  end if;
end;
/

Eventually, i hit to an error message... I created a simple text file in the oracle directory and using your anonymous pl/sql block, I get the error messages:

ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
ORA-06512: at "SYS.DBMS_LOB", line 1031
ORA-06512: at line 4

But why... i have been granted read, write access to this dir.

As regards the call (the v_lob):
declare
  v_blob blob;
x number(10);
begin
begin
CSV_PROC(P_TABLENAME  =>'V_XRE_HIST_C2022 WHERE SUPPLIER_CODE = ''E'' '
                        , P_DIR      =>'DR'
                       , P_FILENAME =>'x.csv'
                       , P_BLOB     =>v_blob);
 select dbms_lob.getlength(v_blob) into x from dual;
 dbms_output.put_line(x);
convBlobToFile_(p_dir =>'DR',
                                   p_file =>'x.csv',
                                   p_lob =>v_blob);
exception
when others
then dbms_output.put_line('ERROR');
raise;
end;
end;
The variable x shows the blob length- which is then loaded via the variable v_blob to the routine convBlobToFile_ . This routine is supposed to create the filename x.csv in the oracle directory DR. The routine CSV_PROC is used to create the blob as the result of the sql query: select * from V_XRE_HIST_C2022 WHERE SUPPLIER_CODE = ''E''.
The message 'ERROR' has not appeared.

Thanks, for your response,
Sim

...Unfortunately, i cannot because i have not been granted the privs to execute the dbms_crypto package...
Thanks,
Sim

Paul,
i used your anonymous block, in another db schema - which does have the necessary privs for the dbms_crypto package- and the file created successfully in the oracle directory. For a reason, there is a problem creating a file connected with the previous db user...
Thanks,
Sim

Why? Oracle directory object privileges are just database privileges stating Oracle user U1 is allowed to access (read/write/execute depending on Oracle privilege) from Oracle. Will OS let it is completely different story. Creating Oracle directory object doesn't create OS directory. It must be created separately. In addition, UTL_FILE runs under OS user oracle (OS user Oracle is installed as, to be precise) therefore OS user oracle must have read permission on each parent directory on that directory path and read + write ondirectory itself (e.g. if Oracle directory object points to /dir1/dir2/dir3 OS directory /dir1/dir2/dir3 must exist and user Oracle must have read on /dir1, /dir1/dir2 and read + write on /dir1/dir2/dir3 in order to create file in /dir1/dir2/dir3).
SY.

Solomon beat me to it. I would check your Oracle directory's mapped OS dir exists and the Oracle process has access to it.
As for the dbms_crypto permissions on your original schema, change GetBlobOfLength proc to this :

  function GetBlobOfLength(pLen integer) return Blob is
    vChunkLen integer;
    vChunk raw(1000);
    vCount integer := pLen;
    vResult Blob;
  begin
    if coalesce(pLen, 0) > 0 then
      select hextoraw(listagg(to_char(level, 'fmxx')) within group (order by level))
      into vChunk
      from dual
      connect by level <= 255;
      vChunkLen := dbms_lob.getlength(vChunk);
      dbms_lob.createtemporary(vResult, false, dbms_lob.call);
      loop
        dbms_lob.writeappend(vResult, least(vChunkLen, vCount), vChunk);
        vCount := vCount - vChunkLen;
        exit when vCount <= 0;
      end loop;
    end if;
    return case when pLen = 0 then empty_blob() else vResult end;
  end;

In addition:

GRANT EXECUTE, READ, WRITE ON DIRECTORY DR TO DD WITH GRANT OPTION;

Is DD stored procedure owner or a role that is granted to stored procedure owner? If latter, then it is one reason procedure is failing. Definer rights stored procedures ignore role baed privileges.
SY.