| External Tables & Trigger [message #154257] |
Fri, 06 January 2006 07:13  |
buck444
Messages: 80
Registered: January 2006
Location: Luxembourg
|
Member |
|
|
Hi,
i would like to copy a external Table into a regular Oracle Table automatically.
Thats why i thought a trigger would be the right choice, but triggers aren't working with external Tables 
(I'm receiving ORA-30657: operation not supported on external organized table
How could i perform this task, that a external table is checked (i.e. date correct...) and if yes, it is copied to a regular table?
|
|
|
|
| Re: External Tables & Trigger [message #154310 is a reply to message #154257] |
Fri, 06 January 2006 12:55  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
You would need a DBMS_JOB to check for the presence/date stamp of the O/S file driving the external table (I'm assuming this is what you mean by "date correct"). You can use UTL_FILE methods for this. The job can also then copy the data to the other table.
|
|
|
|
| Re: External Tables & Trigger [message #154741 is a reply to message #154257] |
Tue, 10 January 2006 06:42 |
buck444
Messages: 80
Registered: January 2006
Location: Luxembourg
|
Member |
|
|
Todd,
thanks for your answer!
So, am i getting you right, that i have to check with the DBMS_JOB Package whether the file is correct?
What i'm doing right now is:
DBMS_JOB copying and renaming a file from Folder A to Folder B with UTL_FILE.
If the File is available in Folder B, the external table loads the data.
Could you get me a hint how i can copy the external table to a regular Table with this scenario?
thank you!
|
|
|
|
| Re: External Tables & Trigger [message #154798 is a reply to message #154741] |
Tue, 10 January 2006 12:24 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Just a simple:
insert into regular_table
select * from external_table;
I'm assuming here that the two tables have identical structures. If not, modify the statement to map the columns as needed.
|
|
|
|
| Re: External Tables & Trigger [message #154803 is a reply to message #154798] |
Tue, 10 January 2006 13:15 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
Or make "regular_table" a materialized view, then it's an even simpler
dbms_mview.refresh('STAGING_MV')
We use this as our standard loading approach and it works pretty well. The MVs are all defined along the lines of
CREATE MATERIALIZED VIEW example_mv
PCTFREE 0 COMPRESS
BUILD DEFERRED
REFRESH ON DEMAND WITH ROWID
AS
SELECT blah1
, blah2
, blah3
FROM example_xt;
We have a generic package procedure to refresh an MV along with logging, analyzing, rebuilding indexes and so on.
|
|
|
|
| Re: External Tables & Trigger [message #154819 is a reply to message #154803] |
Tue, 10 January 2006 14:45 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
The MV will work great if the data in the external_table is supposed to completely replace the data in the regular table. I wasn't sure as the original post said "copy ... into" the regular table.
|
|
|
|
| Re: External Tables & Trigger [message #154877 is a reply to message #154257] |
Wed, 11 January 2006 01:56 |
buck444
Messages: 80
Registered: January 2006
Location: Luxembourg
|
Member |
|
|
Thanks for the answers!
I am trying the idea with MV's right now, because all i need this table for, is to do some data checking (i.e. is it the correct date?).
I don't have to handle a lot of data. My external table has 12 rows at the moment, with other files it will be about 60000 max.
So the performance shouldn't be a problem 
That means, YES the data from the external table has to replace the data in the MV on a daily basis. Then i do some data checking on the MV and afterwards i have to insert the data into different other tables.
I have to read a bit about the MV, because i thought it was a new feature to 10g, but i have to use 9iR2 here in my company.
For example, i would need to know, whether i'm able to use trigger on MV's to do the data checking.
i will see...thank you!
[Updated on: Wed, 11 January 2006 01:57] Report message to a moderator |
|
|
|
| Re: External Tables & Trigger [message #154902 is a reply to message #154257] |
Wed, 11 January 2006 03:43 |
buck444
Messages: 80
Registered: January 2006
Location: Luxembourg
|
Member |
|
|
Hi,
i created a MV based on my external table:
CREATE MATERIALIZED VIEW mv_abc_prices
PCTFREE 0 COMPRESS
REFRESH ON DEMAND WITH ROWID
AS
SELECT
TRADEDATE,
SEDOL_CODE,
ISIN_CODE,
FUNDNAME,
NAV,
SHARES_ISSUED,
MILLRATE,
DAY1_GROSS_YIELD,
DAY30_GROSS_YIELD from ext_tab_abc_prices;
Of course this MV will not update automatically, but that's what i need to do.
The reason is that the underlying table is an external table and that there is no Primary Key available.
I don't want to use another DBMS_JOB for the refresh, because i have already a job running, to create the file on which the external table is based. So, i have no clue how i could manage that these jobs are running always "parallel". (i mean, how could i avoid that the external table is updated right now but the MV will be updated the next day).
But, is there another way to update the MV automatically as soon as the external Table is changing?
I hope you understand what i need to do, if you aren't let me know, i will try to explain it different.
|
|
|
|
| Re: External Tables & Trigger [message #154994 is a reply to message #154902] |
Wed, 11 January 2006 11:28 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
|
I'm not quite clear what should trigger the refresh. Is it when the OS file changes? There is no straightforward way to check that from the database but you should be able to write a procedure using UTL_FILE.FGETATTR etc.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
