Oracle PL/SQL/Large Objects/bfile

BFILE Comparisons

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

BFILE File Operations

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

BFILENAME function

  
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.

Call BFILENAME to get pointer to a BFILE

  
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>

Create the BFILE directory

    

SQL> CREATE OR REPLACE DIRECTORY SAMPLE_FILES_DIR AS "C:\sample_files";
Directory created.
SQL>
SQL>
SQL> drop directory SAMPLE_FILES_DIR;
Directory dropped.

Insert value to bfile column

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

Two locators pointing to the same file

  
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>