Oracle PL/SQL Tutorial/Large Objects/BLOB — различия между версиями

Текущая версия на 10:09, 26 мая 2010

Содержание

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.

Oracle PL/SQL Tutorial/Large Objects/BLOB — различия между версиями

Текущая версия на 10:09, 26 мая 2010

Содержание

Adding Content to a BLOB

Close the BLOB

Creating Tables Containing BLOB Objects

Display the length of the BLOB

Initialize BLOB column data

INSERT a row with an empty blob

Loading a page to a BLOB

Obtain length of the populated BLOB

Open the BLOB

Populating a BLOB column from an external BFILE

UPDATE sets blob_column to a binary number:

Use update statement to change the value in blob type column

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты