/*
 * create_bfile_directory.sql
 * Chapter 15, Oracle Database XE PHP Web Programming
 * by Michael McLaughlin
 *
 * This script checks the directory to ensure that the BFILE
 * directory is set, and it must be run by the SYSTEM user. You
 * need to change it to mirror your environment.
 */

-- Unremark for debugging script.
SET ECHO ON
SET FEEDBACK ON
SET PAGESIZE 49999
SET SERVEROUTPUT ON SIZE 1000000

-- Conditionally drop objects.
BEGIN
  FOR i IN (SELECT   directory_name
            FROM     dba_directories
            WHERE    directory_name = 'PRESIDENT' ) LOOP
    EXECUTE IMMEDIATE 'DROP DIRECTORY my_directory';
  END LOOP;
END;
/

-- Create directory.
CREATE DIRECTORY my_directory AS
'C:\Program Files\Apache Group\Apache2\htdocs\photo';


COL owner          FORMAT A6
COL directory_name FORMAT A12
COL directory_path FORMAT A60

-- Query if directory exists.
SELECT   owner
,        directory_name
,        directory_path
FROM     dba_directories
WHERE    directory_name = 'MY_DIRECTORY';
