Oracle BLOB data is saved as a file

Long time not to write the text, recently hollow write a little. Oracle database The number of domestic users is mainly in the enterprise, where there is a byte storage called blob, and can not be directly seen.

Sometimes in order to debug the need, you can pass:

Select Utl_raw. Cast_to_varchar2 (Dbms_lob. SUBSTR (XXX,1, fromwhere=9667796;

This SQL goes to string view, but it is inconvenient to turn out up to 2000 bytes at a time. The full text needs to be spelled through index.

In another case, if you are storing a picture, word, or other binary document in a non-text format, there is no way to view it from the method above. Here's a way to save the blob to the local disk on the machine where the database server is located.

Stored procedures:

1 CREATE OR REPLACE 2 PROCEDURESaveblob (filenameVARCHAR)3  as4 V_lob_loc BLOB;5V_bufferRAW(32767);6 v_buffer_size Binary_integer;7 V_amount Binary_integer;8V_offset Number( -) := 1;9V_chunksizeINTEGER;Ten v_out_file Utl_file. File_type; One  A BEGIN -     --query Criteria (modified here as required) -     SELECTcontent the      intoV_lob_loc -      fromObj_code_file -     WHEREId= 9667793; -  +     --size -V_chunksize:=Dbms_lob. Getchunksize (v_lob_loc); +     IF(v_chunksize< 32767) Then AV_buffer_size:=v_chunksize; at     ELSE -V_buffer_size:= 32767; -     END IF; -V_amount:=v_buffer_size; -  -     --Open inDbms_lob.OPEN(V_lob_loc, Dbms_lob. lob_readonly); -  to     --Write +V_out_file:=Utl_file. FOPEN ( -Location= 'Expdp_dir',  theFileName=filename, *Open_mode= 'WB', $Max_linesize= 32767);Panax Notoginseng  -      whileV_amount>=v_buffer_size the LOOP +Dbms_lob.READ( ALob_loc=V_lob_loc, theAmount=V_amount, +Offset=V_offset, -Buffer=v_buffer); $V_offset:=V_offset+V_amount; $ Utl_file. Put_raw ( -           file      =V_out_file, -Buffer=V_buffer, theAutoFlush=true); -Utl_file. FFLUSH (file =v_out_file);Wuyi     ENDLOOP; theUtl_file. FFLUSH (file =v_out_file); - Utl_file. FCLOSE (v_out_file); Wu  -     --Close File AboutDbms_lob.CLOSE(v_lob_loc); $ END;

The stored procedure is imported into Oracle, called by Saveblob (), and the parameter is the name of the file to be saved. The saved directory is in ' Expdp_dir ', via:

Select *  from All_directories;

You can view the physical path that corresponds to this directory.

After the stored procedure is executed, you can see the exported binaries.

Reprint Please specify original site: http://www.cnblogs.com/lekko/p/5624748.html

Oracle BLOB data is saved as a file