Re: External Table Question
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_IDMANAGER_ID
----------- -------------------- ------------------------- ---------- ---------- HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_IDEMAIL
--------- ---------- -------------- -------------
360 Jane Janus ST_CLERK 121 17-MAY-01 3000 0 50jjanus
361 Mark Jasper SA_REP 145 17-MAY-01 8000 .1 80mjasper
362 Brenda Starr AD_ASST 200 17-MAY-01 5500 0 10bstarr
EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_IDMANAGER_ID
----------- -------------------- ------------------------- ---------- ---------- HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_IDEMAIL
--------- ---------- -------------- -------------
363 Alex Alda AC_MGR 145 17-MAY-01 9000 .15 80aalda
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_IDMANAGER_ID
----------- -------------------- ------------------------- ---------- ---------- HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_IDEMAIL
--------- ---------- -------------- -------------
401 Jesse Cromwell HR_REP 203 17-MAY-01 7000 0 40jcromwel
402 Abby Applegate IT_PROG 103 17-MAY-01 9000 .2 60aapplega
403 Carol Cousins AD_VP 100 17-MAY-01 27000 .3 90ccousins
EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_IDMANAGER_ID
----------- -------------------- ------------------------- ---------- ---------- HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_IDEMAIL
--------- ---------- -------------- -------------
404 John Richardson AC_ACCOUNT 205 17-MAY-01 5000 0 110jrichard
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