ORACLE与非结构化数据

botang

1. select  * from dba_directories;
   
2. 
   
3. select  * from dba_tab_privs  tp where tp.grantee='HR';
   
4. 
   
5. create table t071fi11_blob ( pic#  number(2),   pic_name  varchar2(20),   photo  blob ) ;
   
6. 
   
7. CREATE OR REPLACE PROCEDURE  proc071fi11_blob (  p_1 number, p_2 varchar2,  p_dir  VARCHAR2, p_file  VARCHAR2)
   
8. IS
   
9.    v_f  BFILE;
   
10.    v_b blob;
    
11. BEGIN
    
12.     INSERT INTO t071fi11_blob values(p_1,   p_2,   EMPTY_BLOB ()) RETURN   photo    into v_b;
    
13.     v_f := BFILENAME (p_dir, p_file);
    
14.    DBMS_LOB.FILEOPEN  (v_f, DBMS_LOB.FILE_READONLY);
    
15.    DBMS_LOB.LOADFROMFILE (v_b, v_f,   DBMS_LOB.GETLENGTH (v_f));
    
16.    DBMS_LOB.FILECLOSE (v_f);
    
17.    commit;
    
18. end;
    
19. /
    
20. 
    
21. begin
    
22.    proc071fi11_blob(1, 'RedHat is good', 'DIR1', 'c.jpg');
    
23. end;
    
24. /
    
25. 
    
26. select  * from t071fi11_blob;
    
27.    

复制代码