Oracle PL/SQL/Large Objects/bfile
Содержание
BFILE Comparisons
<source lang="sql">
SQL> SQL> CREATE OR REPLACE DIRECTORY books_Dir AS "C:\BOOKS"; Directory created. SQL> SQL> DECLARE
2 v_FILE1 BFILE;
3 v_FILE2 BFILE;
4 v_FILE3 BFILE;
5 v_GETLENGTH1 INTEGER;
6 v_GETLENGTH2 INTEGER;
7 v_GETLENGTH3 INTEGER;
8 v_COMPARELENGTH INTEGER;
9 v_COMPARERESULT INTEGER;
10
11 BEGIN
12 v_FILE1 := BFILENAME("BOOKS_DIR","1.GIF");
13 v_FILE2 := BFILENAME("BOOKS_DIR","2.GIF");
14 v_FILE3 := BFILENAME("BOOKS_DIR","5.GIF");
15
16 DBMS_LOB.FILEOPEN(v_FILE1);
17 DBMS_LOB.FILEOPEN(v_FILE2);
18 DBMS_LOB.FILEOPEN(v_FILE3);
19
20 v_GETLENGTH1 := DBMS_LOB.GETLENGTH(v_FILE1);
21 v_GETLENGTH2 := DBMS_LOB.GETLENGTH(v_FILE2);
22 v_GETLENGTH3 := DBMS_LOB.GETLENGTH(v_FILE3);
23
24 IF v_GETLENGTH1 < v_GETLENGTH2 THEN
25 v_COMPARELENGTH := v_GETLENGTH1;
26 ELSE
27 v_COMPARELENGTH := v_GETLENGTH2;
28 END IF;
29
30 v_COMPARERESULT := DBMS_LOB.ruPARE(v_FILE1,v_FILE2,v_COMPARELENGTH,1,1);
31
32 IF v_COMPARERESULT = 0 THEN
33 DBMS_OUTPUT.PUT_LINE("Both Files Are Identical");
34 ELSE
35 DBMS_OUTPUT.PUT_LINE("Both Files Are Different");
36 END IF;
37
38 IF v_GETLENGTH1 < v_GETLENGTH3 THEN
39 v_COMPARELENGTH := v_GETLENGTH1;
40 ELSE
41 v_COMPARELENGTH := v_GETLENGTH3;
42 END IF;
43
44 v_COMPARERESULT := DBMS_LOB.ruPARE(v_FILE1,v_FILE3,v_COMPARELENGTH,1,1);
45
46 IF v_COMPARERESULT = 0 THEN
47 DBMS_OUTPUT.PUT_LINE("Both Files Are Identical");
48 ELSE
49 DBMS_OUTPUT.PUT_LINE("Both Files Are Different");
50 END IF;
51
52 DBMS_LOB.FILECLOSEALL;
53 END;
54 /
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 16
SQL> --
</source>
BFILE File Operations
<source lang="sql">
SQL> SQL> CREATE OR REPLACE DIRECTORY books_Dir AS "C:\BOOKS"; Directory created. SQL> SQL> SQL> DECLARE
2 v_BOOKFILE BFILE;
3 v_DIRNAME VARCHAR2(30);
4 v_LOCATION VARCHAR2(2000);
5 v_FILEISOPEN INTEGER;
6 v_FILEEXISTS INTEGER;
7
8 BEGIN
9 v_BOOKFILE := BFILENAME("BOOKS_DIR","BOOK1.GIF");
10 v_FILEISOPEN := DBMS_LOB.FILEISOPEN(v_BOOKFILE);
11
12 v_FILEEXISTS := DBMS_LOB.FILEEXISTS(v_BOOKFILE);
13
14 IF v_FILEEXISTS = 1 THEN
15 DBMS_OUTPUT.PUT_LINE("The file exists");
16 ELSE
17 DBMS_OUTPUT.PUT_LINE("The file cannot be found");
18 END IF;
19
20 IF v_FILEISOPEN = 1 THEN --Determine actions if file is opened
21 DBMS_OUTPUT.PUT_LINE("The file is open");
22 ELSE
23 DBMS_OUTPUT.PUT_LINE("Opening the file");
24 DBMS_LOB.FILEOPEN(v_BOOKFILE);
25 END IF;
26 DBMS_LOB.FILEGETNAME(v_BOOKFILE,v_DIRNAME,v_LOCATION);
27 DBMS_OUTPUT.PUT_LINE("The Directory Object is: " || v_DIRNAME ||
28 " The File Name is: " || v_LOCATION);
29
30 DBMS_LOB.FILECLOSE(v_BOOKFILE); -- Close the BFILE
31
32 END;
33 /
The file cannot be found Opening the file 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 24
SQL> SQL> SQL> --
</source>
BFILENAME function
<source lang="sql">
SQL> SQL> CREATE TABLE lobdemo (
2 key NUMBER, 3 bfile_col BFILE);
Table created. SQL> SQL> CREATE DIRECTORY utils AS "/home/utils";
SQL> SQL> CREATE DIRECTORY code AS "/home/code";
SQL> SQL> INSERT INTO lobdemo (key, bfile_col)
2 VALUES (10, BFILENAME("utils", "file1.txt"));
1 row created. SQL> SQL> INSERT INTO lobdemo (key, bfile_col)
2 VALUES (11, BFILENAME("utils", "file2.txt"));
1 row created. SQL> SQL> SQL> drop table lobdemo; Table dropped.
</source>
Call BFILENAME to get pointer to a BFILE
<source lang="sql">
SQL> SQL> CREATE TABLE lobdemo (
2 key NUMBER, 3 bfile_col BFILE);
Table created. SQL> SQL> SQL> DECLARE
2 v_BFILE1 BFILE;
3 v_BFILE2 BFILE;
4 BEGIN
5 v_BFILE1 := BFILENAME("code", "file1.c");
6 v_BFILE2 := BFILENAME("code", "file2.c");
7
8 INSERT INTO lobdemo (key, bfile_col)
9 VALUES (12, v_BFILE1);
10
11 UPDATE lobdemo
12 SET bfile_col = v_BFILE2
13 WHERE key = 10;
14
15 COMMIT;
16 END;
17 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table lobdemo; Table dropped. SQL> SQL>
</source>
Create the BFILE directory
<source lang="sql">
SQL> CREATE OR REPLACE DIRECTORY SAMPLE_FILES_DIR AS "C:\sample_files"; Directory created. SQL> SQL> SQL> drop directory SAMPLE_FILES_DIR; Directory dropped.
</source>
Insert value to bfile column
<source lang="sql">
SQL> SQL> SQL> create table bfile_table(
2 name varchar2(255), 3 the_file bfile );
Table created. SQL> insert into bfile_table values ( "doc 1", bfilename( "WROX_DIR", "my_doc.pdf" ) ); 1 row created. SQL> SQL> select * from bfile_table; SP2-0678: Column or attribute type can not be displayed by SQL*Plus SQL> SQL> drop table bfile_table; Table dropped. SQL> SQL> SQL> --
</source>
Two locators pointing to the same file
<source lang="sql">
SQL> SQL> CREATE TABLE lobdemo (
2 key NUMBER, 3 bfile_col BFILE);
Table created. SQL> SQL> INSERT INTO lobdemo (key, bfile_col)
2 VALUES (15, BFILENAME("xxxx", "yyyy"));
1 row created. SQL> SQL> INSERT INTO lobdemo (key, bfile_col)
2 VALUES (19, BFILENAME("code", "file3.c"));
1 row created. SQL> SQL> INSERT INTO lobdemo (key, bfile_col)
2 VALUES (20, BFILENAME("code", "file3.c"));
1 row created. SQL> SQL> DELETE FROM lobdemo
2 WHERE key = 20;
1 row deleted. SQL> SQL> drop table lobdemo; Table dropped. SQL> SQL>
</source>
