Oracle lob field learning

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.