Oracle lob field learning

Release date: Source: collected:

-- Insert bfile create or replace procedure insert_book (filename varchar2)
Book_file bfile: = NULL;
Bookexists Boolean: = false;
Begin
Book_file: = bfilename ('book _ text', filename );
Bookexists: = dbms_lob.fileexists (book_file) = 1;

If bookexists then
Insert into my_book_files values (select count (*) from my_book_files) + 1, book_file );
Dbms_output.put_line ('insert sucess! File: '| filename );
Else
Dbms_output.put_line ('not exists! File: '| filename );
End if;
Exception
When dbms_lob.noexist_directory then
Dbms_output.put_line ('error: '| sqlerrm );
When dbms_lob.invalid_directory then
Dbms_output.put_line ('error: '| sqlerrm );
When others then
Dbms_output.put_line ('unkown error: '| sqlerrm );
End insert_book;
/

Create or replace procedure insertpdf (filename varchar2) is
Fileloc bfile;
NID number;
N1_size integer;
Bfileexists Boolean: = false;
Begin
Fileloc: = bfilename ('your dir', filename );
Bfileexists: = dbms_lob.fileexists (fileloc) = 1;
If bfileexists = false then
Dbms_output.put_line (filename | 'not exists ');
Return;
End if;

N1_size: = dbms_lob.getlength (fileloc );
Dbms_output.put_line ('the length of '| filename | 'is' | n1_size );
Select count (*) + 1 into NID from pdftable;
Insert into pdftable (ID, pdffile)
Values (NID, fileloc );
Exception
When dbms_lob.noexist_directory then
Dbms_output.put_line ('error: '| sqlerrm );
When dbms_lob.invalid_directory then
Dbms_output.put_line ('error: '| sqlerrm );
When others then
Dbms_output.put_line ('unkown error: '| sqlerrm );
End;
/

-- Insert blob

Create or replace procedure insertimg (imgname varchar2) is
V_file_loc bfile;
V_image blob;
NID number;
Nimgsize integer;
Bfileexists Boolean: = false;
Begin
V_file_loc: = bfilename ('imagedir', imgname );
Bfileexists: = dbms_lob.fileexists (v_file_loc) = 1;
If bfileexists = false then
Dbms_output.put_line (imgname | 'not exists ');
Return;
End if;

Nimgsize: = dbms_lob.getlength (v_file_loc );
Dbms_output.put_line (imgname | 'size is '| nimgsize );
Dbms_output.put_line ('now inserting empty image row ');

Select count (*) + 1 into NID from imagetable;
Insert into imagetable (ID, image)
Values (NID, empty_blob)
Returning image into v_image;

Dbms_lob.fileopen (v_file_loc );
Dbms_output.put_line ('Open file ');
Dbms_lob.loadfromfile (v_image, v_file_loc, nimgsize );
Dbms_lob.fileclose (v_file_loc );
Commit;
Exception
When others then
Dbms_output.put_line ('error happen! '| Sqlerrm );
Dbms_lob.fileclose (v_file_loc );
End insertimg;
/

-- ===================================================== ============

SQL> Create Table view_sites_info (
2 site_id number (3 ),
3 audio blob default empty_blob (),
4 Document clob default empty_clob (),
5 video_file bfile default null
6 );

The table has been created.

SQL> commit;

Submitted.

SQL> @ E:/writelob

The PL/SQL process is successfully completed.

SQL> DESC view_sites_info;
Is the name empty? Type
-----------------------------------------------------------------------------
Site_id number (3)
Audio blob
Document clob
Video_file binary file lob

SQL & gt; select document from view_sites_info where site_id = 100;

Document
--------------------------------------------------------------------------------
This is a writing example
SQL> DESC view_sites_info
Is the name empty? Type
-----------------------------------------------------------------------------
Site_id number (3)
Audio blob
Document clob
Video_file binary file lob

SQL> insert into blobtest values (1, bfilename ('tempdir ',
'C:/Documents and Settings/Administrator/My Documents ents/My
Pictures/tu1.jpg '));
Insert into blobtest values (1,
Bfilename ('tempdir', 'c:/Documents and Settings/Administrator/My
Documents/my pictures/tu1.jpg '))
*
Error is located in row 1st:
ORA-00932: inconsistent data types

SQL> DESC bfiletest
Is the name empty? Type
-----------------------------------------------------------------------------
ID number (3)
Fname binary file lob

SQL> insert into bfiletest values (1, bfilename ('tempdir ',
'C:/Documents and Settings/Administrator/My Documents ents/My
Pictures/tu1.jpg '));

One row has been created.

SQL> Get E:/insertimg
1 create or replace procedure img_insert (
2 TID varchar2,
3 filename varchar2)
4 f_lob bfile;
5 B _lob blob;
6 begin
7 insert into image_lob (t_id, t_image) values (TID, empty_blob () return t_image into B _lob;
8 f_lob: = bfilename ('images', filename );
9 dbms_lob.fileopen (f_lob, dbms_lob.file_readonly );
10 dbms_lob.loadfromfile (B _lob, f_lob, dbms_lob.getlength (f_lob ));
11 dbms_lob.fileclose (f_lob );
12 commit;
13 * end;
SQL> r
1 create or replace procedure img_insert (
2 TID varchar2,
3 filename varchar2)
4 f_lob bfile;
5 B _lob blob;
6 begin
7 insert into image_lob (t_id, t_image) values (TID, empty_blob () return t_image into B _lob;
8 f_lob: = bfilename ('images', filename );
9 dbms_lob.fileopen (f_lob, dbms_lob.file_readonly );
10 dbms_lob.loadfromfile (B _lob, f_lob, dbms_lob.getlength (f_lob ));
11 dbms_lob.fileclose (f_lob );
12 commit;
13 * end;

SQL> Create Table image_lob (t_id varchar2 (5) not null, t_image blob not null); the table has been created.

SQL> commit;

Submitted.

SQL> Get E:/insertimg
1 create or replace procedure img_insert (
2 TID varchar2,
3 filename varchar2)
4 f_lob bfile;
5 B _lob blob;
6 begin
7 insert into image_lob (t_id, t_image) values (TID, empty_blob () return t_image into B _lob;
8 f_lob: = bfilename ('images', filename );
9 dbms_lob.fileopen (f_lob, dbms_lob.file_readonly );
10 dbms_lob.loadfromfile (B _lob, f_lob, dbms_lob.getlength (f_lob ));
11 dbms_lob.fileclose (f_lob );
12 commit;
13 * end;
SQL> r
1 create or replace procedure img_insert (
2 TID varchar2,
3 filename varchar2)
4 f_lob bfile;
5 B _lob blob;
6 begin
7 insert into image_lob (t_id, t_image) values (TID, empty_blob () return t_image into B _lob;
8 f_lob: = bfilename ('images', filename );
9 dbms_lob.fileopen (f_lob, dbms_lob.file_readonly );
10 dbms_lob.loadfromfile (B _lob, f_lob, dbms_lob.getlength (f_lob ));
11 dbms_lob.fileclose (f_lob );
12 commit;
13 * end;

The process has been created.

SQL> commit;

Submitted.

SQL> commit;

Submitted.

SQL> $ CLS

SQL> @ E:/insertimg

The process has been created.

SQL> commit;

Submitted.

SQL> exec img_insert ('1', 'e:/tu1.jpg ');

The PL/SQL process is successfully completed.

SQL> @ E:/insertimg

The process has been created.

SQL> exec img_insert ('2', 'e:/tu2.jpg ');

The PL/SQL process is successfully completed.

SQL> select count (*) from image_lob;

Count (*)
----------
3

SQL> @ E:/insertimg

The process has been created.
The PL/SQL process is successfully completed.

SQL> @ E:/insertimg

The process has been created.

The PL/SQL process is successfully completed.

SQL> @ E:/insertimg

The process has been created.

The PL/SQL process is successfully completed.

SQL> @ E:/insertimg

The process has been created.

The PL/SQL process is successfully completed.

SQL> Get E:/insertimg
1 create or replace procedure "img_insert "(
2 TID varchar2,
3 filename varchar2)
4 f_lob bfile;
5 B _lob blob;
6 begin
7 dbms_output.put_line ('now begin ');
8 insert into image_lob (t_id, t_image) values (TID, empty_blob () return t_image into B _lob;
9 f_lob: = bfilename ('images', filename );
10 dbms_output.put_line ('Open SUCCESS ');
11 dbms_output.put_line ('Now open: '| filename );
12 dbms_lob.fileopen (f_lob, dbms_lob.file_readonly );
13 dbms_lob.loadfromfile (B _lob, f_lob, dbms_lob.getlength (f_lob ));
14 dbms_lob.fileclose (f_lob );
15 commit;
16 exception
17 when others
18 then
19 dbms_output.put_line ('others' exception' | sqlerrm );
20 * end;
21/

The process has been created.

SQL> r
1 create or replace procedure "img_insert "(
2 TID varchar2,
3 filename varchar2)
4 f_lob bfile;
5 B _lob blob;
6 begin
7 dbms_output.put_line ('now begin ');
8 insert into image_lob (t_id, t_image) values (TID, empty_blob () return t_image into B _lob;
9 f_lob: = bfilename ('images', filename );
10 dbms_output.put_line ('Open SUCCESS ');
11 dbms_output.put_line ('Now open: '| filename );
12 dbms_lob.fileopen (f_lob, dbms_lob.file_readonly );
13 dbms_lob.loadfromfile (B _lob, f_lob, dbms_lob.getlength (f_lob ));
14 dbms_lob.fileclose (f_lob );
15 commit;
16 exception
17 when others
18 then
19 dbms_output.put_line ('others' exception' | sqlerrm );
20 * end;

SQL> select table_name from user_tables;

Table_name
------------------------------
Anydatatab
Bfiletest
Blobtest
Bonus
Dept
EMP
Image_lob
Lineitem_cv
Lineitem_dp
Salgrade
Tab2

Table_name
------------------------------
Test
Test2
View_sites_info

14 rows have been selected.