Home » RDBMS Server » Server Utilities » External table question
External table question [message #270620] Thu, 27 September 2007 12:02 Go to next message
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 #270631 is a reply to message #270620] Thu, 27 September 2007 12:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
External table is just a staging area, just a view of your file. It is not the target.
The target is your final table.
You have to load the final table using "insert select from external_table".
As for any "select" statement you can add a where clause.

Regards
Michel
Re: External table question [message #270633 is a reply to message #270620] Thu, 27 September 2007 12:44 Go to previous messageGo to next message
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 #270640 is a reply to message #270633] Thu, 27 September 2007 12:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If code tags were useless when you didn't post code, they are mandatory when you post code.

You can use that is you want but if you change the condition next time you have to recreate the table.
Don't use "when" clause and use a "where" clause when you load the target table.

Regards
Michel
Re: External table question [message #270645 is a reply to message #270640] Thu, 27 September 2007 13:29 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member

Sorry about it.
Could you please give me an example if possible?

Thanks,
Re: External table question [message #270655 is a reply to message #270645] Thu, 27 September 2007 13:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
An example of what?
An external is like another table (from a SQL point of view), only its definition is different, just query it with a where clause.

Regards
Michel
Re: External table question [message #270668 is a reply to message #270620] Thu, 27 September 2007 13:59 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You could even create a VIEW on top of the external table which automagically excluded the rows you did not want to see/process.
Re: External table question [message #270706 is a reply to message #270668] Thu, 27 September 2007 16:31 Go to previous messageGo to next message
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 #270772 is a reply to message #270706] Fri, 28 September 2007 00:11 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

http://babudba.blogspot.com/2007/07/external-table.html
Re: External table question [message #270797 is a reply to message #270706] Fri, 28 September 2007 01:12 Go to previous message
Michel Cadot
Messages: 64103
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
Previous Topic: Opera Schema Management Tool for Oracle
Next Topic: Sql Loader log file result in table? (merged)
Goto Forum:
  


Current Time: Sun Dec 04 08:58:03 CST 2016

Total time taken to generate the page: 0.07106 seconds