External table question [message #270620] |
Thu, 27 September 2007 12:02 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
poratips
Messages: 345 Registered: April 2005 Location: IL
|
Senior Member |
|
|
Hi,
I have already created external table and i am loading data from external table to another table.
Now I want to restrict the data when its loads (refresh) in external table.
Ex.
DEPT_Ext table is loading/refreshing when new file coming every day so currently we loading all the dept.
Now we wants to restrcit (not to load) dept called sales.
Howw can I restrict/avoid that dept when new file coming which has that dept.
I know that in sql*loader, I can use WHEN (DEPT != 'SALES')
but i don't know how to use in external table and if it is then where exactly in ext. definition. (If you have example which will be better)
Thanks,
[Edit: remove useless code tag]
[Updated on: Thu, 27 September 2007 12:41] by Moderator Report message to a moderator
|
|
|
|
Re: External table question [message #270633 is a reply to message #270620] |
Thu, 27 September 2007 12:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
poratips
Messages: 345 Registered: April 2005 Location: IL
|
Senior Member |
|
|
I think I got it. Please correct me if it's wrong.
I created ext table through sql*loader using EXTERNAL_TABLE=GENERATE_ONLY and it gives me where exactly i have to use it.
CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"
(
DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY MY_FILES
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
LOAD WHEN (DEPTNO != "10")
........
.........
location
(
'Gen_ext_tb_from_sqlLdr_ulcase1A.ctl'
)
)REJECT LIMIT UNLIMITED;
Thanks,
|
|
|
|
|
|
|
Re: External table question [message #270706 is a reply to message #270668] |
Thu, 27 September 2007 16:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
poratips
Messages: 345 Registered: April 2005 Location: IL
|
Senior Member |
|
|
Thanks.
I was talking about in external table script not while using.
See following which i have used.
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
LOAD WHEN (DEPTNO != "10")
|
|
|
|
Re: External table question [message #270797 is a reply to message #270706] |
Fri, 28 September 2007 01:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68678 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
And I answered:
Quote: | Don't use "when" clause [in table definition] and use a "where" clause when you load the target table.
|
Regards
Michel
|
|
|