OraFAQ Forum: Server Utilities » External table question

Home » RDBMS Server » Server Utilities » External table question

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

External table question [message #270620]

Thu, 27 September 2007 12:02

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

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: External table question [message #270633 is a reply to message #270620]

Thu, 27 September 2007 12:44

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,

Report message to a moderator

Re: External table question [message #270640 is a reply to message #270633]

Thu, 27 September 2007 12:54

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: External table question [message #270645 is a reply to message #270640]

Thu, 27 September 2007 13:29

poratips
Messages: 345
Registered: April 2005
Location: IL

Senior Member

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

Thanks,

Report message to a moderator

Re: External table question [message #270655 is a reply to message #270645]

Thu, 27 September 2007 13:46

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: External table question [message #270668 is a reply to message #270620]

Thu, 27 September 2007 13:59

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: External table question [message #270706 is a reply to message #270668]

Thu, 27 September 2007 16:31

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")

Report message to a moderator