Oracle PL/SQL Tutorial/Large Objects/DIRECTORY

CREATE OR REPLACE DIRECTORY

SQL> CREATE OR REPLACE DIRECTORY SAMPLE_FILES_DIR AS "C:\"
  2  /
Directory created.
SQL>
SQL> CREATE TABLE bfile_content (
  2    id           INTEGER PRIMARY KEY,
  3    bfile_column BFILE NOT NULL
  4  )
  5  /
Table created.
SQL>
SQL> INSERT INTO bfile_content (id,bfile_column) VALUES (1,BFILENAME("SAMPLE_FILES_DIR", "textContent.txt"));
1 row created.
SQL>
SQL> --A BFILE column doesn"t care what format a file is stored in.
SQL>
SQL> INSERT INTO bfile_content (id,bfile_column) VALUES (2,BFILENAME("SAMPLE_FILES_DIR", "binaryContent.doc"));
1 row created.
SQL>
SQL> select * from bfile_content;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
SQL>
SQL> drop directory SAMPLE_FILES_DIR;
Directory dropped.
SQL> drop table bfile_content;
Table dropped.
SQL>

Creating a Directory Object

Before you can store a pointer to a file in a BFILE column, you must first create a directory object.

The directory object represents the directory in the file system.

You create a directory object using the CREATE DIRECTORY statement.

To perform such a statement, you must have the CREATE ANY DIRECTORY database privilege.

CREATE OR REPLACE DIRECTORY SAMPLE_FILES_DIR AS "C:\";

When you create a directory object you must ensure that

The actual directory exists in the file system.

The user account has read permission on the directories and files.

34. 5. DIRECTORY 34. 5. 1. Creating a Directory Object 34. 5. 2. <A href="/Tutorial/Oracle/0660__Large-Objects/CREATEORREPLACEDIRECTORY.htm">CREATE OR REPLACE DIRECTORY</a>