CODEWITHSUNDEEP
oracle-databaseoracle11g
VictorGram
VictorGram

Reputation: 2661

ORACLE 11g: How to query for the location in external file?

If I create a table like this in Oracle:

SQL> CREATE TABLE emp_load
  2    (employee_number      CHAR(5),
  3     employee_dob         CHAR(20),
  4     employee_last_name   CHAR(20),
  5     employee_first_name  CHAR(15),
  6     employee_middle_name CHAR(15),
  7     employee_hire_date   DATE)
  8  ORGANIZATION EXTERNAL
  9    (TYPE ORACLE_LOADER
 10     DEFAULT DIRECTORY def_dir1
 11     ACCESS PARAMETERS
 12       (RECORDS DELIMITED BY NEWLINE
 13        FIELDS (employee_number      CHAR(2),
 14                employee_dob         CHAR(20),
 15                employee_last_name   CHAR(18),
 16                employee_first_name  CHAR(11),
 17                employee_middle_name CHAR(11),
 18                employee_hire_date   CHAR(10) date_format DATE mask "mm/dd/yyyy"
 19               )
 20       )
 21     LOCATION ('info.dat')
 22    );

 Table created.

I need to query and update the file used in the part

LOCATION('info.dat')

I am trying to get the file name as :

select LOCATION from <something>.emp_load;

getting error as:

Error at line 1
ORA-00904: "LOCATION": invalid identifier

What is the right way to make this query?

Thanks in advance

Upvotes: 0

Views: 1249

Answers (2)

VictorGram
VictorGram

Reputation: 2661

the way it worked :

select * from all_external_locations where table_name='<table name>' and owner='<owner>';

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191265

You can get the operating system path for the file from the all_directories view:

select directory_path
from all_directories
where directory_name = 'DEF_DIR1';

Your file will be in there.

If you want to find the directory for a table, and other information about it, you can get that from the all_external_tables view.

If you want to find the directory and the location you can get that from the all_external_locations view:

select directory_name, location
from all_external_locations
where table_name = 'EMP_LOAD';

You may need to specify the owner for any of those queries if you have duplicates in different schemas; or if you own the table then query the user_* views instead.

Upvotes: 3

Related Questions