в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:02:05Z

1 версия

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

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

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

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

в 13:45, 26 мая 2010

Admin: 1 версия