Oracle PL/SQL Tutorial/Large Objects/BLOB
Содержание
- 1 Adding Content to a BLOB
- 2 Close the BLOB
- 3 Creating Tables Containing BLOB Objects
- 4 Display the length of the BLOB
- 5 Initialize BLOB column data
- 6 INSERT a row with an empty blob
- 7 Loading a page to a BLOB
- 8 Obtain length of the populated BLOB
- 9 Open the BLOB
- 10 Populating a BLOB column from an external BFILE
- 11 UPDATE sets blob_column to a binary number:
- 12 Use update statement to change the value in blob type column
Adding Content to a BLOB
<source lang="sql">
SQL> SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY, 3 blob_column BLOB NOT NULL 4 );
Table created. SQL> SQL> INSERT INTO myTable(id, blob_column) VALUES (1, EMPTY_BLOB()); 1 row created. SQL> SQL> UPDATE myTable
2 SET blob_column = "VVVVV" 3 WHERE id = 1;
SET blob_column = "VVVVV"
*
ERROR at line 2: ORA-01465: invalid hex number
SQL> SQL> SELECT *
2 FROM myTable;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus SQL> SQL> drop table myTable; Table dropped. SQL></source>
Close the BLOB
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE myBlob
2 (id NUMBER PRIMARY KEY, 3 blob_data BLOB);
Table created. SQL> SQL> SQL> SQL> DECLARE
2 bfile_pointer BFILE;
3 blob_pointer BLOB;
4 bfile_offset NUMBER :=1;
5 blob_offset NUMBER :=1;
6 tot_len INTEGER;
7 BEGIN
8
9 INSERT INTO myBlob VALUES (1,EMPTY_BLOB());
10
11 SELECT blob_data INTO blob_pointer FROM myBlob WHERE id =1 FOR UPDATE;
12
13 bfile_pointer :=bfilename("BFILE_DIR","test.bmp");
14
15 dbms_lob.fileopen(bfile_pointer,dbms_lob.file_readonly);
16
17 dbms_lob.OPEN(blob_pointer,dbms_lob.lob_readwrite);
18
19 dbms_lob.LOADBLOBFROMFILE(blob_pointer,bfile_pointer,dbms_lob.lobmaxsize,bfile_offset,blob_offset);
20
21 tot_len :=DBMS_LOB.GETLENGTH(blob_pointer);
22
23 dbms_lob.close(blob_pointer);
24
25 dbms_lob.fileclose(bfile_pointer);
26
27 DBMS_OUTPUT.PUT_LINE(TO_CHAR(tot_len));
28 END;
29 /
DECLARE
ERROR at line 1: ORA-22288: file or LOB operation FILEOPEN failed The system cannot find the path specified. ORA-06512: at "SYS.DBMS_LOB", line 523 ORA-06512: at line 15
SQL> SQL> drop table myBlob; Table dropped.</source>
Creating Tables Containing BLOB Objects
<source lang="sql">
SQL> SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY, 3 blob_column BLOB NOT NULL 4 );
Table created. SQL> SQL> desc myTable;
Name Null? Type --------------- ID NOT NULL NUMBER(38) BLOB_COLUMN NOT NULL BLOB
SQL> SQL> drop table myTable; Table dropped. SQL></source>
Display the length of the BLOB
<source lang="sql">
SQL> SQL> SQL> SQL> CREATE TABLE myBlob
2 (id NUMBER PRIMARY KEY, 3 blob_data BLOB);
Table created. SQL> SQL> SQL> SQL> DECLARE
2 bfile_pointer BFILE;
3 blob_pointer BLOB;
4 bfile_offset NUMBER :=1;
5 blob_offset NUMBER :=1;
6 tot_len INTEGER;
7 BEGIN
8
9 INSERT INTO myBlob VALUES (1,EMPTY_BLOB());
10
11 SELECT blob_data INTO blob_pointer FROM myBlob WHERE id = 1 FOR UPDATE;
12
13 bfile_pointer :=bfilename("BFILE_DIR","test.bmp");
14
15 dbms_lob.fileopen(bfile_pointer,dbms_lob.file_readonly);
16
17 dbms_lob.OPEN(blob_pointer,dbms_lob.lob_readwrite);
18
19 dbms_lob.LOADBLOBFROMFILE(blob_pointer,bfile_pointer,dbms_lob.lobmaxsize,bfile_offset,blob_offset);
20
21 tot_len :=DBMS_LOB.GETLENGTH(blob_pointer);
22
23 dbms_lob.close(blob_pointer);
24
25 dbms_lob.fileclose(bfile_pointer);
26
27 DBMS_OUTPUT.PUT_LINE(TO_CHAR(tot_len));
28 END;
29 /
DECLARE
ERROR at line 1: ORA-22288: file or LOB operation FILEOPEN failed The system cannot find the path specified. ORA-06512: at "SYS.DBMS_LOB", line 523 ORA-06512: at line 15
SQL> SQL> drop table myBlob; Table dropped.</source>
Initialize BLOB column data
<source lang="sql">
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY, 3 blob_column BLOB NOT NULL 4 );
Table created. SQL> SQL> INSERT INTO myTable(id, blob_column) VALUES (1, EMPTY_BLOB()); 1 row created. SQL> SQL> drop table myTable; Table dropped. SQL></source>
INSERT a row with an empty blob
<source lang="sql">
SQL> SQL> CREATE TABLE myBlob
2 (id NUMBER PRIMARY KEY, 3 blob_data BLOB);
Table created. SQL> SQL> SQL> SQL> DECLARE
2 bfile_pointer BFILE;
3 blob_pointer BLOB;
4 bfile_offset NUMBER :=1;
5 blob_offset NUMBER :=1;
6 tot_len INTEGER;
7 BEGIN
8
9 INSERT INTO myBlob VALUES (1,EMPTY_BLOB());
10
11 SELECT blob_data INTO blob_pointer FROM myBlob WHERE id = 1 FOR UPDATE;
12
13 bfile_pointer :=bfilename("BFILE_DIR","test.bmp");
14
15 dbms_lob.fileopen(bfile_pointer,dbms_lob.file_readonly);
16
17 dbms_lob.OPEN(blob_pointer,dbms_lob.lob_readwrite);
18
19 dbms_lob.LOADBLOBFROMFILE(blob_pointer,bfile_pointer,dbms_lob.lobmaxsize,bfile_offset, blob_offset);
20
21 tot_len :=DBMS_LOB.GETLENGTH(blob_pointer);
22
23 dbms_lob.close(blob_pointer);
24
25 dbms_lob.fileclose(bfile_pointer);
26
27 DBMS_OUTPUT.PUT_LINE(TO_CHAR(tot_len));
28 END;
29 /
DECLARE
ERROR at line 1: ORA-22288: file or LOB operation FILEOPEN failed The system cannot find the path specified. ORA-06512: at "SYS.DBMS_LOB", line 523 ORA-06512: at line 15
SQL> SQL> drop table myBlob; Table dropped.</source>
Loading a page to a BLOB
<source lang="sql">
SQL> SQL> SQL> create table catalog
2 (id number, 3 name VARCHAR2(2000), 4 manual_cl CLOB, 5 firstpage_bl BLOB, 6 mastertxt_bf BFILE 7 );
Table created. SQL> SQL> SQL> create directory IO as "C:\IO"; Directory created. SQL> --grant read, write on directory IO to public; SQL> SQL> insert into catalog(id, name, mastertxt_bf) values (1, "TEXT.HTM", BFILENAME ("IO", "text.htm")); 1 row created. SQL> SQL> SQL> declare
2 v_file_bf BFILE:= BFILENAME ("IO","picture.gif");
3 v_firstpage_bl BLOB;
4 src_offset_nr NUMBER := 1;
5 dst_offset_nr NUMBER := 1;
6 begin
7 update catalog set firstpage_bl = EMPTY_BLOB() where id = 1;
8
9 select firstpage_bl into v_firstpage_bl from catalog where id = 1;
10
11 DBMS_LOB.fileopen (v_file_bf, DBMS_LOB.file_readonly);
12 DBMS_LOB.loadblobfromfile (v_firstpage_bl, v_file_bf,
13 DBMS_LOB.getlength (v_file_bf),
14 dst_offset_nr, src_offset_nr);
15 DBMS_LOB.fileclose (v_file_bf);
16 end;
17 /
declare
ERROR at line 1: ORA-22288: file or LOB operation FILEOPEN failed The system cannot find the path specified. ORA-06512: at "SYS.DBMS_LOB", line 523 ORA-06512: at line 11
SQL> SQL> drop directory IO; Directory dropped. SQL> drop table catalog; Table dropped.</source>
Obtain length of the populated BLOB
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE myBlob
2 (id NUMBER PRIMARY KEY, 3 blob_data BLOB);
Table created. SQL> SQL> SQL> SQL> DECLARE
2 bfile_pointer BFILE;
3 blob_pointer BLOB;
4 bfile_offset NUMBER :=1;
5 blob_offset NUMBER :=1;
6 tot_len INTEGER;
7 BEGIN
8
9 INSERT INTO myBlob VALUES (1,EMPTY_BLOB());
10
11 SELECT blob_data INTO blob_pointer FROM myBlob WHERE id = 1 FOR UPDATE;
12
13 bfile_pointer :=bfilename("BFILE_DIR","test.bmp");
14
15 dbms_lob.fileopen(bfile_pointer,dbms_lob.file_readonly);
16
17 dbms_lob.OPEN(blob_pointer,dbms_lob.lob_readwrite);
18
19 dbms_lob.LOADBLOBFROMFILE(blob_pointer,bfile_pointer,dbms_lob.lobmaxsize,bfile_offset,blob_offset);
20
21 tot_len :=DBMS_LOB.GETLENGTH(blob_pointer);
22
23 dbms_lob.close(blob_pointer);
24
25 dbms_lob.fileclose(bfile_pointer);
26
27 DBMS_OUTPUT.PUT_LINE(TO_CHAR(tot_len));
28 END;
29 /
DECLARE
ERROR at line 1: ORA-22288: file or LOB operation FILEOPEN failed The system cannot find the path specified. ORA-06512: at "SYS.DBMS_LOB", line 523 ORA-06512: at line 15
SQL> SQL> drop table myBlob; Table dropped.</source>
Open the BLOB
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE myBlob
2 (id NUMBER PRIMARY KEY, 3 blob_data BLOB);
Table created. SQL> SQL> SQL> SQL> DECLARE
2 bfile_pointer BFILE;
3 blob_pointer BLOB;
4 bfile_offset NUMBER :=1;
5 blob_offset NUMBER :=1;
6 tot_len INTEGER;
7 BEGIN
8
9 INSERT INTO myBlob VALUES (1,EMPTY_BLOB());
10
11 SELECT blob_data INTO blob_pointer FROM myBlob WHERE id = 1 FOR UPDATE;
12
13 bfile_pointer :=bfilename("BFILE_DIR","test.bmp");
14
15 dbms_lob.fileopen(bfile_pointer,dbms_lob.file_readonly);
16
17 dbms_lob.OPEN(blob_pointer,dbms_lob.lob_readwrite);
18
19 dbms_lob.LOADBLOBFROMFILE(blob_pointer,bfile_pointer,dbms_lob.lobmaxsize,bfile_offset,blob_offset);
20
21 tot_len :=DBMS_LOB.GETLENGTH(blob_pointer);
22
23 dbms_lob.close(blob_pointer);
24
25 dbms_lob.fileclose(bfile_pointer);
26
27 DBMS_OUTPUT.PUT_LINE(TO_CHAR(tot_len));
28 END;
29 /
DECLARE
ERROR at line 1: ORA-22288: file or LOB operation FILEOPEN failed The system cannot find the path specified. ORA-06512: at "SYS.DBMS_LOB", line 523 ORA-06512: at line 15
SQL> SQL> drop table myBlob; Table dropped.</source>
Populating a BLOB column from an external BFILE
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE myBlob
2 (id NUMBER PRIMARY KEY, 3 blob_data BLOB);
Table created. SQL> SQL> SQL> SQL> DECLARE
2 bfile_pointer BFILE;
3 blob_pointer BLOB;
4 bfile_offset NUMBER :=1;
5 blob_offset NUMBER :=1;
6 tot_len INTEGER;
7 BEGIN
8
9 INSERT INTO myBlob VALUES (1,EMPTY_BLOB());
10
11 SELECT blob_data INTO blob_pointer FROM myBlob WHERE id = 1 FOR UPDATE;
12
13 bfile_pointer :=bfilename("BFILE_DIR","test.bmp");
14
15 dbms_lob.fileopen(bfile_pointer,dbms_lob.file_readonly);
16
17 dbms_lob.OPEN(blob_pointer,dbms_lob.lob_readwrite);
18
19 dbms_lob.LOADBLOBFROMFILE(blob_pointer,bfile_pointer,dbms_lob.lobmaxsize,bfile_offset,blob_offset);
20
21 tot_len :=DBMS_LOB.GETLENGTH(blob_pointer);
22
23 dbms_lob.close(blob_pointer);
24
25 dbms_lob.fileclose(bfile_pointer);
26
27 DBMS_OUTPUT.PUT_LINE(TO_CHAR(tot_len));
28 END;
29 /
DECLARE
ERROR at line 1: ORA-22288: file or LOB operation FILEOPEN failed The system cannot find the path specified. ORA-06512: at "SYS.DBMS_LOB", line 523 ORA-06512: at line 15
SQL> SQL> drop table myBlob; Table dropped. SQL></source>
UPDATE sets blob_column to a binary number:
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY, 3 blob_column BLOB NOT NULL 4 );
Table created. SQL> SQL> INSERT INTO myTable(id, blob_column) VALUES (1, EMPTY_BLOB()); 1 row created. SQL> SQL> SQL> UPDATE myTable
2 SET blob_column = "100111010101011111" 3 WHERE id = 1;
1 row updated. SQL> SQL> SQL> SELECT *
2 FROM myTable;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
Use update statement to change the value in blob type column
<source lang="sql">
SQL> SQL> create table demo
2 ( id int primary key, 3 theBlob blob 4 ) 5 /
Table created. SQL> SQL> SQL> update demo set theBlob = "Hello World"
2 where id = 1 3 /
0 rows updated. SQL> SQL> SQL> SQL> drop table demo; Table dropped.</source>
