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
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>
Close the BLOB
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.
Creating Tables Containing BLOB Objects
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>
Display the length of the BLOB
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.
Initialize BLOB column data
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>
INSERT a row with an empty blob
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.
Loading a page to a BLOB
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.
Obtain length of the populated BLOB
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.
Open the BLOB
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.
Populating a BLOB column from an external BFILE
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>
UPDATE sets blob_column to a binary number:
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>
Use update statement to change the value in blob type column
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.