Re: External Table Question

From: <fitzjarrell_at_cox.net>
Date: Mon, 14 Jan 2008 10:51:30 -0800 (PST)
Message-ID: <e382d7fa-f4f4-4035-b68c-654bd182a2a6@f3g2000hsg.googlegroups.com>

On Jan 9, 10:33 am, patrick <pgov..._at_u.washington.edu> wrote:
> On Jan 8, 8:15 pm, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
>
> > I love these external tables.  It really gets rid of the old
> > UTL_FILE.GET_LINE.
>
> > Anyhow, is there a way to create an external table where the filename
> > is dynamic?  Basically the filename changes a couple of times during
> > the process.......
>
> > Not sure if this can be done short of re-creating the table, but I
> > thought I'd ask.....
>
> > Regards & Thanks!
>
> Try     ALTER <table>
>         LOCATION ('<file1>', '<file2>')

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 Received on Mon Jan 14 2008 - 12:51:30 CST