Clob & Blob

Questo è un esempio:

procedure load_attachment(dir in varchar2, nam in varchar2) is
 fl BFILE;
 att blob;
 begin
 Dbms_Lob.createtemporary(att, TRUE, Dbms_Lob.CALL); 
 fl := BFileName(dir, nam);
 if (dbms_lob.fileexists(fl) = 1) then
 
 Dbms_Lob.fileopen(fl, Dbms_Lob.LOB_READONLY);
 Dbms_Lob.loadfromfile(att, fl, Dbms_Lob.getlength(fl));
 Dbms_Lob.fileclose(fl);

....
 else
 dbms_output.put_line('File doesn't exists!');
 end if;  
 end;

Se ottenete l' errore Oracle ORA-22275 - invalid LOB locator specified, forse avete dimenticato l' istruzione Dbms_Lob.createtemporary.

Questo codice PL/SQL converte una variabile clob a una blob.

function clob_to_blob(c in clob) return blob is
    -- typecasts CLOB to BLOB (binary conversion)
   pos PLS_INTEGER := 1;
   buffer RAW( 32767 );
   res BLOB;
   lob_len PLS_INTEGER := DBMS_LOB.getLength( c );
BEGIN
   DBMS_LOB.createTemporary( res, TRUE );
   DBMS_LOB.OPEN( res, DBMS_LOB.LOB_ReadWrite );
   
   LOOP
    buffer := UTL_RAW.cast_to_raw( DBMS_LOB.SUBSTR( c, 16000, pos ) );

    IF UTL_RAW.LENGTH( buffer ) > 0 THEN
       DBMS_LOB.writeAppend( res, UTL_RAW.LENGTH( buffer ), buffer );
    END IF;

    pos := pos + 16000;
    EXIT WHEN pos > lob_len;
   END LOOP;

   RETURN res;
END;

This PL/SQL code convert a clob variable to a blob one.

function clob_to_blob(c in clob) return blob is
    -- typecasts CLOB to BLOB (binary conversion)
   pos PLS_INTEGER := 1;
   buffer RAW( 32767 );
   res BLOB;
   lob_len PLS_INTEGER := DBMS_LOB.getLength( c );
BEGIN
   DBMS_LOB.createTemporary( res, TRUE );
   DBMS_LOB.OPEN( res, DBMS_LOB.LOB_ReadWrite );
   
   LOOP
    buffer := UTL_RAW.cast_to_raw( DBMS_LOB.SUBSTR( c, 16000, pos ) );

    IF UTL_RAW.LENGTH( buffer ) > 0 THEN
       DBMS_LOB.writeAppend( res, UTL_RAW.LENGTH( buffer ), buffer );
    END IF;

    pos := pos + 16000;
    EXIT WHEN pos > lob_len;
   END LOOP;

   RETURN res;
END;

Here's a code example:

procedure load_attachment(dir in varchar2, nam in varchar2) is
 fl BFILE;
 att blob;
 begin
 Dbms_Lob.createtemporary(att, TRUE, Dbms_Lob.CALL);
 fl := BFileName(dir, nam);
 if (dbms_lob.fileexists(fl) = 1) then
 
 Dbms_Lob.fileopen(fl, Dbms_Lob.LOB_READONLY);
 Dbms_Lob.loadfromfile(att, fl, Dbms_Lob.getlength(fl));
 Dbms_Lob.fileclose(fl);

....
 else
 dbms_output.put_line('File doesn't exists!');
 end if;  
 end;

If you get Oracle error ORA-22275 - invalid LOB locator specified, maybe you forgot the Dbms_Lob.createtemporary instruction.