联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
1.插入一条blob数据
SQL> create directory ULTLOBDIR as '/home/oracle'; Directory created. SQL> create table blobtest(col1 BLOB); Table created. SQL> declare a_blob BLOB; 2 3 bfile_name BFILE := BFILENAME('ULTLOBDIR','tt.txt.bak'); 4 begin 5 insert into blobtest values (empty_blob()) 6 returning col1 into a_blob; 7 dbms_lob.fileopen(bfile_name); 8 dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name)); 9 dbms_lob.fileclose(bfile_name); 10 commit; 11 end; 12 / PL/SQL procedure successfully completed. SQL> select dbms_lob.getlength(col1) from blobtest; DBMS_LOB.GETLENGTH(COL1) ------------------------ 4829 SQL> !pwd /home/oracle SQL> !ls -l tt.txt.bak -rw-r--r-- 1 oracle oinstall 4829 03-19 17:26 tt.txt.bak
2.创建存储过程
CREATE OR REPLACE PROCEDURE RETRIEVE_LOB_TO_FILE(TEMP_BLOB IN BLOB, FILE_PATH IN VARCHAR2, FILE_NAME IN VARCHAR2) IS DATA_BUFFER RAW(32767); POSITION INTEGER := 1; FILEHANDLE UTL_FILE.FILE_TYPE; ERROR_NUMBER NUMBER; ERROR_MESSAGE VARCHAR2(100); BLOB_LENGTH INTEGER; CHUNK_SIZE BINARY_INTEGER := 32767; BEGIN BLOB_LENGTH := DBMS_LOB.GETLENGTH(TEMP_BLOB); FILEHANDLE := UTL_FILE.FOPEN(FILE_PATH, FILE_NAME, 'wb', 1024); WHILE POSITION < BLOB_LENGTH LOOP DBMS_LOB.READ(TEMP_BLOB, CHUNK_SIZE, POSITION, DATA_BUFFER); UTL_FILE.PUT_RAW(FILEHANDLE, DATA_BUFFER); POSITION := POSITION + CHUNK_SIZE; DATA_BUFFER := NULL; END LOOP; UTL_FILE.FCLOSE(FILEHANDLE); EXCEPTION WHEN OTHERS THEN BEGIN ERROR_NUMBER := SQLCODE; ERROR_MESSAGE := SUBSTR(SQLERRM, 1, 100); DBMS_OUTPUT.PUT_LINE('Error #: ' || ERROR_NUMBER); DBMS_OUTPUT.PUT_LINE('Error Message: ' || ERROR_MESSAGE); UTL_FILE.FCLOSE_ALL; END; END; /
3.测试读取blob到系统
SQL> declare 2 tmp_blob blob default empty_blob(); 3 begin 4 dbms_lob.createtemporary(tmp_blob, true); 5 select col1 into tmp_blob from blobtest; 6 retrieve_lob_to_file (tmp_blob, 'ULTLOBDIR','xifenfei.txt'); 7 end; 8 / PL/SQL procedure successfully completed. SQL> !pwd /home/oracle SQL> !ls -l xifenfei.txt -rw-r--r-- 1 oracle oinstall 4829 03-20 23:44 xifenfei.txt