в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:09:00Z

1 версия

Новая страница

== 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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Large Objects/BLOB - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия