René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Create directory in Oracle

create or replace directory foo_dir as '/tmp';
Directories must be created if external tables are used.
Created directories are shown in either dba_directories or all_directories. There is no user_directories.

Privileges

When a «directory» has been created, the read and write object privileges can be granted on it:
create directory some_dir;
grant read, write on directory some_dir to micky_mouse;

An example

The following example shows how create directory and utl_file can be used to write text into a file:
create or replace directory dir_temp as 'c:\temp';

declare
  f utl_file.file_type;
begin
  f := utl_file.fopen('DIR_TEMP', 'something.txt', 'w');
  utl_file.put_line(f, 'line one: some text');
  utl_file.put_line(f, 'line two: more text');
  utl_file.fclose(f);
end;
/

Links

See also On reading trace files with PL/SQL where a directory is used to read trace files with PL/SQL.