On Jan 9, 10:33*am, patrick <pgov...@u.washington.edu> wrote:
And if <file1> doesn't exist (as I expect it might not) the table
creates without error but queries return:

select *
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file empxt1.dat in ADMIN_DAT_DIR not found
ORA-06512: at "SYS.ORACLE_LOADER", line 19

which does the OP no good. You are correct, however (and I stand
corrected) that in 10g and later releases one can alter an external
tables file location:

SQL> CONNECT / AS SYSDBA;
Connected.
SQL> -- Set up directories and grant access
SQL> CREATE OR REPLACE DIRECTORY admin_dat_dir
2 AS '/data';

Directory created.

SQL> CREATE OR REPLACE DIRECTORY admin_log_dir
2 AS '/log';

Directory created.

SQL> CREATE OR REPLACE DIRECTORY admin_bad_dir
2 AS '/bad';

Directory created.

SQL> GRANT READ ON DIRECTORY admin_dat_dir TO bing;

Grant succeeded.

SQL> GRANT WRITE ON DIRECTORY admin_log_dir TO bing;

Grant succeeded.

SQL> GRANT WRITE ON DIRECTORY admin_bad_dir TO bing;

Grant succeeded.

SQL> CONNECT bing/*********
Connected.
SQL> -- create the external table
SQL> CREATE TABLE admin_ext_employees
2 (employee_id NUMBER(4),
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(25),
5 job_id VARCHAR2(10),
6 manager_id NUMBER(4),
7 hire_date DATE,
8 salary NUMBER(8,2),
9 commission_pct NUMBER(2,2),
10 department_id NUMBER(4),
11 email VARCHAR2(25)
12 )
13 ORGANIZATION EXTERNAL
14 (
15 TYPE ORACLE_LOADER
16 DEFAULT DIRECTORY admin_dat_dir
17 ACCESS PARAMETERS
18 (
19 records delimited by newline
20 badfile admin_bad_dir:'empxt%a_%p.bad'
21 logfile admin_log_dir:'empxt%a_%p.log'
22 fields terminated by ','
23 missing field values are null
24 ( employee_id, first_name, last_name, job_id,
manager_id,
25 hire_date char date_format date mask "dd-mon-yyyy",
26 salary, commission_pct, department_id, email
27 )
28 )
29 -- LOCATION ('empxt1.dat', 'empxt2.dat')
30 LOCATION ('empxt1.dat')
31 )
32 PARALLEL
33 REJECT LIMIT UNLIMITED;

Table created.

SQL>
SQL> select * from admin_ext_employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID
MANAGER_ID
----------- -------------------- ------------------------- ----------
----------
HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID
EMAIL
--------- ---------- -------------- -------------
-------------------------
360 Jane Janus
ST_CLERK 121
17-MAY-01 3000 0 50
jjanus

361 Mark Jasper
SA_REP 145
17-MAY-01 8000 .1 80
mjasper

362 Brenda Starr
AD_ASST 200
17-MAY-01 5500 0 10
bstarr


EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID
MANAGER_ID
----------- -------------------- ------------------------- ----------
----------
HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID
EMAIL
--------- ---------- -------------- -------------
-------------------------
363 Alex Alda
AC_MGR 145
17-MAY-01 9000 .15 80
aalda








6 rows selected.

SQL>
SQL> alter table admin_ext_employees
2 LOCATION ('empxt2.dat');

Table altered.

SQL>
SQL> select * from admin_ext_employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID
MANAGER_ID
----------- -------------------- ------------------------- ----------
----------
HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID
EMAIL
--------- ---------- -------------- -------------
-------------------------
401 Jesse Cromwell
HR_REP 203
17-MAY-01 7000 0 40
jcromwel

402 Abby Applegate
IT_PROG 103
17-MAY-01 9000 .2 60
aapplega

403 Carol Cousins
AD_VP 100
17-MAY-01 27000 .3 90
ccousins


EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID
MANAGER_ID
----------- -------------------- ------------------------- ----------
----------
HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID
EMAIL
--------- ---------- -------------- -------------
-------------------------
404 John Richardson
AC_ACCOUNT 205
17-MAY-01 5000 0 110
jrichard





SQL> spool off

Which, of course, doesn't make the file name dynamic.


David Fitzjarrell

On Jan 9, 10:33*am, patrick <pgov...@u.washington.edu> wrote:
And if <file1> doesn't exist (as I expect it might not) the table
creates without error but queries return:

select *
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file empxt1.dat in ADMIN_DAT_DIR not found
ORA-06512: at "SYS.ORACLE_LOADER", line 19

which does the OP no good. You are correct, however (and I stand
corrected) that in 10g and later releases one can alter an external
tables file location:

SQL> CONNECT / AS SYSDBA;
Connected.
SQL> -- Set up directories and grant access
SQL> CREATE OR REPLACE DIRECTORY admin_dat_dir
2 AS '/data';

Directory created.

SQL> CREATE OR REPLACE DIRECTORY admin_log_dir
2 AS '/log';

Directory created.

SQL> CREATE OR REPLACE DIRECTORY admin_bad_dir
2 AS '/bad';

Directory created.

SQL> GRANT READ ON DIRECTORY admin_dat_dir TO bing;

Grant succeeded.

SQL> GRANT WRITE ON DIRECTORY admin_log_dir TO bing;

Grant succeeded.

SQL> GRANT WRITE ON DIRECTORY admin_bad_dir TO bing;

Grant succeeded.

SQL> CONNECT bing/*********
Connected.
SQL> -- create the external table
SQL> CREATE TABLE admin_ext_employees
2 (employee_id NUMBER(4),
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(25),
5 job_id VARCHAR2(10),
6 manager_id NUMBER(4),
7 hire_date DATE,
8 salary NUMBER(8,2),
9 commission_pct NUMBER(2,2),
10 department_id NUMBER(4),
11 email VARCHAR2(25)
12 )
13 ORGANIZATION EXTERNAL
14 (
15 TYPE ORACLE_LOADER
16 DEFAULT DIRECTORY admin_dat_dir
17 ACCESS PARAMETERS
18 (
19 records delimited by newline
20 badfile admin_bad_dir:'empxt%a_%p.bad'
21 logfile admin_log_dir:'empxt%a_%p.log'
22 fields terminated by ','
23 missing field values are null
24 ( employee_id, first_name, last_name, job_id,
manager_id,
25 hire_date char date_format date mask "dd-mon-yyyy",
26 salary, commission_pct, department_id, email
27 )
28 )
29 -- LOCATION ('empxt1.dat', 'empxt2.dat')
30 LOCATION ('empxt1.dat')
31 )
32 PARALLEL
33 REJECT LIMIT UNLIMITED;

Table created.

SQL>
SQL> select * from admin_ext_employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID
MANAGER_ID
----------- -------------------- ------------------------- ----------
----------
HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID
EMAIL
--------- ---------- -------------- -------------
-------------------------
360 Jane Janus
ST_CLERK 121
17-MAY-01 3000 0 50
jjanus

361 Mark Jasper
SA_REP 145
17-MAY-01 8000 .1 80
mjasper

362 Brenda Starr
AD_ASST 200
17-MAY-01 5500 0 10
bstarr


EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID
MANAGER_ID
----------- -------------------- ------------------------- ----------
----------
HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID
EMAIL
--------- ---------- -------------- -------------
-------------------------
363 Alex Alda
AC_MGR 145
17-MAY-01 9000 .15 80
aalda








6 rows selected.

SQL>
SQL> alter table admin_ext_employees
2 LOCATION ('empxt2.dat');

Table altered.

SQL>
SQL> select * from admin_ext_employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID
MANAGER_ID
----------- -------------------- ------------------------- ----------
----------
HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID
EMAIL
--------- ---------- -------------- -------------
-------------------------
401 Jesse Cromwell
HR_REP 203
17-MAY-01 7000 0 40
jcromwel

402 Abby Applegate
IT_PROG 103
17-MAY-01 9000 .2 60
aapplega

403 Carol Cousins
AD_VP 100
17-MAY-01 27000 .3 90
ccousins


EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID
MANAGER_ID
----------- -------------------- ------------------------- ----------
----------
HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID
EMAIL
--------- ---------- -------------- -------------
-------------------------
404 John Richardson
AC_ACCOUNT 205
17-MAY-01 5000 0 110
jrichard





SQL> spool off

Which, of course, doesn't make the file name dynamic.


David Fitzjarrell

On Jan 9, 10:33*am, patrick <pgov...@u.washington.edu> wrote:
And if <file1> doesn't exist (as I expect it might not) the table
creates without error but queries return:

select *
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file empxt1.dat in ADMIN_DAT_DIR not found
ORA-06512: at "SYS.ORACLE_LOADER", line 19

which does the OP no good. You are correct, however (and I stand
corrected) that in 10g and later releases one can alter an external
tables file location:

SQL> CONNECT / AS SYSDBA;
Connected.
SQL> -- Set up directories and grant access
SQL> CREATE OR REPLACE DIRECTORY admin_dat_dir
2 AS '/data';

Directory created.

SQL> CREATE OR REPLACE DIRECTORY admin_log_dir
2 AS '/log';

Directory created.

SQL> CREATE OR REPLACE DIRECTORY admin_bad_dir
2 AS '/bad';

Directory created.

SQL> GRANT READ ON DIRECTORY admin_dat_dir TO bing;

Grant succeeded.

SQL> GRANT WRITE ON DIRECTORY admin_log_dir TO bing;

Grant succeeded.

SQL> GRANT WRITE ON DIRECTORY admin_bad_dir TO bing;

Grant succeeded.

SQL> CONNECT bing/*********
Connected.
SQL> -- create the external table
SQL> CREATE TABLE admin_ext_employees
2 (employee_id NUMBER(4),
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(25),
5 job_id VARCHAR2(10),
6 manager_id NUMBER(4),
7 hire_date DATE,
8 salary NUMBER(8,2),
9 commission_pct NUMBER(2,2),
10 department_id NUMBER(4),
11 email VARCHAR2(25)
12 )
13 ORGANIZATION EXTERNAL
14 (
15 TYPE ORACLE_LOADER
16 DEFAULT DIRECTORY admin_dat_dir
17 ACCESS PARAMETERS
18 (
19 records delimited by newline
20 badfile admin_bad_dir:'empxt%a_%p.bad'
21 logfile admin_log_dir:'empxt%a_%p.log'
22 fields terminated by ','
23 missing field values are null
24 ( employee_id, first_name, last_name, job_id,
manager_id,
25 hire_date char date_format date mask "dd-mon-yyyy",
26 salary, commission_pct, department_id, email
27 )
28 )
29 -- LOCATION ('empxt1.dat', 'empxt2.dat')
30 LOCATION ('empxt1.dat')
31 )
32 PARALLEL
33 REJECT LIMIT UNLIMITED;

Table created.

SQL>
SQL> select * from admin_ext_employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID
MANAGER_ID
----------- -------------------- ------------------------- ----------
----------
HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID
EMAIL
--------- ---------- -------------- -------------
-------------------------
360 Jane Janus
ST_CLERK 121
17-MAY-01 3000 0 50
jjanus

361 Mark Jasper
SA_REP 145
17-MAY-01 8000 .1 80
mjasper

362 Brenda Starr
AD_ASST 200
17-MAY-01 5500 0 10
bstarr


EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID
MANAGER_ID
----------- -------------------- ------------------------- ----------
----------
HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID
EMAIL
--------- ---------- -------------- -------------
-------------------------
363 Alex Alda
AC_MGR 145
17-MAY-01 9000 .15 80
aalda








6 rows selected.

SQL>
SQL> alter table admin_ext_employees
2 LOCATION ('empxt2.dat');

Table altered.

SQL>
SQL> select * from admin_ext_employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID
MANAGER_ID
----------- -------------------- ------------------------- ----------
----------
HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID
EMAIL
--------- ---------- -------------- -------------
-------------------------
401 Jesse Cromwell
HR_REP 203
17-MAY-01 7000 0 40
jcromwel

402 Abby Applegate
IT_PROG 103
17-MAY-01 9000 .2 60
aapplega

403 Carol Cousins
AD_VP 100
17-MAY-01 27000 .3 90
ccousins


EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID
MANAGER_ID
----------- -------------------- ------------------------- ----------
----------
HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID
EMAIL
--------- ---------- -------------- -------------
-------------------------
404 John Richardson
AC_ACCOUNT 205
17-MAY-01 5000 0 110
jrichard





SQL> spool off

Which, of course, doesn't make the file name dynamic.


David Fitzjarrell