Oracle series: Image Storage

Oracle series: Image Storage

I. What are basic operations for large objects?
See my blog: Oracle series: Lob big object processing
Http://blog.csdn.net/qfs_v/archive/2008/05/21/2464599.aspx
 
2. Image Storage or binary file storage
 1. Insert common data first. In case of large object columns, use empty_blob () to construct an empty pointer.
Example:
/*
Conn Scott/tiger;
Create tablespace ts5_21;
*/
Create Table mylob
(
No number (8) primary key,
Fname varchar2 (30 ),
Myfile blob
)
Lob (myfile) store
(
Tablespace ts5_21
Chunk 15 K
Disable storage in row
);

Insert into mylob values (1, 'img _ 0210. jpg ', empty_blob ());
 
2. Create the logical directory mydir
Create directory mydir as 'e:/Oracle ';
 
3. Declare a blob-type variable and use the select into statement to point it to the bucket pointed to by the empty pointer of empty_blob ().
Select myfile into blob type variable from mylob where no = 1 for update;
 
4. Declare a bfile type variable, associate the logical directory with the physical directory file, and use bfilename () to direct it to the file to be stored.
Bfile type variable: = bfilename ('mydir', 'img _ 0210. jpg ');
 
 5. Use the dbms_lob.open () method to open the file pointed to by the bfile type variable.
Dbms_lob.open (bfile type variable );
 
6. Use the dbms_lob.loadfromfile () method to read the file pointed to by the bfile type variable to the bucket pointed to by the Blob type variable.
Dbms_lob.loadfromfile (BLOB type variable, bfile type variable, dbms_lob.getlength (bfile type variable ));
 
7. Use the dbms_lob.close () method to close the file pointed to by the bfile variable.
Dbms_lob.close (bfile type variable );
 
8. Submit the transaction.
Commit;
 
Example;
Declare
Varb blob;
Varf bfile;
Begin
Select myfile into varb from mylob where no = 1 for update;
Varf: = bfilename ('mydir', 'img _ 0210. jpg ');
Dbms_lob.open (varf );
Dbms_lob.loadfromfile (varb, varf, dbms_lob.getlength (varf ));
Dbms_lob.close (varf );
Commit;
End;

-- View File Size
Declare
Varb blob;
Begin
Select myfile into varb from mylob where no = 1;
Dbms_output.put_line ('length: '| dbms_lob.getlength (varb ));
End;
 
Example: create a process to store images or binary files
Create or replace procedure setblob (vfilename varchar2)
As
Varf bfile;
Varb blob;
Vno number (8 );
Begin
Varf: = bfilename ('mydir', vfilename );
Dbms_lob.open (varf );
Select max (NO) into vno from mylob;
If vno is null then
Vno: = 1;
Else
Vno: = vno + 1;
End if;
Insert into mylob values (vno, vfilename, empty_blob ());
Select myfile into varb from mylob where no = vno for update;
Dbms_lob.loadfromfile (varb, varf, dbms_lob.getlength (varf ));
Dbms_lob.close (varf );
Commit;
End;

-- Execution Process
Exec setblob ('img _ 0210. jpg ');

-- View File Size
Declare
Varb blob;
Begin
Select myfile into varb from mylob where no = 2;
Dbms_output.put_line ('length: '| dbms_lob.getlength (varb ));
End;
 

Next article: Oracle series: SQL